Add a database

Prompt:

Add a database to this app.

Also works:

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

This page adds a database to an application you already have. For a new service, start with Build a database-backed service instead. Its first step is one line that writes a small working service, with the database declared, the schema, the code that reads and writes it, and its tests. That line refuses unless app/ is empty or does not exist, so it does not serve an application that already has files. Beside app/, it writes system/, replacing a Database entry already fetched there with the library's copy, and a one-sentence AGENTS.md at the root only where none exists.

What your tool does

• Checks the platform's library, chooses the Database package, and copies it into the project's library folder, system/ (Use the library).
• Adds {"kind": "database"} to the manifest's services array and submits the whole manifest with submit_manifest, following the add-service skill. The platform creates the development database and role and returns their connection facts as development_database. Through the Model Context Protocol (MCP) tool, the response states credentials: "withheld".
• For a run on your machine, names local_run: true in that submission, and runs the provision line the response returns. The line calls rotate_secret over the HTTP API for the two platform-minted names with environment: "development". It builds APP_DATABASE_URL from the responses, takes APP_DATABASE_CONNECTION_LIMIT from the database response's connection_limit, and writes .env without printing a credential value.
• Writes the data layer as a factory over a pool with the pg.Pool shape. Beside it, it writes one function that creates the live pool, which only the process entry calls. That function reads APP_DATABASE_URL and fails with the setting's name when it is missing. Tests give the same layer the test double's pool.
• Writes a .env.example containing only the setting's name, and the ordered .sql files under migrations/.
• Calls the package's migration function through its bootWalk before the server listens. When the database connection is lost, bootWalk calls the function again every second for up to 120 seconds.
• Reads the plan's database-connection-limit with read_plan_quotas before it writes the pool, or submits the manifest first and takes connection_limit from the response.
• Sets the client pool's maximum to that connection_limit (read_status returns the same value). The pool function reads it from APP_DATABASE_CONNECTION_LIMIT and uses one connection where the setting is absent. It builds the package's failover pool, which limits each connect and listens for the sessions a failover ends, so the process keeps serving.
• Where the application has a development environment, deploys again after the line has run, because a running development container keeps its old credentials until its next deploy.
• Submits the manifest again naming local_run, and runs the line the response returns, when a development credential is lost.
• Rotates the production credential only when you ask, with rotate_database_credential: true on the call that reaches production: deploy on an application with one environment, promote on one with two. The new value is never shown.
• Moves the application to another plan with set_plan when you ask for more connections. It then calls restart_application for each environment the response lists, so each running copy takes the new limit.
• Clears the development database with clear_development_database only when you ask, and hands you the approval link, because the clear needs your approval in the browser.
• Asks for no browser approval: submit_manifest, rotate_secret, set_plan, restart_application, deploy, and promote are reversible-tier actions. The tool calls deploy or promote with wait_seconds, so the response waits until the deploy or the promote ends, up to 45 seconds. Where the response returns before it has ended, the tool makes the next call the response gives, read_status with wait_seconds.

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).
• An application, its identifier from list_applications, and its manifest (Author the manifest).
• A PostgreSQL driver in the application, such as pg. The application talks to PostgreSQL directly, over TLS, with no vendor software development kit (SDK) or query layer.
• The application's plan, which sets connection_limit: the number of connections each process may hold open. read_plan_quotas returns each plan's value.

Steps

1. Declare it

Add {"kind": "database"} to the manifest's services array and call submit_manifest. The platform creates one PostgreSQL database and one role for each environment:

• the development database at the submission, on a development server;
• the production database on a production server, while the manifest declares the kind, at the first deploy on an application with one environment or the first promote on one with two.

The development database exists on every application. On an application with one environment, only your local runs use it, so local test data never lands in the live database. It makes no difference whether you call create_environment before or after this submission: the submission creates the development database and its owner role either way.

The submission's response includes the development connection facts. Over the HTTP API, a submission that names no local_run also returns the development role's credential once. On the application's first submission, it also returns the development platform credential, the application's own credential. Its running backend presents it to the platform's services: storage, logging, the egress gateway, the push service's send route, and the verification route. Through the MCP tool, and over the HTTP API where the submission names local_run, the response withholds both values, because the platform never puts a platform-minted credential into an assistant's context.

A submission that names local_run: true returns the provision line, which your tool runs. The line fetches both credentials again with rotate_secret over the HTTP API and writes your environment file without printing either value.

A later submission does not return the credentials again. If the development credential is lost, your tool submits again naming local_run and runs the line the response returns. The line calls rotate_secret with the database credential's name, the application, and environment: "development". The platform sets a new password on the role and returns it once, with the connection facts. The line gives the environment because rotate_secret defaults to production.

Two routes refuse these calls:

• The MCP tool refuses rotate_secret for the two platform-minted names with local_route_required, because it returns no credential value. The line submit_manifest returns for local_run: true replaces it.
• On the production scope, the two platform-minted names, the database credential and the platform credential, are refused platform_minted_name.

