tauri_plugin_sql/
wrapper.rs

1// Copyright 2019-2023 Tauri Programme within The Commons Conservancy
2// SPDX-License-Identifier: Apache-2.0
3// SPDX-License-Identifier: MIT
4
5#[cfg(feature = "sqlite")]
6use std::fs::create_dir_all;
7
8use indexmap::IndexMap;
9use serde_json::Value as JsonValue;
10#[cfg(any(feature = "sqlite", feature = "mysql", feature = "postgres"))]
11use sqlx::{Column, Executor, Pool, Row, migrate::MigrateDatabase};
12#[cfg(any(feature = "sqlite", feature = "mysql", feature = "postgres"))]
13use tauri::Manager;
14use tauri::{AppHandle, Runtime};
15
16#[cfg(feature = "mysql")]
17use sqlx::MySql;
18#[cfg(feature = "postgres")]
19use sqlx::Postgres;
20#[cfg(feature = "sqlite")]
21use sqlx::Sqlite;
22
23use crate::LastInsertId;
24
25/// A connection pool for one of the supported database drivers.
26///
27/// The variant is picked from the scheme of the connection string
28/// (`sqlite:`, `mysql:` or `postgres:`) and only the variants whose Cargo
29/// feature is enabled exist.
30pub enum DbPool {
31    /// A SQLite connection pool. Only available with the `sqlite` Cargo feature.
32    #[cfg(feature = "sqlite")]
33    Sqlite(Pool<Sqlite>),
34    /// A MySQL connection pool. Only available with the `mysql` Cargo feature.
35    #[cfg(feature = "mysql")]
36    MySql(Pool<MySql>),
37    /// A PostgreSQL connection pool. Only available with the `postgres` Cargo feature.
38    #[cfg(feature = "postgres")]
39    Postgres(Pool<Postgres>),
40    /// Placeholder used when none of the `sqlite`, `mysql` and `postgres` Cargo
41    /// features is enabled. Connecting always fails and every other operation is a no-op.
42    #[cfg(not(any(feature = "sqlite", feature = "mysql", feature = "postgres")))]
43    None,
44}
45
46// public methods
47/* impl DbPool {
48    /// Get the inner Sqlite Pool. Returns None for MySql and Postgres pools.
49    #[cfg(feature = "sqlite")]
50    pub fn sqlite(&self) -> Option<&Pool<Sqlite>> {
51        match self {
52            DbPool::Sqlite(pool) => Some(pool),
53            _ => None,
54        }
55    }
56
57    /// Get the inner MySql Pool. Returns None for Sqlite and Postgres pools.
58    #[cfg(feature = "mysql")]
59    pub fn mysql(&self) -> Option<&Pool<MySql>> {
60        match self {
61            DbPool::MySql(pool) => Some(pool),
62            _ => None,
63        }
64    }
65
66    /// Get the inner Postgres Pool. Returns None for MySql and Sqlite pools.
67    #[cfg(feature = "postgres")]
68    pub fn postgres(&self) -> Option<&Pool<Postgres>> {
69        match self {
70            DbPool::Postgres(pool) => Some(pool),
71            _ => None,
72        }
73    }
74} */
75
76// private methods
77impl DbPool {
78    pub(crate) async fn connect<R: Runtime>(
79        conn_url: &str,
80        _app: &AppHandle<R>,
81        _auto_create: bool,
82    ) -> Result<Self, crate::Error> {
83        match conn_url
84            .split_once(':')
85            .ok_or_else(|| crate::Error::InvalidDbUrl(conn_url.to_string()))?
86            .0
87        {
88            #[cfg(feature = "sqlite")]
89            "sqlite" => {
90                let app_path = _app
91                    .path()
92                    .app_config_dir()
93                    .expect("No App config path was found!");
94
95                if _auto_create {
96                    create_dir_all(&app_path).expect("Couldn't create app config dir");
97                }
98
99                let db_url = &path_mapper(app_path, conn_url);
100
101                ensure_database::<Sqlite>(db_url, conn_url, _auto_create).await?;
102                Ok(Self::Sqlite(Pool::connect(db_url).await?))
103            }
104            #[cfg(feature = "mysql")]
105            "mysql" => {
106                ensure_database::<MySql>(conn_url, &redact_password(conn_url), _auto_create)
107                    .await?;
108                Ok(Self::MySql(Pool::connect(conn_url).await?))
109            }
110            #[cfg(feature = "postgres")]
111            "postgres" => {
112                ensure_database::<Postgres>(conn_url, &redact_password(conn_url), _auto_create)
113                    .await?;
114                Ok(Self::Postgres(Pool::connect(conn_url).await?))
115            }
116            #[cfg(not(any(feature = "sqlite", feature = "postgres", feature = "mysql")))]
117            _ => Err(crate::Error::InvalidDbUrl(format!(
118                "{conn_url} - No database driver enabled!"
119            ))),
120            #[cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
121            _ => Err(crate::Error::InvalidDbUrl(conn_url.to_string())),
122        }
123    }
124
125    pub(crate) async fn migrate(
126        &self,
127        _migrator: &sqlx::migrate::Migrator,
128    ) -> Result<(), crate::Error> {
129        match self {
130            #[cfg(feature = "sqlite")]
131            DbPool::Sqlite(pool) => _migrator.run(pool).await?,
132            #[cfg(feature = "mysql")]
133            DbPool::MySql(pool) => _migrator.run(pool).await?,
134            #[cfg(feature = "postgres")]
135            DbPool::Postgres(pool) => _migrator.run(pool).await?,
136            #[cfg(not(any(feature = "sqlite", feature = "mysql", feature = "postgres")))]
137            DbPool::None => (),
138        }
139        Ok(())
140    }
141
142    pub(crate) async fn close(&self) {
143        match self {
144            #[cfg(feature = "sqlite")]
145            DbPool::Sqlite(pool) => pool.close().await,
146            #[cfg(feature = "mysql")]
147            DbPool::MySql(pool) => pool.close().await,
148            #[cfg(feature = "postgres")]
149            DbPool::Postgres(pool) => pool.close().await,
150            #[cfg(not(any(feature = "sqlite", feature = "mysql", feature = "postgres")))]
151            DbPool::None => (),
152        }
153    }
154
155    pub(crate) async fn execute(
156        &self,
157        _query: String,
158        _values: Vec<JsonValue>,
159    ) -> Result<(u64, LastInsertId), crate::Error> {
160        Ok(match self {
161            #[cfg(feature = "sqlite")]
162            DbPool::Sqlite(pool) => {
163                let mut query = sqlx::query(&_query);
164                for value in _values {
165                    if value.is_null() {
166                        query = query.bind(None::<JsonValue>);
167                    } else if value.is_string() {
168                        query = query.bind(value.as_str().unwrap().to_owned())
169                    } else if let Some(number) = value.as_number() {
170                        query = query.bind(number.as_f64().unwrap_or_default())
171                    } else {
172                        query = query.bind(value);
173                    }
174                }
175                let result = pool.execute(query).await?;
176                (
177                    result.rows_affected(),
178                    LastInsertId::Sqlite(result.last_insert_rowid()),
179                )
180            }
181            #[cfg(feature = "mysql")]
182            DbPool::MySql(pool) => {
183                let mut query = sqlx::query(&_query);
184                for value in _values {
185                    if value.is_null() {
186                        query = query.bind(None::<JsonValue>);
187                    } else if value.is_string() {
188                        query = query.bind(value.as_str().unwrap().to_owned())
189                    } else if let Some(number) = value.as_number() {
190                        query = query.bind(number.as_f64().unwrap_or_default())
191                    } else {
192                        query = query.bind(value);
193                    }
194                }
195                let result = pool.execute(query).await?;
196                (
197                    result.rows_affected(),
198                    LastInsertId::MySql(result.last_insert_id()),
199                )
200            }
201            #[cfg(feature = "postgres")]
202            DbPool::Postgres(pool) => {
203                let mut query = sqlx::query(&_query);
204                for value in _values {
205                    if value.is_null() {
206                        query = query.bind(None::<JsonValue>);
207                    } else if value.is_string() {
208                        query = query.bind(value.as_str().unwrap().to_owned())
209                    } else if let Some(number) = value.as_number() {
210                        query = query.bind(number.as_f64().unwrap_or_default())
211                    } else {
212                        query = query.bind(value);
213                    }
214                }
215                let result = pool.execute(query).await?;
216                (result.rows_affected(), LastInsertId::Postgres(()))
217            }
218            #[cfg(not(any(feature = "sqlite", feature = "mysql", feature = "postgres")))]
219            DbPool::None => (0, LastInsertId::None),
220        })
221    }
222
223    pub(crate) async fn select(
224        &self,
225        _query: String,
226        _values: Vec<JsonValue>,
227    ) -> Result<Vec<IndexMap<String, JsonValue>>, crate::Error> {
228        Ok(match self {
229            #[cfg(feature = "sqlite")]
230            DbPool::Sqlite(pool) => {
231                let mut query = sqlx::query(&_query);
232                for value in _values {
233                    if value.is_null() {
234                        query = query.bind(None::<JsonValue>);
235                    } else if value.is_string() {
236                        query = query.bind(value.as_str().unwrap().to_owned())
237                    } else if let Some(number) = value.as_number() {
238                        query = query.bind(number.as_f64().unwrap_or_default())
239                    } else {
240                        query = query.bind(value);
241                    }
242                }
243                let rows = pool.fetch_all(query).await?;
244                let mut values = Vec::new();
245                for row in rows {
246                    let mut value = IndexMap::default();
247                    for (i, column) in row.columns().iter().enumerate() {
248                        let v = row.try_get_raw(i)?;
249
250                        let v = crate::decode::sqlite::to_json(v)?;
251
252                        value.insert(column.name().to_string(), v);
253                    }
254
255                    values.push(value);
256                }
257                values
258            }
259            #[cfg(feature = "mysql")]
260            DbPool::MySql(pool) => {
261                let mut query = sqlx::query(&_query);
262                for value in _values {
263                    if value.is_null() {
264                        query = query.bind(None::<JsonValue>);
265                    } else if value.is_string() {
266                        query = query.bind(value.as_str().unwrap().to_owned())
267                    } else if let Some(number) = value.as_number() {
268                        query = query.bind(number.as_f64().unwrap_or_default())
269                    } else {
270                        query = query.bind(value);
271                    }
272                }
273                let rows = pool.fetch_all(query).await?;
274                let mut values = Vec::new();
275                for row in rows {
276                    let mut value = IndexMap::default();
277                    for (i, column) in row.columns().iter().enumerate() {
278                        let v = row.try_get_raw(i)?;
279
280                        let v = crate::decode::mysql::to_json(v)?;
281
282                        value.insert(column.name().to_string(), v);
283                    }
284
285                    values.push(value);
286                }
287                values
288            }
289            #[cfg(feature = "postgres")]
290            DbPool::Postgres(pool) => {
291                let mut query = sqlx::query(&_query);
292                for value in _values {
293                    if value.is_null() {
294                        query = query.bind(None::<JsonValue>);
295                    } else if value.is_string() {
296                        query = query.bind(value.as_str().unwrap().to_owned())
297                    } else if let Some(number) = value.as_number() {
298                        query = query.bind(number.as_f64().unwrap_or_default())
299                    } else {
300                        query = query.bind(value);
301                    }
302                }
303                let rows = pool.fetch_all(query).await?;
304                let mut values = Vec::new();
305                for row in rows {
306                    let mut value = IndexMap::default();
307                    for (i, column) in row.columns().iter().enumerate() {
308                        let v = row.try_get_raw(i)?;
309
310                        let v = crate::decode::postgres::to_json(v)?;
311
312                        value.insert(column.name().to_string(), v);
313                    }
314
315                    values.push(value);
316                }
317                values
318            }
319            #[cfg(not(any(feature = "sqlite", feature = "mysql", feature = "postgres")))]
320            DbPool::None => Vec::new(),
321        })
322    }
323}
324
325/// Creates the database if it does not exist yet, or fails with
326/// [`crate::Error::DatabaseDoesNotExist`] (naming the connection string `db`
327/// the app passed) when `auto_create` is `false`.
328/// Errors of the existence check itself (unreachable server, bad credentials)
329/// are returned as they are, not as a missing database.
330#[cfg(any(feature = "sqlite", feature = "mysql", feature = "postgres"))]
331async fn ensure_database<DB: MigrateDatabase>(
332    conn_url: &str,
333    db: &str,
334    auto_create: bool,
335) -> Result<(), crate::Error> {
336    match DB::database_exists(conn_url).await {
337        Ok(true) => Ok(()),
338        // When the check fails, creating reports the actual error (or fixes it).
339        Ok(false) | Err(_) if auto_create => Ok(DB::create_database(conn_url).await?),
340        Ok(false) => Err(crate::Error::DatabaseDoesNotExist(db.to_string())),
341        Err(e) => Err(e.into()),
342    }
343}
344
345/// Removes the password from a `scheme://user:password@host/db` connection string,
346/// so it can be put in an error message.
347#[cfg(any(feature = "mysql", feature = "postgres"))]
348fn redact_password(conn_url: &str) -> String {
349    let Some((scheme, rest)) = conn_url.split_once("://") else {
350        return conn_url.to_string();
351    };
352    let authority_end = rest.find(['/', '?']).unwrap_or(rest.len());
353    match rest[..authority_end].rsplit_once('@') {
354        Some((userinfo, _)) => match userinfo.split_once(':') {
355            Some((user, _)) => format!("{scheme}://{user}:***@{}", &rest[userinfo.len() + 1..]),
356            None => conn_url.to_string(),
357        },
358        None => conn_url.to_string(),
359    }
360}
361
362#[cfg(feature = "sqlite")]
363/// Maps the user supplied DB connection string to a connection string
364/// with a fully qualified file path to the App's designed "app_path"
365fn path_mapper(mut app_path: std::path::PathBuf, connection_string: &str) -> String {
366    app_path.push(
367        connection_string
368            .split_once(':')
369            .expect("Couldn't parse the connection string for DB!")
370            .1,
371    );
372
373    format!(
374        "sqlite:{}",
375        app_path
376            .to_str()
377            .expect("Problem creating fully qualified path to Database file!")
378    )
379}
380
381#[cfg(test)]
382mod tests {
383    #[cfg(feature = "sqlite")]
384    #[test]
385    fn ensure_database_respects_auto_create() {
386        use super::ensure_database;
387        use sqlx::Sqlite;
388
389        let path = std::env::temp_dir().join(format!("tauri-plugin-sql-{}.db", std::process::id()));
390        let _ = std::fs::remove_file(&path);
391        let url = format!("sqlite:{}", path.display());
392
393        tauri::async_runtime::block_on(async {
394            let err = ensure_database::<Sqlite>(&url, "sqlite:test.db", false)
395                .await
396                .unwrap_err();
397            assert_eq!(err.to_string(), "database sqlite:test.db does not exist");
398            assert!(!path.exists());
399
400            ensure_database::<Sqlite>(&url, "sqlite:test.db", true)
401                .await
402                .unwrap();
403            assert!(path.exists());
404
405            ensure_database::<Sqlite>(&url, "sqlite:test.db", false)
406                .await
407                .unwrap();
408        });
409
410        let _ = std::fs::remove_file(&path);
411    }
412
413    #[cfg(any(feature = "mysql", feature = "postgres"))]
414    #[test]
415    fn redact_password() {
416        use super::redact_password;
417
418        assert_eq!(
419            redact_password("mysql://user:secret@localhost:3306/db"),
420            "mysql://user:***@localhost:3306/db"
421        );
422        assert_eq!(
423            redact_password("postgres://user:p@ss@host/db?sslmode=require"),
424            "postgres://user:***@host/db?sslmode=require"
425        );
426        assert_eq!(
427            redact_password("postgres://user@host/db"),
428            "postgres://user@host/db"
429        );
430        assert_eq!(
431            redact_password("postgres://host/db?user=a@b"),
432            "postgres://host/db?user=a@b"
433        );
434    }
435}
tauri_plugin_sql/wrapper.rs

tauri_plugin_sql/
wrapper.rs
