Docs menu
Docs/Platform/Databases
View as Markdown

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 instead.

Create a database

Database commands require an explicit --site or --subdomain target:

interfold db create --site <site-id>

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:

interfold db query --site <site-id> 'SELECT id, name FROM users WHERE active = ?' --params '[true]'

The equivalent REST endpoint is:

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:

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.

migrations/
  0001_create_users.sql
  0002_add_user_email.sql

interfold db migrations create create_users
interfold db migrations list --site <site-id>
interfold db migrations apply --site <site-id> --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:

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

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:

{
  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:

interfold db delete --site <site-id>
interfold db delete --site <site-id> --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.

On this page