Integrating Rust with SQLite: A Practical Guide For Beginners Devs 🦀

Introduction

Welcome, fellow Rustaceans! Are you ready to embark on an exciting journey of marrying Rust with SQLite to create robust applications? Whether you’re dabbling in database management for the first time or looking to refine your skills, this guide is tailored just for you.

Today, we’ll explore how to seamlessly integrate Rust with SQLite, ensuring you have a solid foundation to build upon.

SQLite, a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine, is an excellent match for Rust’s safety and performance.

By the end of this guide, you’ll have a working example of creating and manipulating a database using Rust, all while embracing asynchronous programming.

The Journey Begins

Our adventure starts with the sqlx crate, a fantastic tool that offers asynchronous connections to various databases, including SQLite. The beauty of sqlx lies in its compile-time checked SQL queries and its compatibility with Rust's async features.

The journey will cover:

• Setting up your Rust project with sqlx and SQLite.
• Creating a database schema: diving into creating tables and understanding foreign keys.
• Inserting data: a practical example of adding data to our database.
• Managing database connections efficiently and safely.

Creating Harmony between Rust and SQLite

This guide will walk you through initializing your Rust project, connecting to an SQLite database, and executing SQL commands to shape your database schema. As a beginner myself, we’ll also explore the intricacies of asynchronous programming in Rust, ensuring that you’re well-equipped to handle database operations in your future projects.

Connecting Rust with SQLite

Here’s a comprehensive look at the Rust program designed to connect with an SQLite database, create a schema, and insert data:

use std::result::Result;
use sqlx::{sqlite::SqliteQueryResult, Sqlite, SqlitePool, migrate::MigrateDatabase};

async fn create_schema(db_url:&str) -> Result<SqliteQueryResult, sqlx::Error> {
    let pool = SqlitePool::connect(&db_url).await?;
    let qry = 
    "PRAGMA foreign_keys = ON;
    CREATE TABLE IF NOT EXISTS settings
    (
        settings_id     INTEGER PRIMARY KEY NOT NULL,
        description     TEXT                NOT NULL,
        created_on      DATETIME DEFAULT    (datetime('now', 'localtime')),
        updated_on      DATETIME DEFAULT    (datetime('now', 'localtime')),
        done            BOOLEAN             NOT NULL DEFAULT 0
    );
    CREATE TABLE IF NOT EXISTS project
    (
        project_id      INTEGER PRIMARY KEY AUTOINCREMENT,
        product_name    TEXT,
        created_on      DATETIME DEFAULT    (datetime('now', 'localtime')),
        updated_on      DATETIME DEFAULT    (datetime('now', 'localtime')),
        img_directory   TEXT     NOT NULL,
        out_directory   TEXT     NOT NULL,
        status          TEXT     NOT NULL,
        settings_id     INTEGER  NOT NULL DEFAULT 1,
        FOREIGN KEY (settings_id) REFERENCES settings (settings_id) ON UPDATE SET NULL ON DELETE SET NULL
    );";
    let result = sqlx::query(&qry).execute(&pool).await;
    pool.close().await;
    return result;
}

#[async_std::main]
async fn main() {
    let db_url = String::from("sqlite://sqlite.db");
    if !Sqlite::database_exists(&db_url).await.unwrap_or(false){
        Sqlite::create_database(&db_url).await.unwrap();
        match create_schema(&db_url).await {
            Ok(_) => println!("database created succesfully"),
            Err(e) => panic!("{}", e)
        }
    }
    let instances = SqlitePool::connect(&db_url).await.unwrap();
    let qry = "INSERT INTO settings (description) VALUES($1)";
    let result = sqlx::query(&qry).bind("testing").execute(&instances).await;

    instances.close().await;
    println!("{:?}", result);
}

You can check the full code on my GitHub repo.

Understanding the Code

As mentioned above, this Rust program demonstrates how to integrate Rust with SQLite using the sqlx crate, which provides asynchronous support for interacting with SQL databases.

Here’s a breakdown of its key components:

1. Importing Necessary Crates and Modules: It starts by importing required modules and crates such as sqlx for database operations and std::result::Result for handling operation results.

2. Creating the Database Schema: The create_schema asynchronous function is defined to create a new schema in the SQLite database. It takes a database URL as an input and attempts to connect to the database using SqlitePool::connect. After establishing a connection, it executes SQL commands to enable foreign keys and create two tables: settings and project, with project having a foreign key reference to settings.

3. Main Function: The async fn main is an asynchronous entry point of the program. It first checks if the database exists using Sqlite::database_exists, and if not, it creates the database with Sqlite::create_database. Then, it calls create_schema to set up the database schema. After the schema creation, it connects to the database again to insert a test entry into the settings table

4. Inserting Data: After creating the schema, the program inserts a row into the settings table with a description of "testing". This demonstrates how to insert data into the database using sqlx::query.

5. Closing Connections: Both in the create_schema function and the main program, database connections are explicitly closed after the operations are completed using pool.close().await.

6. Error Handling: The program includes basic error handling. In the schema creation and main function, it uses Rust’s pattern matching to handle the result of asynchronous operations, printing a success message or panicking in case of an error.

7. Running the Program: To run this program, you’ll need an asynchronous runtime. This is indicated by the #[async_std::main] attribute, which specifies that the async-std crate's runtime should be used.

P.S. In case you want to support me, I also posted about this on X (Twitter). 💙

Concluding Thoughts

As our journey comes to a close, remember that integrating Rust with SQLite is only the beginning. Embrace the process, experiment with different queries, and don’t be afraid to dive deeper into sqlx and its capabilities.

This guide aimed to demystify the process of connecting Rust with SQLite, providing you with the tools and knowledge to start building your own applications. As you continue on your Rust journey, remember that the community is here to support you. Happy coding! 🦀

Note: More Rust content is coming soon!

👋 Hello, I’m Eleftheria, Community Manager, developer, public speaker, and content creator.

🥰 If you liked this article, consider sharing it.

🔗 All links | X | LinkedIn

Originally published at https://eleftheriabatsou.hashnode.dev on March 1, 2024.