Build a database-backed service

Prompt:

Build a small notes service on Turn Zero Cloud that keeps its notes in a database.

Also works: "Put this API online with a Postgres database behind it." "Our backend needs somewhere to store its records."

This page builds the smallest complete service that uses the platform's database: one table, three routes, and a health path that reads the database. One line writes the whole service into your project and tests it. The steps then show each file whole, in the folder it belongs in, and say what the file is for and how to change it.

Add a database describes each of the database's own actions in full. Read this page first for the whole service, then that one for the details of each action. To add a database to an application you already have, start with that page: the line on this page refuses unless app/ is empty or does not exist.

What your tool does

• Runs one line of the turnzero-cloud command at the project's root. The line does the next four things itself.
• Lays out the project with the application folder, app/, as the zip's root, and the library folder, system/, beside it.
• Takes the Database package into system/ and copies its compiled code into app/lib/database/, a copy that app/package.json declares (Use the library).
• Writes the manifest declaring {"kind": "database"}, one migration, the data layer with its live pool and its migration walk, the server with its health route, the process entry, and a test file.
• Runs npm install in app/, so the artifact includes its lockfile, and then runs the tests.
• Changes the files for your own service: the table, the routes, and their tests.
• Submits the manifest with submit_manifest. The pool reads its maximum from APP_DATABASE_CONNECTION_LIMIT, the connection_limit the response returns.
• Tests the data layer with the Database package's test double after each change and before the first local run, as Test your application locally describes.
• Runs the service on your machine first, as Run locally describes, and requests its health path.
• Zips the contents of app/ and deploys it, straight to production on a new application, then requests the routes that only read, as Deploy an application describes. Where you turned development on, it deploys there, requests each route, and promotes on your yes.

What you need

• Node.js 24 or later, and npm, on your computer (What your computer needs).
• The plan the application starts on, free, standard, or pro.

Before your AI starts

This section is for your AI tool: what it checks and gathers before it begins. You don't need to do these steps yourself.

• A connected, signed-in tool (Connect your tool).
• A project folder whose app/ folder is empty or does not exist, because the line in step 1 refuses otherwise. Beside app/, the line writes system/, replacing a Database entry already taken there with the library's copy, and a one-sentence AGENTS.md at the root only where none exists.
• The application, created with create_application, and its identifier, before step 3 submits the manifest. The line in step 1 needs no account.
• In Windows PowerShell, each npm command and each npx line, which step 1 explains, run as Windows PowerShell describes.

Steps

These steps are the sequence the deploy skill, skill:deploy, names for an application whose manifest declares the database kind.

The line in step 1 writes every file the samples below show, and the samples make one service. A test checks them: it holds each sample equal to the file the line writes, runs the code over the Database package's test double, and validates the manifest.

In short, one line per step, each linking its step:

1. Lay out the project: one line writes app/, the zip's root, and system/ beside it, installs, and runs the tests.
2. Take the Database package: the line takes it and copies its compiled code into app/lib/database/.
3. Declare the database: the manifest declares {"kind": "database"}, and submit_manifest records it.
4. Keep the schema as migrations: one Structured Query Language (SQL) file, app/migrations/0001_notes.sql, and a new file for each later change.
5. Extend the data layer: a pool sized by APP_DATABASE_CONNECTION_LIMIT, the migration walk, and the queries your routes call.
6. Serve the health path over the connection: GET /health reads the database and returns 503 while the read fails.
7. Start the process: the entry runs the migrations, then listens on PORT.
8. Test it: the line runs the service's tests once, and you run them again after each change.
9. Run it and deploy it: run it on your machine, then deploy it, straight to production on a new application.

1. Lay out the project

Run this line at the project's root, the folder that will contain app/ and system/:

npx -y https://turnzero.ai/packages/turnzero-cloud-0.8.0.tgz scaffold --template database-service

Where your tool reaches the platform at an address other than https://turnzero.ai, write that address in the line and add --origin with it, as a line you write yourself describes.

npx comes with Node.js. It fetches the turnzero-cloud command from the address in the line and runs it, so you install nothing. In Windows PowerShell where running scripts is disabled, run the line with npx.cmd in place of npx, as Windows PowerShell describes.

