Read and write SQL Database from a Next.js app

Render a Next.js page from SQL Database rows and add rows from a route handler, in an application that Azion deploys from GitHub.

Last updated

View as Markdown Agent setup

You render a page of a Next.js App Router project from the rows of a SQL Database table, and add rows from a route handler, in an application that Azion builds from your GitHub repository. Both files run in the function the build deploys. To write rows from a function you write yourself, refer to Write rows to SQL Database from a function.

The page reads through a read replica, which refuses a statement that writes with attempt to write a readonly database. The route handler therefore writes through the Azion API, with the database identifier and a personal token it reads from environment variables.

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 and a table for the app to read. The examples use a database named portal-app whose projects table is created with CREATE TABLE IF NOT EXISTS projects (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL); and holds one row, Customer portal. To create them, refer to Create a database using the API, Create a table using the API, and Insert rows.
  • The database identifier and a personal token stored as the environment variables PORTAL_DB_ID and PORTAL_SQL_TOKEN, as Store the database identifier and the token shows. A variable reaches the function only after the next deploy, so create both before the repository is imported.
  • A GitHub repository with a Next.js project at its root, on a version Azion supports, imported with the Next.js preset. For the versions, refer to Next.js versions. To import the repository, refer to Import a project from GitHub.

The examples use app.example.com for the application’s domain. Replace it, and the names above, with your own values.

Render a page from the database rows

The page and the route handler both export dynamic = 'force-dynamic', so Next.js renders them on each request instead of once at build time, when the database is out of reach.

Add the page as app/page.js. It reads the rows through a read replica, with the Database class of the Azion.Sql global:

JavaScript
01export const dynamic = 'force-dynamic';
02
03async function readProjects() {
04 const { Database } = globalThis.Azion.Sql;
05 const connection = await Database.open('portal-app');
06 const rows = await connection.query('SELECT id, name FROM projects ORDER BY id');
07 const projects = [];
08 let row = await rows.next();
09 while (row) {
10 projects.push({ id: row.getValue(0), name: row.getValue(1) });
11 row = await rows.next();
12 }
13 return projects;
14}
15
16export default async function Page() {
17 const projects = await readProjects();
18 return (
19 <main>
20 <h1>Projects</h1>
21 <ul>
22 {projects.map((project) => (
23 <li key={project.id}>{project.name}</li>
24 ))}
25 </ul>
26 </main>
27 );
28}

Database.open takes the database name and no token. Once the file is deployed, the page lists the rows of projects on every request.

The Deploy full-stack applications globally use case uses the values of this example.

Add rows from a route handler

Add the route handler as app/api/projects/route.js. It sends the insert as Write rows to SQL Database from a function describes, with PORTAL_DB_ID and PORTAL_SQL_TOKEN, and answers 500 on a failed request or a failed statement:

JavaScript
01export const dynamic = 'force-dynamic';
02
03function json(body, status) {
04 return new Response(JSON.stringify(body), {
05 status,
06 headers: { 'content-type': 'application/json' },
07 });
08}
09
10// The query endpoint takes SQL strings, so a text value is quoted and its quotes doubled.
11function sqlText(value) {
12 return `'${String(value).replaceAll("'", "''")}'`;
13}
14
15export async function POST(request) {
16 const { name } = await request.json();
17 if (typeof name !== 'string' || name.length === 0) {
18 return json({ error: 'name is required' }, 400);
19 }
20 const env = globalThis.Azion.env;
21 const response = await fetch(
22 `https://api.azion.com/v4/workspace/sql/databases/${env.get('PORTAL_DB_ID')}/query`,
23 {
24 method: 'POST',
25 headers: {
26 Accept: 'application/json',
27 Authorization: `Token ${env.get('PORTAL_SQL_TOKEN')}`,
28 'Content-Type': 'application/json',
29 },
30 body: JSON.stringify({
31 statements: [`INSERT INTO projects (name) VALUES (${sqlText(name)}) RETURNING id`],
32 }),
33 },
34 );
35 if (!response.ok) {
36 console.log(`Azion API answered ${response.status}`);
37 return json({ error: 'Internal error' }, 500);
38 }
39 const entry = (await response.json()).data[0];
40 if (entry.error) {
41 console.log(entry.error);
42 return json({ error: 'Internal error' }, 500);
43 }
44 return json({ id: entry.results.rows[0][0], name }, 201);
45}

Commit both files to the repository’s default branch. The page lists the projects of portal-app on every request, and POST /api/projects adds one.

The Deploy full-stack applications globally use case uses the values of this example.

Confirm the page reads and the route writes

Each check requests the application on its domain. A location that does not have the application yet answers a 404 page that reads There's nothing here yet; wait a few minutes and retry.

  • The page renders from the database. Request the home page:

    curl -s https://app.example.com/

    The HTML carries the row the table holds, in the list the page renders:

    <h1>Projects</h1><ul><li>Customer portal</li></ul>

  • Build assets come from the bucket. Copy a URL under /_next/static/ from the page’s HTML and request it:

    curl -sI https://app.example.com/_next/static/<path-from-the-page>

    The response carries 200, without the request reaching the function.

  • A route writes to the database. Create a project:

    Shell
    01curl -i -X POST https://app.example.com/api/projects \
    02 -H "Content-Type: application/json" \
    03 -d '{"name": "Billing dashboard"}'

    The response carries 201 and the ID the database assigned:

    {"id":2,"name":"Billing dashboard"}

    A 500 means the write failed. Read the line the route logged, as Troubleshoot function execution and logs shows: Azion API answered 401 points at the PORTAL_SQL_TOKEN value, and a database error string points at the statement.

  • The page shows the write. Request the home page again. The list carries both projects.

  • A push deploys. Change the <h1> text, commit, and push to the default branch. Request the home page after the deploy propagates. The HTML carries the new heading.

The page reads the table through a read replica, and the route handler writes to it through the Azion API.

These checks confirm the Deploy full-stack applications globally use case.

Next steps

Write rows to SQL Database from a functionThe query endpoint, the two places a write fails, and how to read the rows back.Deploy full-stack applications globallyThe design this app belongs to: build assets in a bucket, pages in a function, data in SQL Database.