Database Guide
Learn how to work with databases in ScribeEngine.
Supported Databases
ScribeEngine has native adapters for four databases. SQLite requires no additional packages.
| Database | type value | Package required |
|---|---|---|
| SQLite | sqlite | None (built into Python) |
| PostgreSQL | postgresql | psycopg2-binary |
| MySQL | mysql | pymysql |
| MSSQL | mssql | pymssql |
All four adapters share the same query API — db['default'].query(), .find(), .insert(), etc. — so switching databases is a config-only change for most apps.
Configuration
Edit scribe.json:
{
"databases": {
"default": {
"type": "sqlite",
"database": "app.db",
"migrations": "migrations",
"auto_migrate": true
}
}
}
migrations and auto_migrate control whether Scribe changes this database. See Migrations.
PostgreSQL
{
"databases": {
"default": {
"type": "postgresql",
"host": "localhost",
"port": 5432,
"user": "myapp_user",
"password": "USE_ENV_VARIABLE",
"database": "myapp_db",
"migrations": "migrations",
"auto_migrate": false
}
}
}
MySQL
{
"databases": {
"default": {
"type": "mysql",
"host": "localhost",
"port": 3306,
"user": "myapp_user",
"password": "USE_ENV_VARIABLE",
"database": "myapp_db",
"migrations": "migrations",
"auto_migrate": false
}
}
}
Microsoft SQL Server
{
"databases": {
"default": {
"type": "mssql",
"host": "localhost",
"port": 1433,
"user": "sa",
"password": "USE_ENV_VARIABLE",
"database": "myapp_db",
"migrations": "migrations",
"auto_migrate": false
}
}
}
For server databases, always set migrations and auto_migrate explicitly (see Per-connection settings). Use "migrations": false for a database whose schema is managed elsewhere.
Multiple Databases
You can connect to multiple databases simultaneously:
{
"databases": {
"default": {
"type": "sqlite",
"database": "app.db"
},
"analytics": {
"type": "postgresql",
"host": "localhost",
"port": 5432,
"user": "dbuser",
"password": "secret",
"database": "analytics",
"migrations": false
}
}
}
Access: db['default'], db['analytics']
Query Methods
Raw SQL
{$
users = db['default'].query("SELECT * FROM users")
user = db['default'].query(
"SELECT * FROM users WHERE id = ?",
(user_id,)
)
$}
Find by ID
{$
user = db['default'].find('users', 123)
# Returns: {'id': 123, 'username': 'alice', ...}
$}
Insert
{$
user_id = db['default'].insert('users',
username='alice',
email='[email protected]',
password_hash=hash_password('secret')
)
$}
Update
{$
db['default'].update('users',
{'email': '[email protected]'},
id=user_id
)
$}
Delete
{$
db['default'].delete('users', id=user_id)
$}
Migrations
Migrations are .sql files, applied in filename order (001_, 002_, ...). Each applied file is recorded in a _migrations table in that database, so it only ever runs once.
Each file runs in a single transaction: if any statement fails, the whole file is rolled back and isn't recorded. (A SQLite file that contains its own BEGIN/COMMIT or VACUUM runs as written instead. MSSQL rolls back most, but not all, DDL.)
Create one with scribe db new-migration add_posts, which writes migrations/003_add_posts.sql (numbered after the existing files):
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT,
user_id INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
Apply it:
scribe db status # show applied and pending migrations (read-only)
scribe db migrate # apply pending migrations
Per-connection settings
Each connection block in scribe.json decides whether Scribe may change that database:
| Key | Values | Default |
|---|---|---|
migrations | Folder path, true (uses migrations/ for default, migrations/<name> otherwise), or false | "migrations" for default; disabled for other connections |
auto_migrate | true: apply pending migrations when scribe dev, scribe ide, or scribe serve starts. false: only report them | true for default; false for other connections |
New projects from scribe new set both explicitly on the SQLite default connection, so migrations (including the starter tables of a --demo project) run on the first scribe dev.
Switching default to PostgreSQL or MSSQL? Revisit these keys in the same block. Migrations written so far use SQLite syntax, and with auto_migrate on they will run against the new database at startup. schemas/schema_default.py also uses SQLite column types; replace it with scribe schema pull output from the new database, or delete it. Set "migrations": false if Scribe should never change it, or "auto_migrate": false to apply migrations yourself with scribe db migrate.
If a PostgreSQL or MSSQL default connection doesn't set migrations (typical for an app that connects to a database whose schema is managed elsewhere), Scribe still treats migrations/ as its folder, as it always has. Startup prints a warning about it every time until the key is set, and scribe db new-migration, scribe schema generate, and scribe schema apply refuse to create migrations for it. Add "migrations": false for a database Scribe should never change, or "migrations": "migrations" if it should manage the schema. (If only auto_migrate is missing, startup prints a warning before running anything.)
Migrations for other connections
Other connections opt in by naming a folder:
"analytics": {
"type": "postgresql",
"database": "analytics",
"migrations": "migrations/analytics",
"auto_migrate": false
}
scribe db new-migration add_page_views --database analytics
scribe db migrate --database analytics
scribe db migrate # every connection with migrations enabled
Folders are never matched to connections by name, so a migrations/analytics/ folder does nothing until analytics points to it. Two connections cannot share a folder.
Schema files
To keep a readable description of each database's tables and compare it with the live database, see Schema Files.
Startup flags
scribe dev, scribe ide, and scribe serve accept --migrate / --no-migrate to override auto_migrate for every connection for one run.
Best Practices
Always Use Parameterized Queries
# ✅ SAFE
users = db['default'].query(
"SELECT * FROM users WHERE username = ?",
(username,)
)
# ❌ DANGEROUS (SQL injection vulnerability)
users = db['default'].query(
f"SELECT * FROM users WHERE username = {username}"
)
Check for Empty Results
{$
users = db['default'].query("SELECT * FROM users WHERE id = ?", (user_id,))
if users:
user = users[0]
else:
return abort(404)
$}
See Template Syntax for more examples.