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 value;
21pub(crate) use value::Value;
22
23use async_trait::async_trait;
24use rusqlite::Connection as RusqliteConnection;
25use std::{
26    borrow::Cow,
27    path::{Path, PathBuf},
28    sync::Arc,
29};
30use toasty_core::{
31    Result, Schema,
32    driver::{
33        Capability, ConnectContext, Driver, ExecResponse, QueryLogConfig,
34        log::QueryLog,
35        operation::{IsolationLevel, Operation, RawSqlRet, Transaction, TypedValue},
36    },
37    schema::{
38        db::{self, Migration, Table},
39        diff,
40    },
41    stmt,
42};
43use toasty_sql::{self as sql};
44use url::Url;
45
46enum SqlReturn {
47    Count,
48    Infer,
49    Types(Vec<stmt::Type>),
50}
51
52/// A SQLite [`Driver`] that opens connections to a file or in-memory database.
53///
54/// # Examples
55///
56/// ```
57/// use toasty_driver_sqlite::Sqlite;
58///
59/// let driver = Sqlite::in_memory();
60/// ```
61#[derive(Debug)]
62pub enum Sqlite {
63    /// A database stored at a filesystem path.
64    File(PathBuf),
65    /// An ephemeral in-memory database.
66    InMemory,
67}
68
69impl Sqlite {
70    /// Create a new SQLite driver with an arbitrary connection URL
71    pub fn new(url: impl Into<String>) -> Result<Self> {
72        let url_str = url.into();
73        let url = Url::parse(&url_str).map_err(toasty_core::Error::driver_operation_failed)?;
74
75        if url.scheme() != "sqlite" {
76            return Err(toasty_core::Error::invalid_connection_url(format!(
77                "connection URL does not have a `sqlite` scheme; url={}",
78                url_str
79            )));
80        }
81
82        if url.path() == ":memory:" {
83            Ok(Self::InMemory)
84        } else {
85            Ok(Self::File(PathBuf::from(
86                percent_encoding::percent_decode(url.path().as_bytes())
87                    .decode_utf8_lossy()
88                    .to_string()
89                    .as_str(),
90            )))
91        }
92    }
93
94    /// Create an in-memory SQLite database
95    pub fn in_memory() -> Self {
96        Self::InMemory
97    }
98
99    /// Open a SQLite database at the specified file path
100    pub fn open<P: AsRef<Path>>(path: P) -> Self {
101        Self::File(path.as_ref().to_path_buf())
102    }
103}
104
105#[async_trait]
106impl Driver for Sqlite {
107    fn url(&self) -> Cow<'_, str> {
108        match self {
109            Sqlite::InMemory => Cow::Borrowed("sqlite::memory:"),
110            Sqlite::File(path) => Cow::Owned(format!("sqlite:{}", path.display())),
111        }
112    }
113
114    fn capability(&self) -> &'static Capability {
115        &Capability::SQLITE
116    }
117
118    async fn connect(
119        &self,
120        cx: &ConnectContext,
121    ) -> toasty_core::Result<Box<dyn toasty_core::Connection>> {
122        let mut connection = match self {
123            Sqlite::File(path) => Connection::open(path)?,
124            Sqlite::InMemory => Connection::in_memory(),
125        };
126        connection.query_log = cx.query_log;
127        Ok(Box::new(connection))
128    }
129
130    fn max_connections(&self) -> Option<usize> {
131        matches!(self, Self::InMemory).then_some(1)
132    }
133
134    fn generate_migration(&self, schema_diff: &diff::Schema<'_>) -> Migration {
135        let statements = sql::MigrationStatement::from_diff(schema_diff, &Capability::SQLITE);
136
137        let sql_strings: Vec<String> = statements
138            .iter()
139            .map(|stmt| sql::Serializer::sqlite(stmt.schema()).serialize(stmt.statement()))
140            .collect();
141
142        Migration::new_sql_with_breakpoints(&sql_strings)
143    }
144
145    async fn reset_db(&self) -> toasty_core::Result<()> {
146        match self {
147            Sqlite::File(path) => {
148                // Delete the file and recreate it
149                if path.exists() {
150                    std::fs::remove_file(path)
151                        .map_err(toasty_core::Error::driver_operation_failed)?;
152                }
153            }
154            Sqlite::InMemory => {
155                // Nothing to do — each connect() creates a fresh in-memory database
156            }
157        }
158
159        Ok(())
160    }
161}
162
163/// An open connection to a SQLite database.
164#[derive(Debug)]
165pub struct Connection {
166    connection: RusqliteConnection,
167    query_log: QueryLogConfig,
168}
169
170impl Connection {
171    /// Open an in-memory SQLite connection.
172    pub fn in_memory() -> Self {
173        let connection = RusqliteConnection::open_in_memory().unwrap();
174
175        Self {
176            connection,
177            query_log: QueryLogConfig::default(),
178        }
179    }
180
181    /// Open a SQLite connection to a file at `path`.
182    pub fn open<P: AsRef<Path>>(path: P) -> Result<Self> {
183        let connection =
184            RusqliteConnection::open(path).map_err(toasty_core::Error::driver_operation_failed)?;
185        let sqlite = Self {
186            connection,
187            query_log: QueryLogConfig::default(),
188        };
189        Ok(sqlite)
190    }
191
192    fn exec_sql(
193        &mut self,
194        sql_str: &str,
195        typed_params: Vec<TypedValue>,
196        ret: SqlReturn,
197    ) -> Result<ExecResponse> {
198        let mut log = QueryLog::sql(
199            &self.query_log,
200            "sqlite",
201            sql_str,
202            typed_params.iter().map(|tv| &tv.value),
203        );
204        let result = self.exec_sql_inner(sql_str, typed_params, ret, &mut log);
205        log.finish(&result);
206        result
207    }
208
209    fn exec_sql_inner(
210        &mut self,
211        sql_str: &str,
212        typed_params: Vec<TypedValue>,
213        ret: SqlReturn,
214        log: &mut QueryLog<'_>,
215    ) -> Result<ExecResponse> {
216        let mut stmt = self
217            .connection
218            .prepare_cached(sql_str)
219            .map_err(toasty_core::Error::driver_operation_failed)?;
220
221        let params = typed_params
222            .into_iter()
223            .map(|tv| Value::from(tv.value))
224            .collect::<Vec<_>>();
225
226        if matches!(ret, SqlReturn::Count) {
227            let count = stmt
228                .execute(rusqlite::params_from_iter(params.iter()))
229                .map_err(toasty_core::Error::driver_operation_failed)?;
230
231            return Ok(ExecResponse::count(count as _));
232        }
233
234        let mut rows = stmt
235            .query(rusqlite::params_from_iter(params.iter()))
236            .map_err(toasty_core::Error::driver_operation_failed)?;
237
238        let mut values = vec![];
239        let column_count = rows.as_ref().map(|stmt| stmt.column_count()).unwrap_or(0);
240
241        loop {
242            match rows.next() {
243                Ok(Some(row)) => {
244                    let items = match &ret {
245                        SqlReturn::Count => unreachable!(),
246                        SqlReturn::Infer => (0..column_count)
247                            .map(|index| Value::from_sql_infer(row, index).into_inner())
248                            .collect(),
249                        SqlReturn::Types(ret_tys) => ret_tys
250                            .iter()
251                            .enumerate()
252                            .map(|(index, ret_ty)| Value::from_sql(row, index, ret_ty).into_inner())
253                            .collect(),
254                    };
255
256                    values.push(stmt::ValueRecord::from_vec(items).into());
257                }
258                Ok(None) => break,
259                Err(err) => {
260                    return Err(toasty_core::Error::driver_operation_failed(err));
261                }
262            }
263        }
264
265        log.rows(values.len() as u64);
266        Ok(ExecResponse::value_stream(stmt::ValueStream::from_vec(
267            values,
268        )))
269    }
270}
271
272#[async_trait]
273impl toasty_core::driver::Connection for Connection {
274    async fn exec(&mut self, schema: &Arc<Schema>, op: Operation) -> Result<ExecResponse> {
275        tracing::trace!(driver = "sqlite", op = %op.name(), "driver exec");
276
277        let (sql, typed_params, ret_tys) = match op {
278            Operation::QuerySql(op) => {
279                assert!(
280                    op.last_insert_id_hack.is_none(),
281                    "last_insert_id_hack is MySQL-specific and should not be set for SQLite"
282                );
283                (sql::Statement::from(op.stmt), op.params, op.ret)
284            }
285            Operation::RawSql(op) => {
286                let ret = match op.ret {
287                    RawSqlRet::None => SqlReturn::Count,
288                    RawSqlRet::Infer => SqlReturn::Infer,
289                    RawSqlRet::Types(types) => SqlReturn::Types(types),
290                };
291                return self.exec_sql(&op.sql, op.params, ret);
292            }
293            // Operation::Insert(op) => op.stmt.into(),
294            Operation::Transaction(mut op) => {
295                if let Transaction::Start { isolation, .. } = &mut op {
296                    if !matches!(isolation, Some(IsolationLevel::Serializable) | None) {
297                        return Err(toasty_core::Error::unsupported_feature(
298                            "SQLite only supports Serializable isolation",
299                        ));
300                    }
301                    *isolation = None;
302                }
303                let sql = sql::Serializer::sqlite(&schema.db).serialize_transaction(&op);
304                self.connection
305                    .execute(&sql, [])
306                    .map_err(toasty_core::Error::driver_operation_failed)?;
307                return Ok(ExecResponse::count(0));
308            }
309            _ => todo!("op={:#?}", op),
310        };
311
312        let ret = match &sql {
313            sql::Statement::Query(stmt) => match &stmt.body {
314                stmt::ExprSet::Select(_) => SqlReturn::Types(ret_tys.unwrap()),
315                _ => todo!(),
316            },
317            sql::Statement::Insert(stmt) => stmt
318                .returning
319                .as_ref()
320                .map(|_| SqlReturn::Types(ret_tys.unwrap()))
321                .unwrap_or(SqlReturn::Count),
322            sql::Statement::Delete(stmt) => stmt
323                .returning
324                .as_ref()
325                .map(|_| SqlReturn::Types(ret_tys.unwrap()))
326                .unwrap_or(SqlReturn::Count),
327            sql::Statement::Update(stmt) => {
328                assert!(stmt.condition.is_none(), "stmt={stmt:#?}");
329                stmt.returning
330                    .as_ref()
331                    .map(|_| SqlReturn::Types(ret_tys.unwrap()))
332                    .unwrap_or(SqlReturn::Count)
333            }
334            _ => SqlReturn::Count,
335        };
336
337        let sql_str = sql::Serializer::sqlite(&schema.db).serialize(&sql);
338        self.exec_sql(&sql_str, typed_params, ret)
339    }
340
341    async fn push_schema(&mut self, schema: &Schema) -> Result<()> {
342        for table in &schema.db.tables {
343            tracing::debug!(table = %table.name, "creating table");
344            self.create_table(&schema.db, table)?;
345        }
346
347        Ok(())
348    }
349
350    async fn applied_migrations(
351        &mut self,
352    ) -> Result<Vec<toasty_core::schema::db::AppliedMigration>> {
353        // Ensure the migrations table exists
354        self.connection
355            .execute(
356                "CREATE TABLE IF NOT EXISTS __toasty_migrations (
357                id INTEGER PRIMARY KEY,
358                name TEXT NOT NULL,
359                applied_at TEXT NOT NULL
360            )",
361                [],
362            )
363            .map_err(toasty_core::Error::driver_operation_failed)?;
364
365        // Query all applied migrations
366        let mut stmt = self
367            .connection
368            .prepare("SELECT id FROM __toasty_migrations ORDER BY applied_at")
369            .map_err(toasty_core::Error::driver_operation_failed)?;
370
371        let rows = stmt
372            .query_map([], |row| {
373                let id: i64 = row.get(0)?;
374                Ok(toasty_core::schema::db::AppliedMigration::new(id as u64))
375            })
376            .map_err(toasty_core::Error::driver_operation_failed)?;
377
378        rows.collect::<rusqlite::Result<Vec<_>>>()
379            .map_err(toasty_core::Error::driver_operation_failed)
380    }
381
382    async fn apply_migration(
383        &mut self,
384        id: u64,
385        name: &str,
386        migration: &toasty_core::schema::db::Migration,
387    ) -> Result<()> {
388        tracing::info!(id = id, name = %name, "applying migration");
389        // Ensure the migrations table exists
390        self.connection
391            .execute(
392                "CREATE TABLE IF NOT EXISTS __toasty_migrations (
393                id INTEGER PRIMARY KEY,
394                name TEXT NOT NULL,
395                applied_at TEXT NOT NULL
396            )",
397                [],
398            )
399            .map_err(toasty_core::Error::driver_operation_failed)?;
400
401        // Start transaction
402        self.connection
403            .execute("BEGIN", [])
404            .map_err(toasty_core::Error::driver_operation_failed)?;
405
406        // Execute each migration statement
407        for statement in migration.statements() {
408            if let Err(e) = self
409                .connection
410                .execute(statement, [])
411                .map_err(toasty_core::Error::driver_operation_failed)
412            {
413                self.connection
414                    .execute("ROLLBACK", [])
415                    .map_err(toasty_core::Error::driver_operation_failed)?;
416                return Err(e);
417            }
418        }
419
420        // Record the migration
421        if let Err(e) = self.connection.execute(
422            "INSERT INTO __toasty_migrations (id, name, applied_at) VALUES (?1, ?2, datetime('now'))",
423            rusqlite::params![id as i64, name],
424        ).map_err(toasty_core::Error::driver_operation_failed) {
425            self.connection.execute("ROLLBACK", []).map_err(toasty_core::Error::driver_operation_failed)?;
426            return Err(e);
427        }
428
429        // Commit transaction
430        self.connection
431            .execute("COMMIT", [])
432            .map_err(toasty_core::Error::driver_operation_failed)?;
433        Ok(())
434    }
435}
436
437impl Connection {
438    fn create_table(&mut self, schema: &db::Schema, table: &Table) -> Result<()> {
439        let serializer = sql::Serializer::sqlite(schema);
440
441        let stmt = serializer.serialize(&sql::Statement::create_table(table, &Capability::SQLITE));
442
443        self.connection
444            .execute(&stmt, [])
445            .map_err(toasty_core::Error::driver_operation_failed)?;
446
447        // Create any indices
448        for index in &table.indices {
449            // The PK has already been created by the table statement
450            if index.primary_key {
451                continue;
452            }
453
454            let stmt = serializer.serialize(&sql::Statement::create_index(index));
455
456            self.connection
457                .execute(&stmt, [])
458                .map_err(toasty_core::Error::driver_operation_failed)?;
459        }
460        Ok(())
461    }
462}
463
464#[cfg(test)]
465mod tests {
466    use super::Sqlite;
467    use std::path::PathBuf;
468
469    /// The file path `Sqlite::new` resolves out of a `sqlite:` URL.
470    fn file_path(url: &str) -> PathBuf {
471        match Sqlite::new(url).unwrap() {
472            Sqlite::File(path) => path,
473            Sqlite::InMemory => panic!("expected a file-backed database for {url}"),
474        }
475    }
476
477    #[test]
478    fn new_decodes_percent_encoded_path() {
479        // `url::Url` stores the path percent-encoded: a space becomes `%20` and
480        // non-ASCII bytes become `%XX` sequences. The driver must decode it back
481        // before opening the file, otherwise it opens one whose name literally
482        // contains `%20`.
483        assert_eq!(
484            file_path("sqlite:/tmp/my db.sqlite"),
485            PathBuf::from("/tmp/my db.sqlite")
486        );
487        assert_eq!(
488            file_path("sqlite:///tmp/my%20db.sqlite"),
489            PathBuf::from("/tmp/my db.sqlite")
490        );
491        assert_eq!(
492            file_path("sqlite:/tmp/d%C3%A9j%C3%A0.db"),
493            PathBuf::from("/tmp/déjà.db")
494        );
495        // Percent-decoding, not form-decoding: a literal `+` must stay a `+`.
496        assert_eq!(
497            file_path("sqlite:/tmp/a+b.db"),
498            PathBuf::from("/tmp/a+b.db")
499        );
500    }
501
502    #[test]
503    fn new_memory_url_stays_in_memory() {
504        assert!(matches!(
505            Sqlite::new("sqlite::memory:").unwrap(),
506            Sqlite::InMemory
507        ));
508    }
509}
toasty_driver_sqlite/lib.rs

toasty_driver_sqlite/
lib.rs
