Database #

View this page as Markdown

On this page

Database gives your application its own PostgreSQL database in each environment, so you set up no database yourself. Each environment gets a database and its owner role, the database role that owns it. Turn Zero Cloud provisions the development database and its owner role when the manifest first declares the service, on every application. On an application with one environment, production, only local runs use the development database.

To start, add {"kind": "database"} to the manifest's services array and call submit_manifest, take the package with turnzero library take --entry database, and open the pool from APP_DATABASE_URL in the process entry. A new service can start from one line instead, turnzero scaffold --template database-service (Build a database-backed service).

Without a shell, start from the database-service example files and follow the example's chat instructions. They explain how to declare library versions and deploy the files with deploy_files.

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.

The package, @turnzero/database, is not on the npm registry. Take it with turnzero library take --entry database (turnzero.cmd in Windows PowerShell). The CLI requires authorizing the computer or supplying a minted token; signing in to MCP does not authorize the CLI. Once installed, its test double runs in your tests without a Turn Zero credential or a database server. It is not an application preview mode; Test your application locally shows how to use it.

The production database is provisioned at the first deploy to production of an application with one environment, or at the first promote of one with two, made while the manifest declares the database. Your request pool reads APP_DATABASE_URL; work that needs one server session reads APP_DATABASE_DIRECT_URL. It changes its schema with migration files that it applies each time it starts. Add a database is the task, step by step.

A server application may declare a database, with 10 client connections per process on either size. For a serverless application serving static files alone, a database declaration is currently refused with serverless_static_only (Serverless applications and servers). The database is one monthly item for the application, covering production and development together. It remains charged while servers sleep, until the application is deleted. Read its rate on the pricing page.

Database records and stored files share the 100 GB stored-data ceiling (Current application limits). Read current quantities with read_plan_quotas, passing plan: "shared-0.5gb" or plan: "shared-1gb".

Use it when #

Use Database for structured records that need filtering, counting, joins, or transactional updates. Use Storage for file and blob contents.

Keep application preferences and other per-account records here as well. The Account client verifies identities and sessions and stores no such data.

Records you already hold in a JSON or CSV file reach the database as a migration file of their own, which the application's start code applies once in each database. Carry existing records into the database shows the file and the script that writes it.

What it provides #

  • A database and role for each environment, provisioned when the manifest declares {"kind": "database"}. The platform provisions the development database and its owner role on a development server at manifest submission. It provisions the production database and its owner role on a production server of the same hosting cell at the first deploy to production or first promote, made while the manifest declares the kind.
  • APP_DATABASE_URL, the environment's pooled connection string, assigned one server connection per transaction (A deployed app's settings). It carries sslmode=verify-full, which the pg driver reads as full certificate checking, so the pool needs no ssl option.
  • APP_DATABASE_DIRECT_URL, the direct connection string for work that needs one server session.
  • APP_DATABASE_CONNECTION_LIMIT, 10 client connections per process, the pool's maximum. The deploy, the promote, and restart_application inject it, and the provision command writes it for a local run (The connection limit).
  • The development connection facts. A submit_manifest call returns them and connection_limit, the pool's maximum. It returns no credential to your tool (Add a database).
  • Credential rotation. The production credential is rotated only on request (Add a database). A lost development credential is issued again when your tool runs turnzero provision --application <application id> with nothing piped, makes any tool call the command prints, then runs the shell command that call returns.
  • A migration function, applyMigrations, a failover pool, and a startup walk, which The client describes, with a migration ledger and an advisory lock.
  • Isolation by database role on the shared server.
  • Removal with the environment. On one environment or two, delete_environment drops the development database and its owner role, and the next manifest submission provisions them again for local runs. Deleting the application drops both databases and their owner roles.
  • A test double at @turnzero/database/testing, a pool over an in-process PostgreSQL engine. Test with the double shows it in use.

Each environment has its own database. A local run using the development environment file reaches the development database, from any address under the developer network rule, and never production's.

Availability #

Version 0.15.0 is the package's current version, and this page describes it. Until the library's next publish, read_library can return an earlier version, the one you can take, so this page can describe a change that version lacks. The entry includes the testing subpath. The entry contains the compiled migration function, migration folder reader, and failover pool, the package's product statement and detailed contract, and its integration guide. Your tool reads the integration guide with read_library, giving entry and file: "features/database/integration.md".

The platform offers no migration, snapshot, or restore action: a call to apply_migration or restore_snapshot returns 501 not_yet_provisioned, and a promote takes no snapshot.

For a read-only look at an environment's data, your tool calls query_database, as Read your data describes.

Storage stores files and blobs. Account verifies the identities your application records belong to.

The client #

A deploy that carries this package requires version 0.14.0 or later; an older copy is refused database_package_outdated. The package works over the pg driver's pool, which your application installs. The package is optional: the platform needs only the manifest's declaration and APP_DATABASE_URL, and Without the Database package shows a server on pg alone. Connect to it shows the three functions together in one data module, which a test runs.

The migration function #

The function applyMigrations(query, files) applies every .sql file the ledger table schema_migrations does not list, in name order, and returns the names it applied. The argument query is a function tied to one database connection, (text, values) => client.query(text, values). The argument files lists the file names and reads one by name. The function migrationsFrom('migrations'), from @turnzero/database, gives one over your migrations folder, and the testing subpath exports the same function. Your application calls applyMigrations before it serves requests.

