--- name: azion-build-a-restful-tasks-api-with-functions-and-sql-database description: >- Create a database in SQL Database, write CRUD routes with Hono, and deploy a task API that runs as a function. --- # Build a RESTful tasks API with Functions and SQL Database In this tutorial, you will build a RESTful tasks API that runs as a function and keeps its records in [SQL Database](/en/documentation/platform/sql-database/). You will create the database and its table, write the data layer and the [Hono](https://hono.dev/docs/) routes, deploy the project with Azion CLI, and call every endpoint. The finished project is the [restful-tasks example](https://github.com/egermano/edge-functions-examples/tree/main/packages/restful-tasks) on GitHub. --- ## Prerequisites - An Azion account. To create one, refer to [How to create an account on Azion](/en/documentation/fundamentals/creating-account/). - Azion CLI installed. Refer to [Azion CLI](/en/documentation/devtools/cli/). - A personal token for the API requests. To create one, refer to [Personal tokens](/en/documentation/guides/platform/account-and-billing/personal-tokens/). - Node.js version 18 or higher. --- ## 1. Create the database The data layer addresses the database by name, and it falls back to `tasks` when no name is configured. Name the database `tasks` so that fallback resolves. Send a `POST` request to the databases endpoint, replacing `[TOKEN VALUE]` with your personal token: ```bash curl --location 'https://api.azion.com/v4/workspace/sql/databases' \ --header 'Authorization: Token [TOKEN VALUE]' \ --header 'Content-Type: application/json' \ --data '{ "name": "tasks" }' ``` The response carries the identifier of the database and its status: ```json { "state": "pending", "data": { "id": 118, "name": "tasks", "client_id": "6832h", "status": "creating", "created_at": "2024-04-18T11:22:59.468536Z", "updated_at": "2024-04-18T11:22:59.468586Z", "deleted_at": null } } ``` Record the `id`. Every request that follows addresses the database by that value. > **Note** > > Creation is not instantaneous. Send `GET` requests to the same endpoint until `status` reads `created`. For the full set of database operations, refer to [How to manage an SQL Database database](/en/documentation/guides/application-development/data/manage-sql-database/). --- ## 2. Create the tasks table The API stores one row per task, with a title, a completion flag, and two timestamps. Both requests send a `POST` to the query endpoint. Replace `` with the `id` of your database: 1. **Create the table** ```bash curl --location 'https://api.azion.com/v4/workspace/sql/databases//query' \ --header 'Authorization: Token [TOKEN VALUE]' \ --header 'Content-Type: application/json' \ --data '{ "statements": [ "CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, completed BOOLEAN DEFAULT FALSE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP);" ] }' ``` The response reports one result object per statement: ```json { "state": "executed", "data": [ { "results": { "columns": [], "rows": [] } } ] } ``` 2. **Insert three rows** The endpoints need rows to return: ```bash curl --location 'https://api.azion.com/v4/workspace/sql/databases//query' \ --header 'Authorization: Token [TOKEN VALUE]' \ --header 'Content-Type: application/json' \ --data '{ "statements": [ "INSERT INTO tasks (title, completed) VALUES ('\''Complete API documentation'\'', FALSE);", "INSERT INTO tasks (title, completed) VALUES ('\''Test function deployment'\'', FALSE);", "INSERT INTO tasks (title, completed) VALUES ('\''Review security configurations'\'', TRUE);" ] }' ``` Three statements return three result objects: ```json { "state": "executed", "data": [ { "results": { "columns": [], "rows": [] } }, { "results": { "columns": [], "rows": [] } }, { "results": { "columns": [], "rows": [] } } ] } ``` The database holds the `tasks` table and three rows. --- ## 3. Create the project Azion CLI scaffolds the project from a Hono template. 1. **Authenticate the CLI** Run the login command and follow the prompts: ```bash azion login ``` The CLI stores the credentials locally and authorizes every later command against your account. 2. **Start the project** Run the init command: ```bash azion init ``` 3. **Name the project** Accept the suggested name, or enter your own: ```sh ? Your application's name: (black-thor) ``` 4. **Select the Hono preset** ```sh ? Choose a preset: [Use arrows to move, type to filter] Angular Astro Docusaurus Eleventy Emscripten Gatsby Hexo > Hono Hugo Javascript ... ``` 5. **Select the Hono Boilerplate template** 6. **Answer N to the local development server** ```sh ? Do you want to start a local development server? (y/N) ``` 7. **Answer N to the deploy prompt** ```sh ? Do you want to deploy your project? (y/N) ``` 8. **Go to the project directory** ```bash cd ``` The CLI creates the project directory with the template source. The `build`, `dev`, and `deploy` commands run from inside it. --- ## 4. Write the data layer The data layer calls `useQuery` and `useExecute` from the `azion/sql` library. Both take the database name first and an array of SQL statements second. For the full library surface, refer to [Azion SQL library](/en/documentation/devtools/azion-lib/sql/). Replace the contents of `src/db.ts`: ```typescript import { useExecute, useQuery } from "azion/sql"; const DATABASE_NAME = Azion.env.get("DATABASE_NAME") || "tasks"; export interface Task { id: number; title: string; completed: boolean; } export const getTasks = async (): Promise => { const { data, error } = await useQuery(DATABASE_NAME, [ "SELECT * FROM tasks", ]); if (error) { throw error; } return ( data?.results?.[0]?.rows?.map( (row) => ({ id: row[0], title: row[1], completed: row[2] === 1, } as Task) ) || [] ); }; export const getTask = async (id: number): Promise => { const { data, error } = await useQuery(DATABASE_NAME, [ `SELECT * FROM tasks WHERE id = ${id}`, ]); if (error) { throw error; } return ( (data?.results?.[0]?.rows?.[0] && ({ id: data?.results?.[0]?.rows?.[0][0], title: data?.results?.[0]?.rows?.[0][1], completed: data?.results?.[0]?.rows?.[0][2] === 1, } as Task)) || null ); }; export const createTask = async (task: Omit): Promise => { const { data, error } = await useExecute(DATABASE_NAME, [ `INSERT INTO tasks (title, completed) VALUES ('${task.title}', ${task.completed}) RETURNING id`, ]); if (error) { throw error; } const newId = data?.results?.[0]?.rows?.[0]?.[0] || ""; return { ...task, id: Number(newId) }; }; export const updateTask = async ( id: number, task: Partial> ): Promise => { const existingTask = await getTask(id); if (!existingTask) { return null; } const updatedTitle = task.title !== undefined ? task.title : existingTask.title; const updatedCompleted = task.completed !== undefined ? task.completed : existingTask.completed; const { data, error } = await useExecute(DATABASE_NAME, [ `UPDATE tasks SET title = '${updatedTitle}', completed = ${updatedCompleted} WHERE id = ${id}`, ]); if (error) { throw error; } return { ...existingTask, title: updatedTitle, completed: updatedCompleted }; }; export const deleteTask = async (id: number): Promise => { const { data, error } = await useExecute(DATABASE_NAME, [ `DELETE FROM tasks WHERE id = ${id}`, ]); if (error) { throw error; } return data?.state === "executed" || data?.state === "pending"; }; ``` The file exports one function per operation: `getTasks`, `getTask`, `createTask`, `updateTask`, and `deleteTask`. `DATABASE_NAME` falls back to `tasks`. To read a database under another name, set `DATABASE_NAME` in the `.env` file of the project. > **Caution** > > Each statement is built by string interpolation, and `title` arrives in the request body. A title that carries a quote changes the statement the database runs. Validate every value from the request before a statement uses it. --- ## 5. Write the API routes Hono binds each HTTP method and path to a handler. Every handler wraps its data call in a `try` block and returns `500` with a JSON error when the call throws. Replace the contents of `src/app.ts`: ```typescript import { Hono } from 'hono'; import { createTask, deleteTask, getTask, getTasks, updateTask, } from './db'; const app = new Hono(); app.get('/tasks', async (c) => { try { const tasks = await getTasks(); return c.json(tasks); } catch (error) { return c.json({ error: 'Failed to fetch tasks' }, 500); } }); app.get('/tasks/:id', async (c) => { try { const { id } = c.req.param(); const task = await getTask(Number(id)); if (!task) { return c.json({ error: 'Task not found' }, 404); } return c.json(task); } catch (error) { return c.json({ error: 'Failed to fetch task' }, 500); } }); app.post('/tasks', async (c) => { try { const { title, completed } = await c.req.json(); const newTask = await createTask({ title, completed: completed || false }); return c.json(newTask, 201); } catch (error) { console.log(error); return c.json({ error: 'Failed to create task' }, 500); } }); app.put('/tasks/:id', async (c) => { try { const { id } = c.req.param(); const { title, completed } = await c.req.json(); const updatedTask = await updateTask(Number(id), { title, completed }); if (!updatedTask) { return c.json({ error: 'Task not found' }, 404); } return c.json(updatedTask); } catch (error) { return c.json({ error: 'Failed to update task' }, 500); } }); app.delete('/tasks/:id', async (c) => { try { const { id } = c.req.param(); const success = await deleteTask(Number(id)); if (!success) { return c.json({ error: 'Task not found' }, 404); } return c.json({ message: 'Task deleted' }); } catch (error) { return c.json({ error: 'Failed to delete task' }, 500); } }); export default app; ``` The file covers five routes: | Method and path | Result | | ------------------- | ----------------------------------------------------------------------- | | `GET /tasks` | Returns every task as a JSON array. | | `GET /tasks/:id` | Returns one task, or `404` with `{ "error": "Task not found" }`. | | `POST /tasks` | Creates a task from `title` and `completed`, and returns it with `201`. | | `PUT /tasks/:id` | Updates `title` and `completed`, and returns the stored task. | | `DELETE /tasks/:id` | Removes the task and returns `{ "message": "Task deleted" }`. | --- ## 6. Export the function handler Azion runs the ES Modules handler: an object with a `fetch` method, exported as the default of the entry file. Hono supplies that method on the app instance. For the pattern and the legacy alternative it replaces, refer to [Migrate handler patterns in Functions](/en/documentation/guides/application-development/functions-and-runtime/migrate-handler-patterns/). Replace the contents of `src/index.ts`: ```typescript import app from './app'; export default app; ``` The entry file exports the app, so every request reaches the Hono router, which matches it against the five routes. --- ## 7. Deploy the project Run the deploy command from the project directory: ```bash azion deploy ``` Azion opens Azion Console in the browser, where the deployment logs run until the build finishes. If the browser does not open, follow the link the CLI prints. Azion builds the project and deploys it to the Azion Web Platform. The deployment returns a workload domain in the format `https://xxxxxxx.map.azionedge.net`. Propagation takes a few minutes, so wait before you send the first request. --- ## 8. Verify the endpoints Replace `` with the workload domain of the deployment. 1. **Request the task list** ```bash curl https:///tasks ``` The response carries the three rows inserted into the table: ```json [ { "id": 1, "title": "Complete API documentation", "completed": false }, { "id": 2, "title": "Test function deployment", "completed": false }, { "id": 3, "title": "Review security configurations", "completed": true } ] ``` 2. **Request one task** ```bash curl https:///tasks/1 ``` The response carries a single object: ```json { "id": 1, "title": "Complete API documentation", "completed": false } ``` 3. **Create a task** ```bash curl -X POST https:///tasks \ -H "Content-Type: application/json" \ -d '{"title": "Write the deployment checklist", "completed": false}' ``` The response returns `201` with the stored task and the identifier the database assigned: ```json { "title": "Write the deployment checklist", "completed": false, "id": 4 } ``` 4. **Update the task** ```bash curl -X PUT https:///tasks/4 \ -H "Content-Type: application/json" \ -d '{"title": "Write the deployment checklist", "completed": true}' ``` The response carries the task with the new values: ```json { "id": 4, "title": "Write the deployment checklist", "completed": true } ``` 5. **Delete the task** ```bash curl -X DELETE https:///tasks/4 ``` The response confirms the removal: ```json { "message": "Task deleted" } ``` The API creates, reads, updates, and deletes tasks from the deployed function, and every write reaches the database. --- ## Next steps - [SQL Database](/en/documentation/platform/sql-database.md): The ACID-compliant database behind the API, its architecture, and its limits. - [Functions](/en/documentation/platform/functions.md): What a function is, how it is invoked, and what it can reach during execution. - [Migrate handler patterns in Functions](/en/documentation/guides/application-development/functions-and-runtime/migrate-handler-patterns.md): The ES Modules handler, the Service Worker handler, and the patterns Azion rejects. - [Azion SQL library](/en/documentation/devtools/azion-lib/sql.md): Every method of azion/sql, with its parameters and return type. - [Local development](/en/documentation/devtools/cli/dev-command.md): Run the project on your own machine with the azion dev command. - [Troubleshoot function execution and logs](/en/documentation/platform/functions/troubleshooting.md): Find the cause when the function never runs, stops early, or writes no logs.