Add a database #

View this page as Markdown

On this page

Add PostgreSQL to an existing app, connect your code, and manage changes to its tables. Read this page when your app needs to save records, or when you need to test or troubleshoot its database connection.

Prompt:

Add a database to this app.

Also works:

  • "We need somewhere to keep the orders and the customers."

Add PostgreSQL to an existing application by declaring {"kind": "database"} in its manifest and connecting through APP_DATABASE_URL. For a new service, start with Build a database-backed service.

Read Before your AI starts, then follow Steps to declare the database, connect your code, rotate a production credential when requested, keep schema changes as migrations and develop locally. Read your data covers read-only queries. Durability and backups, Expected result and Refusals explain persistence, successful responses and remedies.

Your AI tool can use the Database package for its failover pool, migrations, and test double. Taking it requires this computer's token, a minted token, or a signed-in MCP connection. Develop locally explains the command route; a tool without a shell can read the package through its signed-in connection with tzcloud_read_library.

To use the pg driver alone, follow Without the Database package, which gives a whole server with no scaffold or package to take. For an existing CommonJS application, use CommonJS and Express and the CommonJS migration helper. This avoids taking a Turn Zero package; declaring the database and deploying the application still require Turn Zero credentials.

For testing without the CLI, see Add a database, step 5 (Develop locally). It covers offline tests, taking the package without an installed command, and testing through tools without a shell.

Turn Zero Cloud provisions the database and credentials; your application manages its tables and migrations. The migration examples keep schema_migrations: the Database package creates and updates it through applyMigrations, while the pg and CommonJS helpers maintain it without the package. The platform does not maintain that table for your app. If your app already has a migration tool, keep using it and its own migration history.

For documents, uploaded images, and other files, use Storage. The Database and Storage pages explain when to use each.

Durability and backups explains what survives and how a restore works. Start after idle and response time explains the wait when a stopped environment starts again.

What your AI tool does #

  • Reads the add-service skill (read_context, id skill:add-service) and follows it: it declares the database in the manifest, submits it, and follows the steps below.
  • Copies the Database package into the project's library folder, system/, as Use the library describes, and adds pg beside it under dependencies in package.json. The deploy's image build installs those with npm install --omit=dev and then runs npm start (What the deploy zip holds). The package is optional: the platform needs only the declaration and the setting, and Without the Database package shows a server on pg alone. The npm registry carries no @turnzero package, so npm view @turnzero/database finds none: the package reaches a project from the library alone, by the library take command.
  • Writes the data module, the migration files, and the start code as step 2 (Connect to it) shows, and runs the application's tests over the package's test double. The package's migration function, applyMigrations, applies the files, so the project needs no migration runner of its own.
  • Rotates the production credential or clears the development database only when you ask, and gives you the approval link the clear needs.
  • Asks for no separate browser approval for tzcloud_submit_manifest, tzcloud_store_secret with replace: true, tzcloud_set_app_size, tzcloud_restart_application, tzcloud_deploy, or tzcloud_promote. Your AI still follows the work you authorized.

Before your AI starts #

For the AI tool: Check the following before starting.

  • A connected, signed-in AI tool (Connect your AI assistant).
  • The application's identifier from tzcloud_list_applications, and its manifest (Author the manifest).
  • Each process may hold at most 10 client connections on either size. Read the pool limit as connection_limit in tzcloud_read_status.

Steps #

1. Declare it #

Add {"kind": "database"} to the manifest's services array and call tzcloud_submit_manifest with the whole manifest. Declare the database shows that whole manifest, every required field included. For each environment, the platform creates one PostgreSQL database and one database role, the database user your code signs in as:

  • The development database, on a development server, is created when you submit. Your local runs and a deployed development environment use it.
  • The production database, on a production server, is created at the first deploy on an application with one environment, or the first promote on one with two. The manifest must still declare the database then.

Every application with a database gets a development database, even one with a single environment, so local test data never lands in production's. It makes no difference whether you call tzcloud_create_environment before or after this submission: the submission creates the development database and its owner role either way.

The response gives the development database's connection details as development_database and production's pool maximum as connection_limit. The response's note gives development's pool maximum. For this local setup, turnzero provision writes the development credentials to a .env file; step 5 (Develop locally) says how your AI tool runs it.

Only the platform holds the production credential. The first deploy or promote that reaches production creates it. The platform composes production's APP_DATABASE_URL and APP_DATABASE_DIRECT_URL from it; neither setting's value is shown to you. Declaring the service again does not reset a database that already exists.

The shared database is a monthly item, charged once for the application, production and development together. When added, it is charged for the days left in the calendar month. The charge continues while the server sleeps and stops only when the application is deleted, with nothing refunded. A database already in place at the switch is first charged on the next month's first day. Read the rate on the pricing page or with read_rate_card.

2. Connect to it #

APP_DATABASE_URL is a PostgreSQL connection string on port 6432, through a pooler that assigns a server connection for one transaction at a time. APP_DATABASE_DIRECT_URL names the server's direct port, 5432, for work that needs one server session. Both carry sslmode=verify-full, which the pg driver reads as full certificate checking, so neither needs an ssl option.

A deploy selects the pooled address. A promote, rollback, restart or platform redeploy preserves the address recorded for that version; a version deployed with the direct address keeps it. For a local run, turnzero provision writes the pooled address under APP_DATABASE_URL and the direct address under APP_DATABASE_DIRECT_URL in .env.

Take the host name only from these settings, because the server's name can change at a promote or when the platform moves the database. If the platform cannot read the stored credential, the deploy or promote is refused database_credential_unreadable instead of starting without the settings.

A deploy that carries the Database package needs version 0.14.0 or later; an older copy is refused database_package_outdated.

