SQLx
Running SQL queries is an important part of data science and engineering work, so in this section, you will learn the basics of the SQLx crate, a Rust crate with support to PostgreSQL, SQLite and MySQL. My recommendation is SQLite for simple projects running in a single machine and PostgreSQL for anything beyond that.
Setup database connection
First, begin by installing sqlx-cli tools with cargo install sqlx-cli.
next create a .env at the top level of your Rust project (same folder that Cargo.toml is) containing the configuration for connecting to database, if using SQLite, this is basically the location of the database file, e.g., to store it in the db folder:
DATABASE_URL=sqlite://db/db.sqlite3
For Postgresql this can be:
DATABASE_URL=postgres://pg_user:password@server_address/db_name
Then, you can run sqlx database create to test the configuration and create the database if does not exist yet.
Create your database schema
If you database doesn't have a schema (tables, etc), SQLx can do that for you and keep a record which ones it had created.
For that, create a folder called migrations, include your desired schema (see examples here and here) and when ready run:
sqlx migrate run && cargo run --release --locked
As SQLx keeps track of which schemas it had already applied this command (as well as the database create) is idempotent.
Setup your Rust project to use SQLx
Configure your project to use sqlx by adding the following to your Cargo.toml:
1 [package]
2 name = "sqlx-example"
3 version = "0.1.0"
4 edition = "2021"
5
6 # See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
7
8 [dependencies]
9 sqlx = { version = "0.7", features = [
10 "postgres",
11 "runtime-tokio",
12 "tls-native-tls",
13 ] }
14 dotenvy = "0.15"
15 tokio = { version = "1", features = ["full"] }
16 futures-lite = "2"
If using SQLite, all you need to do is change postgres with sqlite in the block. The dotenvy is not strictly necessary but will be handy in the section to read the .env file.
SQLx usage examples
Finally, let's see some examples of CRUD SQL queries using SQLx.
1 use dotenvy::var;
2 use futures_lite::stream::StreamExt;
3 use sqlx::postgres::PgPoolOptions;
4 use sqlx::Row;
5 use tokio::time::{sleep, Duration};
6
7 #[tokio::main]
8 async fn main() -> Result<(), Box<dyn std::error::Error>> {
9 let database_url = var("DATABASE_URL")
10 .map_err(|e| format!("Failed to get DATABASE_URL: {}", e))?;
11
12 let db_pool = PgPoolOptions::new()
13 .max_connections(5)
14 .connect(database_url.as_str())
15 .await?;
16
17 let some_var = "some_value";
18
19 // get a single row
20 let (some_timestamp,): (i64,) = sqlx::query_as(
21 r#"SELECT MAX(timestamp)
22 FROM trades_raw WHERE security = $1;"#,
23 )
24 .bind(some_var)
25 .fetch_one(&db_pool)
26 .await?;
27
28 let price = 13.4;
29 let security = "AAPL";
30
31 // insert/upsert
32 while let Err(err) = sqlx::query(
33 "INSERT INTO trades_resampled
34 (price, security, timestamp)
35 VALUES ($1, $2, $3)
36 ON CONFLICT (security, timestamp)
37 DO UPDATE SET price = EXCLUDED.price;
38 ;",
39 )
40 .bind(price)
41 .bind(security)
42 .bind(some_timestamp)
43 .execute(&db_pool)
44 .await
45 {
46 println!("Error while inserting data: {:?}", err);
47 // sleep for a while and retry
48 sleep(Duration::from_millis(300)).await;
49 }
50
51 let session_token = "some_token";
52
53 // a simple delete
54 let _ = sqlx::query(
55 "DELETE FROM user_sessions
56 WHERE session_token = ?",
57 )
58 .bind(session_token)
59 .execute(&db_pool)
60 .await;
61
62 // get many rows
63 let sql_query = r#"
64 SELECT price,timestamp FROM some_table
65 WHERE security = $1
66 LIMIT 10 OFFSET 5
67 "#;
68 let mut rows = sqlx::query(sql_query).bind(security).fetch(&db_pool);
69 let mut prices = Vec::new();
70 let mut timestamps = Vec::new();
71 while let Some(row) = rows.try_next().await? {
72 let price: i64 = row.try_get("price")?;
73 let timestamp: i64 = row.try_get("timestamp")?;
74 prices.push(price);
75 timestamps.push(timestamp);
76 }
77
78 Ok(())
79 }
Vector search with Postgres and pgvector
See rust-pgvector-example for a didactic example of using Rust SQLx with Postgres for vector search with embeddings generated from Google API.
If you found this project helpful, please consider making a donation.