sqlx_repo/
lib.rs

1//! The `sqlx_repo` is built around Repository Pattern and provides a consistent interface to interact
2//! with relational databases, abstracting away the differences between query syntaxes.
3//!
4//! It supports:
5//! - SQLite
6//! - PostgreSQL
7//! - MySQL
8//!
9//! The objective is to define a minimal, shared core of database operations that present and behave
10//! consistently and predictably across supported backends. Features which are only specific to a particular
11//! database are deliberately excluded from this core.
12//!
13//! # Overview
14//!
15//! This is how it all fits together.
16//!
17//! ```rust
18//! use sqlx_repo::prelude::*;
19//! use anyhow::Result;
20//!
21//! // Create your first migration.
22//! fn migration1() -> Migration {
23//!     migration!(
24//!         "first migration",
25//!         "create table test(id int primary key autoincrement)"
26//!     )
27//! }
28//!
29//! // Create your database repo layer, note `repo`, `migrator`, `query` macros.
30//! #[repo(Send + Sync + std::fmt::Debug)]
31//! impl Repo for DatabaseRepository {
32//!     async fn migrate(&self) -> Result<()> {
33//!         let migrator = migrator!(&[migration1()]).await?;
34//!         migrator.run(&self.pool).await?;
35//!         Ok(())
36//!     }
37//!
38//!     async fn insert(&self) -> Result<()> {
39//!         let query = query!("insert into test values (?)");
40//!         let mut transaction = self.pool.start_transaction().await?;
41//!         sqlx::query(query)
42//!             .bind(1)
43//!             .execute(&mut *transaction)
44//!             .await?;
45//!         sqlx::query(query)
46//!             .bind(2)
47//!             .execute(&mut *transaction)
48//!             .await?;
49//!         transaction.commit().await?;
50//!         Ok(())
51//!     }
52//!
53//!     async fn select_all(&self) -> Result<Vec<i32>> {
54//!         let query = query!("select * from test");
55//!         let res = sqlx::query(query)
56//!             .fetch_all(&self.pool)
57//!             .await?
58//!             .into_iter()
59//!             .map(|row| row.get::<i32, _>(0))
60//!             .collect();
61//!         Ok(res)
62//!     }
63//!
64//!     async fn delete_all(&self) -> Result<()> {
65//!         let query = query!("delete from test");
66//!         sqlx::query(query).execute(&self.pool).await?;
67//!         Ok(())
68//!     }
69//! }
70//!
71//!# use tokio::runtime::Builder;
72//!# let rt = Builder::new_current_thread().enable_all().build().unwrap();
73//!# rt.block_on(async {
74//! // Pick any of supported database backends: SQLite, PostgreSQL, MySQL
75//! // let url = "postgres://postgres:root@127.0.0.1:5432/postgres"
76//! // let url = "mysql://root:root@127.0.0.1:3306/mysql"
77//! let url = "sqlite::memory:";
78//! let repo = <dyn Repo>::new(url).await.unwrap();
79//! repo.migrate().await.unwrap();
80//! repo.delete_all().await.unwrap();
81//! repo.insert().await.unwrap();
82//! assert_eq!(vec![1, 2], repo.select_all().await.unwrap());
83//!# });
84//!  
85//! ```
86//!
87//! # Supported queries
88//!
89//! ## Create table
90//!
91//! ### Supported types
92//!
93//! ```sql
94//! CREATE TABLE test (
95//!     id32 SERIAL PRIMARY KEY,
96//!     i16 SMALLINT,
97//!     i32 INTEGER,
98//!     i64 BIGINT,
99//!     numeric NUMERIC(10, 2),
100//!     real REAL,
101//!     double DOUBLE PRECISION,
102//!     bool BOOLEAN,
103//!     char CHAR(5),
104//!     varchar VARCHAR(100),
105//!     text TEXT,
106//!     date DATE,
107//!     time TIME,
108//!     timestamp TIMESTAMP,
109//!     uuid UUID,
110//!     bytes BYTEA,
111//!     json JSON
112//! );
113//! ```
114//!
115//! ### Primary Key
116//!
117//! ```sql
118//! CREATE TABLE test (id SMALLSERIAL PRIMARY KEY);
119//! ```
120//!
121//! ```sql
122//! CREATE TABLE test (id SERIAL PRIMARY KEY);
123//! ```
124//!
125//! ```sql
126//! CREATE TABLE test (id BIGSERIAL PRIMARY KEY);
127//! ```
128//!
129//! ```sql
130//! CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT);
131//! ```
132//!
133//! Composite primary key supported as well:
134//! ```sql
135//! CREATE TABLE test(left INT, right INT, value TEXT, PRIMARY KEY (left, right));
136//! ```
137//!
138//! ### Foreign key
139//!
140//! ```sql
141//! CREATE TABLE employees (
142//!     emp_id INT PRIMARY KEY,
143//!     dept_id INT,
144//!     location_id INT,
145//!     name TEXT,
146//!     FOREIGN KEY (dept_id, location_id) REFERENCES departments(dept_id, location_id)
147//! );
148//! ```
149//!
150//! ### On delete
151//!
152//! Supported actions `ON DELETE` are `CASCADE`, `SET NULL`, `RESTRICT`:
153//!
154//! ```sql
155//! CREATE TABLE employees (
156//!     emp_id INT PRIMARY KEY,
157//!     dept_id INT,
158//!     FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE CASCADE
159//! );
160//! ```
161//!
162//! ```sql
163//! CREATE TABLE employees (
164//!     emp_id INT PRIMARY KEY,
165//!     dept_id INT,
166//!     FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL
167//! );
168//! ```
169//!
170//! ```sql
171//! CREATE TABLE employees (
172//!     emp_id INT PRIMARY KEY,
173//!     dept_id INT,
174//!     FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE RESTRICT
175//! );
176//! ```
177//!
178//! ## Alter table
179//!
180//! ```sql
181//! ALTER TABLE test RENAME TO foo;
182//! ```
183//!
184//! ```sql
185//! ALTER TABLE test ADD COLUMN foo INT;
186//! ```
187//!
188//! ```sql
189//! ALTER TABLE test DROP COLUMN foo;
190//! ```
191//!
192//! ```sql
193//! ALTER TABLE test RENAME COLUMN old_col TO new_col;
194//! ```
195//!
196//! ## Create index
197//!
198//! ```sql
199//! CREATE INDEX idx ON table_name (id, org);
200//! ```
201//!
202//! ```sql
203//! CREATE UNIQUE INDEX idx ON table_name (id, org);
204//! ```
205//!
206//! ```sql
207//! CREATE INDEX IF NOT EXISTS idx ON table_name (id, org);
208//! ```
209//!
210//! ## Drop
211//!
212//! ```sql
213//! DROP TABLE test;
214//! ```
215//!
216//! ```sql
217//! DROP TABLE IF EXISTS test;
218//! ```
219//!
220//! ```sql
221//! DROP INDEX idx ON test;
222//! ```
223//!
224//! ```sql
225//! DROP INDEX IF EXISTS idx ON test;
226//! ```
227//!
228//! ## Insert
229//!
230//! ```sql
231//! INSERT INTO TEST(id, key, value) VALUES(NULL, 1, "one"), (NULL, 2, "two");
232//! ```
233//!
234//! With placeholders:
235//! ```sql
236//! INSERT INTO TEST(id, key, value) VALUES(?, ?, ?), (?, ?, ?);
237//! ```
238//!
239//! With placeholders casts:
240//! ```sql
241//! INSERT INTO TEST(id, key, value) VALUES(?::json, ?::uuid, ?);
242//! ```
243//!
244//! ## Update
245//!
246//! ```sql
247//! UPDATE TEST SET value="foo" WHERE key = 1;
248//! ```
249//!
250//! ## Select
251//!
252//! ```sql
253//! SELECT * FROM test;
254//! ```
255//!
256//! ```sql
257//! SELECT id, key, * FROM test;
258//! ```
259//!
260//! Two-parts compound identifiers are supported:
261//! ```sql
262//! SELECT test.id, key FROM test;
263//! ```
264//!
265//! ```sql
266//! SELECT test.id, test.key, * FROM test;
267//! ```
268//!
269//! ### Count
270//!
271//! ```sql
272//! SELECT count(*) FROM test;
273//! ```
274//!
275//! ```sql
276//! SELECT count(id) FROM test;
277//! ```
278//!
279//! ### Group by
280//!
281//! ```sql
282//! SELECT key, COUNT(*) FROM test GROUP BY key;
283//! ```
284//!
285//! ### Order by
286//!
287//! ```sql
288//! SELECT * FROM test ORDER BY id ASC, key DESC;
289//! ```
290//!
291//! ### Where
292//!
293//! ```sql
294//! SELECT * FROM test WHERE id = 1 AND key = "foo";
295//! ```
296//!
297//! ```sql
298//! SELECT * FROM test WHERE id = ?;
299//! ```
300//!
301//! ```sql
302//! SELECT * FROM test WHERE id = ? AND value = ? OR id = ?;
303//! ```
304//!
305//! ```sql
306//! SELECT * FROM test WHERE id IN (1, "2", ?);
307//! ```
308//!
309//! ```sql
310//! SELECT * FROM test WHERE id NOT IN (1, "2", ?);
311//! ```
312//!
313//! ### Join
314//!
315//! ```sql
316//! SELECT * FROM foo
317//!     JOIN bar ON foo.id = bar.id
318//!     JOIN baz ON foo.id = baz.id
319//! ;
320//! ```
321//!
322//! ```sql
323//! SELECT * FROM foo
324//!     INNER JOIN bar ON foo.id = bar.id
325//!     INNER JOIN baz ON foo.id = baz.id
326//! ;
327//! ```
328//!
329//! ## Delete
330//! ```sql
331//! DELETE FROM test;
332//! ```
333//!
334//! ```sql
335//! DELETE FROM test WHERE key = 1;
336//! ```
337mod ext;
338mod migrator;
339
340#[doc(hidden)]
341pub use ext::AcquireExt;
342
343pub struct DatabaseRepository<D>
344where
345    D: sqlx::Database,
346{
347    pub database_url: String,
348    pub pool: sqlx::Pool<D>,
349}
350
351type StdError = Box<dyn std::error::Error + Send + Sync + 'static>;
352type Result<T, E = StdError> = std::result::Result<T, E>;
353
354impl<D: sqlx::Database + std::fmt::Debug> std::fmt::Debug for DatabaseRepository<D> {
355    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
356        f.debug_struct("DatabaseRepository")
357            .field("database_url", &self.database_url)
358            .field("pool", &self.pool)
359            .finish()
360    }
361}
362
363fn hide_credentials(url: &str) -> Result<String> {
364    let mut url = url::Url::parse(url)?;
365    match url.scheme() {
366        "sqlite" => Ok(url.to_string()),
367        _ => {
368            url.set_username("").or(Err("failed to hide username"))?;
369            url.set_password(None).or(Err("failed to hide password"))?;
370            Ok(url.to_string())
371        }
372    }
373}
374
375impl<D: sqlx::Database> DatabaseRepository<D> {
376    pub async fn new(url: &str, pool: sqlx::Pool<D>) -> Result<Self> {
377        Ok(Self {
378            database_url: hide_credentials(url)?,
379            pool,
380        })
381    }
382}
383
384#[doc(hidden)]
385pub trait SqlxDBNum: std::fmt::Debug {
386    fn pos() -> usize {
387        usize::MAX
388    }
389}
390
391impl SqlxDBNum for sqlx::Postgres {
392    fn pos() -> usize {
393        0
394    }
395}
396
397impl SqlxDBNum for sqlx::Sqlite {
398    fn pos() -> usize {
399        1
400    }
401}
402
403impl SqlxDBNum for sqlx::MySql {
404    fn pos() -> usize {
405        2
406    }
407}
408
409#[macro_export]
410macro_rules! migration {
411    ($name:expr, $migration:expr) => {
412        ::sqlx_repo::prelude::Migration {
413            name: $name,
414            queries: ::sqlx_repo::__hidden::gen_query!($migration),
415        }
416    };
417}
418
419#[macro_export]
420macro_rules! migrator {
421    ($($migrations:tt)*) => {
422        ::sqlx_repo::prelude::init_migrator::<D>($($migrations)*)
423    }
424}
425
426pub mod prelude {
427    pub use super::{
428        ext::AcquireExt,
429        migration, migrator,
430        migrator::{init_migrator, Migration},
431        DatabaseRepository, SqlxDBNum,
432    };
433    pub use chrono;
434    pub use futures;
435    pub use serde_json;
436    pub use sqlx::{self, Acquire, Arguments, Row as _};
437    pub use sqlx_repo_macros::repo;
438    pub use url;
439    pub use uuid;
440}
441
442#[doc(hidden)]
443pub mod __hidden {
444    pub use sqlx_repo_macros::{gen_query, query};
445}
sqlx_repo/lib.rs

sqlx_repo/
lib.rs
