Published in

Rustaceans

3 min readFeb 26, 2024

Support me by buying an item from my wishlist, visiting my reviews site or buy me a coffee!

Getting Started with SQLx and SQLite in Rust

If you’re diving into Rust and want to work with databases, SQLx is a fantastic choice. In this tutorial, we’ll explore the basics of using SQLx with SQLite, a lightweight embedded SQL database engine. By the end, you’ll have a solid understanding of how to create an SQLite database, perform SQL operations, and set up migrations using SQLx.

What is SQLx?

SQLx is an easy-to-use async SQL crate for Rust. Here are some key features:

Compile-time Checked Queries: SQLx ensures that your queries are valid at compile time, reducing runtime errors.
Async Support: It works seamlessly with async runtimes like async-std, tokio, and actix.
Cross-Platform: SQLx compiles anywhere Rust is supported.
Connection Pooling: Built-in connection pooling for efficient database access.

You can use SQLx with various databases, including PostgreSQL, MySQL, SQLite, and MSSQL.

What is SQLite?

SQLite is an embedded SQL database engine that operates serverless. It reads and writes directly to ordinary disk files, making it lightweight and efficient. Here are some key points about SQLite:

Compact: Even with all features enabled, the library size can be less than 750KiB.
Cross-Platform: The database file format works across different systems (e.g., 32-bit and 64-bit).
Popular: SQLite is widely used as an application file format, especially on edge devices like mobile phones and tablets

Project Setup

Let’s create a simple project to explore SQLx and SQLite. Follow these steps:

Create a new Rust project:

cargo new sqlx-sqlite-basics-tutorial

2. Add the necessary dependencies to your Cargo.toml:

[package]
name = "sqlx-sqlite-basics-tutorial"
version = "0.1.0"
edition = "2021"

[dependencies]
sqlx = { version = "0.6.2", features = ["runtime-tokio-native-tls", "sqlite"] }
tokio = { version = "1.20.0", features = ["macros"] }

sqlx: The Rust SQL Toolkit. It provides async, pure Rust SQL capabilities with compile-time checked queries.
tokio: An async runtime for Rust.

SQLx Basics with SQLite

Creating an SQLite Database

Let’s start by creating an SQLite database. We’ll use SQLx to interact with it. Here’s how:

Define your data struct. For example, let’s create a User struct:

#[derive(Debug, sqlx::FromRow)]
struct User {
    id: i32,
    username: String,
    email: String,
}

2. Establish a connection to the SQLite database:

let pool = sqlx::sqlite::SqlitePool::connect("sqlite:mydb.db").await?;

3. Execute SQL queries using SQLx:

let users: Vec<User> = sqlx::query_as!("SELECT * FROM users").fetch_all(&pool).await?;

SQLx Migrations

Migrations allow you to manage database schema changes over time. Let’s set up migrations:

Install the SQLx CLI tool:

cargo install sqlx-cli

2. Create a migration script:

sqlx migrate add create_users_table

3. Apply migrations in your Rust code:

sqlx::migrate!().run(&pool).await?;

4. Update your queries to reflect the new schema.

Conclusion

SQLx simplifies database interactions in Rust, and SQLite is an excellent choice for lightweight applications. With SQLx, you’ll be well-equipped to handle databases in your future Rust projects.

Happy coding! 🦀