{"id":21193,"date":"2024-03-18T02:26:05","date_gmt":"2024-03-18T06:26:05","guid":{"rendered":"https:\/\/qxf2.com\/blog\/?p=21193"},"modified":"2024-03-18T02:26:05","modified_gmt":"2024-03-18T06:26:05","slug":"rust-meets-relational-databases-a-rusqlite-guide","status":"publish","type":"post","link":"https:\/\/qxf2.com\/blog\/rust-meets-relational-databases-a-rusqlite-guide\/","title":{"rendered":"Rust Meets Relational Databases: A Rusqlite Guide"},"content":{"rendered":"

Rusqlite<\/a> is a Rust implementation of the SQLite database engine. Qxf2<\/a> 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. <\/p>\n

How to get started <\/h3>\n

1. Get started by creating a new RUST project using the command:<\/p>\n

 \r\ncargo new rust_db_rusqlite\r\n<\/pre>\n
2. Change the current directory to the newly created project:<\/p>\n
 \r\ncd rust_db_rusqlite\r\n<\/pre>\n
3. Integrate the Rusqlite crate into the project by executing:<\/p>\n
 \r\ncargo add rusqlite \r\n<\/pre>\n
4. Open the Cargo.toml file to view and manage your project’s dependencies:<\/p>\n
\"cargo_toml_added<\/a>
\n<\/br>
\n<\/br>
\n<\/br>
\n<\/br>
\nThe code snippets provided in this blog can be found here<\/a><\/p>\n
\n
Creating the Database<\/h3>\n
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:<\/p>\n
1. Open main.rs and import the necessary modules by adding the following code: <\/p>\n
 \r\nuse rusqlite::{Connection, Result};\r\n<\/pre>\n
2. Create a new database by initializing a connection to it. We’ll proceed with creating a database named “employee”<\/strong>.<\/p>\n
 \r\nfn main() -> Result<()>  {\r\n    \/\/ File path for the employee database file\r\n    let db_file_path = \"employee.db\";\r\n    let _conn = match Connection::open(&db_file_path) {\r\n        Ok(c) => c,\r\n        Err(e) => {\r\n            eprintln!(\"Error Opening or Creating database: {}\", e);\r\n            return Err(e.into());\r\n        }\r\n    };\r\n\r\n    Ok(())\r\n\r\n   }\r\n<\/pre>\n
Note : Please substitute “db_file_path” with the specific path\/name where you intend to create your database file.<\/em><\/p>\n
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.<\/p>\n
 \r\nuse rusqlite::{Connection, Result};\r\nuse std::fs;\r\n\r\nfn db_exists(db_file_path: &str) -> bool {\r\n\/\/ This function checks if a database file exists at the specified file path\r\n    fs::metadata(db_file_path).is_ok()\r\n}\r\n\r\nfn main() -> Result<()>  {\r\n    \/\/ File path for the employee database file\r\n    let db_file_path = \"employee.db\";\r\n    \/\/ check for DB Exists or not\r\n    if !db_exists(&db_file_path) {         \r\n        println!(\"No existing database! New database will be created in the path:{}\", db_file_path);\r\n    }\r\n    else {        \r\n        println!(\"Database Exists !!! Opened DB created at: {}\", db_file_path);\r\n    }\r\n    \/\/ Connect to the database\r\n    let conn = match Connection::open(db_file_path) {\r\n        Ok(conn) => conn,\r\n        Err(e) => {\r\n            println!(\"Error connecting to database: {}.\", e);\r\n            return Err(e);\r\n        }\r\n    };\r\n<\/pre>\n
Here is a screenshot showing creation of new database:
\n\"rusqlite<\/a>
\n<\/br>
\n<\/br>
\n<\/br>
\nIf the database is already existing, then the output would look something like this: <\/p>\n
\"rusqlite<\/a>
\n<\/br>
\n<\/br>
\n<\/br>
\nThat’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.<\/p>\n
<\/ hr><\/p>\n
Designing and creating tables using Rusqlite<\/h3>\n
Now that we have successfully created a database “employee<\/strong>” 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. <\/p>\n
To design a table, you need to define its schema. This includes specifying the columns and their data types.<\/p>\n
In this example, we are creating tables named “project”, “task”, “employees”. <\/strong><\/p>\n
\r\nfn create_tables(conn: &Connection) -> Result<()> {\r\n    \/\/ Create the project table\r\n    match conn.execute(\r\n        \"CREATE TABLE IF NOT EXISTS project(\r\n            project_name TEXT PRIMARY KEY,\r\n            description  TEXT,\r\n            deadline     DATE\r\n        )\",\r\n        (),\r\n    ) {\r\n        Ok(_) => {\r\n            println!(\"Created table project\");            \r\n        }\r\n        Err(err) => return Err(err.into()),\r\n    }\r\n\r\n    \/\/ Create the task table\r\n    match conn.execute(\r\n        \"CREATE TABLE IF NOT EXISTS task (\r\n            id           INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\r\n            priority     INTEGER DEFAULT 1,\r\n            details      TEXT,\r\n            status       TEXT,\r\n            deadline     DATE,\r\n            completed_on DATE,\r\n            project      TEXT NOT NULL REFERENCES project(project_name)\r\n    \r\n         )\",\r\n        (),\r\n    ) {\r\n        Ok(_) => {\r\n            println!(\"Created table task\");           \r\n        }\r\n        Err(err) => return Err(err.into()),\r\n    }\r\n\r\n    \/\/ Create the employees table\r\n    match conn.execute(\r\n        \"CREATE TABLE IF NOT EXISTS employees (\r\n            id       INTEGER PRIMARY KEY,\r\n            name     TEXT NOT NULL,\r\n            position TEXT\r\n        )\",\r\n        (),\r\n    ) {\r\n        Ok(_) => {\r\n            println!(\"Created table employees\");           \r\n        }\r\n        Err(err) => return Err(err.into()),\r\n    }\r\n    Ok(())\r\n}\r\n\r\n\r\n<\/pre>\n
Note that we are using the `CREATE TABLE IF NOT EXISTS<\/strong>` statement to ensure that the table is only created if it doesn’t already exist.<\/p>\n
Update the main method with the following code <\/p>\n
\r\n    match create_tables(&conn){\r\n            Ok(()) => {\r\n            println!(\"Tables created if they don't exist.\"); \r\n            }       \r\n            Err(err) => {\r\n            eprintln!(\"Error creating tables: {}\", err);              \r\n            return Err(err);\r\n        }\r\n    }\r\n\r\n<\/pre>\n
This will handle the errors while creating the database tables. <\/p>\n
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. <\/p>\n
\n
Inserting Data into the Tables<\/h3>\n
We have successfully designed and created a tables in our Rusqlite database “employee”<\/strong>, it’s time to explore how to insert data into the table. <\/p>\n
First we define struct for the tables that we created . <\/p>\n
 \r\n#[derive(Debug)]\r\nstruct Project {\r\n    project_name: String,\r\n    description: Option,\r\n    deadline: Option,\r\n}\r\n\r\n#[derive(Debug)]\r\nstruct Task {\r\n    id: i32,\r\n    priority: i32,\r\n    details: Option,\r\n    status: Option,\r\n    deadline: Option,\r\n    completed_on: Option,\r\n    project: String,\r\n}\r\n\r\n#[derive(Debug)]\r\nstruct Employee {\r\n    id: i32,\r\n    name: String,\r\n    position: Option,\r\n}\r\n<\/pre>\n
The function insert_ inserts a new row\/data in the database tables. It takes a reference to a Connection object and a reference to a struct as arguments. The function uses the execute method of the connection object to execute an SQL query that inserts a new row into the  tables. The query takes parameters, which are passed in using the ? placeholders. The values of these placeholders are taken from the structs and are passed in as a tuple.<\/p>\n
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.<\/p>\n
 \r\nfn insert_project(conn: &Connection, project: &Project) ->  Result<(), rusqlite::Error> {\r\n    match conn.execute(\r\n        \"INSERT INTO project (project_name, description, deadline) VALUES (?1, ?2, ?3)\",\r\n        (&project.project_name, &project.description, &project.deadline),\r\n    ) {\r\n        Ok(_) => {println!(\"Inserting {:?} into the database.\",project);\r\n        Ok(())},\r\n        Err(err) => Err(err),\r\n    }\r\n}\r\n\r\nfn insert_task(conn: &Connection, task: &Task) ->  Result<(), rusqlite::Error> {\r\n    match conn.execute(\r\n        \"INSERT INTO task (id, priority, details, status, deadline, completed_on, project) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)\",\r\n        (\r\n            &task.id,\r\n            &task.priority,\r\n            &task.details,\r\n            &task.status,\r\n            &task.deadline,\r\n            &task.completed_on,\r\n            &task.project,\r\n        ),\r\n    ) {\r\n        Ok(_) => {println!(\"Inserting {:?} into the database.\",task);\r\n        Ok(())},\r\n        Err(err) => Err(err),\r\n    }\r\n}\r\n\r\nfn insert_employee(conn: &Connection, employee: &Employee) ->  Result<(), rusqlite::Error> {\r\n    match conn.execute(\r\n        \"INSERT INTO employees (id, name, position) VALUES (?1, ?2, ?3)\",\r\n        (&employee.id, &employee.name, &employee.position),\r\n    ) {\r\n        Ok(_) => {println!(\"Inserting {:?} into the database.\",employee);\r\n        Ok(())},\r\n        Err(err) => Err(err),\r\n    }\r\n}\r\n<\/pre>\n
Update the main with the values that need to insert into the database.<\/p>\n
 \r\n    match create_tables(&conn){\r\n            Ok(()) => {\r\n            println!(\"Tables created if they didn't exist.\");\r\n            \/\/ Initializing the project data \r\n            let project = Project {\r\n                project_name: \"Sample Project2\".to_string(),\r\n                description: Some(\"This is a sample project2\".to_string()),\r\n                deadline: Some(\"2023-12-31\".to_string()),\r\n            };\r\n            \/\/ Insert new project into the database.\r\n            match insert_project(&conn, &project) {\r\n                Ok(_) => {\r\n                        println!(\"Project data inserted successfully!\");\r\n                }                            \r\n                Err(e) => {\r\n                    println!(\"Failed to insert project: {:?}\", e);                    \r\n                    return Err(e.into());\r\n                }\r\n            }           \r\n            \/\/ Initializing the task data \r\n            let task = Task {\r\n                id: 3,\r\n                priority: 2,\r\n                details: Some(\"Complete task 1\".to_string()),\r\n                status: Some(\"Incomplete\".to_string()),\r\n                deadline: Some(\"2023-12-15\".to_string()),\r\n                completed_on: None,\r\n                project: \"Sample Project2\".to_string(),\r\n            };\r\n            \/\/ Insert new task into the database.\r\n            match insert_task(&conn, &task) {\r\n                Ok(_) => {\r\n                        println!(\"Task data inserted successfully!\");\r\n                }                            \r\n                Err(e) => {\r\n                    println!(\"Failed to insert task: {:?}\", e);                    \r\n                    return Err(e.into());\r\n                }\r\n            }\r\n            \/\/ Initializing employee data directly            \r\n            let employee = Employee {\r\n                id: 2,\r\n                name: \"John samuel\".to_string(),\r\n                position: Some(\"Developer\".to_string()),\r\n            };\r\n            \/\/ Insert new employee into the database.\r\n            match insert_employee(&conn, &employee) {\r\n                Ok(_) => {\r\n                        println!(\"Employee data inserted successfully!\");\r\n                }                            \r\n                Err(e) => {\r\n                    println!(\"Failed to insert employee: {:?}\", e);                    \r\n                    return Err(e.into());\r\n                }\r\n            }            \r\n        }        \r\n        Err(err) => {\r\n            eprintln!(\"Error creating tables: {}\", err);              \r\n            return Err(err);\r\n        }\r\n    }\r\n\r\n<\/pre>\n
\n
Conclusion<\/h3>\n
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.<\/p>\n
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.<\/p>\n
\n
Hire Qxf2<\/h3>\n
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<\/a>.<\/p>\n
\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":32,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[408,306],"tags":[],"class_list":["post-21193","post","type-post","status-publish","format-standard","hentry","category-rusqlite","category-rust"],"_links":{"self":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/21193","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/users\/32"}],"replies":[{"embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/comments?post=21193"}],"version-history":[{"count":62,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/21193\/revisions"}],"predecessor-version":[{"id":21688,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/21193\/revisions\/21688"}],"wp:attachment":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/media?parent=21193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/categories?post=21193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/tags?post=21193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}