Only the platform has the production credential: the first deploy or promote that reaches production creates it, and each one builds the production connection setting from it. It is never shown to an author. Declaring the service again does not reset a database that already exists.

2. Connect to it

The connection setting, APP_DATABASE_URL, is a PostgreSQL connection string with sslmode=verify-full. The pg driver treats that mode as full certificate checking against the public roots, so it needs no ssl option.

For a hosted copy, the platform builds the string and adds it to the environment as a secure setting. Each deploy or promote adds it to the container of the environment it reaches. If the platform cannot read the stored credential, the deploy or promote is refused database_credential_unreadable instead of starting without the setting.

Locally, the provision line writes the development string into your .env, and .env.example contains only the name. Write the data layer as a factory over a pool with the pg.Pool shape, and keep one function beside it that creates the live pool:

• The function reads the setting and fails with the setting's name when it is missing.
• Only the process entry calls it, and no module opens a pool when it is imported.
• Your tests give the same layer the test double's pool, as Test your application locally shows.

The host inside the string is the platform's database server name, which can change at a promote or a server move. Read it from the setting only, and copy it nowhere else.

One role reaches one database and no other. The platform runs, patches, and backs up the server.

The development server's public endpoint accepts a connection from any address under the platform's developer network rule. The connection uses TLS, and the credential controls access. A local run reaches the development database from any machine, with no address to register. The production servers accept no connection from your machines.

The connection limit

The plan's database-connection-limit is the number of database connections each process may hold open at once. submit_manifest returns it as connection_limit on every submission that declares the database kind. read_status, set_plan, and read_plan_quotas return it too. Read it before you write the pool: read_plan_quotas returns every plan's value before the manifest is submitted.

A deployed copy also receives the value as APP_DATABASE_CONNECTION_LIMIT, beside APP_DATABASE_URL, and the provision line writes the same setting into .env. Have the pool function read its maximum from that setting, and use one connection where the setting is absent. Then a plan change reaches the pool with no code edit.

Set the pool's maximum to connection_limit and never higher. The role's limit counts every open session across every process, and each environment runs one copy of the application. A deploy or promote runs the old copy and the new one against the role at once. The overlap lasts from the new copy's start through its health check, and for about a minute after the switch.

The platform sets the role's CONNECTION LIMIT to twice connection_limit, so both copies can connect during the overlap. It updates the role when the plan or its value changes. The connection string reaches the server directly, with no pooler to queue extra connections. So the server rejects a connection over the limit with the PostgreSQL error too many connections for role.

A pool larger than connection_limit shows up in two ways:

• A redeploy or promote fails its health check, and the new copy's console shows too many connections for role. Read the console in the failed record's gate result, or with read_logs and source: "container".
• The server rejects a running application's connection after set_plan, or after platform staff change its plan's value.

In the first case, read the pool's maximum from APP_DATABASE_CONNECTION_LIMIT rather than a number in the code, and deploy again. In the second, restart each environment that has a database, as the next paragraphs describe.

A running copy keeps the APP_DATABASE_CONNECTION_LIMIT it started with. After set_plan, or after platform staff change the plan's value, the platform sets the role's new limit at once. The setting follows at each environment's next deploy, promote, or restart_application. set_plan's response lists the restart_application call for each environment whose running copy has a database. An environment with no running copy takes the new limit at its first deploy or promote.

A move to a smaller plan lowers the role's limit at once, while the running copy's pool can still hold connections up to the old maximum. A restart starts its new copy beside the old one, and the old copy leaves only after the new one passes its health check. So the server can reject the new copy's connections while the old pool keeps them open, and a health path that queries the database returns 503.

Where the old pool keeps its connections open past the health check's time limit, the restart fails, and the old copy keeps serving with its old setting. Restart again once the old pool's idle connections have closed. A health path that does not query the database passes the check, and the server can reject the new copy's connections until the old copy is removed, about a minute after the switch.

Failover

A production server can switch to its standby, which accepts no connection for up to 120 seconds. The switch ends every session the server had open. Build the live pool with the Database package's createFailoverPool, which prepares the pool for it: The failover pool ships with package version 0.10.0 or later; an older copy has no createFailoverPool.

• It limits each connect to five seconds, so a request returns its own error instead of waiting.
• It adds an error listener to the pg pool, for an idle connection whose session ended. It also adds one to each connection your code checks out, until you release it. An error event with no listener ends the process.
• It passes each ended session to the onConnectionLoss function you give it, for example to print one line.

Give it a function that builds the pg pool from the two settings it passes, and the pool's maximum read from APP_DATABASE_CONNECTION_LIMIT. The statement in progress still fails to its caller, the pool discards the connection, and the next request opens a new one. The package's integration guide, integration.md, shows the function and client.release(error).

3. Rotate the production credential

The production password changes only when you ask. Pass rotate_database_credential: true to the call that reaches production: deploy on an application with one environment, 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.

• 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. For that reason the rotation runs only when you ask, not at every deploy or promote. A 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. Call the Database package's migration function before the server starts listening.

The platform never creates your tables. The database it provisions is empty, and your migration files are the only source of its tables. 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.

