Database Layer

Kirak’s database layer is fully asynchronous and abstracts SQL dialect differences behind a unified interface. Swapping between MySQL and PostgreSQL requires only a single environment variable change.

---

Architecture

Kirak facade
  +-- DatabaseFactory.create()  (factory.py)
        +-- DatabaseDriver       (drivers/mysql.py | drivers/postgres.py)
              +-- Connection pool (asyncmy | asyncpg)
              +-- Dialect         (dialects/mysql.py | dialects/postgres.py)
                    +-- placeholder()    -> %s (MySQL) | $N (PostgreSQL)
                    +-- like_condition() -> dialect-specific LIKE
                    +-- insert_returning() -> PostgreSQL RETURNING clause
                    +-- supports_returning -> bool

All CRUD operations use the dialect to build SQL – no raw driver strings in operation code.

---

Supported Databases

Database	Extra to install	database.type value
MySQL 8+	pip install "kirak[mysql]"	mysql
PostgreSQL 13+	pip install "kirak[postgres]"	postgres or postgresql

Install both for teams that need to test against multiple engines:

pip install "kirak[all-databases]"

---

Connection Configuration

See Configuration for the full reference. Quick summary, in kirak.json:

"database": {
  "type": "mysql",
  "host": "localhost",
  "port": 3306,
  "user": "root",
  "name": "myapp",
  "pool_min": 1,
  "pool_max": 10,
  "pool_recycle_seconds": 3600
}

The password is a secret and is read from the DB_PASSWORD environment variable.

database.name is the only required key – startup raises ConfigurationError if it is empty.

---

Connection Pool

Kirak uses async connection pools for both drivers:

• MySQL – asyncmy with pool recycling to prevent stale connections after long idle periods (database.pool_recycle_seconds).
• PostgreSQL – asyncpg with a native pool.

Datetimes. Kirak’s datetime columns (created_at, updated_at, deleted_at and every datetime/timestamp field) are TIMESTAMP/DATETIME without a time zone, and hold UTC. A timezone-aware Python datetime (for example datetime.now(timezone.utc)) can be written or used in a filter on both databases: on PostgreSQL the pool converts it to UTC for TIMESTAMP columns (asyncpg alone refuses it), and MySQL accepts it as it is. Values read back are naive and in UTC. A TIMESTAMPTZ column you add yourself is left to asyncpg: pass it an aware value, since asyncpg reads a naive one there as the server’s local time.

The pool is created on startup (during kirak.connect()), shared across all requests, and closed during shutdown (kirak.disconnect()). All CRUD operations use async with kirak.db.acquire() as conn.

Pool sizing guide:

Workload	pool_min	pool_max
Development / low-traffic	1	5
Production / standard	2	20
High-concurrency	5	50

Keep pool_max below your database’s max_connections limit (leave headroom for admin and monitoring tools).

---

Raw SQL Queries

Every module inherits execute_query() from BaseModule for running raw SQL when the CRUD API is insufficient:

# From a hook, background task, or custom route (MySQL syntax -- use $1/$2 for PostgreSQL):
rows = await kirak.execute_query(
    "SELECT p.*, u.email AS author_email FROM posts p JOIN users u ON p.user_id = u.id WHERE p.status = %s",
    params=("published",),
    fetch="all",
)

fetch value	Returns
"all"	List of row dicts
"one"	Single row dict or None
"many"	Fetchmany batch
None	Nothing (for INSERT/UPDATE/DELETE)

For PostgreSQL, use $1, $2, … placeholders. For MySQL, use %s. To write dialect-agnostic queries, use kirak.db.dialect.placeholder(index):

dialect = kirak.db.dialect
sql = f"SELECT * FROM orders WHERE status = {dialect.placeholder(0)} AND user_id = {dialect.placeholder(1)}"
rows = await kirak.execute_query(sql, params=("pending", 42), fetch="all")

For bulk operations that run the same query with different parameters, use execute_many():

await kirak.execute_many(
    "INSERT INTO audit_logs (action, user_id) VALUES (%s, %s)",
    [("login", 1), ("login", 2), ("logout", 1)],
)

execute_many() uses the driver’s native batch execution – more efficient than looping execute_query().

---

Migrations

Kirak’s migration system diffs models.json against a snapshot and generates SQL migration files containing both MySQL and PostgreSQL DDL in clearly-marked sections. Only the section matching database.type in kirak.json is executed.

Initial Setup

kirak db init

What it does:

1. Reads all models (user models + built-in auth models)
2. Generates migrations/{UTC timestamp}_initial.sql (e.g. 20250801100000_initial.sql) with both MySQL and PostgreSQL DDL
3. Writes migrations/models_snapshot.json (used for future diffs)
4. Applies the migration immediately

Output:

Created: migrations/20250801100000_initial.sql
Snapshot: migrations/models_snapshot.json

Applying migration...
Done -- 1 applied, 0 already applied.

Incremental Migrations

After adding, removing, or changing fields in models.json:

kirak db makemigrations add-status-field
kirak db migrate

makemigrations diffs the current models against the snapshot and creates a new numbered file. migrate applies any unapplied files in order.

Migration Tracking

Kirak creates a kirak_migrations table to track which files have been applied. This table is created automatically on first migrate or db init.

Commands Reference

Command	Description
kirak db init [--name NAME]	Generate + apply initial migration. Fails if migrations already exist.
kirak db makemigrations [NAME]	Diff models vs snapshot -> generate incremental migration file.
kirak db migrate	Apply all pending migrations in version order.
kirak db status	Show applied/pending/modified status of all migration files.
kirak db reset --force	Destructive. Drop ALL tables. Development only.

Migration File Format

Every migration file, the first one from kirak db init included, is named {UTC timestamp}_{name}.sql (dashes in name become underscores), not a sequential number:

-- Kirak Migration: 20250820103000_add_status_field
-- Generated: 2025-08-20T10:30:00Z

-- BEGIN:MYSQL
ALTER TABLE posts ADD COLUMN status VARCHAR(50) DEFAULT 'draft' NOT NULL;
-- END:MYSQL

-- BEGIN:POSTGRESQL
ALTER TABLE posts ADD COLUMN status VARCHAR(50) DEFAULT 'draft' NOT NULL;
-- END:POSTGRESQL

Files are plain SQL – edit them before applying if you need custom DDL (indexes, stored procedures, data migrations).

---

Dialect Differences

Kirak handles these dialect differences transparently:

Feature	MySQL	PostgreSQL
Placeholders	%s	$1, $2, …
Returning inserted ID	cursor.lastrowid	RETURNING id clause
Case-insensitive LIKE	LOWER(field) LIKE LOWER(?)	field ILIKE ?
Booleans	TINYINT(1)	BOOLEAN
Auto-increment	AUTO_INCREMENT	SERIAL
JSON columns	JSON	JSONB
Timestamp default	CURRENT_TIMESTAMP	NOW()

If you need dialect-specific raw SQL in a hook, check:

is_postgres = kirak.db.dialect.name == "postgres"

---

pool_recycle_seconds and MySQL

MySQL silently closes idle connections after wait_timeout (default 8 hours). Without pool recycling, you will see MySQL server has gone away errors on cold mornings.

"pool_recycle_seconds": 3600 (the default) causes asyncmy to replace any connection older than one hour. Set to a value shorter than your MySQL wait_timeout:

SHOW VARIABLES LIKE 'wait_timeout';
-- typical: 28800 (8 hours)

PostgreSQL does not have this issue – pool_recycle_seconds is ignored for the asyncpg driver.