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):diffcompares 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, ...)
| Argument | Meaning |
|---|---|
type | The 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=True | Part of the primary key (set it on several columns for a composite key) |
autoincrement=True | Database-assigned values (AUTOINCREMENT, SERIAL/identity, IDENTITY) |
nullable=False | NOT NULL |
unique=True | Single-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 whateverincludeallows.- Patterns are case-insensitive globs. A pattern without a dot matches the table name in any schema (
*_backup). A pattern with a dot matchesschema.tableon PostgreSQL:reporting.*covers a whole schema, andpublic.ordersa 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)
| Mark | Meaning |
|---|---|
+ | 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:
| Change | In 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() block | SQL that runs |
| New foreign key on an existing column | Runs on PostgreSQL and MSSQL; review on SQLite |
| Type, nullability, default, or primary key changes; dropped columns, indexes, constraints | Commented out under -- REVIEW |
| Possible rename | Commented 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
generatefor those. - It runs any pending migrations first, as the next
scribe devwould. - 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 asscribe 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=Trueormanaged=Falseon that table'sTable(...)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