An HTTP API in the database

This recipe serves a small JSON API for a task list directly from SurrealDB. Each route is a DEFINE API statement, so the routes, the input checks and the queries they run are all stored in the database, and a client calls them over HTTP like any other web service.

It is based on three tutorials that build the same kind of API with a web framework in front of SurrealDB: Simple API with Gin/Gonic and SurrealDB by Atharva Deshpande in Go, How to use SurrealDB with the Fresh framework and Deno by Rajdeep Singh, and How to build a Rust backend with Actix Web and SurrealDB. Use one of those when the API needs a framework's features, such as templating or calls to other services.

Schema

The task table has PERMISSIONS NONE, so a client that is not a system user cannot query it directly. The API bodies still read and write it, because an API body runs without table permission checks by default.

DEFINE TABLE task SCHEMAFULL PERMISSIONS NONE;
DEFINE FIELD title ON task TYPE string ASSERT $value.len() > 0;
DEFINE FIELD done ON task TYPE bool DEFAULT false;
DEFINE FIELD created ON task TYPE datetime VALUE time::now() READONLY;

DEFINE FUNCTION fn::task_json($task: record<task>) -> object {
	$task.{ id: record::id(id), title, done }
};

fn::task_json gives every route the same response shape. It returns the key of the record ID without the table name, such as "yb6i1rge67pkqmbc369x", because that is the part a client puts in /tasks/:id.

JSON in and out

An API response body reaches an HTTP client as bytes or a string, so the database needs to know how to serialise the objects that the routes return. DEFINE CONFIG API sets middleware for every endpoint of the database:

DEFINE CONFIG API MIDDLEWARE api::res::body("json");

The request side is set for each method instead, with api::req::body("json") on the methods that receive a body. A GET request has no body, and the JSON parser would refuse it.

The routes

DEFINE API "/tasks"
	FOR get THEN {
		{
			status: 200,
			body: (SELECT VALUE id FROM task ORDER BY created).map(|$t| fn::task_json($t)),
		}
	}
	FOR post MIDDLEWARE api::req::body("json") THEN {
		LET $title = $request.body.title;
		IF !$title.is_string() OR $title.len() = 0 {
			RETURN { status: 400, body: { error: 'A task needs a title' } };
		};
		LET $task = CREATE ONLY task CONTENT { title: $title };
		{ status: 201, body: fn::task_json($task.id) }
	};

DEFINE API "/tasks/:id"
	FOR get THEN {
		LET $task = type::record('task', $request.params.id);
		IF $task.* = NONE { RETURN { status: 404, body: { error: 'No such task' } } };
		{ status: 200, body: fn::task_json($task) }
	}
	FOR patch MIDDLEWARE api::req::body("json") THEN {
		LET $done = $request.body.done;
		IF !$done.is_bool() {
			RETURN { status: 400, body: { error: 'Send done as true or false' } };
		};
		LET $task = UPDATE ONLY type::record('task', $request.params.id) MERGE { done: $done };
		IF $task = NONE { RETURN { status: 404, body: { error: 'No such task' } } };
		{ status: 200, body: fn::task_json($task.id) }
	}
	FOR delete THEN {
		DELETE type::record('task', $request.params.id);
		{ status: 204 }
	};

A few details:

• •

  $request.params.id holds the :id segment of the path, and type::record('task', …) turns it into a record ID.

• •

  The input checks return 400 with a message. Without them, a missing title would fail the ASSERT on the field, and a handler that fails answers with a 500 and no explanation.

• •

  UPDATE ONLY on a record that does not exist returns NONE and creates nothing, which the PATCH route turns into a 404.

Call the API

Every endpoint is served at /api/<namespace>/<database>/<path>. With a server started by surreal start --user root --pass secret and the statements above run in the namespace app and the database app:

curl -X POST http://localhost:8000/api/app/app/tasks \
	-H 'Content-Type: application/json' \
	-d '{"title":"Write the release notes"}'

{"done":false,"id":"yb6i1rge67pkqmbc369x","title":"Write the release notes"}

The other routes answer in the same way:

Inside the database, api::invoke calls a route without HTTP, which is useful in tests.

Taking it further

• •

  Give each user their own tasks. Add an owner field set from $auth, filter on it in each route, and allow only signed-in callers with PERMISSIONS WHERE $auth != NONE.

• •

  Accept only API calls. Turn off arbitrary queries for record users with the capabilities of the server, so that the routes are the only way in.

• •

  Add middleware. api::timeout and your own middleware functions run before a route, for limits, headers or checks that every route shares.