toasty_driver_sqlite/
lib.rs

1#![warn(missing_docs)]
2
3//! Toasty driver for [SQLite](https://www.sqlite.org/) using
4//! [`rusqlite`](https://docs.rs/rusqlite).
5//!
6//! Supports both file-backed and in-memory databases.
7//!
8//! # Examples
9//!
10//! ```
11//! use toasty_driver_sqlite::Sqlite;
12//!
13//! // In-memory database
14//! let driver = Sqlite::in_memory();
15//!
16//! // File-backed database
17//! let driver = Sqlite::open("path/to/db.sqlite3");
18//! ```
19
20mod pragma;
21use pragma::Pragmas;
22pub use pragma::{AutoVacuum, JournalMode, LockingMode, Synchronous};
23
24mod value;
25pub(crate) use value::Value;
26
27use async_trait::async_trait;
28use rusqlite::Connection as RusqliteConnection;
29use std::{
30    borrow::Cow,
31    path::{Path, PathBuf},
32    sync::Arc,
33    time::Duration,
34};
35use toasty_core::{
36    Result, Schema,
37    driver::{
38        Capability, ConnectContext, ConnectionUrl, Driver, ExecResponse, QueryLogConfig,
39        log::QueryLog,
40        operation::{IsolationLevel, Operation, RawSqlRet, Transaction, TypedValue},
41    },
42    schema::{
43        db::{self, Migration, Table},
44        diff,
45    },
46    stmt,
47};
48use toasty_sql::{self as sql};
49
50enum SqlReturn {
51    Count,
52    Infer,
53    Types(Vec<stmt::Type>),
54}
55
56/// A SQLite [`Driver`] that opens connections to a file or in-memory database.
57///
58/// # Examples
59///
60/// ```
61/// use toasty_driver_sqlite::Sqlite;
62///
63/// let driver = Sqlite::in_memory();
64/// ```
65#[derive(Debug)]
66pub struct Sqlite {
67    source: Source,
68    busy_timeout: Duration,
69    pragmas: Pragmas,
70}
71
72/// Where a [`Sqlite`] driver's database lives.
73#[derive(Debug)]
74enum Source {
75    /// A database stored at a filesystem path.
76    File(PathBuf),
77    /// An ephemeral in-memory database.
78    InMemory,
79}
80
81impl Sqlite {
82    fn from_source(source: Source) -> Self {
83        Self {
84            source,
85            busy_timeout: Duration::from_secs(5),
86            pragmas: Pragmas::default(),
87        }
88    }
89
90    /// Create a new SQLite driver with an arbitrary connection URL
91    pub fn new(url: impl Into<String>) -> Result<Self> {
92        let url_str = url.into();
93        let url = ConnectionUrl::parse(&url_str)?;
94
95        if !url.has_scheme("sqlite") {
96            return Err(toasty_core::Error::invalid_connection_url(format!(
97                "connection URL does not have a `sqlite` scheme; url={url_str}"
98            )));
99        }
100
101        let path = url.file_path()?;
102        if path == Path::new(":memory:") {
103            return Ok(Self::in_memory());
104        }
105
106        Ok(Self::open(path))
107    }
108
109    /// Create an in-memory SQLite database
110    pub fn in_memory() -> Self {
111        Self::from_source(Source::InMemory)
112    }
113
114    /// Open a SQLite database at the specified file path
115    pub fn open<P: AsRef<Path>>(path: P) -> Self {
116        Self::from_source(Source::File(path.as_ref().to_path_buf()))
117    }
118
119    /// Set the enforcement of
120    /// [foreign key constraints](https://www.sqlite.org/pragma.html#pragma_foreign_keys).
121    ///
122    /// Enabled by default.
123    pub fn foreign_keys(mut self, on: bool) -> Self {
124        self.pragmas
125            .set("foreign_keys", if on { "ON" } else { "OFF" });
126        self
127    }
128
129    /// Set how long a connection waits for a lock held by another
130    /// connection before failing with `database is locked`.
131    ///
132    /// Set through `sqlite3_busy_timeout` as soon as each connection opens,
133    /// before any pragma runs. The default is 5 seconds.
134    pub fn busy_timeout(mut self, timeout: Duration) -> Self {
135        self.busy_timeout = timeout;
136        self
137    }
138
139    /// Set the [journal mode](https://www.sqlite.org/pragma.html#pragma_journal_mode).
140    ///
141    /// Journal modes are per connection, except for
142    /// [WAL](https://www.sqlite.org/wal.html), which the database file
143    /// keeps. Opening a WAL database with a different journal mode takes an
144    /// exclusive lock and converts the file.
145    ///
146    /// Not set by default, so an existing database keeps its mode.
147    ///
148    /// # Examples
149    ///
150    /// ```
151    /// use toasty_driver_sqlite::{JournalMode, Sqlite};
152    ///
153    /// let driver = Sqlite::open("app.db").journal_mode(JournalMode::Wal);
154    /// ```
155    pub fn journal_mode(mut self, mode: JournalMode) -> Self {
156        self.pragmas.set("journal_mode", mode);
157        self
158    }
159
160    /// Set the [synchronous](https://www.sqlite.org/pragma.html#pragma_synchronous)
161    /// setting.
162    ///
163    /// Not set by default; SQLite's default is `FULL`. `NORMAL` is usually
164    /// enough in WAL mode.
165    pub fn synchronous(mut self, synchronous: Synchronous) -> Self {
166        self.pragmas.set("synchronous", synchronous);
167        self
168    }
169
170    /// Set the [locking mode](https://www.sqlite.org/pragma.html#pragma_locking_mode).
171    ///
172    /// Not set by default; SQLite's default is `NORMAL`.
173    pub fn locking_mode(mut self, mode: LockingMode) -> Self {
174        self.pragmas.set("locking_mode", mode);
175        self
176    }
177
178    /// Set the [auto-vacuum](https://www.sqlite.org/pragma.html#pragma_auto_vacuum)
179    /// setting.
180    ///
181    /// Not set by default; SQLite's default is `NONE`. On an existing
182    /// database the change takes effect only after a `VACUUM`.
183    pub fn auto_vacuum(mut self, auto_vacuum: AutoVacuum) -> Self {
184        self.pragmas.set("auto_vacuum", auto_vacuum);
185        self
186    }
187
188    /// Set the [page size](https://www.sqlite.org/pragma.html#pragma_page_size)
189    /// in bytes.
190    ///
191    /// Not set by default; SQLite's default is 4096. On an existing
192    /// database the change takes effect only after a `VACUUM`, and never in
193    /// WAL mode.
194    pub fn page_size(mut self, page_size: u32) -> Self {
195        self.pragmas.set("page_size", page_size.to_string());
196        self
197    }
198
199    /// Set the [SQLCipher](https://www.zetetic.net/sqlcipher/sqlcipher-api/#key)
200    /// passphrase that encrypts the database.
201    ///
202    /// The passphrase is quoted for you, applied before every other pragma,
203    /// and kept out of query logs, error messages, and `Debug` output.
204    /// SQLCipher checks it only when the database is first read, so a wrong
205    /// passphrase fails at the first query rather than on connect.
206    ///
207    /// Available with this crate's `sqlcipher` feature.
208    ///
209    /// # Examples
210    ///
211    /// ```
212    /// use toasty_driver_sqlite::Sqlite;
213    ///
214    /// let driver = Sqlite::open("app.db").key("a passphrase");
215    /// ```
216    #[cfg(feature = "sqlcipher")]
217    pub fn key(mut self, passphrase: impl AsRef<str>) -> Self {
218        let quoted = format!("'{}'", passphrase.as_ref().replace('\'', "''"));
219        self.pragmas.set("key", quoted);
220        self
221    }
222
223    /// Set a `PRAGMA` applied to every connection this driver opens.
224    ///
225    /// Use this for pragmas without a dedicated method. The driver writes
226    /// `PRAGMA {key} = {value}` with both arguments as given, so the caller
227    /// supplies any quoting the value needs. Do not pass untrusted input:
228    /// the value is SQL text.
229    ///
230    /// Pragmas run in a fixed order rather than the order they are set,
231    /// because SQLite and SQLCipher constrain it: `key` and the other
232    /// SQLCipher settings come first, then `page_size`, `locking_mode`,
233    /// `auto_vacuum`, `journal_mode`, `foreign_keys`, `synchronous`, and
234    /// then every other pragma in the order first set. Setting the same
235    /// pragma twice keeps the last value. Names match exactly.
236    ///
237    /// # Examples
238    ///
239    /// ```
240    /// use toasty_driver_sqlite::Sqlite;
241    ///
242    /// let driver = Sqlite::open("app.db")
243    ///     .pragma("cache_size", "-64000")
244    ///     .pragma("temp_store", "MEMORY");
245    /// ```
246    ///
247    /// Values of `key` and other secret SQLCipher pragmas are kept out of
248    /// query logs, error messages, and `Debug` output.
249    pub fn pragma<K, V>(mut self, key: K, value: V) -> Self
250    where
251        K: Into<Cow<'static, str>>,
252        V: Into<Cow<'static, str>>,
253    {
254        self.pragmas.set(key, value);
255        self
256    }
257}
258
259#[async_trait]
260impl Driver for Sqlite {
261    fn url(&self) -> Cow<'_, str> {
262        match &self.source {
263            Source::InMemory => Cow::Borrowed("sqlite::memory:"),
264            Source::File(path) => Cow::Owned(format!("sqlite:{}", path.display())),
265        }
266    }
267
268    fn capability(&self) -> &'static Capability {
269        &Capability::SQLITE
270    }
271
272    async fn connect(
273        &self,
274        cx: &ConnectContext,
275    ) -> toasty_core::Result<Box<dyn toasty_core::Connection>> {
276        let mut connection = match &self.source {
277            Source::File(path) => Connection::open(path)?,
278            Source::InMemory => Connection::in_memory(),
279        };
280        connection.query_log = cx.query_log;
281        connection
282            .connection
283            .busy_timeout(self.busy_timeout)
284            .map_err(toasty_core::Error::driver_operation_failed)?;
285        self.pragmas.apply(&connection.connection, &cx.query_log)?;
286        Ok(Box::new(connection))
287    }
288
289    fn max_connections(&self) -> Option<usize> {
290        matches!(self.source, Source::InMemory).then_some(1)
291    }
292
293    fn generate_migration(&self, schema_diff: &diff::Schema<'_>) -> Migration {
294        let statements = sql::MigrationStatement::from_diff(schema_diff, &Capability::SQLITE);
295
296        let sql_strings: Vec<String> = statements
297            .iter()
298            .map(|stmt| sql::Serializer::sqlite(stmt.schema()).serialize(stmt.statement()))
299            .collect();
300
301        Migration::new_sql_with_breakpoints(&sql_strings)
302    }
303
304    async fn reset_db(&self) -> toasty_core::Result<()> {
305        match &self.source {
306            Source::File(path) => {
307                // Delete the file and recreate it
308                if path.exists() {
309                    std::fs::remove_file(path)
310                        .map_err(toasty_core::Error::driver_operation_failed)?;
311                }
312            }
313            Source::InMemory => {
314                // Nothing to do — each connect() creates a fresh in-memory database
315            }
316        }
317
318        Ok(())
319    }
320}
321
322/// An open connection to a SQLite database.
323#[derive(Debug)]
324pub struct Connection {
325    connection: RusqliteConnection,
326    query_log: QueryLogConfig,
327}
328
329impl Connection {
330    /// Open an in-memory SQLite connection.
331    pub fn in_memory() -> Self {
332        let connection = RusqliteConnection::open_in_memory().unwrap();
333
334        Self {
335            connection,
336            query_log: QueryLogConfig::default(),
337        }
338    }
339
340    /// Open a SQLite connection to a file at `path`.
341    pub fn open<P: AsRef<Path>>(path: P) -> Result<Self> {
342        let connection =
343            RusqliteConnection::open(path).map_err(toasty_core::Error::driver_operation_failed)?;
344        let sqlite = Self {
345            connection,
346            query_log: QueryLogConfig::default(),
347        };
348        Ok(sqlite)
349    }
350
351    fn exec_sql(
352        &mut self,
353        sql_str: &str,
354        typed_params: Vec<TypedValue>,
355        ret: SqlReturn,
356    ) -> Result<ExecResponse> {
357        let mut log = QueryLog::sql(
358            &self.query_log,
359            "sqlite",
360            sql_str,
361            typed_params.iter().map(|tv| &tv.value),
362        );
363        let result = self.exec_sql_inner(sql_str, typed_params, ret, &mut log);
364        log.finish(&result);
365        result
366    }
367
368    fn exec_sql_inner(
369        &mut self,
370        sql_str: &str,
371        typed_params: Vec<TypedValue>,
372        ret: SqlReturn,
373        log: &mut QueryLog<'_>,
374    ) -> Result<ExecResponse> {
375        let mut stmt = self
376            .connection
377            .prepare_cached(sql_str)
378            .map_err(toasty_core::Error::driver_operation_failed)?;
379
380        let params = typed_params
381            .into_iter()
382            .map(|tv| Value::from(tv.value))
383            .collect::<Vec<_>>();
384
385        if matches!(ret, SqlReturn::Count) {
386            let count = stmt
387                .execute(rusqlite::params_from_iter(params.iter()))
388                .map_err(toasty_core::Error::driver_operation_failed)?;
389
390            return Ok(ExecResponse::count(count as _));
391        }
392
393        let mut rows = stmt
394            .query(rusqlite::params_from_iter(params.iter()))
395            .map_err(toasty_core::Error::driver_operation_failed)?;
396
397        let mut values = vec![];
398        let column_count = rows.as_ref().map(|stmt| stmt.column_count()).unwrap_or(0);
399
400        loop {
401            match rows.next() {
402                Ok(Some(row)) => {
403                    let items = match &ret {
404                        SqlReturn::Count => unreachable!(),
405                        SqlReturn::Infer => (0..column_count)
406                            .map(|index| Value::from_sql_infer(row, index).into_inner())
407                            .collect(),
408                        SqlReturn::Types(ret_tys) => ret_tys
409                            .iter()
410                            .enumerate()
411                            .map(|(index, ret_ty)| Value::from_sql(row, index, ret_ty).into_inner())
412                            .collect(),
413                    };
414
415                    values.push(stmt::ValueRecord::from_vec(items).into());
416                }
417                Ok(None) => break,
418                Err(err) => {
419                    return Err(toasty_core::Error::driver_operation_failed(err));
420                }
421            }
422        }
423
424        log.rows(values.len() as u64);
425        Ok(ExecResponse::value_stream(stmt::ValueStream::from_vec(
426            values,
427        )))
428    }
429}
430
431#[async_trait]
432impl toasty_core::driver::Connection for Connection {
433    async fn exec(&mut self, schema: &Arc<Schema>, op: Operation) -> Result<ExecResponse> {
434        tracing::trace!(driver = "sqlite", op = %op.name(), "driver exec");
435
436        let (sql, typed_params, ret_tys) = match op {
437            Operation::Insert(op) => (sql::Statement::from(op.stmt), op.params, op.ret),
438            Operation::QuerySql(op) => (sql::Statement::from(op.stmt), op.params, op.ret),
439            Operation::RawSql(op) => {
440                let ret = match op.ret {
441                    RawSqlRet::None => SqlReturn::Count,
442                    RawSqlRet::Infer => SqlReturn::Infer,
443                    RawSqlRet::Types(types) => SqlReturn::Types(types),
444                };
445                return self.exec_sql(&op.sql, op.params, ret);
446            }
447            Operation::Transaction(mut op) => {
448                if let Transaction::Start { isolation, .. } = &mut op {
449                    if !matches!(isolation, Some(IsolationLevel::Serializable) | None) {
450                        return Err(toasty_core::Error::unsupported_feature(
451                            "SQLite only supports Serializable isolation",
452                        ));
453                    }
454                    *isolation = None;
455                }
456                let sql = sql::Serializer::sqlite(&schema.db).serialize_transaction(&op);
457                self.connection
458                    .execute(&sql, [])
459                    .map_err(toasty_core::Error::driver_operation_failed)?;
460                return Ok(ExecResponse::count(0));
461            }
462            _ => todo!("op={:#?}", op),
463        };
464
465        let ret = match &sql {
466            sql::Statement::Query(stmt) => match &stmt.body {
467                stmt::ExprSet::Select(_) => SqlReturn::Types(ret_tys.unwrap()),
468                _ => todo!(),
469            },
470            sql::Statement::Insert(stmt) => stmt
471                .returning
472                .as_ref()
473                .map(|_| SqlReturn::Types(ret_tys.unwrap()))
474                .unwrap_or(SqlReturn::Count),
475            sql::Statement::Delete(stmt) => stmt
476                .returning
477                .as_ref()
478                .map(|_| SqlReturn::Types(ret_tys.unwrap()))
479                .unwrap_or(SqlReturn::Count),
480            sql::Statement::Update(stmt) => {
481                assert!(stmt.condition.is_none(), "stmt={stmt:#?}");
482                stmt.returning
483                    .as_ref()
484                    .map(|_| SqlReturn::Types(ret_tys.unwrap()))
485                    .unwrap_or(SqlReturn::Count)
486            }
487            _ => SqlReturn::Count,
488        };
489
490        let sql_str = sql::Serializer::sqlite(&schema.db).serialize(&sql);
491        self.exec_sql(&sql_str, typed_params, ret)
492    }
493
494    async fn push_schema(&mut self, schema: &Schema) -> Result<()> {
495        for table in &schema.db.tables {
496            tracing::debug!(table = %table.name, "creating table");
497            self.create_table(&schema.db, table)?;
498        }
499
500        Ok(())
501    }
502
503    async fn applied_migrations(
504        &mut self,
505    ) -> Result<Vec<toasty_core::schema::db::AppliedMigration>> {
506        // Ensure the migrations table exists
507        self.connection
508            .execute(
509                "CREATE TABLE IF NOT EXISTS __toasty_migrations (
510                id INTEGER PRIMARY KEY,
511                name TEXT NOT NULL,
512                applied_at TEXT NOT NULL
513            )",
514                [],
515            )
516            .map_err(toasty_core::Error::driver_operation_failed)?;
517
518        // Query all applied migrations
519        let mut stmt = self
520            .connection
521            .prepare("SELECT id FROM __toasty_migrations ORDER BY applied_at")
522            .map_err(toasty_core::Error::driver_operation_failed)?;
523
524        let rows = stmt
525            .query_map([], |row| {
526                let id: i64 = row.get(0)?;
527                Ok(toasty_core::schema::db::AppliedMigration::new(id as u64))
528            })
529            .map_err(toasty_core::Error::driver_operation_failed)?;
530
531        rows.collect::<rusqlite::Result<Vec<_>>>()
532            .map_err(toasty_core::Error::driver_operation_failed)
533    }
534
535    async fn apply_migration(
536        &mut self,
537        id: u64,
538        name: &str,
539        migration: &toasty_core::schema::db::Migration,
540    ) -> Result<()> {
541        tracing::info!(id = id, name = %name, "applying migration");
542        // Ensure the migrations table exists
543        self.connection
544            .execute(
545                "CREATE TABLE IF NOT EXISTS __toasty_migrations (
546                id INTEGER PRIMARY KEY,
547                name TEXT NOT NULL,
548                applied_at TEXT NOT NULL
549            )",
550                [],
551            )
552            .map_err(toasty_core::Error::driver_operation_failed)?;
553
554        // Start transaction
555        self.connection
556            .execute("BEGIN", [])
557            .map_err(toasty_core::Error::driver_operation_failed)?;
558
559        // Execute each migration statement
560        for statement in migration.statements() {
561            if let Err(e) = self
562                .connection
563                .execute(statement, [])
564                .map_err(toasty_core::Error::driver_operation_failed)
565            {
566                self.connection
567                    .execute("ROLLBACK", [])
568                    .map_err(toasty_core::Error::driver_operation_failed)?;
569                return Err(e);
570            }
571        }
572
573        // Record the migration
574        if let Err(e) = self.connection.execute(
575            "INSERT INTO __toasty_migrations (id, name, applied_at) VALUES (?1, ?2, datetime('now'))",
576            rusqlite::params![id as i64, name],
577        ).map_err(toasty_core::Error::driver_operation_failed) {
578            self.connection.execute("ROLLBACK", []).map_err(toasty_core::Error::driver_operation_failed)?;
579            return Err(e);
580        }
581
582        // Commit transaction
583        self.connection
584            .execute("COMMIT", [])
585            .map_err(toasty_core::Error::driver_operation_failed)?;
586        Ok(())
587    }
588}
589
590impl Connection {
591    fn create_table(&mut self, schema: &db::Schema, table: &Table) -> Result<()> {
592        let serializer = sql::Serializer::sqlite(schema);
593
594        let stmt = serializer.serialize(&sql::Statement::create_table(table, &Capability::SQLITE));
595
596        self.connection
597            .execute(&stmt, [])
598            .map_err(toasty_core::Error::driver_operation_failed)?;
599
600        // Create any indices
601        for index in &table.indices {
602            // The PK has already been created by the table statement
603            if index.primary_key {
604                continue;
605            }
606
607            let stmt = serializer.serialize(&sql::Statement::create_index(index));
608
609            self.connection
610                .execute(&stmt, [])
611                .map_err(toasty_core::Error::driver_operation_failed)?;
612        }
613        Ok(())
614    }
615}
toasty_driver_sqlite/lib.rs

toasty_driver_sqlite/
lib.rs
