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

toasty_driver_sqlite/
lib.rs
