ic-sql-migrate

A lightweight database migration library for Internet Computer (ICP) canisters with support for SQLite (via ic-rusqlite) and Turso databases.

Table of Contents

• Installation
• Deployment Configuration
• Usage
  • 1. Create Migration Files
  • 2. Set Up build.rs
  • 3. Use in Your Canister
• Data Seeding
• API Reference
• How It Works
• Migration Best Practices
• Troubleshooting
• Examples
• Differences Between Database Backends
• Contributing
• License

Installation

Prerequisites

IMPORTANT: You must enable exactly one database feature (sqlite or turso) for this library to work. There is no default feature.

In addition to having the Rust toolchain setup and dfx, you need to install the wasi2ic tool (for SQLite only) that replaces WebAssembly System Interface (WASI) specific function calls with their corresponding polyfill implementations:

cargo install wasi2ic

Add to Cargo.toml

For SQLite support (most common for ICP):

[dependencies]
ic-sql-migrate = { version = "0.0.4", features = ["sqlite"] }
ic-rusqlite = { version = "0.4.2", features = ["precompiled"], default-features = false }
ic-cdk = "0.18.7"

[build-dependencies]
ic-sql-migrate = "0.0.4"

For Turso support:

[dependencies]
ic-sql-migrate = { version = "0.0.4", features = ["turso"] }
turso = "0.1.4"
ic-cdk = "0.18.7"

[build-dependencies]
ic-sql-migrate = "0.0.4"

Important:

• You MUST choose exactly one database feature (sqlite or turso)
• The features are mutually exclusive (cannot use both)
• There is no default feature - the library will not work without selecting one

Deployment Configuration

dfx.json Setup (Required for SQLite)

For SQLite support, you need to configure your dfx.json to compile for the wasm32-wasip1 target and use wasi2ic to process the binary:

{
  "canisters": {
    "your_canister": {
      "candid": "your_canister.did",
      "package": "your_canister",
      "type": "custom",
      "build": [
        "cargo build --target wasm32-wasip1 --release",
        "wasi2ic target/wasm32-wasip1/release/your_canister.wasm target/wasm32-wasip1/release/your_canister-wasi2ic.wasm"
      ],
      "wasm": "target/wasm32-wasip1/release/your_canister-wasi2ic.wasm"
    }
  }
}

This configuration:

1. Compiles your canister for the wasm32-wasip1 target (required for SQLite)
2. Uses wasi2ic to convert WASI function calls to IC-compatible polyfills
3. Points dfx to the processed WASM file for deployment

Note: Turso canisters use the standard wasm32-unknown-unknown target and don't require wasi2ic processing.

Usage

1. Create Migration Files

Create a migrations/ directory with SQL files. Each migration should be:

• Numbered sequentially (e.g., 000_initial.sql, 001_add_users.sql)
• Idempotent when possible (use IF NOT EXISTS clauses)
• Forward-only (this library doesn't support rollbacks)

Example migration file:

-- migrations/000_initial.sql
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT
);

2. Set Up build.rs

Use the Builder to configure discovery of migrations and seeds at compile time:

fn main() {
    ic_sql_migrate::Builder::new()
        .with_migrations_dir("migrations")
        .with_seeds_dir("src/seeds")
        .build()
        .unwrap();
}

The Builder automatically handles missing directories by generating empty arrays.

3. Use in Your Canister

SQLite Example

use ic_cdk::{init, post_upgrade, pre_upgrade};
use ic_rusqlite::{close_connection, with_connection, Connection};

mod seeds;

static MIGRATIONS: &[ic_sql_migrate::Migration] = ic_sql_migrate::include_migrations!();

fn run_migrations_and_seeds() {
    with_connection(|mut conn| {
        let conn: &mut Connection = &mut conn;
        ic_sql_migrate::sqlite::migrate(conn, MIGRATIONS).unwrap();
        ic_sql_migrate::sqlite::seed(conn, seeds::SEEDS).unwrap();
    });
}

#[init]
fn init() {
    run_migrations_and_seeds();
}

#[pre_upgrade]
fn pre_upgrade() {
    close_connection();
}

#[post_upgrade]
fn post_upgrade() {
    run_migrations_and_seeds();
}

Turso Example

