Serve a REST API from a function backed by SQL Database

Deploy one function that serves every endpoint of a REST API, reads SQL Database through a replica, and caches the list response.

Last updated

View as Markdown Agent setup

You deploy one function that serves every endpoint of a REST API under /api/tasks with the Azion CLI: it reads the rows from SQL Database through a read replica, writes them through the Azion API, and keeps the list response in cache for 60 seconds. To add writes to a function you already run, refer to Write rows to SQL Database from a function.

Prerequisites

  • SQL Database enabled on your account, and the Edit SQL Database permission. The product is in Preview, so request access through Technical Support.
  • A database named tasks-api, its identifier, and the tasks table with two rows. The identifier is the id the create response returns. To create them, refer to Create a database using the API and Create tables and query data.
  • The database identifier and a personal token stored as the environment variables TASKS_DB_ID and TASKS_SQL_TOKEN, as Store the database identifier and the token shows. A variable reaches the function only after a deploy, so create both before the deploy.
  • Azion CLI installed, with your personal token saved. To set it up, refer to Azion CLI quickstart.
  • Node.js and a package manager, which the CLI uses to build the project.

The function reads a table created with CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, completed INTEGER NOT NULL DEFAULT 0);, holding the rows INSERT INTO tasks (title, completed) VALUES ('Write the API spec', 0); and INSERT INTO tasks (title, completed) VALUES ('Deploy the API', 1); add. completed is an integer column, 0 or 1, because the function reads it as a number. The examples call the API on api.example.com. Replace it with the domain the deploy prints, and the names with your own.

Write the API function

The function is one ES Modules handler that implements every endpoint under /api/tasks. It reads with the Database class of the Azion.Sql global, sends each write to the query endpoint of the Azion API with TASKS_DB_ID and TASKS_SQL_TOKEN, and stores the list response in the cache tasks-api under max-age=60. Every POST and DELETE deletes that stored list after its write.

To create the project and add the function:

Create the project

Run azion init, enter tasks-api as the name, and select the Javascript preset and the Hello World template, as Deploy a function with Azion CLI shows. Then go to the project directory.

Replace the handler

Replace the contents of index.js, the entrypoint the build reads, with the code below.

JavaScript
01const { Database } = Azion.Sql;
02
03const DATABASE_NAME = 'tasks-api';
04const CACHE_NAME = 'tasks-api';
05const LIST_MAX_AGE = 60;
06
07function json(body, status = 200, headers = {}) {
08 return new Response(JSON.stringify(body), {
09 status,
10 headers: { 'content-type': 'application/json', ...headers },
11 });
12}
13
14// Reads come from a read replica. Integer parameters bind; string parameters are refused.
15async function readTasks(id) {
16 const connection = await Database.open(DATABASE_NAME);
17 const rows = id === undefined
18 ? await connection.query('SELECT id, title, completed FROM tasks ORDER BY id')
19 : await connection.query('SELECT id, title, completed FROM tasks WHERE id = ?', [id]);
20 const tasks = [];
21 let row = await rows.next();
22 while (row) {
23 tasks.push({ id: row.getValue(0), title: row.getValue(1), completed: row.getValue(2) === 1 });
24 row = await rows.next();
25 }
26 return tasks;
27}
28
29// Writes go to the main instance through the Azion API. A failed statement still answers 200.
30async function writeTasks(statement) {
31 const response = await fetch(
32 `https://api.azion.com/v4/workspace/sql/databases/${Azion.env.get('TASKS_DB_ID')}/query`,
33 {
34 method: 'POST',
35 headers: {
36 Accept: 'application/json',
37 Authorization: `Token ${Azion.env.get('TASKS_SQL_TOKEN')}`,
38 'Content-Type': 'application/json',
39 },
40 body: JSON.stringify({ statements: [statement] }),
41 },
42 );
43 if (!response.ok) {
44 throw new Error(`Azion API answered ${response.status}`);
45 }
46 const entry = (await response.json()).data[0];
47 if (entry.error) {
48 throw new Error(entry.error);
49 }
50 return entry.results;
51}
52
53// The query endpoint takes SQL strings, so a text value is quoted and its quotes doubled.
54function sqlText(value) {
55 return `'${String(value).replaceAll("'", "''")}'`;
56}
57
58export default {
59 async fetch(request) {
60 const url = new URL(request.url);
61 const match = url.pathname.match(/^\/api\/tasks(?:\/(\d+))?$/);
62 if (!match) {
63 return json({ error: 'Not found' }, 404);
64 }
65 const id = match[1] === undefined ? undefined : Number(match[1]);
66 const cache = await caches.open(CACHE_NAME);
67 const listKey = `${url.origin}/api/tasks`;
68
69 try {
70 if (request.method === 'GET' && id === undefined) {
71 const cached = await cache.match(listKey);
72 if (cached) {
73 return cached;
74 }
75 const list = json(await readTasks(), 200, {
76 'cache-control': `max-age=${LIST_MAX_AGE}`,
77 'x-tasks-cached-at': new Date().toISOString(),
78 });
79 await cache.put(listKey, list.clone());
80 return list;
81 }
82 if (request.method === 'GET') {
83 const [task] = await readTasks(id);
84 return task ? json(task) : json({ error: 'Task not found' }, 404);
85 }
86 if (request.method === 'POST' && id === undefined) {
87 const { title, completed = false } = await request.json();
88 if (typeof title !== 'string' || title.length === 0) {
89 return json({ error: 'title is required' }, 400);
90 }
91 const results = await writeTasks(
92 `INSERT INTO tasks (title, completed) VALUES (${sqlText(title)}, ${completed ? 1 : 0}) RETURNING id`,
93 );
94 await cache.delete(listKey);
95 return json({ id: results.rows[0][0], title, completed: Boolean(completed) }, 201);
96 }
97 if (request.method === 'DELETE' && id !== undefined) {
98 const results = await writeTasks(`DELETE FROM tasks WHERE id = ${id}`);
99 await cache.delete(listKey);
100 return results.rows_written > 0
101 ? json({ message: 'Task deleted' })
102 : json({ error: 'Task not found' }, 404);
103 }
104 return json({ error: 'Method not allowed' }, 405);
105 } catch (error) {
106 console.log(error.message);
107 return json({ error: 'Internal error' }, 500);
108 }
109 },
110};

