• Initialize a cargo workspace

[workspace]
resolver = "3"
members = [
    "backend",
    "db",
]

• Add 1 new binary and 2 new libraries

cargo new backend
cargo new db --lib

• Add sqlx, anyhow, serde to the db crate, enable feature postgres

[package]
name = "db"
version = "0.1.0"
edition = "2024"

[dependencies]
anyhow = "1.0.100"
serde = {version = "1.0.228", features = ["derive"]}
serde_json = "1.0.145"
sqlx = {version = "0.8.6", features = ["postgres", "runtime-tokio", "uuid"]}

• Create a wrapper struct for the database

use sqlx::postgres::{PgPool}
struct Store {
	pool: PgPool
}

• Implement the new function for the Store

use anyhow::Result;
use serde::{Serialize, Deserialize};
use std::env;

use sqlx::{Postgres, postgres::{PgPool, PgPoolOptions}};
pub struct Store {
	pub pool: PgPool
}

impl Store {
    pub async fn new() -> Result<Self> {
        let db_url = env::var("DATABASE_URL")?;
        let pool = PgPoolOptions::new()
            .max_connections(5)
            .connect(&db_url).await?;

        Ok(Self {
            pool
        })
    }
}

• Implement user specific DB (models/user.rs)

use anyhow::Result;
use serde::{Deserialize, Serialize};

use crate::Store;

#[derive(Serialize, Deserialize)]
pub struct CreateUserResponse {
    pub user_id: String,
}

#[derive(Serialize, Deserialize)]
pub struct CreateUserRequest {
    username: String,
    password: String
}

#[derive(Serialize, Deserialize)]
pub struct GetUserRequest {
    username: String,
}

#[derive(Serialize, Deserialize)]
pub struct GetUserResponse {
    pub user: User,
}

#[derive(Serialize, Deserialize)]
pub struct User {
    pub id: String,
    pub username: String,
    pub password: String,
}

impl Store {
    pub async fn create_user(&self, request: CreateUserRequest) -> Result<CreateUserResponse> {
        let user = sqlx::query_as!(User, 
            "INSERT INTO users (username, password) VALUES ($1, $2) RETURNING id, username, password",
            request.username,
            request.password
        )
        .fetch_one(&self.pool)
        .await?;

        Ok(CreateUserResponse {
            user_id: user.id,
        })
    }

    pub async fn get_user(&self, request: GetUserRequest) -> Result<GetUserResponse> {
        let user = sqlx::query_as!(User, "SELECT id, username, password FROM users WHERE username = $1", request.username)
            .fetch_one(&self.pool)
            .await?;

        Ok(GetUserResponse {
            user: user,
        })
    }
}

<aside> 💡

We have not migrated the DB yet/crated the DB. So you should start the DB locally and migrate it

</aside>

• Start postgres locally

docker run -p 5432:5432 -d -e POSTGRES_PASSWORD=postgres postgres

• create .env with the right credentials

DATABASE_URL=postgres://postgres:postgres@localhost:5432/tiktaktoe

• Create a migrations folder with the initial migration (20240101000000_create_users_table.sql)

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create index on username for faster lookups
CREATE INDEX idx_users_username ON users(username);

• Create the DB