The function 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 a second copy starts during a deploy, it waits for the first to finish. The Database page describes the function's other behaviour.

A file is applied in a database once that database's schema_migrations table records its name. The function compares names alone, so it never runs an edited file again where the name is recorded. 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 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.

Save each file as UTF-8 without a byte-order mark (BOM). In Windows PowerShell 5.1, -Encoding utf8 writes a BOM, and Out-File and the > redirection write UTF-16 by default. The -Encoding ascii option is safe only where every character in the file is ASCII, because it turns any other character into ?.

To write UTF-8 without a BOM in Windows PowerShell 5.1, use [System.IO.File]::WriteAllText($path, $text), whose default is UTF-8 without a BOM. In PowerShell 7, use -Encoding utf8NoBOM. In an editor, choose UTF-8 without a BOM when you save.

The migration function strips a BOM at the start of a file before it runs the file. A copy of the package older than that change passes the BOM to the server, which rejects the file. With such a copy, save every file without a BOM.

If the function cannot open a connection, or the server ends the connection during the run, a failover is the likely cause. Run the migrations through the package's bootWalk, which runs the function again every second. It fails startup with failover_window_ended only after 120 seconds from the first attempt.

Running the function again is safe, because each applied file is recorded. The server does not listen while it waits, so the application handles no request before the schema is ready. The deploy's health check treats a server that is not yet listening as starting and keeps waiting, as The health check describes.

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.

5. Develop locally

Keep every schema change in an ordered migration file, and note the changes that may remove or rewrite data. Your tool can read the platform's schema-evolution guide with read_context and the id guide:schema_evolution. It describes development tasks such as migrate, reset, seed, status, diff, and snapshot. Run them with your own development tools; the platform has no commands for them.

A local run that loads the development environment file reaches the development database, which is separate from production's on every application, also one with a single environment. 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 (Test your application locally). Use the development database for reset, seed, and other destructive experiments.

Reset development data through APP_DATABASE_URL, the connection string the provision line wrote into .env. Before the reset, your 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 tool tells you, and never resets under that value.

Your 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 locally describes the file.

To start development with an empty database, your tool calls clear_development_database with application and environment: "development". Like a deletion, it needs a person's approval in the browser. 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 line need not run again. read_pending_action returns the outcome with the database and role names.

A deploy, promote, or restart_application of the application started while the clear runs waits for the clear to end, for 45 seconds at most; past that, it fails and you start it again. Once the clear has completed, a development deploy or restart runs against the empty database. 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 tool calls 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 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 delete_environment, as Applications and environments 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 submit_manifest naming local_run to set the database up again, and run the line the response returns.

The migration function applies pending files when the application starts. A promote takes no snapshot, and a roll_back moves code only. 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 build serves apply_migration or restore_snapshot: a call to either answers 501 not_yet_provisioned. Applications and environments lists the actions available now.

The platform measures each database's size daily, and the size counts toward the plan's stored-data allowance. Deleting the development environment drops its database and role, and deleting the application drops both databases and their owner roles. The metering history is kept.

Expected result

submit_manifest returns outcome: "recorded". Once the development database exists, it also returns 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 line 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 and APP_DATABASE_CONNECTION_LIMIT. Over the HTTP API, each rotate_secret returns secret with rotated: true and the value once. For the database credential, it also returns development_database, with connection_limit among its members.

read_status returns connection_limit beside plan, and:

• environments: one member per environment, each with its database. That member contains the database name, the role name, the server host, the time it was created, and the environment, or null where the environment has no database. The development database appears as soon as the submission creates it.
• With tables: true, each member also lists its database's table names as tables.
• 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.

After the first deploy or promote that reaches production, a submission's response also includes database, the production database's connection facts, and never its credential. The application connects through the setting, and the migration function records each applied file in schema_migrations.

Refusals

A refusal gives its cause in detail. Four refusals come after the manifest is recorded, before the development database is complete: plan_quantity_unset for a database, database_provisioning_failed, server_unavailable, and cell_not_configured. Every other submission refusal comes before the manifest is recorded. A refused rotate_secret changes no credential, and a refused set_plan leaves the plan unchanged.

The table covers a database declaration, the provision line's rotate_secret calls, read_status, set_plan, and clear_development_database. The deploy's and the promote's own refusals, which a rotation can also meet, are in Deploy an application. The manifest's other refusals are in Author the manifest, and the refusals page lists every refusal.

Related

• Database describes what the package provides, how its migration function behaves, and its availability.
• Deploy an application covers the deploy and the promote of an application with two environments. Running on your machine describes the line that writes the environment file for a local run.
• Author the manifest covers submitting the declaration and the other refusals a submission can meet.
• Store a secret explains the two platform-minted names in its step 4.
• Use the library copies the package into the project.
• Test your application locally runs the application's tests on the package's test double, with no database.
• Plan and usage covers plans, set_plan, and usage.
• Applications and environments explains the environments, deletion, and the version history.
• submit_manifest, rotate_secret, read_status, and set_plan in the generated reference give each action's arguments and result.
• Glossary defines the terms used on this page.