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:
Importing Necessary Crates and Modules : It starts by importing required modules and crates such as
sqlx
for database operations andstd::result::Result
for handling operation results.
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 usingSqlitePool::connect
. After establishing a connection, it executes SQL commands to enable foreign keys and create two tables:settings
andproject
, withproject
having a foreign key reference tosettings.
Main Function : The
async fn main
is an asynchronous entry point of the program. It first checks if the database exists usingSqlite::database_exists
, and if not, it creates the database withSqlite::create_database
. Then, it callscreate_schema
to set up the database schema. After the schema creation, it connects to the database again to insert a test entry into thesettings
table
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 usingsqlx::query
.Closing Connections : Both in the
create_schema
function and the main program, database connections are explicitly closed after the operations are completed usingpool.close().await
.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.
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 theasync-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! 🦀
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.
Top comments (0)