The platform needs only the declaration and the setting, and any PostgreSQL driver works. The sample below uses the Database package for three things: its failover pool, its code that applies the migrations at start, and its test double. It is the data module that Build a database-backed service writes, and it works unchanged in an application you already have. A test checks that each of its three files matches that service's file, and runs the module over the package's test double. For a server on the pg driver alone, see Without the Database package.

src/db.mjs opens the pool from the two settings, runs the migrations, and holds the queries:

// src/db.mjs
import { fileURLToPath } from 'node:url';
import pg from 'pg';
import { bootWalk, createFailoverPool, migrationsFrom } 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. APP_DATABASE_CONNECTION_LIMIT sets its maximum.
// The platform supplies it; the turnzero command's provision writes it for
// a local run. The pool uses 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) {
  return bootWalk(pool, migrationsFrom(MIGRATIONS), {
    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;
    },
  };
}

migrations/0001_notes.sql creates the table the queries use:

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

src/main.mjs, the start code, opens the pool, runs the migrations, and only then listens:

// 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}`));

Replace the notes table and its two queries with your own, and createAppServer with your application's server. Its health route calls reachable() and returns 200 when it is true and 503 otherwise, as Serve the health path over the connection shows. Keep the split the sample makes. Only the start code opens the live pool, and no module opens one when it is imported. Your tests hand the same data layer the test double's pool, as Test your application locally shows.

Each role can reach its own database and no other. The platform runs, patches, and backs up the database server. The development server's public endpoint accepts a connection from any address under the platform's developer network rule. The connection is encrypted with TLS, and the credential controls access, so a local run reaches the development database from any machine. The production servers accept no connection from your machines.

The connection limit #

Each process is bounded by its size's database-connection-limit. tzcloud_submit_manifest returns the limit as connection_limit, and tzcloud_read_status and tzcloud_read_plan_quotas return it too. A deployed copy receives it as APP_DATABASE_CONNECTION_LIMIT; the provision command writes the setting for a local run.

Set your pool's maximum from that setting, never higher. The sample's openLivePool does this and uses one connection if the setting is absent. During a deploy or promote, both copies run from the new copy's start through its health check and for about 15 seconds after the switch. Through the pooled address, both copies share the pooler's server connections for their role and database.

The database role admits twice connection_limit, so 20 server connections. These include the pooler's server connections and every direct connection: APP_DATABASE_DIRECT_URL, tzcloud_query_database, and a version whose APP_DATABASE_URL still names the direct port. The role's bound counts server connections, rather than each client's connection to the pooler.

Through the pooled address, a transaction that finds no server connection free waits up to 15 seconds, then fails. A pool larger than connection_limit therefore does not fail at once with too many connections for role; transactions wait instead. Keep queries short and return connections promptly, so the health path's read does not wait behind long work.

Through the direct address, PostgreSQL refuses a connection over the role's limit with too many connections for role. An oversized old pool can leave the new copy without a connection during a promote, restart or platform redeploy, so its health check fails. Read the failed record's gate result or tzcloud_read_logs with source: "container". Set the pool's maximum from APP_DATABASE_CONNECTION_LIMIT, then deploy again.

A running copy keeps the setting it started with. After a change to the connection limit, the role's limit changes at once; each environment's next deploy, promote or tzcloud_restart_application supplies the new setting.

A restart starts its new copy beside the old one; the old copy leaves only after the new one passes its health check. If an old pool holds connections past the health-check window, the restart can fail and leave the old copy serving. Retry once its idle connections close.

A running database environment not re-created by a size change reads its connection setting only at its next restart, deploy or promote. Restart such an environment after the size change; production re-created by the change needs no further restart. The size act's answer does not list environments needing a restart. An environment with no running copy takes the setting at its first deploy or promote.

Work that needs one server session #

Limit your APP_DATABASE_DIRECT_URL pool to 4 connections, or 3 beside your own direct migration client, leaving room for both deployed copies and a query session.

The pooled address cannot preserve a server session between transactions. Use APP_DATABASE_DIRECT_URL for session advisory locks, LISTEN, session-level SET, temporary tables that must survive a transaction, or cursors held open after one. Ordinary queries and transactions use APP_DATABASE_URL. Database package 0.14.0 and later take their migration locks inside transactions and work through that address.

Code of your own that holds a migration lock across transactions must use the direct setting for that walk, or take and release the lock inside each transaction. A local run using the provisioned APP_DATABASE_URL also exercises the pooler, so test the walk locally with those settings.

Statements prepared by name through the protocol work through APP_DATABASE_URL. In pg, a query prepares by name only when its config includes name; the Database package passes that config through. The SQL statement PREPARE belongs on APP_DATABASE_DIRECT_URL: a later EXECUTE through the pooled address may reach another server connection.

A schema change that alters a named statement's result shape can fail with 0A000, "cached plan must not change result type". One example is SELECT * over a table that gains a column. The old plan keeps failing until the pooler replaces its server connection: about 30 seconds on an idle pool or an hour on a busy one. Other statements keep working.

Restarting or deploying again with the same statement text does not end the failure if the new copy reconnects while the pooled server connection is still live. Change the text, for example by listing the columns, send the statement unnamed, or use APP_DATABASE_DIRECT_URL within the connection bound above.

Driver options through the pooled address #

The pooled address refuses these pg client or pool options at sign-in with 08P01: statement_timeout, lock_timeout, idle_in_transaction_session_timeout, and options carrying one of them or search_path. The server's words are unsupported startup parameter: statement_timeout, unsupported startup parameter: lock_timeout, or unsupported startup parameter: idle_in_transaction_session_timeout. For options carrying -c statement_timeout=..., they are unsupported startup parameter in options: statement_timeout. With -c search_path=pg_catalog, they are unsupported startup parameter in options: search_path.

Set the timeouts or search path inside each transaction with SET LOCAL. Alternatively, keep that pool on APP_DATABASE_DIRECT_URL within the 4-connection bound, or 3 beside a direct migration client. query_timeout is different: it is the driver's timer in the client process, is sent nowhere, and works on both addresses. The Database package's own pool sets none of the four refused options.

