This blog is slowly taking shape now. It is becoming more and more like the blog you are currently reading this post on. Now we should store our data somewhere. I decided to use a SQL database.

If you just got here, please check out my previous posts, from where I started building the blog application from day 24.

Day 28 - Storing posts in a Database

In order for our application to store and be able to query posts dynamically, we need to store the posts in a database. For this, I will use a SQL Lite database. It allows for quick testing as compared to something like Postgres which is more suitable, in my opinion, for production.

Dependencies

For this, we're going to need to add the rusqlite Dependency to our server. Add the line below to your Cargo.toml file.

rusqlite = "0.28.0"

Creating the Database

In order to create a database, rusqlite makes it very easy. With just one line of code. The open method requires a path to the database. If the db is not found at that path, a new one is created and a Connection is returned.

let conn = Connection::open("./test_db.db")

We then need to add a table to host all our posts. The name of the table is called Posts. We execute SQL code on our connection.

conn.execute(
       "CREATE TABLE posts(
           id TEXT PRIMARY KEY,
           title TEXT NOT NULL,
           content TEXT NOT NULL
       )"
       , ()
   );

Inserting Data

Now that we have successfully created a Table inside our database, insert a post.

conn.execute(
       "INSERT INTO posts (ID,title, content) VALUES (?1, ?2, ?3)",
       (&post1.id, &post1.title, &post1.content)
   );

Query Data

Now that we're all set up, it's time to query a post. We shall query by ID. The .prepare method allows us to build a query which will be executed by the .query_map method.

let mut stmt = conn.prepare("SELECT id, title, content FROM posts WHERE ID = (?1) limit 1").expect(&format!("Could not find the post with id {}", id.to_string()));
   let mut posts = stmt.query_map(rusqlite::params![id.to_string()], |row| {
       Ok(Post {
           id:row.get("id")?,
           title : row.get("title")?,
           content: row.get("content")?
       })
   }).unwrap();
 
 
   let found_post = posts.next().unwrap().unwrap();

Making the DB Connection global

In order for all our endpoints to have access to the database, I decided to make the path to the database global. It's a String which can easily be added to the AppState struct.

#[derive(Serialize)]
struct AppState {
   posts: Vec<Post>,
   db_path: String
}

I then create a function called create_db, which runs the code above of creating the database and inserting data. This function returns the db_path.

let db_path : String= create_db();

This can then be passed into AppState within the main function.

App::new()
       .wrap(cors)
       .app_data(web::Data::new(AppState {
           posts: all_posts,
           db_path: db_path.clone()
       }))

We can now use the id variable found in the path "/blog/{id} to search the database for the right post. If found, the post is returned.

#[get("/blog/{id}")]
async fn blog_post(id: web::Path<usize>, data : web::Data<AppState>) -> impl Responder {
   let db_path = &data.db_path;
   let conn = Connection::open(db_path).unwrap();
 
   let mut stmt = conn.prepare("SELECT id, title, content FROM posts WHERE ID = (?1) limit 1").expect(&format!("Could not find the post with id {}", id.to_string()));
   let mut posts = stmt.query_map(rusqlite::params![id.to_string()], |row| {
       Ok(Post {
           id:row.get("id")?,
           title : row.get("title")?,
           content: row.get("content")?
       })
   }).unwrap();
 
 
   let found_post = posts.next().unwrap().unwrap();
 
   found_post
}

After adding a second post, now the query http://localhost:8000/api/blog/2 returns;

Conclusion

This project was quite cool. It can be improved with error handling for a real world use case. For now, I think it's cool and serves the purpose of learning how to use a database with Rust.

Thanks for spending some time learning Rust with me. Until next time. Peace ✌🏼 .