Database

Database

CWIST embeds SQLite (the amalgamation is compiled into the library) and wraps it with:

• a thin query API that returns rows as cJSON (sql.h);
• a connection pool (pool.h);
• schema migrations (migrate.h);
• Nuke DB, an in-memory read path synced to disk (nuke_db.h);
• envelope encryption of whole database images (db_crypt.h) and encrypted server-to-server replication (db_sync.h).

The ORM builds SQL on top of a database socket. For PostgreSQL, MySQL and MariaDB, CWIST only detects the server (see Application); it has no client for them.

Queries

Header: <cwist/core/db/sql.h>

cwist_db *db = NULL;
cwist_db_open(&db, "app.db");                       /* or ":memory:" */
cwist_db_exec(db, "CREATE TABLE IF NOT EXISTS posts(id INTEGER PRIMARY KEY, title TEXT)");
cwist_db_exec(db, "INSERT INTO posts(title) VALUES ('hello')");

cJSON *rows = NULL;
cwist_error_t err = cwist_db_query(db, "SELECT id, title FROM posts", &rows);
if (cwist_error_is_ok(&err)) {
    /* rows: [{"id":1,"title":"hello"}] */
    cJSON_Delete(rows);
}
cwist_db_close(db);

Function	Description
cwist_error_t cwist_db_open(cwist_db **db, const char *path)	Open a file or ":memory:".
cwist_error_t cwist_db_open_memory(cwist_db **db, const void *buf, size_t len, int readonly)	Open a serialized SQLite image (copied). With readonly, writes fail with SQLITE_READONLY.
cwist_error_t cwist_db_serialize(cwist_db *db, void **out, size_t *out_len)	Serialize the database into a new buffer (free with cwist_free()).
void cwist_db_close(cwist_db *db)	Close.
cwist_error_t cwist_db_exec(cwist_db *db, const char *sql)	Run statements that return no rows. INT16 -1 for a NULL handle or SQL.
cwist_error_t cwist_db_query(cwist_db *db, const char *sql, cJSON **result)	Run a SELECT; *result becomes a cJSON array of row objects, or stays NULL on failure.
cwist_error_t cwist_db_query_strict(db, sql, &result, schema)	Like cwist_db_query() but drops rows that fail Zod-style validation.
cwist_error_t cwist_db_insert_healed(db, table, json_str, schema, heal_cfg)	Heal a possibly broken JSON object, validate it strictly, then INSERT it into table (the table name is used verbatim).

cwist_db exposes the raw sqlite3 *conn for anything the wrapper does not cover (prepared statements with bound parameters, for example). The SQL strings passed to exec and query are executed as given: build them from trusted text only, or use db->conn with sqlite3_bind_*.

The app database

cwist_app_use_db(app, path) opens a database for the app and every request sees it as req->db. cwist_app_get_db(app) returns it.

Connection pool

Header: <cwist/core/db/pool.h>

cwist_db_pool_t *pool = cwist_db_pool_create("app.db", 8);
cwist_db *c = cwist_db_pool_acquire(pool);               /* blocks until one is free */
cwist_db_exec(c, "UPDATE counters SET n = n + 1");
cwist_db_pool_release(pool, c);
cwist_db_pool_destroy(pool);

Function	Description
cwist_db_pool_t *cwist_db_pool_create(const char *path, size_t max_conns)	Pool of up to max_conns connections.
cwist_db *cwist_db_pool_acquire(pool) / cwist_db_pool_acquire_timeout(pool, ms)	Check a connection out (blocking, or with a timeout).
void cwist_db_pool_release(pool, conn)	Return it.
size_t cwist_db_pool_in_use(pool)	Checked-out connections.
cwist_db_pool_exec(pool, sql) / cwist_db_pool_query(pool, sql, &result)	Run one statement on a pooled connection.
void cwist_db_pool_destroy(pool)	Close everything (waits for outstanding leases).
bool cwist_db_pool_destroy_timeout(pool, timeout_ms)	Close to new acquisitions and destroy once leases return; on timeout returns false and the pool stays valid for releases (call again later). -1 waits forever.

cwist_app_use_db_pool(app, path, max_conns) attaches a pool to the app (cwist_app_get_db_pool(app)). Tutorial 6 uses it.

Migrations

Header: <cwist/core/db/migrate.h>

static const cwist_migration_t migrations[] = {
    {1, "create posts", "CREATE TABLE posts(id INTEGER PRIMARY KEY, title TEXT)", "DROP TABLE posts"},
    {2, "add body",     "ALTER TABLE posts ADD COLUMN body TEXT", NULL},   /* irreversible */
};

int rc = cwist_migrate_up(db->conn, migrations, 2);       /* CWIST_MIGRATE_OK on success */
int v  = cwist_migrate_version(db->conn);                 /* 2 */

Function	Description
int cwist_migrate_up(sqlite3 *db, const cwist_migration_t *migrations, int count)	Apply every migration newer than the current version in ascending order (the array need not be sorted). Each runs in its own transaction and is rolled back on failure.
int cwist_migrate_down(sqlite3 *db, const cwist_migration_t *migrations, int count, int steps)	Roll back the last steps migrations (0 = all); entries with down_sql = NULL are skipped.
int cwist_migrate_version(sqlite3 *db)	Highest applied version, 0 for none, -1 on error.

up_sql and down_sql may hold several statements separated by ;. Return codes: CWIST_MIGRATE_OK (0), _ERR_GENERIC (-1), _ERR_SQL (-2), _ERR_ARGS (-3).

Nuke DB

Header: <cwist/core/db/nuke_db.h>

Nuke DB loads a SQLite file into memory, checks it with PRAGMA integrity_check, serves every query from RAM, and writes changes back to the disk file (in WAL mode) after each commit, plus on a timer and at exit. Reads are as fast as an in-memory database; writes pay for durability.

if (cwist_nuke_init("data.db", 5000) == CWIST_NUKE_OK) {   /* periodic sync every 5 s */
    sqlite3 *db = cwist_nuke_get_db();
    /* use db with the SQLite API */
    cwist_nuke_close();                                     /* final sync */
}

Function	Description
int cwist_nuke_init(const char *disk_path, int sync_interval_ms)	Load the file, install SIGINT/SIGTERM handlers that force a final sync, and start the background sync (0 disables the periodic part). Returns CWIST_NUKE_OK, CWIST_NUKE_ERR_GENERIC or CWIST_NUKE_ERR_LOW_MEMORY.
int cwist_nuke_sync(void)	Sync memory to disk now.
void cwist_nuke_close(void)	Final sync and close.
sqlite3 *cwist_nuke_get_db(void)	The in-memory handle, or the disk handle in low-memory fallback mode.
unsigned char *cwist_nuke_serialize(sqlite3_int64 *out_size)	Image of the in-memory database (free with sqlite3_free()).
int cwist_nuke_deserialize(unsigned char *data, sqlite3_int64 len)	Replace the in-memory database with an image allocated by sqlite3_malloc(); SQLite takes ownership.

Nuke DB is a process-wide singleton. If loading fails or memory is low, it falls back to working on the disk file directly. cwist_app_use_nuke_db(app, path, sync_interval_ms) makes it the app database.

Encryption and replication

Headers: <cwist/security/db_crypt/db_crypt.h>, <cwist/net/db_sync/db_sync.h>

db_crypt encrypts a whole SQLite image with a two-level key hierarchy: a random 256-bit data key (DEK) encrypts the bytes with AES-256-CBC, and your key-encryption key (KEK) wraps the DEK. A new DEK and IVs are generated for every seal.

cwist_db_crypt_ctx_t kc;
memcpy(kc.kek, my_32_byte_key, CWIST_DB_CRYPT_KEY_LEN);

size_t sealed_len;
unsigned char *sealed = cwist_db_crypt_seal(&kc, image, image_len, &sealed_len);
size_t plain_len;
unsigned char *plain = cwist_db_crypt_open(&kc, sealed, sealed_len, &plain_len);
free(sealed);
free(plain);          /* both buffers come from malloc(): release with free() */

Function	Description
unsigned char *cwist_db_crypt_seal(ctx, sqlite_bytes, len, &out_len)	Encrypt; returns a malloced blob (free() it) or NULL.
unsigned char *cwist_db_crypt_open(ctx, blob, blob_len, &out_len)	Decrypt; returns a malloced buffer or NULL.

Blob layout: magic CWDB, version 0x01, KEK IV, wrapped DEK, DEK IV, plaintext length, ciphertext (CWIST_DB_CRYPT_HDR_LEN = 109 header bytes). Return codes CWIST_DB_CRYPT_ERR_ARGS, _CRYPTO, _FORMAT, _MEM. The format uses CBC without a MAC, so it provides confidentiality but no tamper detection; authenticate the blob separately if an attacker can modify it. Tutorial 28 and example/db-crypt use it.

db_sync replicates a database between servers over TCP using that format:

Function	Description
int cwist_db_sync_serve(sqlite3 *db, const cwist_db_crypt_ctx_t *ctx, int port)	Serialize and seal db, accept one connection on port (default CWIST_DB_SYNC_DEFAULT_PORT = 9877), send the blob, return. Call it in a loop or thread to serve repeatedly.
int cwist_db_sync_pull(const char *host, int port, const cwist_db_crypt_ctx_t *ctx, unsigned char **out, size_t *out_len)	Connect, receive and decrypt; *out is a plain SQLite image (free() it) to load with sqlite3_deserialize() or cwist_nuke_deserialize() (after copying into sqlite3_malloc memory).

Protocol: the client sends the magic CWSY, the server answers with an 8-byte little-endian length and the sealed blob. Only holders of the KEK can read the data, but the server does not authenticate clients.