Common Pitfalls When Migrating From SQLite to Turso

Turso maintains SQLite file-format compatibility but requires explicit migration handling for WITHOUT ROWID mutability, generated column constraints, MVCC transaction models, and dynamically loadable extensions.

Turso is a ground-up rewrite of SQLite in Rust that preserves SQL dialect and storage format while introducing asynchronous I/O, MVCC concurrency, and encryption-at-rest. When migrating existing SQLite databases to Turso, you must account for partial feature implementations and experimental flags that gate functionality like WITHOUT ROWID tables and specific PRAGMA settings.

File Format Compatibility Guarantees

Turso can open any standard SQLite .db file without conversion. The storage layer in core/storage/ maintains binary compatibility with SQLite’s B-tree structure, ensuring zero-downtime file migration. However, file format compatibility does not guarantee behavioral compatibility for advanced schema features or concurrency models.

Schema Migration Pitfalls

Several SQLite schema features behave differently or require explicit opt-in flags in Turso.

WITHOUT ROWID Limitations

SQLite supports fully mutable WITHOUT ROWID tables for clustered index optimization. In Turso, as documented in COMPAT.md, WITHOUT ROWID support is partial and insert-only even when enabled with --experimental-without-rowid.

If your existing schema contains:

CREATE TABLE metric (id INTEGER PRIMARY KEY, value TEXT) WITHOUT ROWID;

Turso will reject UPDATE and DELETE operations. You must migrate by removing the clause:

// Recreate table without WITHOUT ROWID
conn.execute(
    "CREATE TABLE metric_new (id INTEGER PRIMARY KEY, value TEXT)",
    (),
)?;
conn.execute("INSERT INTO metric_new SELECT * FROM metric", ())?;
conn.execute("DROP TABLE metric", ())?;
conn.execute("ALTER TABLE metric_new RENAME TO metric", ())?;

Generated Column Constraints

SQLite supports both VIRTUAL and STORED generated columns. Turso, as implemented in core/translate/, supports only virtual generated columns and requires the --experimental-generated-columns flag.

Enable the feature in your database builder:

let db = Builder::new_local("my.db")
    .experimental_generated_columns(true)
    .build()
    .await?;

Then rewrite STORED columns as VIRTUAL:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    username TEXT GENERATED ALWAYS AS (substr(email, 1, instr(email, '@')-1)) VIRTUAL
);

Triggers on Views

Turso does not support INSTEAD OF triggers on views. The parser in parser/src/parser.rs rejects these statements with a "no such table" error. Migration requires refactoring view triggers into application-side logic or replacing views with standard tables.

Concurrency Model and MVCC Differences

Turso implements Multi-Version Concurrency Control (MVCC) in core/mvcc/, a feature absent from vanilla SQLite. While SQLite uses serialized writes, Turso defaults to serializable transactions but supports concurrent writers via BEGIN CONCURRENT.

To utilize MVCC for higher write throughput:

let tx = conn.begin_concurrent()?; // Equivalent to BEGIN CONCURRENT
tx.execute("INSERT INTO logs (msg) VALUES (?)", ("hello",))?;
tx.commit()?;

Without BEGIN CONCURRENT, concurrent writers will serialize, potentially creating bottlenecks that did not exist in your SQLite deployment.

PRAGMA Support and Configuration Gaps

Turso implements a subset of SQLite’s 200+ PRAGMAs. Many write-only pragmas, including auto_vacuum and foreign_key_check, are gated behind experimental flags.

If your migration scripts execute:

PRAGMA auto_vacuum = FULL;

You must enable the experimental flag:

let db = Builder::new_local("my.db")
    .experimental_autovacuum(true)
    .build()
    .await?;

Otherwise, the pragma is silently ignored, causing vacuum behavior divergence between SQLite and Turso deployments.

Extension Loading Architecture

SQLite supports dynamic loading of .so or .dll extensions via load_extension(). Turso disables this for security—only Turso-native extensions compiled into the Rust core are loadable.

