Schema Files

Schema files are an optional, readable description of the tables a database connection has. They live in schemas/, one file per connection, and Scribe compares them with the live database.

Schema files never change the database themselves. scribe schema generate turns the differences into an ordinary .sql migration, which you review and apply with scribe db migrate (see Database Guide).

Projects without a schemas/ directory are unaffected.

Getting Started

New projects

scribe new creates schemas/schema_default.py with no tables yet (--demo projects declare the starter tasks and users tables, matching their migrations). To add a table or column, edit the file and run:

scribe schema apply                  # writes the next migrations/NNN_....sql and runs it

Commit the new migration file along with the schema change. The rest of this guide covers the details.

Existing database

scribe schema pull                   # write schemas/schema_<name>.py for every connection

Pull only reads the database. Every table it adds starts as managed=False, which means Scribe documents it but never reports changes for it. Set managed=True on the tables Scribe should own, then compare:

scribe schema diff

Running pull again later only adds tables the file doesn't mention yet. Tables you've already declared, edited, or marked unmanaged() are never rewritten.

For a large database where your app only uses some of the tables, limit the first pull, and the scope is saved in the file:

scribe schema pull --database erp --include "customers" --include "orders*" --include "reporting.*" --exclude "*_backup"

See scope() below.

Starting empty

scribe schema init                   # one empty schema file per connection

Writing a Schema File

schemas/schema_default.py describes the default connection; schemas/schema_analytics.py describes analytics, and so on.

from scribe.schema import Table, Column, Index, ForeignKey, sql, raw_sql, unmanaged

Table("users",
    Column("id", "INTEGER", primary_key=True, autoincrement=True),
    Column("username", "TEXT", unique=True, nullable=False),
    Column("is_active", "BOOLEAN", default=True),
    Column("created_at", "TIMESTAMP", default=sql("CURRENT_TIMESTAMP")),
)

Table("orders",
    Column("id", "INTEGER", primary_key=True, autoincrement=True),
    Column("total", "NUMERIC(12,2)", nullable=False),
    Column("status", "TEXT", default="open"),
    Column("user_id", "INTEGER", ForeignKey("users.id")),
)

Index("idx_orders_status", "orders", ["status"])

Table(name, *columns, managed=True)

  • managed=True (the default for tables you write): diff compares the table with the database.
  • managed=False: the table is documented but never compared. Columns are optional.
  • On PostgreSQL, use "schema.table" for tables outside the current schema.

Column(name, type, *constraints, ...)

ArgumentMeaning
typeThe connection's own type name, e.g. "INTEGER" on SQLite or "varchar(255)" on PostgreSQL. Scribe doesn't translate types between databases.
ForeignKey("table.column")Optional, passed positionally after the type
primary_key=TruePart of the primary key (set it on several columns for a composite key)
autoincrement=TrueDatabase-assigned values (AUTOINCREMENT, SERIAL/identity, IDENTITY)
nullable=FalseNOT NULL
unique=TrueSingle-column UNIQUE
default=A literal ("open", 0, True) or a SQL expression wrapped in sql("CURRENT_TIMESTAMP")

Index(name, table, columns, unique=False)

Declares an index. Composite unique constraints are declared as unique indexes.

unmanaged(*names)

Lists tables that exist but that Scribe should ignore entirely. diff and status won't report them as untracked.

scope(include=None, exclude=None)

Limits which database tables this file covers. On a database with hundreds of tables, this keeps the file to the ones your app uses.

scope(
    include=["customers", "orders*", "reporting.*"],
    exclude=["*_backup", "*_old"],
)
  • include: if given, only matching tables are in scope. If omitted, every table is.
  • exclude: removes matches from whatever include allows.
  • Patterns are case-insensitive globs. A pattern without a dot matches the table name in any schema (*_backup). A pattern with a dot matches schema.table on PostgreSQL: reporting.* covers a whole schema, and public.orders a single table.
  • Tables declared with Table(...) are always in scope, whether or not a pattern matches them.

Scope affects pull, which only adds in-scope tables, and status, which only reports in-scope tables as untracked and shows how many of the database's tables are in scope. diff, generate, and apply only ever look at declared tables.

pull --include/--exclude writes this call into a new schema file. Once the file exists, change the scope by editing the call.

raw_sql(name, sql)

A named block of SQL for things the DSL can't describe, such as views and expression indexes. These blocks aren't compared with the database. Instead, generate includes a block in a migration when it's new or its text has changed. Write blocks so they're safe to run again (IF NOT EXISTS, CREATE OR REPLACE), and note that IF NOT EXISTS won't update an object that already exists.

Reading the Diff

default (sqlite) — schemas/schema_default.py
  tasks
    + priority: add column INTEGER
    ! title: type TEXT → VARCHAR(200)
  users
    ! pw_hash: add column TEXT (possible rename of password_hash → pw_hash)
    - password_hash: column exists in the database but not in the schema file (...)
  orders
    + create table (4 columns)
  3 additive, 3 need review (1 would drop data)
MarkMeaning
+Additive: a new table, a nullable column or one with a default, a new index
!Needs review: type, nullability, or default changes; anything that can't be done in place
-Would drop data: a column, index, or constraint the database has but the schema file doesn't

A column that disappears while a column of the same type appears is flagged as a possible rename, since running it as a drop plus an add would lose the data.

