sea_orm/driver/
sqlx_sqlite.rs

1use futures::lock::Mutex;
2use log::LevelFilter;
3use sea_query::Values;
4use std::{future::Future, pin::Pin, sync::Arc};
5
6use sqlx::{
7    pool::PoolConnection,
8    sqlite::{SqliteConnectOptions, SqliteQueryResult, SqliteRow},
9    Connection, Executor, Sqlite, SqlitePool,
10};
11
12use sea_query_binder::SqlxValues;
13use tracing::{instrument, warn};
14
15use crate::{
16    debug_print, error::*, executor::*, sqlx_error_to_exec_err, AccessMode, ConnectOptions,
17    DatabaseConnection, DatabaseTransaction, IsolationLevel, QueryStream, Statement,
18    TransactionError,
19};
20
21use super::sqlx_common::*;
22
23/// Defines the [sqlx::sqlite] connector
24#[derive(Debug)]
25pub struct SqlxSqliteConnector;
26
27/// Defines a sqlx SQLite pool
28#[derive(Clone)]
29pub struct SqlxSqlitePoolConnection {
30    pub(crate) pool: SqlitePool,
31    metric_callback: Option<crate::metric::Callback>,
32}
33
34impl std::fmt::Debug for SqlxSqlitePoolConnection {
35    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
36        write!(f, "SqlxSqlitePoolConnection {{ pool: {:?} }}", self.pool)
37    }
38}
39
40impl SqlxSqliteConnector {
41    /// Check if the URI provided corresponds to `sqlite:` for a SQLite database
42    pub fn accepts(string: &str) -> bool {
43        string.starts_with("sqlite:") && string.parse::<SqliteConnectOptions>().is_ok()
44    }
45
46    /// Add configuration options for the SQLite database
47    #[instrument(level = "trace")]
48    pub async fn connect(options: ConnectOptions) -> Result<DatabaseConnection, DbErr> {
49        let mut options = options;
50        let mut opt = options
51            .url
52            .parse::<SqliteConnectOptions>()
53            .map_err(sqlx_error_to_conn_err)?;
54        if let Some(sqlcipher_key) = &options.sqlcipher_key {
55            opt = opt.pragma("key", sqlcipher_key.clone());
56        }
57        use sqlx::ConnectOptions;
58        if !options.sqlx_logging {
59            opt = opt.disable_statement_logging();
60        } else {
61            opt = opt.log_statements(options.sqlx_logging_level);
62            if options.sqlx_slow_statements_logging_level != LevelFilter::Off {
63                opt = opt.log_slow_statements(
64                    options.sqlx_slow_statements_logging_level,
65                    options.sqlx_slow_statements_logging_threshold,
66                );
67            }
68        }
69
70        if options.get_max_connections().is_none() {
71            options.max_connections(1);
72        }
73
74        let pool = if options.connect_lazy {
75            options.sqlx_pool_options().connect_lazy_with(opt)
76        } else {
77            options
78                .sqlx_pool_options()
79                .connect_with(opt)
80                .await
81                .map_err(sqlx_error_to_conn_err)?
82        };
83
84        let pool = SqlxSqlitePoolConnection {
85            pool,
86            metric_callback: None,
87        };
88
89        #[cfg(feature = "sqlite-use-returning-for-3_35")]
90        {
91            let version = get_version(&pool).await?;
92            ensure_returning_version(&version)?;
93        }
94
95        Ok(DatabaseConnection::SqlxSqlitePoolConnection(pool))
96    }
97}
98
99impl SqlxSqliteConnector {
100    /// Instantiate a sqlx pool connection to a [DatabaseConnection]
101    pub fn from_sqlx_sqlite_pool(pool: SqlitePool) -> DatabaseConnection {
102        DatabaseConnection::SqlxSqlitePoolConnection(SqlxSqlitePoolConnection {
103            pool,
104            metric_callback: None,
105        })
106    }
107}
108
109impl SqlxSqlitePoolConnection {
110    /// Execute a [Statement] on a SQLite backend
111    #[instrument(level = "trace")]
112    pub async fn execute(&self, stmt: Statement) -> Result<ExecResult, DbErr> {
113        debug_print!("{}", stmt);
114
115        let query = sqlx_query(&stmt);
116        let mut conn = self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
117        crate::metric::metric!(self.metric_callback, &stmt, {
118            match query.execute(&mut *conn).await {
119                Ok(res) => Ok(res.into()),
120                Err(err) => Err(sqlx_error_to_exec_err(err)),
121            }
122        })
123    }
124
125    /// Execute an unprepared SQL statement on a SQLite backend
126    #[instrument(level = "trace")]
127    pub async fn execute_unprepared(&self, sql: &str) -> Result<ExecResult, DbErr> {
128        debug_print!("{}", sql);
129
130        let conn = &mut self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
131        match conn.execute(sql).await {
132            Ok(res) => Ok(res.into()),
133            Err(err) => Err(sqlx_error_to_exec_err(err)),
134        }
135    }
136
137    /// Get one result from a SQL query. Returns [Option::None] if no match was found
138    #[instrument(level = "trace")]
139    pub async fn query_one(&self, stmt: Statement) -> Result<Option<QueryResult>, DbErr> {
140        debug_print!("{}", stmt);
141
142        let query = sqlx_query(&stmt);
143        let mut conn = self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
144        crate::metric::metric!(self.metric_callback, &stmt, {
145            match query.fetch_one(&mut *conn).await {
146                Ok(row) => Ok(Some(row.into())),
147                Err(err) => match err {
148                    sqlx::Error::RowNotFound => Ok(None),
149                    _ => Err(sqlx_error_to_query_err(err)),
150                },
151            }
152        })
153    }
154
155    /// Get the results of a query returning them as a Vec<[QueryResult]>
156    #[instrument(level = "trace")]
157    pub async fn query_all(&self, stmt: Statement) -> Result<Vec<QueryResult>, DbErr> {
158        debug_print!("{}", stmt);
159
160        let query = sqlx_query(&stmt);
161        let mut conn = self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
162        crate::metric::metric!(self.metric_callback, &stmt, {
163            match query.fetch_all(&mut *conn).await {
164                Ok(rows) => Ok(rows.into_iter().map(|r| r.into()).collect()),
165                Err(err) => Err(sqlx_error_to_query_err(err)),
166            }
167        })
168    }
169
170    /// Stream the results of executing a SQL query
171    #[instrument(level = "trace")]
172    pub async fn stream(&self, stmt: Statement) -> Result<QueryStream, DbErr> {
173        debug_print!("{}", stmt);
174
175        let conn = self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
176        Ok(QueryStream::from((
177            conn,
178            stmt,
179            self.metric_callback.clone(),
180        )))
181    }
182
183    /// Bundle a set of SQL statements that execute together.
184    #[instrument(level = "trace")]
185    pub async fn begin(
186        &self,
187        isolation_level: Option<IsolationLevel>,
188        access_mode: Option<AccessMode>,
189    ) -> Result<DatabaseTransaction, DbErr> {
190        let conn = self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
191        DatabaseTransaction::new_sqlite(
192            conn,
193            self.metric_callback.clone(),
194            isolation_level,
195            access_mode,
196        )
197        .await
198    }
199
200    /// Create a MySQL transaction
201    #[instrument(level = "trace", skip(callback))]
202    pub async fn transaction<F, T, E>(
203        &self,
204        callback: F,
205        isolation_level: Option<IsolationLevel>,
206        access_mode: Option<AccessMode>,
207    ) -> Result<T, TransactionError<E>>
208    where
209        F: for<'b> FnOnce(
210                &'b DatabaseTransaction,
211            ) -> Pin<Box<dyn Future<Output = Result<T, E>> + Send + 'b>>
212            + Send,
213        T: Send,
214        E: std::error::Error + Send,
215    {
216        let conn = self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
217        let transaction = DatabaseTransaction::new_sqlite(
218            conn,
219            self.metric_callback.clone(),
220            isolation_level,
221            access_mode,
222        )
223        .await
224        .map_err(|e| TransactionError::Connection(e))?;
225        transaction.run(callback).await
226    }
227
228    pub(crate) fn set_metric_callback<F>(&mut self, callback: F)
229    where
230        F: Fn(&crate::metric::Info<'_>) + Send + Sync + 'static,
231    {
232        self.metric_callback = Some(Arc::new(callback));
233    }
234
235    /// Checks if a connection to the database is still valid.
236    pub async fn ping(&self) -> Result<(), DbErr> {
237        let conn = &mut self.pool.acquire().await.map_err(sqlx_conn_acquire_err)?;
238        match conn.ping().await {
239            Ok(_) => Ok(()),
240            Err(err) => Err(sqlx_error_to_conn_err(err)),
241        }
242    }
243
244    /// Explicitly close the SQLite connection
245    pub async fn close(self) -> Result<(), DbErr> {
246        self.pool.close().await;
247        Ok(())
248    }
249}
250
251impl From<SqliteRow> for QueryResult {
252    fn from(row: SqliteRow) -> QueryResult {
253        QueryResult {
254            row: QueryResultRow::SqlxSqlite(row),
255        }
256    }
257}
258
259impl From<SqliteQueryResult> for ExecResult {
260    fn from(result: SqliteQueryResult) -> ExecResult {
261        ExecResult {
262            result: ExecResultHolder::SqlxSqlite(result),
263        }
264    }
265}
266
267pub(crate) fn sqlx_query(stmt: &Statement) -> sqlx::query::Query<'_, Sqlite, SqlxValues> {
268    let values = stmt
269        .values
270        .as_ref()
271        .map_or(Values(Vec::new()), |values| values.clone());
272    sqlx::query_with(&stmt.sql, SqlxValues(values))
273}
274
275pub(crate) async fn set_transaction_config(
276    _conn: &mut PoolConnection<Sqlite>,
277    isolation_level: Option<IsolationLevel>,
278    access_mode: Option<AccessMode>,
279) -> Result<(), DbErr> {
280    if isolation_level.is_some() {
281        warn!("Setting isolation level in a SQLite transaction isn't supported");
282    }
283    if access_mode.is_some() {
284        warn!("Setting access mode in a SQLite transaction isn't supported");
285    }
286    Ok(())
287}
288
289#[cfg(feature = "sqlite-use-returning-for-3_35")]
290async fn get_version(conn: &SqlxSqlitePoolConnection) -> Result<String, DbErr> {
291    let stmt = Statement {
292        sql: "SELECT sqlite_version()".to_string(),
293        values: None,
294        db_backend: crate::DbBackend::Sqlite,
295    };
296    conn.query_one(stmt)
297        .await?
298        .ok_or_else(|| {
299            DbErr::Conn(RuntimeErr::Internal(
300                "Error reading SQLite version".to_string(),
301            ))
302        })?
303        .try_get_by(0)
304}
305
306#[cfg(feature = "sqlite-use-returning-for-3_35")]
307fn ensure_returning_version(version: &str) -> Result<(), DbErr> {
308    let mut parts = version.trim().split('.').map(|part| {
309        part.parse::<u32>().map_err(|_| {
310            DbErr::Conn(RuntimeErr::Internal(
311                "Error parsing SQLite version".to_string(),
312            ))
313        })
314    });
315
316    let mut extract_next = || {
317        parts.next().transpose().and_then(|part| {
318            part.ok_or_else(|| {
319                DbErr::Conn(RuntimeErr::Internal("SQLite version too short".to_string()))
320            })
321        })
322    };
323
324    let major = extract_next()?;
325    let minor = extract_next()?;
326
327    if major > 3 || (major == 3 && minor >= 35) {
328        Ok(())
329    } else {
330        Err(DbErr::Conn(RuntimeErr::Internal(
331            "SQLite version does not support returning".to_string(),
332        )))
333    }
334}
335
336impl
337    From<(
338        PoolConnection<sqlx::Sqlite>,
339        Statement,
340        Option<crate::metric::Callback>,
341    )> for crate::QueryStream
342{
343    fn from(
344        (conn, stmt, metric_callback): (
345            PoolConnection<sqlx::Sqlite>,
346            Statement,
347            Option<crate::metric::Callback>,
348        ),
349    ) -> Self {
350        crate::QueryStream::build(stmt, crate::InnerConnection::Sqlite(conn), metric_callback)
351    }
352}
353
354impl crate::DatabaseTransaction {
355    pub(crate) async fn new_sqlite(
356        inner: PoolConnection<sqlx::Sqlite>,
357        metric_callback: Option<crate::metric::Callback>,
358        isolation_level: Option<IsolationLevel>,
359        access_mode: Option<AccessMode>,
360    ) -> Result<crate::DatabaseTransaction, DbErr> {
361        Self::begin(
362            Arc::new(Mutex::new(crate::InnerConnection::Sqlite(inner))),
363            crate::DbBackend::Sqlite,
364            metric_callback,
365            isolation_level,
366            access_mode,
367        )
368        .await
369    }
370}
371
372#[cfg(feature = "proxy")]
373pub(crate) fn from_sqlx_sqlite_row_to_proxy_row(row: &sqlx::sqlite::SqliteRow) -> crate::ProxyRow {
374    // https://docs.rs/sqlx-sqlite/0.7.2/src/sqlx_sqlite/type_info.rs.html
375    // https://docs.rs/sqlx-sqlite/0.7.2/sqlx_sqlite/types/index.html
376    use sea_query::Value;
377    use sqlx::{Column, Row, TypeInfo};
378    crate::ProxyRow {
379        values: row
380            .columns()
381            .iter()
382            .map(|c| {
383                (
384                    c.name().to_string(),
385                    match c.type_info().name() {
386                        "BOOLEAN" => Value::Bool(Some(
387                            row.try_get(c.ordinal()).expect("Failed to get boolean"),
388                        )),
389
390                        "INTEGER" => Value::Int(Some(
391                            row.try_get(c.ordinal()).expect("Failed to get integer"),
392                        )),
393
394                        "BIGINT" | "INT8" => Value::BigInt(Some(
395                            row.try_get(c.ordinal()).expect("Failed to get big integer"),
396                        )),
397
398                        "REAL" => Value::Double(Some(
399                            row.try_get(c.ordinal()).expect("Failed to get double"),
400                        )),
401
402                        "TEXT" => Value::String(Some(Box::new(
403                            row.try_get(c.ordinal()).expect("Failed to get string"),
404                        ))),
405
406                        "BLOB" => Value::Bytes(Some(Box::new(
407                            row.try_get(c.ordinal()).expect("Failed to get bytes"),
408                        ))),
409
410                        #[cfg(feature = "with-chrono")]
411                        "DATETIME" => Value::ChronoDateTimeUtc(Some(Box::new(
412                            row.try_get(c.ordinal()).expect("Failed to get timestamp"),
413                        ))),
414                        #[cfg(all(feature = "with-time", not(feature = "with-chrono")))]
415                        "DATETIME" => Value::TimeDateTimeWithTimeZone(Some(Box::new(
416                            row.try_get(c.ordinal()).expect("Failed to get timestamp"),
417                        ))),
418
419                        #[cfg(feature = "with-chrono")]
420                        "DATE" => Value::ChronoDate(Some(Box::new(
421                            row.try_get(c.ordinal()).expect("Failed to get date"),
422                        ))),
423                        #[cfg(all(feature = "with-time", not(feature = "with-chrono")))]
424                        "DATE" => Value::TimeDate(Some(Box::new(
425                            row.try_get(c.ordinal()).expect("Failed to get date"),
426                        ))),
427
428                        #[cfg(feature = "with-chrono")]
429                        "TIME" => Value::ChronoTime(Some(Box::new(
430                            row.try_get(c.ordinal()).expect("Failed to get time"),
431                        ))),
432                        #[cfg(all(feature = "with-time", not(feature = "with-chrono")))]
433                        "TIME" => Value::TimeTime(Some(Box::new(
434                            row.try_get(c.ordinal()).expect("Failed to get time"),
435                        ))),
436
437                        _ => unreachable!("Unknown column type: {}", c.type_info().name()),
438                    },
439                )
440            })
441            .collect(),
442    }
443}
444
445#[cfg(all(test, feature = "sqlite-use-returning-for-3_35"))]
446mod tests {
447    use super::*;
448
449    #[test]
450    fn test_ensure_returning_version() {
451        assert!(ensure_returning_version("").is_err());
452        assert!(ensure_returning_version(".").is_err());
453        assert!(ensure_returning_version(".a").is_err());
454        assert!(ensure_returning_version(".4.9").is_err());
455        assert!(ensure_returning_version("a").is_err());
456        assert!(ensure_returning_version("1.").is_err());
457        assert!(ensure_returning_version("1.a").is_err());
458
459        assert!(ensure_returning_version("1.1").is_err());
460        assert!(ensure_returning_version("1.0.").is_err());
461        assert!(ensure_returning_version("1.0.0").is_err());
462        assert!(ensure_returning_version("2.0.0").is_err());
463        assert!(ensure_returning_version("3.34.0").is_err());
464        assert!(ensure_returning_version("3.34.999").is_err());
465
466        // valid version
467        assert!(ensure_returning_version("3.35.0").is_ok());
468        assert!(ensure_returning_version("3.35.1").is_ok());
469        assert!(ensure_returning_version("3.36.0").is_ok());
470        assert!(ensure_returning_version("4.0.0").is_ok());
471        assert!(ensure_returning_version("99.0.0").is_ok());
472    }
473}
sea_orm/driver/sqlx_sqlite.rs

sea_orm/driver/
sqlx_sqlite.rs
