Turbosql

Turbosql automates database schema management via the #[derive(Turbosql)] macro:

1. Generation: At compile time, the macro generates a migrations.toml file in your project root describing the schema.
2. Migration: When you change a struct, the macro generates new SQL migration statements and appends them to migrations.toml. These migrations are embedded in your binary.
3. Application: On launch, the binary automatically applies any pending migrations in sequence to the existing database file.

Important Migration Rules:

• One-way Ratchet: Migrations are append-only. An older binary cannot operate on a database file that has been migrated to a newer schema.
• Manual Edits: You can manually remove unused or reverted migrations from migrations.toml before release, but any database that has already applied them will error and require a rebuild.
• Custom Migrations: You can manually add your own SQL migration entries (e.g., for creating indexes) to the bottom of the migrations_append_only list in migrations.toml.

Because SQLite uses blocking synchronous APIs, you should run Turbosql operations within a thread pool using your executor's blocking task facility (e.g., tokio::task::spawn_blocking).

Transactions in Async

Turbosql uses persistent thread_local database connections. A continuous sequence of database calls from the same thread is guaranteed to use the same exclusive connection. This allows you to perform transactions by executing BEGIN IMMEDIATE TRANSACTION and COMMIT manually within a spawn_blocking block.

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    tokio::task::spawn_blocking(|| -> Result<(), turbosql::Error> {
        Person { rowid: None, age: Some(21) }.insert()?;
        execute!("BEGIN IMMEDIATE TRANSACTION")?;
        let p = select!(Person "WHERE rowid = ?", 1)?;
        execute!(
            "UPDATE person SET age = ? WHERE rowid = ?",
            p.age.unwrap_or_default() + 1,
            1
        )?;
        execute!("COMMIT")?;
        Ok(())
    }).await??;
    Ok(())
}

Turbosql provides a high-level API for CRUD operations using Rust structs. To use it, derive Turbosql on your struct. Note that a rowid: Option<i64> member is required and enforced at compile time for all Turbosql structs.

Common operations include:

• .insert(): Creates a new row and returns the rowid.
• select!(): Retrieves data using the select! macro.
• .update(): Updates the database row with the current values in the struct (based on rowid).
• execute!(): Runs manual SQL commands.

use turbosql::{Turbosql, select, execute};

#[derive(Turbosql, Default)]
struct Person {
    rowid: Option<i64>, // rowid member required & enforced at compile time
    name: Option<String>,
    age: Option<i64>,
    image_jpg: Option<Vec<u8>>
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let name = "Joe";

    // INSERT a row
    let rowid = Person {
        name: Some(name.to_string()),
        age: Some(42),
        ..Default::default()
    }.insert()?;

    // SELECT all rows
    let people = select!(Vec<Person>)?;

    // SELECT multiple rows with a predicate
    let people = select!(Vec<Person> "WHERE age > " 21)?;

    // SELECT a single row with a predicate
    let mut person = select!(Person "WHERE name = " name)?;

    // UPDATE based on rowid, rewrites all fields in database row
    person.age = Some(43);
    person.update()?;

    // UPDATE with manual SQL
    execute!("UPDATE person SET age = " 44 " WHERE name = " name)?;

    // DELETE
    execute!("DELETE FROM person WHERE rowid = " 1)?;

    Ok()
}

The select! macro supports various return types and syntax patterns:

When you use #[derive(Turbosql)] on a struct, it implements the Turbosql trait, providing high-level methods for database interaction.

Key requirements for rowid:

• For insert operations (insert, insert_mut, insert_batch), the rowid field in your struct must be None.
• For update and delete operations, the rowid field must be Some(id).

Methods:

• insert(&self) -> Result<i64, Error>: Inserts the row and returns the new rowid.
• insert_mut(&mut self) -> Result<i64, Error>: Inserts the row and updates the struct's rowid field to match the database.
• insert_batch<T: AsRef<Self>>(rows: &[T]) -> Result<(), Error>: Inserts multiple rows.
• update(&self) -> Result<usize, Error>: Updates the existing row based on rowid. Returns the number of rows updated (should be 1).
• update_batch<T: AsRef<Self>>(rows: &[T]) -> Result<(), Error>: Updates multiple rows.
• delete(&self) -> Result<usize, Error>: Deletes the existing row based on rowid. Returns the number of rows deleted (should be 1).

// Example usage of the Turbosql trait methods
// (Assuming a struct `User` has `#[derive(Turbosql)]`)

// 1. Insert a new user
let new_user = User { rowid: None, name: "Alice".to_string() };
let id = new_user.insert()?;

// 2. Update an existing user
let mut existing_user = User { rowid: Some(id), name: "Alice Smith".to_string() };
existing_user.update()?;

// 3. Delete a user
existing_user.delete()?;