Skip to content
Install

Use the database

Store a graft’s data in its own Postgres schema. The schema is named ext_<graft_id>, with dashes turned into underscores. Orchard creates it, runs the graft’s migrations in it, and points the graft’s connections at it. A migration writes CREATE TABLE notes and does not name the schema.

Add a folder under migrations/ with a migration.sql file:

migrations/
0001_init/migration.sql
0002_pinned/migration.sql

Folders run in name order, and each runs once. A graft’s pending migrations run in one transaction.

A migration that cannot run in a transaction starts with a marker, and runs on its own:

-- graft:no-transaction
CREATE INDEX CONCURRENTLY notes_body ON notes (body);

graft doctor notes these migrations.

Follow these rules:

  • Do not edit a released migration. Orchard checksums each one and refuses a graft whose history changed. Add a new migration.
  • Support two versions during a rollout. Old and new pods run side by side. Add new structures in one version, and remove what they replace in the next.
  • Change only your own schema. You can read core tables. graft doctor reports a problem for migrations that change them, unless the graft lists them in database.adopts.

Declare the core columns you need in graft.json, and join against them as public.<table>:

{ "database": { "coreReads": { "users": ["id", "username"] } } }
SELECT n.body, u.username
FROM notes n
LEFT JOIN public.users u ON u.id = n.created_by;

Foreign keys to core tables are allowed. Use ON DELETE CASCADE or ON DELETE SET NULL, so that a deleted user or project does not leave orphaned rows.

When the database user can create roles, the graft’s connections run as orchard_ext_<graft_id>. This role can use the graft’s schema and read only the declared columns.

Doors.db gives you a pg pool that points at the schema:

const { rows } = await this.db.query('SELECT * FROM notes WHERE id = $1', [id]);

To use a query builder or an ORM that accepts a pg pool, call this.db.pool().

The pool holds 3 connections per pod by default. To ask for more, up to 50, set this in graft.json:

"database": { "pool": 10 }

Operators can override the size with orchardctl graft pool. Orchard checks the pools of all pods against the database’s connection limit.