Database Guide

Learn how to work with databases in ScribeEngine.

Supported Databases

ScribeEngine has native adapters for four databases. SQLite requires no additional packages.

Databasetype valuePackage required
SQLitesqliteNone (built into Python)
PostgreSQLpostgresqlpsycopg2-binary
MySQLmysqlpymysql
MSSQLmssqlpymssql

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:

KeyValuesDefault
migrationsFolder path, true (uses migrations/ for default, migrations/<name> otherwise), or false"migrations" for default; disabled for other connections
auto_migratetrue: apply pending migrations when scribe dev, scribe ide, or scribe serve starts. false: only report themtrue 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.