For a resource other than notes, add --resource with its plural, such as --resource orders. Add --singular too where the singular is not the plural without its closing s, such as --resource people --singular person. The line then writes the same service under those names, in its table, its routes, its functions, and two file names, and runs its tests on the files as written.

The line needs no sign-in and asks no question. It works in this order:

1. It reads the Database package from the platform's library and checks every file against its hash. It writes nothing until every file is checked.
2. It writes the service's nine files into app/.
3. It writes the package into system/ and copies the package's compiled code into app/lib/database/, as step 2 describes.
4. It runs npm install and then npm test in app/.

The line prints a written: line for each of the service's files and for the root's AGENTS.md where it writes one, and a taken: line and a copied: line for the package. Where the tests pass, it prints a tested: line and then its numbered next steps: create_application, submit_manifest, the local run, and deploy. For a new project they are four. Step 3 of this page covers the manifest, and step 9 the local run and the deploy.

The line starts a new application and never writes over one. Where app/ already contains any file or folder, it refuses and writes nothing. Its last line then gives the library take line, which takes the Database package for an application you already have. Taking a library entry describes that line, and Add a database the database's own actions for such an application.

Where the install fails or the run stops before its end, the last printed line says how to continue. Where the tests fail, they failed on the files as written, before any edit of yours, and the printed line asks you to report it. Starting a new service describes each ending and the line's options.

To write into another folder, name the project's root with --path, and the command makes that folder where it does not exist. On Windows, a path written into the line contains none of the characters &, |, <, >, ^, and %: where your folder's path contains one, run the line from that folder without --path. Write a path that contains a space between double quotes, with no backslash before the closing quote, because on Windows the command refuses a path that still contains a double quote.

To build the service by hand instead, write each sample on this page at the path the tree below gives it. Then take the package as step 2 describes, and run npm install and npm test in app/.

The project keeps two folders side by side. app/ is the application and the zip's root, the folder that contains package.json. system/ contains the library entries as the line took them, and never enters the zip.

notes-service/
  system/                      the library entries as taken, never zipped
    manifest.json
    features/database/
  app/                         the zip's root
    package.json
    package-lock.json
    manifest.json
    .gitignore
    AGENTS.md
    migrations/0001_notes.sql
    src/db.mjs
    src/server.mjs
    src/main.mjs
    test/notes.test.mjs
    lib/database/              the package's package.json and lib/, copied from system/

The zip contains the contents of app/, not the folder itself. It leaves out node_modules and the .env file the provision line writes for a local run.

app/.gitignore keeps node_modules and .env files out of Git. It changes nothing in a deploy, because the zip reads no ignore file.

node_modules/
.env
.env.*

app/AGENTS.md tells an AI coding tool that the service runs on Turn Zero Cloud and where the platform serves its rules. It repeats none of them, so it stays correct as the platform changes. The zip includes it, and the platform does not read it.

# This service runs on Turn Zero Cloud

The service in this folder is hosted on Turn Zero Cloud. It started from the platform's `database-service` template: a Node.js service that keeps its data in the platform's database.

## Where the rules are

Turn Zero Cloud serves its rules to a coding tool through its MCP server, `turnzero-cloud`, and they change with the platform, so none is repeated here.

- Where the server is not connected, connect it first. The platform's page "Connect your tool" says how.
- Before you change or deploy this service, read the server's `deploy` skill.

## What you run here

- The tests: `npm test`, in this folder.
- A local run and a deploy: the `deploy` skill gives each step and the line to run, in this order: `submit_manifest`, the local run, `deploy`.

A tool started at the project's root reads the root's AGENTS.md first. So the line writes an AGENTS.md of one sentence at the root, only where it has none: "The service in app/ runs on Turn Zero Cloud: read app/AGENTS.md before you work on it."

Where the root already has an AGENTS.md, the line leaves it as it is and prints the sentence, so add that sentence to it. Where the project has its own CLAUDE.md, add that sentence to it too, because Claude Code then reads no AGENTS.md on its own. Where the line left either file as it was, its next steps begin with a step for each, giving the sentence to add.

2. Take the Database package

The line in step 1 takes the package into system/features/database/ and records its row in system/manifest.json. It checks each file against the hash the library lists for it before it writes any. It then copies the entry's package.json and lib/ into app/lib/database/. app/package.json declares that copy as a path, file:lib/database.

