# Common Pitfalls When Migrating From SQLite to Turso

> Migrating from SQLite to Turso? Learn about common pitfalls like WITHOUT ROWID, generated columns, MVCC, and extensions. Ensure a smooth transition.

- Repository: [Turso Database/turso](https://github.com/tursodatabase/turso)
- Tags: migration-guide
- Published: 2026-06-22

---

**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`](https://github.com/tursodatabase/turso/blob/main/COMPAT.md), `WITHOUT ROWID` support is **partial and insert-only** even when enabled with `--experimental-without-rowid`.

If your existing schema contains:

```sql
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:

```rust
// 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:

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

```

Then rewrite `STORED` columns as `VIRTUAL`:

```sql
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`](https://github.com/tursodatabase/turso/blob/main/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:

```rust
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:

```sql
PRAGMA auto_vacuum = FULL;

```

You must enable the experimental flag:

```rust
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`](https://github.com/tursodatabase/turso/blob/main/COMPAT.md), custom extensions must be ported to Rust and registered via the Extension API. The bytecode executor in [`core/vdbe/execute.rs`](https://github.com/tursodatabase/turso/blob/main/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`](https://github.com/tursodatabase/turso/blob/main/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

```rust
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

```rust
// 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:

```rust
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`](https://github.com/tursodatabase/turso/blob/main/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`](https://github.com/tursodatabase/turso/blob/main/parser/src/parser.rs) – Grammar parsing and unsupported feature detection
- **C API Bindings**: [`bindings/c_api/src/lib.rs`](https://github.com/tursodatabase/turso/blob/main/bindings/c_api/src/lib.rs) – SQLite C API compatibility layer
- **Compatibility Matrix**: [`COMPAT.md`](https://github.com/tursodatabase/turso/blob/main/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`](https://github.com/tursodatabase/turso/blob/main/COMPAT.md) in the Turso repository root. Additionally, [`bindings/c_api/src/lib.rs`](https://github.com/tursodatabase/turso/blob/main/bindings/c_api/src/lib.rs) documents the C API coverage, while `core/translate/` contains the SQL feature implementation status in the source code.