ic-sql-migrate

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

Table of Contents

• Features
• Prerequisites
  • For SQLite Support
  • For Turso Support
• Installation
• Deployment Configuration
• Usage
  • 1. Create migration files
  • 2. Set up build.rs
  • 3. Use in your canister
• API Reference
  • Core Functions
  • Build Script Function
  • Macros
• Migration Best Practices
• How It Works
• Examples
• Differences Between Database Backends
• License

Features

• 🚀 Multi-Database Support: Works with SQLite (via ic-rusqlite) and Turso databases
• 📦 Compile-Time Embedding: Migration files are embedded into your binary at compile time
• 🔄 Automatic Migration: Tracks and applies migrations automatically on canister init and upgrade
• 🔒 Transactional: All migrations run in transactions for safety
• 🏗️ ICP Native: Designed specifically for Internet Computer canisters

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 that replaces WebAssembly System Interface (WASI) specific function calls with their corresponding polyfill implementations. This allows you to run Wasm binaries compiled for wasm32-wasi on the Internet Computer.

cargo install wasi2ic

Configure dfx.json

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

Installation

Add to both [dependencies] and [build-dependencies] in your Cargo.toml:

For SQLite support (most common for ICP):

[dependencies]
# Note: You MUST specify either "sqlite" or "turso" feature - there is no default
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]
# Note: You MUST specify either "sqlite" or "turso" feature - there is no default
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

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

Quick Start

1. Create migration files

Create a migrations directory in your project root and add SQL files:

migrations/
├── 001_initial.sql
├── 002_add_users.sql
└── 003_add_indexes.sql

Example migration file (migrations/001_initial.sql):

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. Set up build.rs

Create a build.rs file in your project root:

fn main() {
    // This will embed all SQL files from the migrations directory
    ic_sql_migrate::list(Some("migrations")).unwrap();
}

3. Use in your canister

SQLite Example (with ic-rusqlite):

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

// Include all migrations at compile time
static MIGRATIONS: &[ic_sql_migrate::Migration] = ic_sql_migrate::include!();

fn run_migrations() {
    with_connection(|mut conn| {
        let conn: &mut Connection = &mut conn;
        ic_sql_migrate::sqlite::up(conn, MIGRATIONS).unwrap();
    });
}

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

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

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

Turso Example:

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

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

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

async fn get_connection() -> Connection {
    // Initialize or return existing connection
    // See examples/turso for complete implementation
}

async fn run_migrations() {
    let mut conn = get_connection().await;
    ic_sql_migrate::turso::up(&mut conn, MIGRATIONS).await.unwrap();
}

#[init]
async fn init() {
    // Initialize memory/storage (see examples)
    run_migrations().await;
}

#[pre_upgrade]
fn pre_upgrade() {
    CONNECTION.with_borrow_mut(|c| *c = None);
}

#[post_upgrade]
async fn post_upgrade() {
    // Re-initialize memory/storage
    run_migrations().await;
}

How It Works

1. Build Time: The list() function in build.rs scans your migrations directory and generates code to embed all SQL files into your canister binary.

2. WASI to IC Conversion: The wasi2ic tool converts WASI-specific function calls to IC-compatible polyfills, allowing the WASM binary to run on the Internet Computer.

3. Canister Init/Upgrade:

   • On init: Runs all migrations to set up the database schema
   • On post_upgrade: Runs any new migrations added since the last deployment

4. Migration Tracking: A _migrations table is automatically created to track which migrations have been applied, preventing duplicate execution.

5. Transaction Safety: All pending migrations run in a single transaction. If any migration fails, all changes are rolled back.

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

Examples

Complete working examples are available in the repository:

• examples/sqlite - ICP canister with SQLite
• examples/turso - ICP canister with Turso

Running the SQLite Example

cd examples/sqlite
dfx start --clean
dfx deploy
dfx canister call sqlite run '()'

API Reference

Core Functions

For SQLite

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

Executes all pending migrations synchronously.

For Turso

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

Executes all pending migrations asynchronously.

Build Script Function

pub fn list(migrations_dir_name: Option<&str>) -> std::io::Result<()>

Discovers and embeds migration files at compile time. Call this in build.rs.

Macros

ic_sql_migrate::include!()

Includes all migrations discovered by list() at compile time.

Types

Migration

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

Error

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

Migration Table Schema

The library automatically creates this table:

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

Troubleshooting

Library doesn't compile / "module not found" errors

You must enable either the sqlite or turso feature in your Cargo.toml. The library has no default features and will not work without explicitly selecting a database backend.

"Both features enabled" error

You can only use one database backend at a time. Remove one of the features.

Migrations not found

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

Migration failures

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

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

Acknowledgments

Built specifically for the Internet Computer ecosystem to provide reliable database migrations for canisters using SQL databases.