Turbosql

An easy local data persistence layer, backed by SQLite.

• Schema auto-defined by your Rust structs
• Automatic schema migrations
• Super-simple basic INSERT/SELECT/UPDATE/DELETE operations
• Use complex SQL if that's your jam
• Validates all SQL (including user-supplied) at compile time

Usage

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

[derive(Turbosql, Default)]

struct Person { rowid: Option, // rowid member required & enforced at compile time name: Option, age: Option, image_jpg: Option> }

fn main() -> Result<(), Box> {

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(())

} ```

See integration_test.rs or trevyn/turbo for more usage examples!

Under the Hood

Turbosql generates a SQLite schema and prepared queries for each struct:

```rust use turbosql::Turbosql;

[derive(Turbosql, Default)]

struct Person { rowid: Option, // rowid member required & enforced name: Option, age: Option, image_jpg: Option> } ```

        ↓      auto-generates and validates the schema

```sql CREATE TABLE person ( rowid INTEGER PRIMARY KEY, name TEXT, age INTEGER, image_jpg BLOB, ) STRICT

INSERT INTO person (rowid, name, age, image_jpg) VALUES (?, ?, ?, ?)

SELECT rowid, name, age, image_jpg FROM person ```

Queries with SQL predicates are also assembled and validated at compile time. Note that SQL types vs Rust types for parameter bindings are not currently checked at compile time.

rust,ignore let people = select!(Vec<Person> "WHERE age > ?", 21);

        ↓

sql SELECT rowid, name, age, image_jpg FROM person WHERE age > ?

Automatic Schema Migrations

At compile time, the #[derive(Turbosql)] macro runs and creates a migrations.toml file in your project root that describes the database schema.

Each time you change a struct declaration and the macro is re-run (e.g. by cargo or rust-analyzer), migration SQL statements are generated that update the database schema. These new statements are recorded in migrations.toml, and are automatically embedded in your binary.

```rust

[derive(turbosql::Turbosql, Default)]

struct Person { rowid: Option, name: Option } ```

        ↓      auto-generates migrations.toml

toml migrations_append_only = [ 'CREATE TABLE person(rowid INTEGER PRIMARY KEY) STRICT', 'ALTER TABLE person ADD COLUMN name TEXT', ] output_generated_schema_for_your_information_do_not_edit = ''' CREATE TABLE person ( rowid INTEGER PRIMARY KEY, name TEXT ) STRICT '''

When your schema changes, any new version of your binary will automatically migrate any older database file to the current schema by applying the appropriate migrations in sequence.

This migration process is a one-way ratchet: Old versions of the binary run on a database file with a newer schema will detect a schema mismatch and will be blocked from operating on the futuristically-schema'd database file.

Unused or reverted migrations that are created during development can be manually removed from migrations.toml before being released, but any database files that have already applied these deleted migrations will error and must be rebuilt. Proceed with care. When in doubt, refrain from manually editing migrations.toml, and everything should work fine.

• Just declare and freely append fields to your structs.
• Check out the migrations.toml file that is generated in your project root to see what's happening.
• If you run into any weird compiler errors, try just re-compiling first; depending on the order the proc macros run, sometimes it just needs a little push to get in sync after a schema change.
• Schema migrations are one-way, append-only. This is similar to the approach taken by leafac/sqlite-migration for the Node.js ecosystem; see that project for a discussion of the advantages!
• On launch, versions of your binary built with a newer schema will automatically apply the appropriate migrations to an older database.
• If you're feeling adventurous, you can add your own schema migration entries to the bottom of the list. (For creating indexes, etc.)
• You can hand-write complex migrations as well, see turbo/migrations.toml for some examples.
• Please open a GitHub issue with any questions or suggestions!

Where's my data?

The SQLite database file is created in the directory returned by directories_next::ProjectDirs::data_dir() + your executable's filename stem, which resolves to something like: