# Databases Each Interfold site can have one SQL database. Use it for structured application data, queries, relationships, counters, and concurrent updates. Functions and Cron Jobs access it through `ctx.db`; API, CLI, MCP, and browser-agent callers can also issue a generic D1 query. If your state naturally belongs in a text, JSON, or JSONL file and writes are infrequent, use [Data files](/data-storage) instead. ## Create a database Database commands require an explicit `--site` or `--subdomain` target: ```sh interfold db create --site ``` Creation is idempotent. Running it again safely resumes provisioning or binding updates. It does not apply application migrations. ## Query a database directly Use a write-authorized API key to forward SQL and optional parameter values to the site database without deploying a Function: ```sh interfold db query --site 'SELECT id, name FROM users WHERE active = ?' --params '[true]' ``` The equivalent REST endpoint is: ```http POST /v0/sites/:siteId/databases/:databaseId/query Content-Type: application/json { "sql": "UPDATE users SET active = ? WHERE id = ?", "params": [false, 42] } ``` This is a generic D1-compatible query surface: it can read or change database data and schema, and it returns the D1-style `success`, `errors`, `messages`, and `result` envelope. It requires `WRITE` permission even for `SELECT` statements. Use placeholders for values; D1 evaluates the SQL and returns any statement-level error. Batch and raw-query variants are not part of this first surface. The browser-agent and MCP query tools use the same capability. Browser-agent queries always require user approval because they can change data. ## Cloud migration history Interfold's migration ledger lives in the site database. Agents can inspect it and apply one new migration without access to a local project or every earlier SQL file: ```http GET /v0/sites/:siteId/databases/:databaseId/migrations GET /v0/sites/:siteId/databases/:databaseId/migrations/:migrationName POST /v0/sites/:siteId/databases/:databaseId/migrations ``` The apply body contains `name`, `sql`, and `confirm: true`. One D1 transaction writes the name, SHA-256 checksum, exact SQL, and applied time together with the schema change. A successful receipt is immutable. A confirmed failure creates no migration resource, so corrected SQL may be retried under a name that has never successfully applied. `_interfold_migrations` is the authoritative successful migration resource. A receipt proves that the stored SQL committed with the receipt; it does not assert application-level correctness, represent unmanaged SQL changes, or detect arbitrary schema drift. Legacy receipts created before SQL storage was added remain visible with `sourceAvailable: false`; Interfold does not invent their source. When a request times out, Interfold leaves the short execution lease in place and checks for the receipt on retry. A missing receipt immediately after a timeout is not treated as proof of rollback. Receipt-first execution and the unique migration name prevent the same migration SQL from running twice when a later retry reaches D1. ## CLI migration files Run migration commands from the project. Interfold always uses the project-root `migrations/` directory and searches upward when a command runs in a nested folder. ```text migrations/ 0001_create_users.sql 0002_add_user_email.sql ``` ```sh interfold db migrations create create_users interfold db migrations list --site interfold db migrations apply --site --confirm ``` `list` reports successful cloud migration receipts. `apply` reads the complete ordered migration prefix from the project migration directory, validates it against provider history, and applies pending files. Applied files are immutable: if a schema must change, add a new forward migration. Interfold records names and SHA-256 checksums in `_interfold_migrations`. The Function and Cron Job runtime reserves that exact table, but the generic network query endpoint deliberately passes it through like any other D1 table. Avoid changing the ledger directly: migration validation and recovery rely on its contents. The CLI complete-prefix validation runs as part of `apply`; it is not a separate public operation or a requirement of the cloud migration resource. ## Export a database The Database page in the dashboard can create a full SQL export. Interfold keeps a history of export records so a long-running export remains visible after leaving and returning to the page. Only one export can be in progress for a database. API-key clients can use the equivalent resource endpoints: ```http POST /v0/sites/:siteId/databases/:databaseId/exports GET /v0/sites/:siteId/databases/:databaseId/exports POST /v0/sites/:siteId/databases/:databaseId/exports/:exportId/reconcile ``` When a create or reconcile response is `PROCESSING`, keep calling reconcile with the export ID until the status is `COMPLETE` or `ERROR`. The dashboard does this automatically for processing records; the CLI does not run a poller yet. Completed records remain in history after their temporary download URL expires. ## Runtime interface ```ts const rows = await ctx.db.query('SELECT id, name FROM users WHERE active = ?', [ true, ]) const user = await ctx.db.first('SELECT * FROM users WHERE id = ?', [1]) const result = await ctx.db.execute('INSERT INTO users (name) VALUES (?)', [ 'Ada', ]) ``` `execute()` returns an Interfold-owned result: ```ts { changes: number lastInsertRowId: number | null } ``` `batch()` returns one result per statement with `rows`, `changes`, and `lastInsertRowId`. Provider metadata is never part of the `ctx.db` runtime contract; the direct Query API intentionally preserves the D1 response envelope and metadata. Use SQL placeholders for values. A batch is atomic; separate `ctx.db` calls are not one transaction. Runtime failures expose a stable `DATABASE_*` code without provider diagnostics. ## Permanently delete a database Deletion deliberately takes two commands. The first returns a short-lived code and a warning; the second supplies that code: ```sh interfold db delete --site interfold db delete --site --confirm DELETE-XXXXXXXX ``` The code is bound to that site database and expires after ten minutes. Deletion removes the database binding before deleting the provider resource. Local migration files remain. Export the database first if you need a copy; deletion cannot be undone.