Restarts and zone failures #

Each application database server runs in one availability zone with no standby, in production and development alike. A server restarts in place for maintenance, on Sunday between 14:00 and 15:00 UTC, or after a machine failure. A restart ends every open session, and the database accepts no new connection until the server is ready.

The sample's createFailoverPool keeps the process serving through a restart. It limits each connection attempt to five seconds and listens for ended connections. A request returns its own error; the next one opens a new connection. The sample's migrate retries lost connections at start for up to 120 seconds. A longer outage ends that start with failover_window_ended. The Database page describes both.

If the server's zone fails, its databases stay unavailable until that zone returns. The platform does not move them to another zone. An operator can restore a backup to a new server, but no recovery time is promised.

With pg alone or another driver, your code does the same three things itself:

  • It listens for the pool's error event. Without that listener, an ended connection stops the process.
  • It gives each connection attempt a timeout of a few seconds, so a request fails at once instead of waiting out the restart.
  • It retries a lost connection during startup migrations for up to 120 seconds.

Without the Database package #

A server can use the pg driver alone, with no scaffold and no Database package. The sample below is one whole file. It reads the same two settings and does the three things the list above describes. It creates its table before it listens on PORT.

For migrations of your own, follow Work that needs one server session: use the direct setting for a lock held across transactions, or keep the lock inside each transaction.

For a complete app with ordered migrations, a public form and routes to read, change and delete entries, use Build a form backed by a database. It includes a deploy route for a tool with no shell.

// server.mjs: a server on the pg driver alone, with no scaffold and no Database package
import { createServer } from 'node:http';
import pg from 'pg';

// A request's abort signal. The deployed runtime attaches its own signal to each
// request, and it aborts at the request's window. A local run has none of its own,
// and Node's request.signal can abort at a normal request end on some Node 24
// releases, so a local run's signal aborts when the response closes unfinished.
function abortSignalOf(request, response) {
  const own = Object.hasOwn(request, 'signal') ? request.signal : undefined;
  if (own) return own;
  const controller = new AbortController();
  response.once('close', () => {
    if (!response.writableFinished) controller.abort();
  });
  return controller.signal;
}

// The connection string carries sslmode=verify-full, so the pool takes no ssl option.
const url = process.env.APP_DATABASE_URL;
if (!url) throw new Error('APP_DATABASE_URL is not set');
// APP_DATABASE_CONNECTION_LIMIT sets its maximum.
// The platform supplies it; the turnzero command's provision writes it for
// a local run. The pool uses one connection where the setting is absent.
const limit = process.env.APP_DATABASE_CONNECTION_LIMIT ?? '';
const max = /^[1-9][0-9]*$/.test(limit) ? Number(limit) : 1;
// Each connect waits at most five seconds, so a request fails at once during a restart.
const pool = new pg.Pool({ connectionString: url, max, connectionTimeoutMillis: 5_000 });
// Without an error listener, a session the server ends stops the process.
pool.on('error', (error) => console.warn('database session ended:', error.message));

// The table, created at start. A lost connection is retried every second for up to 120 seconds.
const LOST = ['ECONNREFUSED', 'ECONNRESET', 'ETIMEDOUT', 'EAI_AGAIN', '57P01', '57P03', '08006'];
const deadline = Date.now() + 120_000;
for (;;) {
  try {
    await pool.query('CREATE TABLE IF NOT EXISTS "notes" (id bigserial PRIMARY KEY, title text NOT NULL)');
    break;
  } catch (error) {
    const lost = LOST.includes(error.code) || /timeout|terminated/i.test(error.message);
    if (!lost || Date.now() + 1_000 > deadline) throw error;
    await new Promise((resolve) => setTimeout(resolve, 1_000));
  }
}

const port = Number(process.env.PORT ?? 8080);
createServer(async (request, response) => {
  // End the response when the request is aborted (abortSignalOf, above).
  const signal = abortSignalOf(request, response);
  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));
  };
  const path = request.url.split('?')[0];
  try {
    if (request.method === 'GET' && path === '/health') {
      await pool.query('SELECT 1');
      return send(200, { ok: true });
    }
    if (request.method === 'GET' && path === '/notes') {
      const { rows } = await pool.query('SELECT id, title FROM "notes" ORDER BY id');
      return send(200, rows);
    }
    send(404, { error: 'not_found' });
  } catch (error) {
    console.error(error);
    send(path === '/health' ? 503 : 500, { ok: false });
  }
}).listen(port, () => console.log(`listening on ${port}`));

Its health path is /health, which the manifest's health member names. The health route runs SELECT 1 and returns 503 while that read fails. Change the route and the manifest together. With no library entry, the manifest's packages member is [].

When the platform's abort signal fires, the sample ends the response, and send then writes nothing more. Node.js Runtime describes the signal.

The sample keeps no migration files. CREATE TABLE IF NOT EXISTS creates a missing table and leaves an existing table unchanged, so adding a column to that statement later does not update the table. Before changing the schema, use ordered migration files. The package-free pg helper in step 4 (Keep the schema as migrations) applies each file once, records it, and holds a lock so a second copy waits. You can also use the Database package's applyMigrations.

For offline data-layer tests with no library, sign-in or credential file, follow Without the Database package. That example runs the layer's SQL on PGlite directly through a query-only adapter. It does not test the pg driver or its connection behavior.

For the fuller pool interface and migration handling, take the Database package for its test double alone, as Use the library describes; the server keeps importing only pg. The double ships inside the package, so the committed package copy gives every clone and CI run the same double, which runs without a Turn Zero credential.

An AI tool with no shell or no CLI token can instead call tzcloud_read_library over its signed-in MCP connection to find the Database entry. It passes that entry's listed name in entry and reads lib/testing.js, lib/migrations.js, and lib/files.js by naming each path in file. The double runs PGlite, PostgreSQL inside Node.js, behind a pg.Pool-shaped interface.