use ic_cdk::{init, post_upgrade, pre_upgrade};
use turso::Connection;

mod seeds;

static MIGRATIONS: &[ic_sql_migrate::Migration] = ic_sql_migrate::include_migrations!();

thread_local! {
    static CONNECTION: RefCell<Option<Connection>> = const { RefCell::new(None) };
}

async fn get_connection() -> Connection {
    if let Some(conn) = CONNECTION.with_borrow(|c| c.clone()) {
        conn
    } else {
        // Initialize connection
        init_db().await
    }
}

async fn run_migrations_and_seeds() {
    let mut conn = get_connection().await;
    ic_sql_migrate::turso::migrate(&mut conn, MIGRATIONS).await.unwrap();
    ic_sql_migrate::turso::seed(&mut conn, seeds::SEEDS).await.unwrap();
}

#[init]
async fn init() {
    run_migrations_and_seeds().await;
}

#[post_upgrade]
async fn post_upgrade() {
    run_migrations_and_seeds().await;
}

Data Seeding

In addition to schema migrations, this library supports data seeding using Rust functions. Seeds are useful for populating initial data, test data, or reference data.

Creating Seed Files

Create seed files in the src/seeds/ directory (or a custom directory specified in build.rs). Each seed file is a regular Rust module (.rs file) that exports a seed function.

Seed files are executed in alphabetical order by filename, so use a sortable prefix:

• src/seeds/seed_001_initial_users.rs
• src/seeds/seed_002_categories.rs

SQLite Seed Example

File: src/seeds/seed_001_initial_users.rs

use ic_sql_migrate::MigrateResult;
use ic_rusqlite::Connection;

pub fn seed(conn: &Connection) -> MigrateResult<()> {
    conn.execute(
        "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')",
        [],
    )?;
    conn.execute(
        "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')",
        [],
    )?;
    Ok(())
}

Turso Seed Example

File: src/seeds/seed_001_initial_users.rs

use ic_sql_migrate::MigrateResult;
use turso::Connection;
use std::pin::Pin;
use std::future::Future;

pub fn seed(conn: &Connection) -> Pin<Box<dyn Future<Output = MigrateResult<()>> + Send>> {
    let conn = conn.clone();
    Box::pin(async move {
        conn.execute(
            "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')",
            (),
        ).await?;
        Ok(())
    })
}

Using Seeds in Your Canister

Step 1: Add the seeds module to your src/lib.rs:

mod seeds;  // This is auto-generated by the build script

Step 2: Use seeds in your lifecycle functions (see examples above)

Seed Best Practices

1. Naming Convention: Use sequential numbering with descriptive names (e.g., seed_001_initial_users.rs)
2. One Seed Per File: Each seed file should contain a single pub fn seed() function
3. Part of Source Tree: Seeds are in src/seeds/, giving you full IDE support and access to your app code
4. Import from Your App: You can import types, functions, and modules from your application using crate::
5. Forward-Only: Seeds do not support rollbacks - once applied, they remain
6. Idempotent Functions: Write seed functions that can safely run multiple times if needed
7. Alphabetical Order: Seeds are executed alphabetically by filename
8. Run After Migrations: Seeds always execute after migrations to ensure schema is ready

API Reference

Core Functions

Migrations

For SQLite:

pub fn migrate(conn: &mut rusqlite::Connection, migrations: &[Migration]) -> MigrateResult<()>

Executes all pending migrations synchronously.

For Turso:

pub async fn migrate(conn: &mut turso::Connection, migrations: &[Migration]) -> MigrateResult<()>

Executes all pending migrations asynchronously.

Seeds

For SQLite:

pub fn seed(conn: &mut rusqlite::Connection, seeds: &[Seed]) -> MigrateResult<()>

Executes all pending seeds synchronously.

For Turso:

pub async fn seed(conn: &mut turso::Connection, seeds: &[Seed]) -> MigrateResult<()>

Executes all pending seeds asynchronously.

Build Script

Builder::new()

Creates a new builder with default settings.

// Use defaults (migrations/ and src/seeds/)
ic_sql_migrate::Builder::new().build().unwrap();

// Custom directories
ic_sql_migrate::Builder::new()
    .with_migrations_dir("db/migrations")
    .with_seeds_dir("src/db/seeds")
    .build()
    .unwrap();