The project’s index.js now holds the API function. A path outside /api/tasks answers 404, a method the function does not handle answers 405, and a failed write answers 500 and logs its message.

The Build REST and GraphQL APIs use case uses the values of this example.

Deploy the API

One function serves every endpoint, so one deploy ships them all together. To deploy the API, run azion deploy from the project directory. The command builds the project, creates the application and the function, instantiates the function, and prints the domain that serves it. For the output of the command, refer to Deploy the project.

The API answers on the domain the deploy printed, every endpoint runs in the function, and the list response is cached for 60 seconds. The first deploy can take several minutes to answer from every location.

The Build REST and GraphQL APIs use case uses the values of this example.

Confirm the API answers

Each check calls the API on its domain. A first deploy that does not answer yet is still propagating; wait a few minutes and retry.

  • The list endpoint reads the database. Request the list:

    curl -i https://api.example.com/api/tasks

    The response carries 200 and the two rows the table holds:

    [{"id":1,"title":"Write the API spec","completed":false},{"id":2,"title":"Deploy the API","completed":true}]

  • The list comes from the cached copy. Repeat the request within 60 seconds. The x-tasks-cached-at header carries the same value as in the first response.

  • A write reaches the database. Create a task:

    Shell
    01curl -i -X POST https://api.example.com/api/tasks \
    02 -H "Content-Type: application/json" \
    03 -d '{"title": "Review the logs"}'

    The response carries 201 and the ID the database assigned:

    {"id":3,"title":"Review the logs","completed":false}

    A 500 here means the write failed. Read the message the function logged, as Query function console logs shows: Azion API answered 401 points at the TASKS_SQL_TOKEN value, and a database error string points at the statement.

  • A write refreshes the list. Request the list again. The response carries a new x-tasks-cached-at value and three tasks.

  • A missing task answers 404. Delete the same task twice:

    curl -X DELETE https://api.example.com/api/tasks/3

    The first call answers {"message":"Task deleted"}, and the second answers 404 with {"error":"Task not found"}.

The API reads, writes, and deletes tasks, and serves the list from its cached copy between writes.

These checks confirm the Build REST and GraphQL APIs use case.

Next steps

Cache a function's response with the Cache APIHow the key, the max-age, and the delete after a write keep the list response fresh.Build REST and GraphQL APIsThe design this function implements, with the measurements that show it works.