To take the package without step 1's line, or to add another package later, your tool follows the add-library-entry skill. A tool whose client lists no prompts reads the skill with read_context, id skill:add-library-entry. The skill finds the entry with list_library and runs the line that read_library_entry returns for it, at the project's root. That line makes the same copy, keeps the declaration app/package.json already holds, and runs npm install in app/. Use the library describes the line.

The platform itself needs only the manifest's services entry declaring the database kind, and your code the APP_DATABASE_URL setting (environment variable) the platform injects; any PostgreSQL driver serves. A manifest holding no library entry still carries packages, as []. This guide takes the Database package for its failover pool, its boot walk, and its test double; Add a database shows the driver-only path.

app/package.json:

{
  "name": "notes-service",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node src/main.mjs",
    "test": "node --test"
  },
  "dependencies": {
    "@turnzero/database": "file:lib/database",
    "pg": "^8.22.0"
  },
  "devDependencies": {
    "@electric-sql/pglite": "0.5.8"
  }
}

The line runs npm install in app/. The install links the copy into node_modules and writes package-lock.json. The platform's image build runs npm install --omit=dev over the two files, then npm start, so the start script is the entry point. The test script and the development dependency serve step 8, and the image build leaves the development dependency out.

To use another npm package, add it under dependencies and run npm install in app/ again, so the lockfile records it. Leave the start script and the @turnzero/database line as they are.

3. Declare the database

app/manifest.json declares the database kind. The line fills its packages list with the name and version of each entry in the library folder, read from system/manifest.json. In a new project that is the Database package's row alone, so the version is the one the line took. A manifest written by hand copies the same two values.

{
  "manifest_version": 1,
  "services": [{ "kind": "database" }],
  "health": "/health",
  "region": "usa",
  "egress": [],
  "audience": { "kind": "public" },
  "packages": [{ "name": "database", "version": "0.10.2" }]
}

Your tool submits the whole document with submit_manifest. The platform creates the development database and its role, and the response includes connection_limit, the pool's maximum for the application's plan. Through the Model Context Protocol (MCP) tool, the response withholds the credentials, which the provision line writes for a local run. Add a database describes the response.

Change health only together with the route in src/server.mjs, because the path in the manifest must be the path the server routes, character for character. After any change to the manifest, submit the whole document again. Author the manifest describes each member.

4. Keep the schema as migrations

app/migrations/0001_notes.sql creates the one table. The number in the file's name sets its order among later files.

-- migrations/0001_notes.sql
CREATE TABLE "notes" (
  id bigserial PRIMARY KEY,
  title text NOT NULL CHECK (title <> '')
);

For your own service, change this file to your own table before the service first runs against a database, in a local run or a deploy. The test file in step 8 names the notes table's columns and this file's name, so change it too. Where the development database already ran the file, edit it only as Add a database says, by clearing that database first.

Once production has started a version carrying a file, a failed deploy included, never edit or rename the file. Make a later change as a new file, such as 0002_add_tags.sql. Add a database says how to write a migration that the running version keeps working with.

5. Extend the data layer

app/src/db.mjs contains three functions:

• openLivePool builds the live pool from APP_DATABASE_URL, and fails with the setting's name when it is missing. It sizes the pool from APP_DATABASE_CONNECTION_LIMIT, the plan's connection_limit, and at one connection where that setting is absent. Only the process entry calls it.
• migrate runs the migrations through the package's bootWalk, on one checked-out connection. It retries a lost connection every second for up to 120 seconds, and any other failure ends the start.
• createDataLayer takes any pool with the pg.Pool shape, so a test can hand it the test double's pool.

The live pool is the Database package's failover pool, built with createFailoverPool over a pg pool. It limits each connect to five seconds, and it listens for the sessions a database failover ends, so the process keeps serving.

// src/db.mjs
import { readdirSync, readFileSync } from 'node:fs';
import { join } from 'node:path';
import { fileURLToPath } from 'node:url';
import pg from 'pg';
import { bootWalk, createFailoverPool } from '@turnzero/database';

const MIGRATIONS = fileURLToPath(new URL('../migrations/', import.meta.url));
const HEALTH_READ_MS = 2_000;