Note: Missing directories are handled automatically - they generate empty arrays.

Macros

ic_sql_migrate::include_migrations!()

Includes all migrations discovered by the Builder at compile time.

static MIGRATIONS: &[ic_sql_migrate::Migration] = ic_sql_migrate::include_migrations!();

ic_sql_migrate::seeds!()

Helper macro to manually create a static array of seeds (for advanced use cases).

static SEEDS: &[ic_sql_migrate::Seed] = ic_sql_migrate::seeds![
    Seed::new("001_users", my_seed_fn),
];

Note: In most cases, seeds are auto-discovered from src/seeds/ and accessed via the generated mod seeds module.

Types

Migration

pub struct Migration {
    pub id: &'static str,    // Unique identifier (filename without extension)
    pub sql: &'static str,   // SQL statements to execute
}

Seed

pub struct Seed {
    pub id: &'static str,          // Unique identifier
    pub seed_fn: SeedFn,           // Function to execute
}

Error

Custom error type that wraps database-specific errors and migration/seed failures.

Database Schema

The library automatically creates these tracking tables:

Migrations Table:

CREATE TABLE _migrations (
    id TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)

Seeds Table:

CREATE TABLE _seeds (
    id TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)

How It Works

1. Build Time: Builder in build.rs scans your migrations and seeds directories

   • Migrations: SQL files embedded as static strings into your canister binary
   • Seeds: Rust modules discovered and auto-generated into src/seeds/mod.rs with a SEEDS constant

2. WASI to IC Conversion: The wasi2ic tool converts WASI-specific function calls to IC-compatible polyfills (SQLite only)

3. Canister Init/Upgrade:

   • On init: Calls migrate() to set up the database schema, then calls seed() to populate initial data
   • On post_upgrade: Calls migrate() and seed() to apply any new migrations and seeds

4. Migration Tracking:

   • A _migrations table is automatically created to track which migrations have been applied
   • Pending migrations are executed in alphabetical order within a transaction
   • Each successful migration is recorded to prevent duplicate execution

5. Seed Tracking:

   • A _seeds table is automatically created to track which seeds have been applied
   • Pending seeds are executed in alphabetical order within transactions
   • Each successful seed is recorded to prevent duplicate execution

6. Transaction Safety: All pending migrations and seeds run in transactions. If any operation fails, changes are rolled back, ensuring data consistency.

Migration Best Practices

1. Naming Convention: Use sequential numbering like 001_description.sql, 002_description.sql to ensure correct execution order

2. Forward-Only: This library only supports forward migrations (no rollbacks). Plan your schema changes carefully.

3. Idempotent SQL: While migrations are tracked, write idempotent SQL when possible using IF NOT EXISTS clauses

4. Small Changes: Keep each migration focused on a single logical change

5. Test Locally: Always test migrations using dfx deploy --local before mainnet deployment

6. Document Changes: Include comments in your migration files explaining what each migration does

Troubleshooting

"Both features enabled" error

You can only use one database backend at a time. Ensure exactly one of sqlite or turso is enabled in your Cargo.toml.

Migrations not found

Ensure your migrations directory exists and contains .sql files, and that build.rs is properly configured to point to it.

"wasi2ic: command not found"

Install the wasi2ic tool:

cargo install wasi2ic

Migration failures

Check the canister logs with dfx canister logs <canister_name> for detailed error messages. Common issues:

• Invalid SQL syntax in migration files
• Trying to create tables that already exist (use IF NOT EXISTS)
• Foreign key constraint violations

Seeds not executing

Verify:

• Seed files are in the src/seeds/ directory (or configured directory)
• Each seed file exports a pub fn seed() function
• The module is declared in your canister code: mod seeds;

Examples

Complete working examples are available in the repository:

• examples/sqlite - Advanced example with the Chinook database and complex queries
• examples/turso - Turso integration example with basic migrations

Running the SQLite Example

cd examples/sqlite
dfx start --clean
dfx deploy
dfx canister call sqlite-example verify_migrations

Running the Turso Example

cd examples/turso
dfx start --clean
dfx deploy
dfx canister call turso run

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Author

Kristofer Lund