Rusqlite is a Rust implementation of the SQLite database engine. Qxf2 is learning Rust and sharing our learnings through blog posts. In this post, we will explore the key features of Rusqlite and how to get started using it in Rust projects. We’ll cover Installation, Creating the Database , Creating tables and adding data into the tables.
How to get started
1. Get started by creating a new RUST project using the command:
cargo new rust_db_rusqlite |
2. Change the current directory to the newly created project:
cd rust_db_rusqlite |
3. Integrate the Rusqlite crate into the project by executing:
cargo add rusqlite |
4. Open the Cargo.toml file to view and manage your project’s dependencies:
The code snippets provided in this blog can be found here
Creating the Database
Now that Rusqlite is installed and configured, we can move on to creating a database and connecting to it. Here’s how you can do it:
1. Open main.rs and import the necessary modules by adding the following code:
use rusqlite::{Connection, Result}; |
2. Create a new database by initializing a connection to it. We’ll proceed with creating a database named “employee”.
fn main() -> Result<()> { // File path for the employee database file let db_file_path = "employee.db"; let _conn = match Connection::open(&db_file_path) { Ok(c) => c, Err(e) => { eprintln!("Error Opening or Creating database: {}", e); return Err(e.into()); } }; Ok(()) } |
Note : Please substitute “db_file_path” with the specific path/name where you intend to create your database file.
In order to streamline the creation of a new database, we’ll introduce a method to verify its existence in advance. This adjustment ensures that the database is either created if it doesn’t exist or opened if it already does.
use rusqlite::{Connection, Result}; use std::fs; fn db_exists(db_file_path: &str) -> bool { // This function checks if a database file exists at the specified file path fs::metadata(db_file_path).is_ok() } fn main() -> Result<()> { // File path for the employee database file let db_file_path = "employee.db"; // check for DB Exists or not if !db_exists(&db_file_path) { println!("No existing database! New database will be created in the path:{}", db_file_path); } else { println!("Database Exists !!! Opened DB created at: {}", db_file_path); } // Connect to the database let conn = match Connection::open(db_file_path) { Ok(conn) => conn, Err(e) => { println!("Error connecting to database: {}.", e); return Err(e); } }; |
Here is a screenshot showing creation of new database:
If the database is already existing, then the output would look something like this:
That’s it! You have now created a database and connected to it using Rusqlite. In the next section, we will explore how to create tables within the database.
Designing and creating tables using Rusqlite
Now that we have successfully created a database “employee” and connected to it using Rusqlite, let’s move on to designing and creating tables within the database. Tables are where we store our data, and they define the structure of the data we want to store.
To design a table, you need to define its schema. This includes specifying the columns and their data types.
In this example, we are creating tables named “project”, “task”, “employees”.
fn create_tables(conn: &Connection) -> Result<()> { // Create the project table match conn.execute( "CREATE TABLE IF NOT EXISTS project( project_name TEXT PRIMARY KEY, description TEXT, deadline DATE )", (), ) { Ok(_) => { println!("Created table project"); } Err(err) => return Err(err.into()), } // Create the task table match conn.execute( "CREATE TABLE IF NOT EXISTS task ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, priority INTEGER DEFAULT 1, details TEXT, status TEXT, deadline DATE, completed_on DATE, project TEXT NOT NULL REFERENCES project(project_name) )", (), ) { Ok(_) => { println!("Created table task"); } Err(err) => return Err(err.into()), } // Create the employees table match conn.execute( "CREATE TABLE IF NOT EXISTS employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, position TEXT )", (), ) { Ok(_) => { println!("Created table employees"); } Err(err) => return Err(err.into()), } Ok(()) } |
Note that we are using the `CREATE TABLE IF NOT EXISTS` statement to ensure that the table is only created if it doesn’t already exist.
Update the main method with the following code
match create_tables(&conn){ Ok(()) => { println!("Tables created if they don't exist."); } Err(err) => { eprintln!("Error creating tables: {}", err); return Err(err); } } |
This will handle the errors while creating the database tables.
That’s it! Now that you have used Rusqlite to design and create tables in your database . We will dive into the inserting of data in the next section.
Inserting Data into the Tables
We have successfully designed and created a tables in our Rusqlite database “employee”, it’s time to explore how to insert data into the table.
First we define struct for the tables that we created .
#[derive(Debug)] struct Project { project_name: String, description: Option<String>, deadline: Option<String>, } #[derive(Debug)] struct Task { id: i32, priority: i32, details: Option<String>, status: Option<String>, deadline: Option<String>, completed_on: Option<String>, project: String, } #[derive(Debug)] struct Employee { id: i32, name: String, position: Option<String>, } |
The function insert_
If the execution of the query is successful, the function returns Ok(()), indicating that the operation was successful. If there is an error, the function returns an error value.
fn insert_project(conn: &Connection, project: &Project) -> Result<(), rusqlite::Error> { match conn.execute( "INSERT INTO project (project_name, description, deadline) VALUES (?1, ?2, ?3)", (&project.project_name, &project.description, &project.deadline), ) { Ok(_) => {println!("Inserting {:?} into the database.",project); Ok(())}, Err(err) => Err(err), } } fn insert_task(conn: &Connection, task: &Task) -> Result<(), rusqlite::Error> { match conn.execute( "INSERT INTO task (id, priority, details, status, deadline, completed_on, project) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)", ( &task.id, &task.priority, &task.details, &task.status, &task.deadline, &task.completed_on, &task.project, ), ) { Ok(_) => {println!("Inserting {:?} into the database.",task); Ok(())}, Err(err) => Err(err), } } fn insert_employee(conn: &Connection, employee: &Employee) -> Result<(), rusqlite::Error> { match conn.execute( "INSERT INTO employees (id, name, position) VALUES (?1, ?2, ?3)", (&employee.id, &employee.name, &employee.position), ) { Ok(_) => {println!("Inserting {:?} into the database.",employee); Ok(())}, Err(err) => Err(err), } } |
Update the main with the values that need to insert into the database.
match create_tables(&conn){ Ok(()) => { println!("Tables created if they didn't exist."); // Initializing the project data let project = Project { project_name: "Sample Project2".to_string(), description: Some("This is a sample project2".to_string()), deadline: Some("2023-12-31".to_string()), }; // Insert new project into the database. match insert_project(&conn, &project) { Ok(_) => { println!("Project data inserted successfully!"); } Err(e) => { println!("Failed to insert project: {:?}", e); return Err(e.into()); } } // Initializing the task data let task = Task { id: 3, priority: 2, details: Some("Complete task 1".to_string()), status: Some("Incomplete".to_string()), deadline: Some("2023-12-15".to_string()), completed_on: None, project: "Sample Project2".to_string(), }; // Insert new task into the database. match insert_task(&conn, &task) { Ok(_) => { println!("Task data inserted successfully!"); } Err(e) => { println!("Failed to insert task: {:?}", e); return Err(e.into()); } } // Initializing employee data directly let employee = Employee { id: 2, name: "John samuel".to_string(), position: Some("Developer".to_string()), }; // Insert new employee into the database. match insert_employee(&conn, &employee) { Ok(_) => { println!("Employee data inserted successfully!"); } Err(e) => { println!("Failed to insert employee: {:?}", e); return Err(e.into()); } } } Err(err) => { eprintln!("Error creating tables: {}", err); return Err(err); } } |
Conclusion
You’ve successfully learned how to create databases and tables using Rusqlite. This powerful library allows you to manage and manipulate your database with ease. By following the step-by-step guide we provided, you should now have the necessary knowledge to create and design tables according to your specific needs. Remember, creating a well-organized and efficient database is essential for the performance of your application. Make sure to properly plan and design your tables before creating them. Use appropriate data types, primary keys, and indexes to optimize your database’s performance.
We hope this guide has been helpful in understanding how to create databases and tables using Rusqlite. If you have any questions or need further assistance, feel free to reach out to us.
Hire Qxf2
Qxf2 employs experienced testers with a technical bent of mind. Our testers stay connected with the latest tooling, trends and techniques in the testing world. We also go out of our way to learn new things from our developer colleagues at clients. Like you see in this post (worked on in 2023) we have started to adopt Rust. If you are looking for smart, hands-on technical testers who can work independently and guide your testing strategies, get in touch with Qxf2.
I started my career in IIIT Bangalore incubated startup company as a software engineer and soon started working on digital and e-learning solutions as QA. Later moved to Oracle as User Assistance QA Engineer and gained multiple Oracle cloud products and testing skills. Oracle gave opportunities with products, courses, and environments to explore and enhance testing skills. Now joined Qxf2, to explore in testing perspective. I believe Quality means doing it right, even when no one is looking. A number of bugs are not evaluating the product, the quality of the bug also matters. I love cooking, reading, and DIY crafts.