applyMigrations creates and updates schema_migrations in your application's database. Turn Zero Cloud provisions the database and credentials; it does not maintain your application's migration history. If you use another migration tool, follow that tool's schema and history rules instead.

  • It takes an advisory lock inside each transaction, released at commit or rollback. Each file has its own transaction, which reads its ledger row again after taking the lock and skips a file another copy applied.
  • A file may contain several SQL statements and dollar-quoted ($$) function bodies because the migration function and the test double run it whole as one query without values.
  • It ignores names that do not end in .sql, so a folder listing can be passed as it is.
  • It strips one byte-order mark (BOM) from the start of each file before it runs the file. A BOM anywhere else in the file is passed to the server as written.
  • If a file fails, the earlier files stay applied, and the function throws: your application must stop before serving.
  • It records a hash of each file's content, and throws MigrationEditedError, naming the file, before any file runs where a recorded file was edited. Add a new file instead, or clear the development database where only it ran the file.
  • It also throws if a commit fails. The transaction's lock is released by the server; the walk issues no unlock statement.
  • When it throws, release the connection it ran on with the error, as in client.release(error). The pg pool then discards that connection instead of reusing it.

The schema_migrations columns are name (text, the file name and primary key), applied (timestamptz, the row's recording time), and sha256 (text, the file's content hash in hexadecimal).

The failover pool #

The platform's application database servers have no standby. A server restarts in place for maintenance or after a machine failure. During a restart, the server ends every open database session and accepts no connection until it is ready. In Node.js, an ended session your code does not listen for stops the process. If the server's zone fails, its databases stay unavailable until the zone returns.

The function createFailoverPool({ connect, max, windowMs, connectTimeoutMs, onConnectionLoss }) returns a pool with query, connect, and end, and the driver's pool as driverPool. It calls your factory, which builds the pg pool, and listens for those ended sessions, so the process keeps serving.

Option Default Meaning
connect Required Your factory, (settings) => new pg.Pool({ connectionString, ...settings }), called once with max and connectionTimeoutMillis.
max Required The pool's maximum: the number your process entry reads from APP_DATABASE_CONNECTION_LIMIT.
windowMs 120,000 How long bootWalk retries a lost connection at start, in milliseconds.
connectTimeoutMs 5,000 The longest one connection attempt waits.
onConnectionLoss None A function called for each session the server or the network ended.

The pool also bounds each connection attempt at five seconds, so a request fails quickly instead of waiting out the restart. Your process entry still reads APP_DATABASE_CONNECTION_LIMIT and passes it to the pool as max.

The startup walk #

The function bootWalk(pool, files, { onRetry }) runs applyMigrations over one connection from the pool and returns the names it applied. If the function cannot open a connection, or the server ends it during the run, it retries within the failover window. This does not mark the file as failed. The startup walk runs the migration function at startup and retries it every second for up to 120 seconds. After that it fails with the code failover_window_ended. Your onRetry(error, attempt) function is called at each retry.

Run bootWalk before the server listens, so a failed migration ends the process before it serves. Once it listens, answer your health path with 200 only while one short database read succeeds, and with 503 while it fails (The deploy health check).

Failures #

  • The startup walk, bootWalk, rejects with a FailoverPoolError whose code is failover_window_ended and whose cause is the last lost connection, once the window has passed.
  • The functions applyMigrations and bootWalk reject at once, with the server's error unchanged, for a file or a commit that fails. The process entry then refuses to serve.
  • For a recorded file that was edited, they reject at once with a MigrationEditedError whose code is migration_edited, and run nothing. The process entry refuses to serve, as for a failed file.
  • A query on the pool rejects with the driver's own error. The function isConnectionLoss(error) says whether the error is a lost connection.

Test with the double #

The test double, createDatabaseDouble({ engine, files }) from @turnzero/database/testing, is a pool with the pg.Pool shape over a PGlite engine your test constructs, with your migration files applied at construction. PGlite is a PostgreSQL build that runs inside the Node.js process, so the tests run offline with no development database. The engine package is @electric-sql/pglite, a development dependency at version 0.5.8, the version the double is tested against. The function migrationsFrom('migrations') reads a folder of migration files.

The package's library entry includes the double as lib/testing.js, which imports lib/migrations.js and lib/files.js. Your committed package copy gives every clone and CI run the same double, which runs without a Turn Zero credential. A signed-in AI tool with no CLI token can read these files through read_library. Set entry to database and file to each path: features/database/lib/testing.js, features/database/lib/migrations.js, and features/database/lib/files.js (Use the library).

The pool's query takes a text and its values, or a query object such as { text, values }, and returns the same rows either way. An object that sets rowMode: 'array', types, or binary, or passes a callback, is refused with a code naming the option, such as query_row_mode_unsupported, because the double cannot return those results. A test runs this sample over the double.

import assert from 'node:assert/strict';
import { PGlite } from '@electric-sql/pglite';
import { applyMigrations } from '@turnzero/database';
import { createDatabaseDouble } from '@turnzero/database/testing';

// One migration file, given in memory; migrationsFrom('migrations') reads the folder instead.
const files = { list: () => ['0001_notes.sql'], read: () => 'CREATE TABLE notes (id serial PRIMARY KEY, title text NOT NULL);' };
const database = await createDatabaseDouble({ engine: new PGlite(), files });
assert.deepEqual(database.applied, ['0001_notes.sql']);

await database.pool.query('INSERT INTO notes (title) VALUES ($1)', ['first']);
const { rows } = await database.pool.query('SELECT title FROM notes');
assert.deepEqual(rows, [{ title: 'first' }]);
// The ledger already lists the file, so the migration function applies nothing more.
const client = await database.pool.connect();
assert.deepEqual(await applyMigrations((text, values) => client.query(text, values), files), []);
client.release();
await database.close();

Test your application locally gives the double's full treatment. It installs the engine, writes the data layer as a factory over a pool, and starts one engine per test file with reset() between tests. It also says what the double reproduces of the managed server, and lists the double's refusals.