Integrating Rust with SQLite: A Practical Guide For Beginners Devs 🦀
Tags: rust
Published: 2024-03-02 18:02:25

---
title: Integrating Rust with SQLite: A Practical Guide For Beginners Devs 🦀
published: true
date: 2024-03-01 14:39:11 UTC
tags: Rust
canonical_url: https://eleftheriabatsou.hashnode.dev/integrating-rust-with-sqlite-guide-for-beginners
---

## Introduction

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r11bauo2moot7of7s7nu.jpeg)

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](https://github.com/EleftheriaBatsou/connect-SQLite-and-rust).

## **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.
![Importing Necessary Crates and Modules](https://cdn.hashnode.com/res/hashnode/image/upload/v1709135002004/9215ebff-3e46-46e9-b95d-52c4087686e4.png)

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.`
![Creating the Database Schema](https://cdn.hashnode.com/res/hashnode/image/upload/v1709135019752/5d2d2e61-ce4d-48e9-b4e6-bb1efbfe459b.png)

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
![Main Function](https://cdn.hashnode.com/res/hashnode/image/upload/v1709135038901/1ff468a7-ac23-4b52-b9df-718172e5017a.png)

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)](https://twitter.com/BatsouElef/status/1762866984302063938). 💙

## **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! 🦀

P.S. More Rust content is coming soon! Stay tuned.

* * *

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

🥰 If you liked this article, consider sharing it.

🔗 [**All links**](https://limey.io/batsouelef) | [**X**](https://twitter.com/BatsouElef) | [**LinkedIn**](https://www.linkedin.com/in/eleftheriabatsou/)