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"
    }
  }
}

PostgreSQL

{
  "databases": {
    "default": {
      "type": "postgresql",
      "host": "localhost",
      "port": 5432,
      "user": "myapp_user",
      "password": "USE_ENV_VARIABLE",
      "database": "myapp_db"
    }
  }
}

MySQL

{
  "databases": {
    "default": {
      "type": "mysql",
      "host": "localhost",
      "port": 3306,
      "user": "myapp_user",
      "password": "USE_ENV_VARIABLE",
      "database": "myapp_db"
    }
  }
}

Microsoft SQL Server

{
  "databases": {
    "default": {
      "type": "mssql",
      "host": "localhost",
      "port": 1433,
      "user": "sa",
      "password": "USE_ENV_VARIABLE",
      "database": "myapp_db"
    }
  }
}

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"
    }
  }
}

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

Create migrations/002_add_posts.sql:

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)
);

Run: scribe db migrate

Migrations run in numerical order (001_, 002_, etc.).

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.