As documented in COMPAT.md, custom extensions must be ported to Rust and registered via the Extension API. The bytecode executor in core/vdbe/execute.rs handles only built-in or pre-registered extension functions.

C API Compatibility Gaps

While Turso provides a C API shim in bindings/c_api/src/lib.rs, several SQLite C functions remain unimplemented, including sqlite3_prepare, sqlite3_sql, and certain sqlite3_stmt_status opcodes. Native applications using direct C bindings must verify symbol availability against the compatibility matrix before migration.

Practical Migration Code Examples

The following Rust examples demonstrate handling common migration scenarios.

Opening Existing SQLite Files

use turso::Builder;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let db = Builder::new_local("legacy.db")
        .build()
        .await?;
    let conn = db.connect()?;
    
    // Verify schema compatibility
    let mut rows = conn.query(
        "SELECT name FROM sqlite_master WHERE type='table'", 
        ()
    )?;
    
    while let Some(row) = rows.next()? {
        println!("Table: {}", row.get::<String>(0)?);
    }
    Ok(())
}

Implementing Concurrent Transactions

// For high-throughput writes, use MVCC
let tx = conn.begin_concurrent()?;
tx.execute(
    "UPDATE counters SET value = value + 1 WHERE id = ?", 
    (1,)
)?;
tx.commit()?;

Porting Custom Extensions

Instead of load_extension(), register functions natively:

use turso::extension::ExtensionApi;

fn register_custom(api: &mut ExtensionApi) {
    api.create_scalar_function("my_func", 1, |ctx| {
        let arg = ctx.get_text(0)?;
        ctx.set_result(&format!("processed: {}", arg));
        Ok(())
    });
}

Key Source Files for Reference

Understanding these implementation locations helps debug migration issues:

• SQL Execution: core/vdbe/execute.rs – Bytecode interpreter handling statement execution
• Query Translation: core/translate/ – AST to bytecode compilation where feature flags are checked
• Storage Engine: core/storage/ – File format compatibility layer
• MVCC Implementation: core/mvcc/ – Concurrent transaction handling
• SQL Parser: parser/src/parser.rs – Grammar parsing and unsupported feature detection
• C API Bindings: bindings/c_api/src/lib.rs – SQLite C API compatibility layer
• Compatibility Matrix: COMPAT.md – Definitive source for supported features

Summary

• WITHOUT ROWID tables require schema refactoring as they become insert-only in Turso
• Generated columns must use VIRTUAL instead of STORED and require experimental flags
• Triggers on views are unsupported and require application-side logic migration
• MVCC concurrency requires BEGIN CONCURRENT for write throughput benefits
• PRAGMA settings like auto_vacuum need explicit experimental flags to enable writes
• Extensions cannot be dynamically loaded; they require Rust porting and native registration
• C API has missing symbols that may break native bindings

Frequently Asked Questions

Can Turso open my existing SQLite database files without modification?

Yes. Turso maintains full file-format compatibility with SQLite and can open .db files directly without conversion or export/import steps. However, while the storage format is compatible, certain schema features like WITHOUT ROWID may behave differently once opened.

Why do my UPDATE and DELETE statements fail on WITHOUT ROWID tables?

Turso's WITHOUT ROWID implementation in core/storage/ is partial and experimental. When enabled with --experimental-without-rowid, these tables support only INSERT operations. You must either remove the WITHOUT ROWID clause from your schema or handle the table as append-only during migration.

How do I enable MVCC concurrency in Turso?

Unlike SQLite, Turso supports concurrent writers via Multi-Version Concurrency Control. To enable this, start transactions with BEGIN CONCURRENT (or conn.begin_concurrent() in Rust bindings) instead of the standard BEGIN. Without this flag, Turso serializes writes similarly to SQLite, negating the concurrency benefits.

Where can I find the complete list of unsupported SQLite features?

The definitive compatibility reference is COMPAT.md in the Turso repository root. Additionally, bindings/c_api/src/lib.rs documents the C API coverage, while core/translate/ contains the SQL feature implementation status in the source code.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}