Turbosql

Easy local data persistence layer, backed by SQLite.

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

Status

In active development, updated February 2021! Contributions are very much welcome!

Usage

[dependencies]
turbosql = "0.1"

use turbosql::{Turbosql, Blob, 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<Blob>
}

// INSERT a row
let rowid = Person {
    rowid: None,
    name: Some("Joe".to_string()),
    age: Some(42),
    image_jpg: None
}.insert().unwrap();

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

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

// SELECT a single row with a predicate
let person: Person = select!(Person "WHERE name = ?", "Joe").unwrap();

// UPDATE
execute!("UPDATE person SET age = ? WHERE name = ?", 18, "Joe").unwrap();

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

See integration_test.rs for more usage examples!

Under the Hood

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

use turbosql::{Turbosql, Blob};

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

        ↓      auto-generates and validates the schema

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

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.

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

        ↓

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.

#[derive(turbosql::Turbosql, Default)]
struct Person {
    rowid: Option<i64>,
    name: Option<String>
}

        ↓      auto-generates migrations.toml

migrations_append_only = [
  'CREATE TABLE person(rowid INTEGER PRIMARY KEY)',
  '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
  )
'''

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. (SQLite doesn't even support ALTER TABLE DROP {column}, so we're not even going there for now.)
• 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.)
• Questions? Ask on Discord (https://discord.gg/RX3rTWUzUD) or open a GitHub discussion! -> https://github.com/trevyn/turbosql/discussions/new

Where's my data?

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

Linux	$XDG_DATA_HOME/{exe_name} or $HOME/.local/share/{exe_name} /home/alice/.local/share/fooapp/fooapp.sqlite
macOS	$HOME/Library/Application Support/{exe_name} /Users/Alice/Library/Application Support/org.fooapp.fooapp/fooapp.sqlite
Windows	{FOLDERID_LocalAppData}\{exe_name}\data C:\Users\Alice\AppData\Local\fooapp\fooapp\data\fooapp.sqlite

-wal and -shm files

SQLite is an extremely reliable database engine, but it helps to understand how it interfaces with the filesystem. The main .sqlite file contains the bulk of the database. During database writes, SQLite also creates .sqlite-wal and .sqlite-shm files. If the host process is terminated without flushing writes, you may end up with these three files when you expected to have a single file. This is always fine; on next launch, SQLite knows how to resolve any interrupted writes and make sense of the world. However, if the -wal and/or -shm files are present, they must be considered essential to database integrity. Deleting them may result in a corrupted database. See https://sqlite.org/tempfiles.html .

⚠️ Example Query Forms

⚠️ NOTE: This table is somewhat speculative and not completely aligned with the code yet. Check integration_test.rs for examples of what works today and is tested in CI. ⚠️

⚠️ Primitive type	let result = select!(String "SELECT name FROM person")?; Returns one value cast to specified type, returns TurboSql::Error::QueryReturnedNoRows if no rows available. let result = select!(String "name FROM person WHERE rowid = ?", rowid)?; SELECT keyword is always optional when using select!; it's added automatically as needed. Parameter binding is straightforward.
⚠️ Tuple	let result = select!((String, i64) "name, age FROM person")?; Use tuple types for multiple manually declared columns.
⚠️ Anonymous struct	let result = select!("name_String, age_i64 FROM person")?; println!("{}", result.name); Types must be specified in column names to generate an anonymous struct.
⚠️ Vec<_>	let result = select!(Vec<String> "name FROM person")?; Returns Vec of another type. If no rows, returns empty Vec. (Tuple types work inside, as well.) let result = select!(Vec<_> "name_String, age_i64 FROM person")?; Anonymous structs work, too.
⚠️ Option<_>	let result = select!(Option<String> "name FROM person")?; Returns Ok(None) if no rows, Error(Turbosql::Error) on error.
⚠️ Your struct	let result = select!(Person "WHERE name = ?", name)?; Column list and table name are optional if type is a #[derive(Turbosql)] struct. let result = select!(Vec<NameAndAdult> "name, age >= 18 AS adult FROM person")?; You can use other struct types as well; column names must match the struct. Implement Default to avoid specifying unused column names. (And, of course, you can put it all in a Vec or Option as well.) let result = select!(Vec<Person>)?; Sometimes everything is optional; this example will retrieve all Person rows.
⚠️ Transactions	transaction! { if select!(Option<Person> "WHERE name = ?", name)?.is_none() { Person { ... }.insert!()?; } } Haha just kidding, this doesn't exist yet. How might this work with threads and async? What if the transaction fails to commit? Nested transactions not supported? Calling other functions in a transaction? Async? This gets messy. Just say that any Turbosql calls outside of the literal text transaction!{} body will work fine, but not be part of the transaction? Inititally, this implementation might just open a new SQLite connection, and use it for all child calls.

"turbosql" or "Turbosql"?

Your choice, but you definitely do not want to capitalize any of the other letters in the name! ;)

License: MIT OR Apache-2.0 OR CC0-1.0 (public domain)