// The live pool, which only the process entry opens: the package's failover
// pool over a pg pool. Its maximum is the plan's connection_limit, which the
// platform injects and the turnzero-cloud command's provision writes; one
// connection where the setting is absent.
export function openLivePool() {
  const url = process.env.APP_DATABASE_URL;
  if (!url) throw new Error('APP_DATABASE_URL is not set');
  const limit = process.env.APP_DATABASE_CONNECTION_LIMIT ?? '';
  const max = /^[1-9][0-9]*$/.test(limit) ? Number(limit) : 1;
  return createFailoverPool({
    // settings is { max, connectionTimeoutMillis }: the maximum and the five-second connect limit.
    connect: (settings) => new pg.Pool({ connectionString: url, ...settings }),
    max,
    onConnectionLoss: (error) => console.warn('database session ended:', error.message),
  });
}

// The migrations through the package's boot walk: a lost connection retried
// every second for up to 120 seconds, and any other failure ends the start.
export function migrate(pool) {
  const files = { list: () => readdirSync(MIGRATIONS), read: (name) => readFileSync(join(MIGRATIONS, name), 'utf8') };
  return bootWalk(pool, files, {
    onRetry: (error, attempt) => {
      if (attempt === 1) console.warn('database unreachable at start; retrying:', error.message);
    },
  });
}

// The data layer over any pool with the pg.Pool shape: the live one, or a test double's.
export function createDataLayer(pool) {
  return {
    // One read over the connection, bounded so a probe never waits on a lost database.
    async reachable() {
      let timer;
      const late = new Promise((resolve) => { timer = setTimeout(resolve, HEALTH_READ_MS, 'late'); });
      try {
        return (await Promise.race([pool.query('SELECT 1'), late])) !== 'late';
      } catch {
        return false;
      } finally {
        clearTimeout(timer);
      }
    },
    async addNote(title) {
      const { rows } = await pool.query('INSERT INTO "notes" (title) VALUES ($1) RETURNING id, title', [title]);
      return rows[0];
    },
    async listNotes() {
      const { rows } = await pool.query('SELECT id, title FROM "notes" ORDER BY id');
      return rows;
    },
  };
}

For your own service, replace addNote and listNotes with the reads and writes your routes need, each a query on pool. Leave openLivePool, migrate, and reachable as they are: the first two follow the platform's rules for the connection, and the health route calls the third.

The Database package's integration guide, integration.md in system/features/database/, explains the pool's listeners and the connection loss the walk retries.

6. Serve the health path over the connection

app/src/server.mjs serves three routes. GET /health performs one read over the connection, limited to two seconds, and returns 503 while that read fails. A pass at the deploy's health check then shows that the connection setting reaches the database, not only that the process responds.

Serve the health path from the moment the server listens: 503 until the application is ready, never a 4xx, then 200. The notes routes return 400 for a note with no title. The health check gives the check's figures.

For your own service, add your routes beside the notes routes or in their place. Leave the /health route and the line that reads request.signal as they are.

// src/server.mjs
import { createServer } from 'node:http';

const readBody = async (request) => {
  // One decoder for the whole body: a character whose bytes arrive in two chunks is read whole.
  request.setEncoding('utf8');
  let text = '';
  for await (const chunk of request) text += chunk;
  return text;
};

export function createAppServer(db) {
  return createServer(async (request, response) => {
    // The deployed runtime's abort signal: end the response when it fires.
    request.signal?.addEventListener('abort', () => response.destroy(), { once: true });
    const send = (status, body) => {
      if (response.destroyed) return;
      response.writeHead(status, { 'content-type': 'application/json' });
      response.end(JSON.stringify(body));
    };
    try {
      // A target that does not parse as a URL names no route, so it is answered 404 below.
      let pathname = null;
      try {
        pathname = new URL(request.url, 'http://localhost').pathname;
      } catch {}
      if (request.method === 'GET' && pathname === '/health') {
        return (await db.reachable()) ? send(200, { ok: true }) : send(503, { ok: false });
      }
      if (request.method === 'GET' && pathname === '/notes') return send(200, await db.listNotes());
      if (request.method === 'POST' && pathname === '/notes') {
        const posted = JSON.parse(await readBody(request));
        return send(201, await db.addNote(String(posted?.title ?? '')));
      }
      send(404, { error: 'not_found' });
    } catch (error) {
      const refused = error instanceof SyntaxError || error?.code === '23514';
      if (!refused) console.error(error);
      send(refused ? 400 : 500, { error: refused ? 'invalid_note' : 'internal' });
    }
  });
}