The sample opens its pool and creates its table when it starts, so first move both into its entry block, as Write the data layer over a pool shows. Then give the double the table as a migration file, such as migrations/0001_notes.sql from step 2 (Connect to it). A local run needs neither change: it reaches the development database, as step 5 (Develop locally) describes.

CommonJS and Express #

For a CommonJS application using Express, save this as src/server.cjs. Keep the application's other dependencies and add express and pg. Set its start script to node src/server.cjs. The .cjs extension works without changing an ESM application's package type.

// src/server.cjs: CommonJS and Express, using pg without the Database package.
const express = require('express');
const pg = require('pg');

// A request's abort signal. The deployed runtime attaches its own signal to each
// request, and it aborts at the request's window. A local run has none of its own,
// and Node's request.signal can abort at a normal request end on some Node 24
// releases, so a local run's signal aborts when the response closes unfinished.
function abortSignalOf(request, response) {
  const own = Object.hasOwn(request, 'signal') ? request.signal : undefined;
  if (own) return own;
  const controller = new AbortController();
  response.once('close', () => {
    if (!response.writableFinished) controller.abort();
  });
  return controller.signal;
}

async function main() {
  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;
  const pool = new pg.Pool({ connectionString: url, max, connectionTimeoutMillis: 5_000 });
  pool.on('error', (error) => console.warn('database session ended:', error.message));

  const lostCodes = ['ECONNREFUSED', 'ECONNRESET', 'ETIMEDOUT', 'EAI_AGAIN', '57P01', '57P03', '08006'];
  const deadline = Date.now() + 120_000;
  try {
    for (;;) {
      try {
        await pool.query('CREATE TABLE IF NOT EXISTS "notes" (id bigserial PRIMARY KEY, title text NOT NULL)');
        break;
      } catch (error) {
        const lost = lostCodes.includes(error.code) || /timeout|terminated/i.test(error.message);
        if (!lost || Date.now() + 1_000 > deadline) throw error;
        await new Promise((resolve) => setTimeout(resolve, 1_000));
      }
    }
  } catch (error) {
    await pool.end();
    throw error;
  }

  const app = express();
  app.use((request, response, next) => {
    // End the response when the request is aborted (abortSignalOf, above).
    const signal = abortSignalOf(request, response);
    signal.addEventListener('abort', () => response.destroy(), { once: true });
    next();
  });
  const send = (response, status, body) => {
    if (!response.destroyed) response.status(status).json(body);
  };
  app.get('/health', async (_request, response) => {
    try {
      await pool.query('SELECT 1');
      send(response, 200, { ok: true });
    } catch (error) {
      console.error(error);
      send(response, 503, { ok: false });
    }
  });
  app.get('/notes', async (_request, response) => {
    try {
      const { rows } = await pool.query('SELECT id, title FROM "notes" ORDER BY id');
      send(response, 200, rows);
    } catch (error) {
      console.error(error);
      send(response, 500, { ok: false });
    }
  });
  app.use((_request, response) => send(response, 404, { error: 'not_found' }));
  const port = Number(process.env.PORT ?? 8080);
  app.listen(port, () => console.log(`listening on ${port}`));
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

This variant keeps the same connection limit, startup retry, health check, and abort handling. It creates the initial table only; use step 4 (Keep the schema as migrations) before changing that schema.

3. Rotate the production credential #

The production password changes only when you ask. Pass rotate_database_credential: true to the call that reaches production: tzcloud_deploy on an application with one environment, tzcloud_promote on one with two. That call creates a new password and sets it on the production role before it starts the new container, which receives the new connection string. On an application with one environment, the command route takes rotate_database_credential: true on tzcloud_deploy_command. Run its returned command; the rotation happens when that command starts the deploy.

  • When the health check passes, the new password stays in use.
  • When the deploy or promote fails, the role's old password is restored.

Between the password change and the switch, the old container cannot open new database connections, although its open connections keep working. That is why the rotation runs only when you ask, and never at every deploy or promote. A tzcloud_roll_back never rotates the database credential.

4. Keep the schema as migrations #

Keep schema changes as ordered .sql files in the application's migrations/ folder, named with zero-padded numbers that set their order. Your application's own start code applies them before the server starts listening, with the Database package's migration function, applyMigrations, or with code of your own. That code applies each file once, records it, and holds a lock while it runs, so a second copy waits. The platform runs no migration itself.

Code of your own on the pg driver alone can be one file, as below. It keeps the Database package's ledger table, schema_migrations, and the package's lock, so applyMigrations can take over later with nothing applied twice.

Both helpers below read migrations/ relative to the working directory, so start local runs at the application's root; the image runs npm start at /app, the deploy ZIP's root.

// src/migrate.mjs: applies each file of migrations/ once, in name order, under a lock, on the pg driver alone
import { readdir, readFile } from 'node:fs/promises';
import { join } from 'node:path';

// The Database package's ledger and lock, so its applyMigrations can take over later.
const LEDGER = 'schema_migrations';
const LOCK = "hashtext('mark_one_database_migrations')";

export async function migrate(client, folder = 'migrations') {
  // One client, not a pool: an advisory lock belongs to the connection that takes it.
  await client.query(`SELECT pg_advisory_lock(${LOCK})`);
  try {
    await client.query(`CREATE TABLE IF NOT EXISTS ${LEDGER} (name text PRIMARY KEY, applied timestamptz NOT NULL DEFAULT now())`);
    const { rows } = await client.query(`SELECT name FROM ${LEDGER}`);
    const applied = new Set(rows.map((row) => row.name));
    const names = (await readdir(folder)).filter((name) => name.endsWith('.sql')).sort();
    for (const name of names) {
      if (applied.has(name)) continue;
      // Remove leading whitespace, including a byte-order mark, before running the migration.
      const sql = (await readFile(join(folder, name), 'utf8')).trimStart();
      // Each file and its ledger row in one transaction, so a failed file records nothing.
      await client.query('BEGIN');
      try {
        await client.query(sql);
        await client.query(`INSERT INTO ${LEDGER} (name) VALUES ($1)`, [name]);
        await client.query('COMMIT');
      } catch (error) {
        await client.query('ROLLBACK');
        throw error;
      }
    }
  } finally {
    await client.query(`SELECT pg_advisory_unlock(${LOCK})`);
  }
}

Call the migration helper at start, before the server listens, on a client of its own:

const directUrl = process.env.APP_DATABASE_DIRECT_URL;
if (!directUrl) throw new Error('APP_DATABASE_DIRECT_URL is not set');
const client = new pg.Client({ connectionString: directUrl });
await client.connect();
await migrate(client);
await client.end();

The helper holds its lock across transactions, so its client uses the direct setting. Unlike applyMigrations, it keeps no hash of each file, so it cannot refuse a file edited after it ran. Unlike the package's bootWalk, it does not retry a lost connection at start. Where a restart can meet the start, run it inside a retry like the one in step 2 (Connect to it).

CommonJS migration helper #

For the Express server above, save this helper as src/migrate.cjs. Its default migrations folder is relative to the process's working directory, so start the server from the application's root.

// src/migrate.cjs: applies each file of migrations/ once, in name order, under a lock, on the pg driver alone
const { readdir, readFile } = require('node:fs/promises');
const { join } = require('node:path');

// The Database package's ledger and lock, so its applyMigrations can take over later.
const LEDGER = 'schema_migrations';
const LOCK = "hashtext('mark_one_database_migrations')";

async function migrate(client, folder = 'migrations') {
  // One client, not a pool: an advisory lock belongs to the connection that takes it.
  await client.query(`SELECT pg_advisory_lock(${LOCK})`);
  try {
    await client.query(`CREATE TABLE IF NOT EXISTS ${LEDGER} (name text PRIMARY KEY, applied timestamptz NOT NULL DEFAULT now())`);
    const { rows } = await client.query(`SELECT name FROM ${LEDGER}`);
    const applied = new Set(rows.map((row) => row.name));
    const names = (await readdir(folder)).filter((name) => name.endsWith('.sql')).sort();
    for (const name of names) {
      if (applied.has(name)) continue;
      // Remove leading whitespace, including a byte-order mark, before running the migration.
      const sql = (await readFile(join(folder, name), 'utf8')).trimStart();
      // Each file and its ledger row in one transaction, so a failed file records nothing.
      await client.query('BEGIN');
      try {
        await client.query(sql);
        await client.query(`INSERT INTO ${LEDGER} (name) VALUES ($1)`, [name]);
        await client.query('COMMIT');
      } catch (error) {
        await client.query('ROLLBACK');
        throw error;
      }
    }
  } finally {
    await client.query(`SELECT pg_advisory_unlock(${LOCK})`);
  }
}

module.exports = { migrate };

Add const { migrate } = require('./migrate.cjs'); beside the server's other imports. Replace its CREATE TABLE IF NOT EXISTS query inside the startup retry loop with this dedicated-client call. Keep the surrounding retry, and listen only after it succeeds. The migration client uses APP_DATABASE_DIRECT_URL because the helper holds its lock across transactions:

// Inside src/server.cjs's startup retry loop, within async function main().
const directUrl = process.env.APP_DATABASE_DIRECT_URL;
if (!directUrl) throw new Error('APP_DATABASE_DIRECT_URL is not set');
const client = new pg.Client({ connectionString: directUrl, connectionTimeoutMillis: 5_000 });
try {
  await client.connect();
  await migrate(client);
} finally {
  await client.end();
}

Like the ESM helper, this helper uses one connection for its lock, records each file, and rolls back a failed file. It keeps no content hashes and does not retry by itself; the server's startup loop supplies the retry.

The platform provisions an empty database. Your application's startup code creates and changes its tables, using the migration files in this example. The same files reach the development database at a local or deployed start, production at the start of the deploy or promote that reaches it, and the test double when it is created. A promote copies no schema and runs nothing itself: the new version's start code applies the files production lacks.

Records an application already kept in files reach the database the same way, as one migration file that carries them. Carry existing records into the database shows the file and the script that writes it.

applyMigrations applies each file not yet recorded in the schema_migrations table, each in its own transaction. If a file fails, startup fails, and earlier files stay applied. When copies start together, each transaction takes the same lock and reads the ledger again before applying a file. Running the function again is safe, and the sample's migrate runs it through the package's bootWalk, which retries a lost connection every second and fails startup with failover_window_ended after 120 seconds.

A file is applied in a database once that database's schema_migrations table records its name. The table also records a hash of each file's content, and a start that finds a recorded file edited fails before any file runs, naming the file. Edit a file freely until a database records it; the test double starts empty each time.

Where only the development database recorded it, edit it, then clear the development database as step 5 (Develop locally) describes, so the next start applies the edited file. Once production has started a version carrying the file, a failed deploy included, never edit or rename it; add a new file.

A later change is a new file, such as migrations/0002_add_created_at.sql holding ALTER TABLE "notes" ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();. A deploy or promote starts the new version beside the running one and switches only when the health path responds. So a migration runs against the database the old version is still using, and a failed deploy does not undo it. Write each migration so the running version keeps working until the switch:

  • Add columns and tables before code reads them.
  • Drop or rename only in a later version of your application, once no running version needs the old shape.

Save migration files as UTF-8 without a byte-order mark (BOM). Windows PowerShell shows how to avoid PowerShell 5.1's default BOM or UTF-16 output.

5. Develop locally #

To run a database app before its first deploy without platform services, follow Run against your own PostgreSQL.

Get the credential for a local run #

A local run reads the development database's credential from .env. Over the HTTP API, a submission that names no local_run returns the development role's credential once. Through the Model Context Protocol (MCP) tool, and over the HTTP API where the submission names local_run, the response withholds it, because the platform never puts a platform-minted credential into an AI tool's conversation.

With a shell that reaches the platform, your AI tool writes .env using its MCP sign-in alone, without computer sign-in or a minted token. It runs turnzero provision --application <application id> with nothing piped, makes any printed tzcloud_submit_manifest call, then runs the returned provision command. That command uses this computer's verifier and prints no credential values; Run your app on your computer gives the steps.

If the development credential is lost later, your AI tool does the same again: the command calls HTTP store_secret with replace: true and environment: "development", which sets a new password on the role. Your AI tool never makes that call itself: through the MCP tool it is refused local_route_required, and on the production scope platform_minted_name.

A running development container keeps using its old, revoked credentials until it restarts. Where development runs a version, the command restarts it after creating the last replacement credential. Creating a replacement credential is called re-minting. The restart reads the new database connection string and platform credential from the platform's secret store. It does not count toward the day's deploys.

If a development deploy is still running, the response names tzcloud_restart_application on development. Your AI tool makes that call once the deploy ends.

A local run that loads the development environment file reaches the development database, which is separate from production's on every application. The production connection string is never shown to you, so it cannot end up in a local file. The application's tests run on the package's test double and need no database. Use the development database for reset, seed, and other destructive experiments.

Run tests or check a deployed copy #

Tests need neither turnzero provision nor .env. Taking the package needs a sign-in and the turnzero command, because npm carries no @turnzero package. The take line reads this computer's token, which login keeps, or a minted token. Run download.command_npx first, or its Windows form; for a refusal, see Running and writing lines. Once taken, npm test needs no account or connection (Test your application locally).

A tool without a shell cannot run tests or a local run. With development enabled, it deploys using tzcloud_deploy_files (Deploying from a chat whose shell cannot reach the platform). It calls each changed route with tzcloud_request_route, or tzcloud_write_route for one that writes, and environment: "development", then reads the rows with tzcloud_query_database and environment: "development" (6. Read your data).

On an application with one environment, a deploy reaches production, so Test in development, then promote comes first. Where you ask for a fresh start, the tool requests tzcloud_clear_development_database and gives you the approval link, then restarts development so the migrations rebuild the tables, as the clear below describes.

Migrations, resets and data limits #

Keep schema changes in ordered migration files, noting any that remove or rewrite data. Read read_context with id guide:schema_evolution for development tasks: migrate, reset, seed, status, diff, and snapshot. Run them with your development tools; the platform supplies no such commands.

Reset development data through APP_DATABASE_URL, the connection string the provision command wrote into .env. Before the reset, your AI tool checks that no shell variable of that name exists, with the check the troubleshooting page Secrets gives. A shell value takes precedence over the file and could point to another application's database. Where the check shows the name, your AI tool tells you, and never resets under that value.

Your AI tool then clears the name and runs the reset in one command, since its next command can start a new shell that inherits the name again. For example, env -u APP_DATABASE_URL node --env-file=.env …, or in PowerShell Remove-Item Env:APP_DATABASE_URL -ErrorAction SilentlyContinue; node --env-file=.env … as one invocation.

In that command, the reset loads the string from the file as the local run does, and never prints the value or reads it into the conversation. Run your app on your computer describes the file.

To start development with an empty database, your AI tool calls tzcloud_clear_development_database with application and environment: "development". Like a deletion, it needs a person's approval in the browser. Emptying the database cannot be undone, so it waits for that approval; to delete only some development rows, your application's own route or migration deletes them with no platform action. The approval page shows the database, its role, and the environment.

After approval, the platform drops the development database with every table and row in it, and creates it again empty under the same role. Open connections to it are closed. The role keeps its password, so the connection string in .env keeps working and the provision command need not run again. read_approval_status returns the outcome with the database and role names.

If a deploy, promote, or tzcloud_restart_application of the application starts while the clear runs, it waits for the clear to end, for 45 seconds at most. Past that, it fails, and you start it again. If the database server takes longer than 20 seconds over any step of the clear, the clear fails; request it again.

The tables come back when the migrations run again. Your AI tool calls tzcloud_restart_application on the development environment, or deploys, and the migration function rebuilds the schema as the application starts. On an application with one environment, nothing hosted uses that database, so the migrations run at your next local start.

The clear is refused in three cases, and nothing is cleared:

  • production, with 400 invalid_request. No action clears the production database.
  • An application with no development database, with 404 not_found.
  • A deploy or redeploy (a tzcloud_restart_application) of development still in progress when the approved clear runs, with 409 deploy_in_flight. Wait for it to end, then request the clear again.

To remove the whole development environment instead, use tzcloud_delete_environment, as Delete the development environment describes. On an application with one environment, the same call removes the development database and every other record your local runs keep: the credentials, the stored secrets, the files, and the logs. Production is untouched. Then call tzcloud_submit_manifest naming no local_run to set the database up again, and run turnzero provision --application <application id> with nothing piped, and make any call it prints.

A promote takes no snapshot, and a tzcloud_roll_back restores the earlier version's code, setting bindings and plain values. A migration the new version applied stays applied when you roll the code back. So write migrations the previous version can run against, or plan how to migrate or restore the data before you roll back. No MCP tool applies a migration or restores a snapshot. The reserved HTTP actions apply_migration and restore_snapshot return 501 not_yet_provisioned.

The platform measures each database's size daily. It counts toward the application's stored-data ceiling and posts a day's share on the database storage line (Size and usage). Read the rate on the pricing page or with read_rate_card.

6. Read your data #

To see what an environment's database holds, your AI tool calls tzcloud_query_database with application and sql, one SQL statement. Without environment, it reads production. With environment: "development", it reads the development database, the one your local runs use. Production's credential is never shown to you, so this is how you read production's data.

For example, sql: "SELECT count(*) FROM comments" answers columns: ["count"], rows: [["42"]], row_count: 1, and truncated: false. Each value comes back as PostgreSQL's text for it, such as "42" for a count, or as null.

The statement starts with SELECT, WITH, VALUES, or TABLE. It runs as the environment's own database role, inside a read-only transaction that is always rolled back, so it changes nothing. A write, such as INSERT, UPDATE, DELETE, or CREATE, is refused query_not_read_only, and so is a write inside a function the statement calls. One call runs one statement.

The answer is bounded. It carries at most limit rows, 100 where you give none and 1,000 at most, and at most 64 KiB of row data. The statement runs for 10 seconds at most. Where a bound cut the answer, truncated says which, "rows" or "bytes", and it is false where nothing was cut. To see the rest, narrow the read with WHERE, LIMIT, or fewer columns, and call again.

tzcloud_query_database is an observe-tier action, so it needs no approval. Only your own account can call it. Neither the statement nor the rows it reads are written to the platform's logs.

Check data after a restart or idle stop #

Use an existing record to check persistence without adding test data to production. These calls also work in an AI tool with no shell.

  1. Choose the application and environment. Use tzcloud_query_database to read stable fields of an existing record by its identifier, and keep the result for comparison. Name that same application and environment in every call. If no record exists, use an approved development test instead, or report that there is no data to check.

  2. For a restart test, call tzcloud_restart_application only when the person has asked for the test, and follow its returned status read until it finishes. For an idle test, use an environment that stops when idle, production with production_sleep on, or a development environment (Start after idle and response time), and let it idle normally.

    Read tzcloud_read_logs with source: "container" until its detail confirms that the environment is idle and scaled to zero. Only an answer that returns lines carries that sentence. Status and console reads do not wake it. Application requests and scheduled runs can keep it awake; elapsed time or compute_state: "Idle" alone does not prove an idle stop.

  3. After an idle stop, call a known read-only application route with tzcloud_request_route to start it again. Then repeat the same database query and compare the saved fields. After a restart, repeat the query once the restart has finished.

Report the event you observed and the before-and-after values. If the console does not confirm an idle stop, report that the idle check is unverified. This checks the selected record, not every row or a backup restore.

Durability and backups #

Automatic backups are kept for 35 days, with copies in a second United States region, for every application. Data survives idle stops, restarts, deploys and promotes.

An operator can restore one database to a chosen time in that window. Email support@turnzero.ai with the application, the environment and the UTC instant to restore to. There is no self-serve restore.

Deleting an application, an environment or an account, or clearing the development database, removes that data. Backups cannot restore it. Save an export before deleting or clearing data.

Expected result #

tzcloud_submit_manifest returns outcome: "recorded" and, once the development database exists, development_database with host, dbName, roleName, and connection_setting: "APP_DATABASE_URL", and connection_limit beside it. Through the MCP tool, the first submission states credentials: "withheld".

The provision command then prints the settings it wrote, each credential value as a count of characters, then a line about the development database. .env then contains APP_DATABASE_URL, APP_DATABASE_DIRECT_URL and APP_DATABASE_CONNECTION_LIMIT, and the start code prints migrations applied at this start: 0001_notes.sql at its first start.

tzcloud_read_status returns connection_limit beside size, and each environment's database, or null where the environment has none. With tables: true, it also lists each database's table names. Without environment, the top-level database and tables members describe production, and are null until the first deploy or promote that reaches production; with environment, they describe that environment, which the top-level environment member gives.

tzcloud_query_database returns the statement's columns, its rows, row_count, and truncated, and the database is unchanged.

Refusals #

See the refusals reference for other platform errors.

The refusal tables below list errors from the database declaration, the provision command's HTTP store_secret calls with replace: true, tzcloud_set_app_size, tzcloud_clear_development_database, and tzcloud_query_database. Author the manifest lists the manifest's other refusals, Deploy from your computer the deploy's and the promote's, and Run your app on your computer the provision command's grant refusals.

A refusal gives its cause in detail. A refused HTTP store_secret with replace: true changes no credential, and a refused tzcloud_set_app_size records no size or sleep change. The tzcloud_submit_manifest refusals below happen after the manifest is recorded and before the development database is ready.

Database operations #

Refusal Status Cause Remedy
invalid_request 400 detail names the missing or malformed member. HTTP store_secret with replace: true refuses an environment other than development or production. tzcloud_clear_development_database refuses production; no action clears it. Correct the named member.
query_not_read_only 400 tzcloud_query_database's statement would write: its first word is not SELECT, WITH, VALUES, or TABLE, or the read-only transaction refused a write inside it. Nothing changed. Send a statement that only reads. Make changes through your application's own routes or migrations.
not_found 404 HTTP store_secret with replace: true named the development database credential of an application with no development database, or a name not stored. tzcloud_clear_development_database named an application with no development database. tzcloud_query_database named an environment with no database. Submit a manifest that declares the database first. For tzcloud_query_database on production, deploy or promote to production first.
plan_quantity_unset 409 A required quantity for the size is unset. The manifest stays recorded. Read its row with tzcloud_read_plan_quotas and wait for platform staff to set it.
deploy_in_flight 409 A deploy or redeploy (a tzcloud_restart_application) of development was in progress when the approved tzcloud_clear_development_database ran, so nothing was cleared. Wait until tzcloud_read_status shows it ended, then request the clear again.
deletion_in_progress 409 The application or one of its environments is being deleted. A deletion approved while another deletion of the same application holds it ends failed with this refusal. That refusal's detail gives the instant the other deletion's hold lapses; if its process stopped, request your deletion again after that instant. Wait for the deletion to finish; read_approval_status shows whether it completed. If it failed, request the same deletion again to complete it.
query_failed 422 or 503 tzcloud_query_database's statement or its session failed. At 422, the database server refused it, and the answer gives its sqlstate and message: 42601 for a second statement, 57014 for the 10-second limit, 53300 where the role's connections are all in use. At 503, the session failed without an answer from the server: it never reached it, or it ended mid-statement at the 15-second limit or on a network drop. At 422, correct the statement, or narrow it to finish within 10 seconds. For 53300, call again once your application frees a connection. At 503, call again later.
database_provisioning_failed 502 tzcloud_submit_manifest could not create the role or password, though the manifest stays recorded. HTTP store_secret with replace: true found a recorded development database with no role and changed nothing. After tzcloud_submit_manifest, read the response and resubmit. After the HTTP replacement call, call tzcloud_delete_environment for development to remove the database, its data and secrets on one or two environments. Resubmit to create a new database; on two environments, call tzcloud_create_environment first. Report a failed deletion with the reference in its detail.
entitlement_apply_failed 502 A compute or connection-limit write failed; the size and sleep setting were not recorded. Repeat the same tzcloud_set_app_size call. Report a repeated failure with its reference and detail.
server_unavailable 503 tzcloud_submit_manifest: no server has room, or an unfinished setup recorded a server that no longer accepts databases; that record prevents moving the setup. The manifest stays recorded. On a promote or production deploy, the same refusal ends the record failed at database_pair. After tzcloud_submit_manifest, resubmit later if detail names no previously recorded server; changing the manifest does not help. If it names one, resubmit once in case the earlier run is finishing. If it repeats, call tzcloud_delete_environment for development to remove the setup, data, and secrets, on one environment or two. Resubmit to recreate them; with two environments, call tzcloud_create_environment first. If deletion fails, report its detail and resubmit after staff remove the earlier setup or reopen the server. After a promote or production deploy, report a detail naming an earlier run; that refusal clears after staff remove that setup or reopen the server. Otherwise retry later.
cell_not_configured 503 The platform cannot place the database now; the manifest stays recorded. A promote or a production deploy whose production database has no placement ends failed the same way at its database_pair step. Nothing on your side corrects this: report the refusal with its detail, and repeat the action once platform staff have configured the hosting cell.

Credentials and local provisioning #

Refusal Status Cause Remedy
authentication_required 401 An unattended provision sent no minted token, or one that matches no account. Set TURNZERO_CLOUD_MINTED_TOKEN to a token minted for the application.
token_scope_refused 403 The script's token is for one application, and the request named another. Mint the token for the application the request names.
app_credential_not_admitted 403 An unattended provision ran under the application's platform credential, such as a TURNZERO_CLOUD_APP_TOKEN copied from .env into TURNZERO_CLOUD_MINTED_TOKEN. Only read_account accepts that credential. Set TURNZERO_CLOUD_MINTED_TOKEN to a token bounded to the application, minted with mint_token from your signed-in session. Then run the command again.
platform_minted_name 409 HTTP store_secret with replace: true named database-<application id> or credential-<application id> on the production scope, or tzcloud_store_secret named either. Run turnzero provision --application <application id> with nothing piped and make any call it prints. If development runs a version, restart it with tzcloud_restart_application; this takes both re-minted credentials without using a daily deploy. Rotate production's credential with rotate_database_credential: true on a production deploy or promote.
local_route_required 409 tzcloud_store_secret with replace: true named a platform-minted name on the development scope through the MCP tool. Run turnzero provision --application <application id> with nothing piped and make any call it prints; its line uses the HTTP API. If development has a running version, call tzcloud_restart_application there afterwards. It takes both re-minted credentials without using a daily deploy.
rotation_in_flight 409 Another HTTP store_secret with replace: true call for the same development database credential was still running, so nothing changed. Wait for that call to finish, then run turnzero provision --application <application id> with nothing piped and make any call it prints. The line writes the current password. If development runs a version, restart it with tzcloud_restart_application; this takes both re-minted credentials without using a daily deploy.
database_credential_unreadable 502 A deploy, promote, platform redeploy, or tzcloud_query_database could not read the stored database credential. A version already serving stays in place; where none was serving, none serves afterwards. Retry. If it repeats for development, run turnzero provision --application <application id> with nothing piped. Follow Restore access to a database credential for the remaining development steps or a repeated production refusal.

Restore access to a database credential #

Retry the original action first. If database_credential_unreadable repeats for development, follow steps 1 to 4. For production, follow steps 5 to 7.

  1. Run turnzero provision --application <application id> with nothing piped.

  2. Make any printed tzcloud_submit_manifest call through your signed-in connection, then run the returned provision command on the same computer. With no shell, ask the person to run the command on a computer with a shell and paste the printed tzcloud_submit_manifest call into this chat. Make that call through your own connection, then ask them to run the returned command on that same computer.

  3. The command replaces the development credential. Where development serves a version, the command restarts that version; it does not deploy the failed version. Where nothing serves, it restarts nothing, and the next development deploy takes the new credential. Follow any instruction to restart once an in-flight deploy ends, as Develop locally describes.

  4. After a failed deploy, deploy development again with the same files; after tzcloud_query_database, retry that query. If the refusal repeats after successful provisioning, follow the production report route below.

  5. For production, including a first deploy or promote, provisioning development does not repair it. For a repeated production refusal, call submit_feedback with the refusal. For a failed deploy or promote, quote that call’s returned reference, not a later read_status call’s. For another failed call, quote its reference where returned. Platform staff restore access to the credential for either environment.

  6. Passing rotate_database_credential: true does not repair this refusal: the platform reads the standing credential before rotating it.

  7. Keep the returned issue id and call read_feedback with it. Read note_to_reporters and live_in; fixed means built and being released. Follow the platform’s recovery instructions, and retry the original action only after it confirms credential restoration or that the fix is live.