# SQLite For an argument for using SQLite as an application file format see [[SQLite as an Application File Format]]. ![[SQLite as an Application File Format#^ceunla]] For full documentation of SQLite language syntax see [SQL As Understood By SQLite](https://sqlite.org/lang.html). All queries and commands can use parameters. See [[SQLite Language Expressions#Parameters]] for information about parameter binding. SQLite operates in two different journaling modes: [[Atomic Commit in SQLite|rollback journal]] and [[SQLite Write-Ahead Logging||write-ahead logging]] (WAL). ![[SQLite Write-Ahead Logging#^pey8go]] An SQLite connection is thread-safe by default, meaning that a single connection can be used by multiple threads simultaneously (note that multi-connection synchronization is handled by database internals, not client API). SQLite client API uses mutexes internally to ensure only one thread accesses a connection at a single time. This behavior can be disabled per connection. See [[Using SQLite in Multi-Threaded Applications]] for more. ## Using SQLite from Rust The recommended low-level library for using SQLite inside rust is [rusqlite](https://docs.rs/rusqlite/). All Rust examples below are using rusqlite. Opening a connection: ```rust let connection = Connection::open("my_database.db")?; ``` Executing multiple commands in a single batch is done using `execute_batch`: ```rust connection.execute_batch(indoc! { r#" CREATE TABLE IF NOT EXISTS urls ( url, expiration_date ); CREATE TABLE IF NOT EXISTS chunks ( start INTEGER PRIMARY KEY, length, completed, blob );"# })?; ``` Queries that return single rows can be executed directly on the connection, but for queries that return multiple rows, you need to prepare a statement first. For example: ```rust let mut statement = connection.prepare(indoc! { r#" INSERT INTO chunks (start, length, completed, blob) VALUES (?, ?, 0, ZEROBLOB(?2)) "#})?; // Can fail if the SQL is invalid statement.execute((start, length))?; //Can fail if parameter binding fails ``` ### Specifying Parameters Rusqlite has the [named_params!](https://docs.rs/rusqlite/latest/rusqlite/macro.named_params.html) macro for named parameters, and the [params!](https://docs.rs/rusqlite/latest/rusqlite/macro.params.html) macro for positional parameters. These macros make working with parameters easier. For example, a statement created using: #### `named_params!` ```rust let mut insert_file_statement = transaction.prepare(indoc! {" INSERT INTO files ( path, published, last_modified, hash_last_modified, hash ) VALUES ( :path, :published, :last_modified, :last_modified, :hash ) })?; ``` Is executed using `named_params!` like: ```rust insert_file_statement.execute(named_params! { ":path": &stripped_path_with_extension, ":published": published, ":last_modified": last_modified, ":hash": hash, })?; ``` #### `params!` `params!` works similarly for positional parameters: ```rust params![person.name, person.age_in_years, person.data] ``` > [!important] > The number in `?1` is 1-based i.e. `?1` refers to `person.name` ## Creating Tables ```sql CREATE TABLE IF NOT EXISTS chunks ( start INTEGER PRIMARY KEY, length, completed, blob ); ``` Notice that SQLite uses dynamic typing, so you can create a table without specifying column types. The only exception to this rule is the automatically created ID (`rowid`) and `INTEGER PRIMARY KEY`. Columns can have various properties assigned to them. For a full list see [SQLite CREATE TABLE](https://sqlite.org/lang_createtable.html). Here are notable ones: `INTEGER PRIMARY KEY` is used to indicate which column should be used as the primary key. If you don't specify a primary key, SQLite will create an internal `rowid` column that is used as a primary key. `UNIQUE` ensures that all values in the column are distinct. Implemented as a unique index on the column. See [[SQLite Foreign Key Support]] for info about foreign keys. ![[SQLite Foreign Key Support#^sll1kt]] ![[SQLite Foreign Key Support#^gmyorn]] ## Creating Rows ```sql INSERT INTO chunks (start, length, completed, blob) VALUES (?, ?, 0, ZEROBLOB(?2)) ``` Upserting: ```sql INSERT INTO files (path, hash) VALUES (?, ?) ON CONFLICT(path) DO UPDATE SET hash=excluded.hash; ``` `excluded` refers to the values that would have been inserted if there was no conflict. Conflict occurs if the `path` already exists in the table and is defined as a unique column. ## Read ```sql SELECT rowid, published, last_modified, hash_last_modified, hash FROM files WHERE path = ? ORDER BY last_modified DESC LIMIT 10 ``` ## Update ```sql UPDATE files SET published = ?, last_modified = ?, hash_last_modified = ?, hash = ? WHERE rowid = ? ``` ## Delete ```sql DELETE FROM files WHERE rowid = ? ``` ## Transactions ![[SQLite Transactions#^l33yhl]] ![[SQLite Transactions#^0foyux]] ![[SQLite Transactions#^vm7v2b]] ![[SQLite Transactions#^yigjnr]] ![[SQLite Transactions#^uvyqsm]] ```sql BEGIN; -- Starts a deferred transaction by default -- statements... COMMIT; ``` ```sql BEGIN EXCLUSIVE; -- Starts an exclusive transaction -- statements... COMMIT; ``` ## Savepoints ![[SQLite Savepoints#^t3djkc]] ![[SQLite Savepoints#^s7oqgc]] ![[SQLite Savepoints#^mn014g]] ![[SQLite Savepoints#^c2smey]] ![[SQLite Savepoints#^pzqavu]] ![[SQLite Savepoints#^1rb5h2]] ## `VACUUM` ![[SQLite VACUUM#^ahlp1y]] For more information see [[SQLite VACUUM]]. ## Query Optimization ![[SQLite ANALYZE#^iri5gk]] ![[SQLite ANALYZE#^naxqbp]] ## Creating a Database Replica There are several ways to create a database replica in SQLite: backup API, the `sqlite3_rsync` utility, and the `VACUUM INTO` command. ### Backup API ![[Using the SQLite Online Backup API#^aomsjt]] ### `sqlite3_rsync` The [[Database Remote-Copy Tool for SQLite|sqlite3_rsync]] utility can be used to copy a database file to a remote location using SSH, using a protocol that minimizes the amount of data transferred across the network. ### `VACUUM INTO` Is basically a `VACUUM` but into a new database file: ![[SQLite VACUUM#^zgss1s]] ![[SQLite VACUUM#^bjff9z]] For more info see [[SQLite VACUUM]].