7. Start the process

app/src/main.mjs is the process entry the start script runs. It opens the live pool, runs the migrations, and only then listens on PORT. The server waits to listen until its migrations finish, and until it listens, the health check treats it as starting.

// src/main.mjs, the process entry the start script runs
import { createDataLayer, migrate, openLivePool } from './db.mjs';
import { createAppServer } from './server.mjs';

const pool = openLivePool();
const applied = await migrate(pool);
console.log(applied.length > 0 ? `migrations applied at this start: ${applied.join(', ')}` : 'migrations: none pending at this start');
const port = Number(process.env.PORT ?? 8080);
createAppServer(createDataLayer(pool)).listen(port, () => console.log(`listening on ${port}`));

The pool's maximum comes from APP_DATABASE_CONNECTION_LIMIT, the connection_limit the platform returned, and is never set higher. The connection limit says why. A deploy, a promote, and a restart_application inject the setting, and the provision line writes it into the environment file, so a plan move reaches the pool with no code edit. A deployed copy receives PORT as 8080. A local run's environment file sets no port, so the entry falls back to the same one.

migrate returns the files this start applied. The first start against a database prints their names, such as migrations applied at this start: 0001_notes.sql. Every later start prints migrations: none pending at this start, because each file is applied once and recorded. A restart and a copy waking from its idle stop are later starts. A local run uses the development database, so it can apply a file before the first deploy to development does.

8. Test it

The line in step 1 runs the service's tests once, on the files as it wrote them, and prints its tested: line where they pass. Run them again after each change, and before the first local run.

The tests run over the Database package's test double, which runs a PostgreSQL engine inside the test process with your migrations applied. Test your application locally shows the double, what to install, and a sample test.

app/test/notes.test.mjs tests the migration, the data layer, the health route, a title with characters outside ASCII read back through GET /notes, and the refusal of an empty title. It builds one test double for the file and empties its tables between tests. The engine is @electric-sql/pglite, the development dependency in app/package.json.

// test/notes.test.mjs
import assert from 'node:assert/strict';
import { after, before, beforeEach, test } from 'node:test';
import { fileURLToPath } from 'node:url';
import { PGlite } from '@electric-sql/pglite';
import { createDatabaseDouble, migrationsFrom } from '@turnzero/database/testing';
import { createDataLayer } from '../src/db.mjs';
import { createAppServer } from '../src/server.mjs';

const MIGRATIONS = fileURLToPath(new URL('../migrations/', import.meta.url));

// One test double for the file: a PostgreSQL engine inside this process, with
// the migrations applied, and the server over it on a port of this machine.
let double;
let db;
let server;
let base;

before(async () => {
  double = await createDatabaseDouble({ engine: new PGlite(), files: migrationsFrom(MIGRATIONS) });
  db = createDataLayer(double.pool);
  server = createAppServer(db);
  await new Promise((resolve) => server.listen(0, '127.0.0.1', resolve));
  base = `http://127.0.0.1:${server.address().port}`;
});

// Every table emptied between tests, so each test starts from empty tables.
beforeEach(() => double.reset());

after(async () => {
  await new Promise((resolve) => server.close(resolve));
  await double.close();
});

test('the migration applies', () => {
  assert.deepEqual(double.applied, ['0001_notes.sql']);
});

test('addNote adds a row and listNotes lists it', async () => {
  assert.deepEqual(await db.addNote('first'), { id: '1', title: 'first' });
  assert.deepEqual(await db.listNotes(), [{ id: '1', title: 'first' }]);
});

test('GET /health reads the database and returns 200', async () => {
  const response = await fetch(`${base}/health`);
  assert.equal(response.status, 200);
  assert.deepEqual(await response.json(), { ok: true });
});

test('POST /notes keeps a title with characters outside ASCII, and GET /notes returns it', async () => {
  const title = 'café ☕ 東京';
  const response = await fetch(`${base}/notes`, {
    method: 'POST',
    headers: { 'content-type': 'application/json' },
    body: JSON.stringify({ title }),
  });
  assert.equal(response.status, 201);
  assert.equal((await response.json()).title, title);
  const listed = await fetch(`${base}/notes`);
  assert.equal(listed.status, 200);
  assert.deepEqual((await listed.json()).map((row) => row.title), [title]);
});

test('POST /notes refuses an empty title, and GET /notes returns no row', async () => {
  const response = await fetch(`${base}/notes`, {
    method: 'POST',
    headers: { 'content-type': 'application/json' },
    body: JSON.stringify({ title: '' }),
  });
  assert.equal(response.status, 400);
  assert.deepEqual(await response.json(), { error: 'invalid_note' });
  assert.deepEqual(await (await fetch(`${base}/notes`)).json(), []);
});

Run npm test in app/. Its test script runs node --test. When you change a route, the data layer, or the schema, change its test in the same edit. With Node's built-in runner, run node --test alone, or name the test files by a quoted pattern such as node --test "test/**/*.test.mjs", not a folder such as test/.

9. Run it and deploy it

Run the service on your machine first, under the environment file the provision line writes: node --env-file=.env src/main.mjs in app/. Request /health, then add a note and read it back. The note lands in the development database, which local runs use, never in production's. Run locally gives the settings and the line.

The next steps the line in step 1 prints run the same service through the command's provision and run lines. Your tool calls submit_manifest with the application, the manifest, and local_run: true. In app/, it runs the line the response returns as provisioning.command, or as provisioning.command_windows on Windows, which writes .env. The printed run line then starts the service with those settings, as node --env-file=.env src/main.mjs does, both in app/. Running on your machine describes both lines.

Then deploy from app/: your tool calls deploy with the application and runs, in app/, the line the response returns, as Deploy an application describes. On a new application the deploy goes straight to production. Once its state is deployed, request the routes that only read, GET /health and GET /notes, on the production hostname, <label>.ai.host, where <label> is the label create_application returned. Leave POST /notes to the local run, because a write on production changes production's data.

Where you turned development on, the deploy goes to development instead. Request each route on the development hostname, <label>-dev.ai.host. To add a note from a shell such as bash:

curl -i -H 'Content-Type: application/json' --data '{"title":"first"}' 'https://<label>-dev.ai.host/notes'

In Windows PowerShell, write the body to a file without a byte-order mark and send it with curl.exe, as Prerequisites explains:

[System.IO.File]::WriteAllText("$PWD\body.json", '{"title":"first"}')
curl.exe -i -H 'Content-Type: application/json' --data-binary '@body.json' 'https://<label>-dev.ai.host/notes'

Or send it with Invoke-RestMethod, which prints the note that comes back:

Invoke-RestMethod -Method Post -ContentType 'application/json' -Body (@{title='first'} | ConvertTo-Json -Compress) -Uri 'https://<label>-dev.ai.host/notes'

Then list the notes with curl -i 'https://<label>-dev.ai.host/notes' in bash, or Invoke-RestMethod -Uri 'https://<label>-dev.ai.host/notes' in Windows PowerShell, and the note is listed.

Promote on your yes, then request the routes that only read on the production hostname.

Expected result

The line in step 1 ends after the service's five tests pass and it has printed its next steps. read_status shows production deployed, with its database row, and the development database your local runs use. GET /health returns 200 with {"ok":true} on your machine and on the production hostname. On your machine, POST /notes with {"title":"first"} returns 201 with the note, and GET /notes lists it. On production, GET /notes returns 200 with [], since production's database contains no note yet. Each database records 0001_notes.sql in schema_migrations.

Refusals

The table covers the refusals and outcomes this service's first release most often meets. Add a database and Deploy an application list the rest.

Related

• Add a database describes the database's actions in full: the declaration, the connection, the migrations, the connection limit, failover, and the production credential.
• The turnzero-cloud command describes the line in step 1: its options, its order, and how it ends.
• Use the library describes the line that takes a package and copies its compiled code, for another package or for an application you already have.
• Deploy an application covers the artifact, the deploy, the health check, and the promote.
• Test your application locally runs a data layer's tests over the package's test double.
• Deploys starts from what a slow or failed deploy shows.