Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Turn Sqlite into a public, read-only, JSON API
<?php
function json_response(int $code = 200, array $data = null)
{
// clear the old headers
header_remove();
// set the actual code
http_response_code($code);
// set the header to make sure cache is forced
header('Cache-Control: public, max-age=300');
// treat this as json
header('Content-Type: application/json');
// allow CORS
header('Access-Control-Allow-Origin: *');
$status = [
200 => '200 OK',
204 => '204 No Content',
400 => '400 Bad Request',
405 => '405 Method Not Allowed',
422 => '422 Unprocessable Entity',
500 => '500 Internal Server Error',
];
// set the status header
header('Status: ' . $status[$code]);
// return the encoded json
return json_encode($data,
JSON_NUMERIC_CHECK
|JSON_UNESCAPED_UNICODE
|JSON_UNESCAPED_SLASHES
|JSON_PARTIAL_OUTPUT_ON_ERROR
|JSON_INVALID_UTF8_IGNORE
|JSON_THROW_ON_ERROR);
}
$method = strtoupper($_SERVER['REQUEST_METHOD']);
if ($method !== 'GET' && $method !== 'POST') {
echo json_response(405, [
'error' => "method {$method} is not supported",
'meta' => [],
]);
return;
}
$query = $_GET['query'] ?? '';
if ($method === 'POST') {
// if you are doing ajax with application-json headers
$query = empty($_POST) ? json_decode(
(string)file_get_contents('php://input'),
true)['query'] : $_POST['query'] ?? '';
}
if (empty($query)) {
$wording = [
'GET' => 'query string',
'POST' => 'parameter',
];
echo json_response(400, [
'error' => 'must provide a "query" ' . $wording[$method],
'meta' => [],
]);
return;
}
// Create (connect to) SQLite database in file
// http://www.sqlitetutorial.net/sqlite-sample-database/
$db = new SQLite3('chinook.sqlite', SQLITE3_OPEN_READONLY);
// throw real exceptions
$db->enableExceptions(true);
try {
$rows = $db->query($query);
$results = [
'data' => [],
'meta' => [
'query' => $query,
'total' => 0,
]
];
while ($row = $rows->fetchArray(SQLITE3_ASSOC)) {
$results['data'][] = $row;
}
$results['meta']['total'] = count($results['data']);
$code = empty($results) ? 404 : 200;
echo json_response($code, $results);
} catch (Exception $e) {
echo json_response(500, [
'message' => $e->getMessage(),
'trace' => $e->getTrace(),
'meta' => [],
]);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment