Databases

The eventual goal for Azle is to support as many database solutions as possible. This is difficult for a number of reasons related to ICP's decentralized computing paradigm and Wasm environment.

SQLite is the current recommended approach to databases with Azle. We plan to provide Postgres support through pglite next.

Azle has good support for SQLite through sql.js. It also has good support for ORMs like Drizzle and TypeORM using sql.js.

The following examples should be very useful as you get started using SQLite in Azle:

Examples:

• sqlite
• sqlite_drizzle
• sqlite_typeorm

sql.js

SQLite in Azle works using an asm.js build of SQLite from sql.js without modifications to the library. The database is stored entirely in memory on the heap, giving you ~2 GiB of space. Serialization across upgrades is possible using stable memory like this:

// src/index.its

import {
    init,
    postUpgrade,
    preUpgrade,
    Server,
    StableBTreeMap,
    stableJson
} from 'azle/experimental';
import { Database } from 'sql.js/dist/sql-asm.js';

import { initDb } from './db';
import { initServer } from './server';

export let db: Database;

let stableDbMap = StableBTreeMap<'DATABASE', Uint8Array>(0, stableJson, {
    toBytes: (data: Uint8Array) => data,
    fromBytes: (bytes: Uint8Array) => bytes
});

export default Server(initServer, {
    init: init([], async () => {
        db = await initDb();
    }),
    preUpgrade: preUpgrade(() => {
        stableDbMap.insert('DATABASE', db.export());
    }),
    postUpgrade: postUpgrade([], async () => {
        db = await initDb(stableDbMap.get('DATABASE').Some);
    })
});

// src/db/index.ts

import initSqlJs, {
    Database,
    QueryExecResult,
    SqlValue
} from 'sql.js/dist/sql-asm.js';

import { migrations } from './migrations';

export async function initDb(
    bytes: Uint8Array = Uint8Array.from([])
): Promise<Database> {
    const SQL = await initSqlJs({});

    let db = new SQL.Database(bytes);

    if (bytes.length === 0) {
        for (const migration of migrations) {
            db.run(migration);
        }
    }

    return db;
}