dpway

We will explore how to use two powerful libraries in Rust for building safe and flexible SQL queries for your application: SQLx and SeaQuery. Each of these libraries plays a specific role in interacting with databases, from query construction to result mapping and filtering.

1. SQLx

SQLx is a Rust library for executing asynchronous SQL queries. It supports multiple databases, including PostgreSQL, MySQL, and SQLite. SQLx's key feature is its ability to validate SQL queries at compile time, allowing you to catch SQL errors early during the development process.

Installing SQLx

To use SQLx in your Rust project, add the library to your Cargo.toml file:

[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres"] }
tokio = { version = "1", features = ["full"] }

Starting the DB

# Start postgresql server docker image:
docker run --rm --name pg -p 5432:5432 \
   -e POSTGRES_PASSWORD=welcome \
   postgres

# (optional) To have a psql terminal on pg.
# In another terminal (tab) run psql:
docker exec -it -u postgres pg psql

# (optional) For pg to print all sql statements.
# In psql command line started above.
ALTER DATABASE postgres SET log_statement = 'all';

Connecting and Executing Queries with SQLx

Here is a basic example of how to connect to a database and execute a SQL query using SQLx:

use sqlx::postgres::PgPool;
use sqlx::Row;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPool::connect("postgres://postgres:welcome@localhost/postgres").await?;

    // Check if the table exists, and create it if it does not
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                username VARCHAR(50) NOT NULL,
                email VARCHAR(100) NOT NULL
            )",
    )
    .execute(&pool)
    .await?;

    // Execute a SELECT query
    let mut rows = sqlx::query("SELECT id, username, email FROM users")
        .fetch_all(&pool)
        .await?;

    if rows.is_empty() {
        // Insert a new user if the table exists
        let username = "new_user";
        let email = "[email protected]";
        sqlx::query("INSERT INTO users (username, email) VALUES ($1, $2)")
            .bind(username)
            .bind(email)
            .execute(&pool)
            .await?;

        rows = sqlx::query("SELECT id, username, email FROM users")
            .fetch_all(&pool)
            .await?;
    }

    // Process the result
    for row in rows {
        let id: i32 = row.try_get("id")?;
        let username: String = row.try_get("username")?;
        let email: String = row.try_get("email")?;
        println!("ID: {}, Username: {}, Email: {}", id, username, email);
    }

    Ok(())
}

2. SeaQuery

SeaQuery is a Rust library that provides a powerful way to build safe SQL queries. It allows you to construct SQL queries without writing raw SQL code, and it supports databases like PostgreSQL, MySQL, and SQLite.

Installing SeaQuery

To add SeaQuery to your project, update your Cargo.toml file:

sqlx = { version = "0.8", features = ["runtime-tokio", "postgres"] }
tokio = { version = "1", features = ["full"] }
sea-query = "0"

Building Queries with SeaQuery

SeaQuery provides a safe and easy-to-use API to construct SQL queries. Here’s an example of how to build and execute a SELECT query using SeaQuery:

use sea_query::{Iden, PostgresQueryBuilder, Query};
use sqlx::postgres::PgPool;

#[derive(Iden)]
enum Users {
    Table,
    Id,
    Username,
    Email,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPool::connect("postgres://postgres:welcome@localhost/postgres").await?;

    // Build SELECT query using SeaQuery
    let mut query = Query::select();
    query
        .from(Users::Table)
        .columns([Users::Id, Users::Username, Users::Email]);

    // Convert query to SQL
    let (sql, _) = query.build(PostgresQueryBuilder);

    // Execute the query with SQLx
    let rows = sqlx::query_as::<_, (i32, String, String)>(&sql)
        .fetch_all(&pool)
        .await?;

    // Process the result
    for (id, username, email) in rows {
        println!("ID: {}, Username: {}, Email: {}", id, username, email);
    }

    Ok(())
}

Conclusion

Combining SQLx and SeaQuery allows you to build safe, flexible, and efficient SQL queries in Rust.

• SQLx provides asynchronous support and compile-time SQL validation.
• SeaQuery helps you build SQL queries easily and safely without writing raw SQL.