Spelling differences aren't reported. For example, PostgreSQL's character varying(255) matches varchar(255), 'open'::text matches "open", and MSSQL's ((0)) matches 0.

Not compared: tables marked managed=False or unmanaged(), raw_sql() blocks, expression indexes, and composite foreign keys.

Status

scribe schema status
default (sqlite) — schemas/schema_default.py
  managed:     3 table(s) — 2 in sync, 1 with changes (orders)
  unmanaged:   4 table(s)
  untracked:   1 table(s) in the database aren't in schemas/schema_default.py: audit
               'scribe schema pull --database default' adds them
  changes:     1 additive, 0 need review — 'scribe schema diff --database default' lists them
  migrations:  0 pending

Generating Migrations

scribe schema generate              # write migrations/NNN_schema_<tables>.sql
scribe schema generate --dry-run    # print it instead
scribe db migrate                   # apply it (or let auto_migrate do it on startup)

The connection must have migrations enabled (see Per-connection settings). generate refuses to run while that connection has unapplied migrations, because the diff is computed against the live database and would repeat them.

What goes in the file:

ChangeIn the migration
New table (created after any new tables it references), new nullable column or one with a constant default, new index, new UNIQUE, new raw_sql() blockSQL that runs
New foreign key on an existing columnRuns on PostgreSQL and MSSQL; review on SQLite
Type, nullability, default, or primary key changes; dropped columns, indexes, constraintsCommented out under -- REVIEW
Possible renameCommented out: a RENAME COLUMN statement, plus the add/drop alternative

Uncomment and adjust the REVIEW sections you want before running the file. Dropping a column, for example, deletes its data. Databases may also refuse a change for reasons Scribe can't see: PostgreSQL won't change the type of a column that a view uses until the view is dropped.

SQLite can't change a column's type, nullability, or default, or add constraints to an existing table, in place. For those, the migration contains a commented-out rebuild sequence (create a new table, copy the rows, drop the old one, rename) that copies every column the new definition keeps. It starts with PRAGMA foreign_keys = OFF;: keep that line if other tables reference this one. Scribe turns enforcement off before the migration's transaction, runs PRAGMA foreign_key_check before committing (rolling back if anything is broken), and turns enforcement back on.

If every change needs review, generate writes nothing unless you pass --force.

scribe dev prints a one-line notice at startup when a schema file has changes that aren't in a migration yet.

Applying Changes During Development

scribe schema apply                 # write a migration with the additive changes and run it now
scribe schema apply --dry-run       # print it instead

apply is generate plus db migrate in one step, for local development:

  • It only runs on connections with "auto_migrate": true, which already apply new migrations whenever the app starts. Non-SQLite connections must set the key explicitly. Connections where migrations are applied deliberately (auto_migrate: false, typically production) refuse.
  • It includes only additive changes. Anything that needs review is left out and listed; use generate for those.
  • It runs any pending migrations first, as the next scribe dev would.
  • It writes a normal numbered migration file. Commit that file with your schema change. Every other database (a teammate's, CI, production, or a fresh app.db) is built from migration files, so a change made without one would exist only in your database.
  • If the migration fails, it is rolled back and the file is removed, so nothing is left half-applied.

In the IDE

scribe ide has a Schema tab next to Database and Routes. Pick a connection to see its schema file compared with the live database:

  • Each declared table, colored by status: in sync, changed, not in the database yet, or unmanaged. Changes are listed under the table with the same + / ! / - marks as scribe schema diff. Expand a table to see its declared columns and indexes; click its name to jump to the declaration in the editor.
  • The managed checkbox sets managed=True or managed=False on that table's Table(...) call in the file, leaving the rest of the file as you wrote it.
  • Pull, Generate migration, and Apply do what the commands do. Generate and Apply show the SQL first and only write anything when you confirm; a generated migration opens in the editor for review. They follow the same rules as the CLI: Apply is only offered on connections with "auto_migrate": true, and both are disabled while the connection has pending migrations (Run pending migrations applies them), because the comparison is against the live database.
  • Saving the schema file in the editor compares it again. If the file doesn't load, the panel shows the error and links to the line.
  • With no schema file yet, the tab offers to pull one from the database or create an empty one.

The panel reads the saved file, and re-reads scribe.json each time, so you don't need to restart the IDE after editing either.

Commands

scribe schema init                         # empty schema file per connection
scribe schema pull     [--database name] [--include PATTERN] [--exclude PATTERN]
                                           # add live tables to the schema file (read-only on the database)
scribe schema diff     [--database name]   # compare managed tables with the database (read-only)
scribe schema status   [--database name]   # summary (read-only)
scribe schema generate [--database name] [--name NAME] [--dry-run] [--force]
scribe schema apply    [--database name] [--name NAME] [--dry-run]

Testing against PostgreSQL

tests/test_schema_postgres.py runs the full pull → generate → migrate cycle against a real PostgreSQL database. It is skipped unless SCRIBE_TEST_POSTGRES is set, and it drops and recreates the public and reporting schemas in the database you point it at. The database name must contain test:

SCRIBE_TEST_POSTGRES='{"host": "localhost", "port": 5432, "user": "scribe", "database": "scribe_test"}' pytest tests/test_schema_postgres.py