Adam Hitzger's blog - Tuono part II: CRUD with libsql💽

In the previous article, we set up Tuono and created a simple form. We also covered routing, handlers, and built an API route to process form data. In this tutorial, we’ll extend version 1 with CRUD operations using an SQL database. I’ll be using Turso (SQLite), but the same example works with PostgreSQL or MySQL—just use the right client and update the API route accordingly, as the command flow is always the same. To follow this tutorial exactly, you’ll need:

Showcase:

After successfully logging into Turso (or another database provider), create a Group (the grey button in the image) — a shared space on the provider’s server. Name the group and choose a region.

Now, create a database by clicking the green button. Name your database and select the group you created earlier.

After creation, you’ll be redirected to the database dashboard. We need to save url of our database that you can see on top of page for further operations. Save it into your env.local. Click the “Edit Database” button and choose one of the two available GUI DB editors.

example.sh

//.env.local
TURSO_DB_URL=libsql://example-...

I used Outerbase Studio, but the choice is up to you. As you’ve likely noticed, the right section is where you write queries (the terminal at the bottom runs them). In the center, you’ll find your tables, and on the left is a sidebar with options to view tables, the ER diagram, and OpenAI integration. Let’s create our first table!

example.mysql

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER,
  email TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  salt TEXT NOT NULL,
);

Enter the SQL query into your chosen editor or terminal and run it! Then, view the table in the middle section of the editor to see its header. Now let's move on to setting up the libSQL client. Jump into terminal!

Start with login to Turso:

example.sh

turso auth login

example.sh

turso db tokens create your-db-name

Save key that will show your terminal.

example.sh

TURSO_AUTH_TOKEN=ey...

Now install libsql:

example.sh

cargo add libsql

Setup client in src/lib/utils.rs:

example.undefined

//src/lib/utils.rs
use libsql::Builder;
use libsql::Connection;

pub async fn connect_to_db() -> Connection{
    let url = std::env::var("TURSO_DB_URL").expect("TURSO_DB_URL must be set");
    let token = std::env::var("TURSO_AUTH_TOKEN").expect("TURSO_AUTH_TOKEN must be set");
    let client = Builder::new_remote(url, token).build().await.expect("Problems with connection!");
    let connection = client.connect().expect("Problems with connection!");
    return {
        connection
    }
}

and create lib.rs in src directory:

example.undefined

//src/lib.rs

pub mod lib {
    pub mod utils;
}

In your api route formdata.rs import connect_to_db():

example.undefined

//src/rotes/api/formdata.rs
use tuono_lib::{axum::response::Redirect, Request};
use serde::Deserialize;
use with_tailwind::lib::utils::connect_to_db;

#[derive(Deserialize)]
struct FormData {
    name: String,
    age: i8,
    email: String,
    password: String,
}


#[tuono_lib::api(POST)]
async fn send_data(_req: Request) -> Redirect {
    let data: FormData = _req.form_data::<FormData>().unwrap();
    let mut message: String= "/?message=Hello ".to_string();
    println!("Name: {}, Age: {}, Email: {}, Password. {}", data.name, data.age, data.email, data.password);
    
    let conn: libsql::Connection = connect_to_db().await; 
    
    let mut _rows: u64 = conn
    .execute(
    "INSERT INTO users (name, age, email, password, salt) 
    VALUES (:name, :age, :email, :password, :salt);", libsql::named_params! {
    ":name": data.name.clone(), 
    ":age": data.age.clone(), 
    ":email": data.email.clone(), 
    ":password": data.password.clone(), 
    ":salt": "sssss".to_string()})
    .await
    .expect("Problem with query");
    
    message.push_str(&data.name);
    
    Redirect::to(&message)
}

Create variable conn and execute query. If you check you table in database, you should see new row!

Reading data!

example.undefined

use tuono_lib::{Response,Props, Request, Type};
use serde::Serialize;
use with_tailwind::lib::utils::connect_to_db;

#[derive(Debug,Serialize)]
struct FormData {
    id: i64,
    name: String,
    age: i64,
    email: String,
}
#[derive(Debug,Serialize, Type)]
struct FormDatas {
    form_data: Vec<FormData>
}

#[tuono_lib::handler]
async fn get_all_users(_req: Request) -> Response{
    let mut users = Vec::new();
    let conn = connect_to_db().await;
    let mut res = conn.query("SELECT id,name,age,email FROM users", ()).await.expect("Cannot read data");
    while let Some(row) = res.next().await.unwrap(){
        let id: i64 = row.get(0).unwrap();
        let name: String = row.get(1).unwrap();
        let age: i64 = row.get(2).unwrap();
        let email: String = row.get(3).unwrap();

        users.push(FormData {
            id,
            name,
            age,
            email,
        })
    }
    println!("{:#?}", users);
    Response::Props(Props::new(FormDatas{
        form_data: users
    }))
}

For reading data in Tuono, a .rs file with the same name as the route is used — for example, index.tsx will have a corresponding index.rs. Using the #[tuono_lib::handler] macro, we inform the server that this file defines a GET route. If you're familiar with Next.js 13 or earlier, this is similar to getServerSideProps. In the file, I defined a structure for the database data and imported the DB client. Then I iterated over each row and stored the data in a users variable. Finally, I passed the data into Props, which is a Tuono structure used to send data to the frontend.

Updating and deleting

Since Tuono doesn't allow receiving form_data with methods other than POST and only supports one HTTP method per file, and I don't want to create a separate file for each operation, we'll use an old trick. In the existing form, simply add a name and value to the button, like in the example.

example.html

<button name='action' value={"create"} type='submit'>Submit</button>
    </form>

Next, it's necessary to create an interface for the data coming from the index.rs file and add TuonoRouteProps as the type for our component’s props. I also created a table for our data, which I placed inside a <form>—this is because we’ll be deleting or updating the data directly from the table. We’ll also need useState to toggle the "mode" between editing and deleting.

example.tsx

//src/routes/index.tsx
interface IndexProps {
 
    form_data: Array<{
    id: number,
    name: string,
    age: number,
    email: string,
}>
}
export default function IndexPage({
  data, 
}: TuonoRouteProps<IndexProps>): JSX.Element {
  const [edit, setEdit] = useState<boolean>(false)
...

example.typescript

{users && users.map((u) => (
        <form className='w-full col-span-5 grid grid-cols-5' action={"/api/formdata"} 
        method='POST'>
        <button type='button' onClick={() => setEdit(true)} className="rounded-full bg-black text-white w-fit p-1"><Pen/></button>
        <input name='name' type='text' readOnly={!edit} defaultValue={u.name}/>
        <input name='age' type='number' readOnly={!edit}  defaultValue={u.age}/>
        <input name='email' type='email' readOnly={!edit} defaultValue={u.email}/>
        {edit ? 
        <button type='submit' name='action' value={"update"} className="rounded-full bg-red-500 text-white w-fit p-1"><Save/></button>
        :<button type='submit' name='action' value={"delete"} className="rounded-full bg-amber-400 text-white w-fit p-1"><X/></button>
        }
        </form>
      ))}

As you can see in the form, I used name and value on the buttons so I can tell on the backend whether to delete, update, or insert the data.

example.undefined

//full file src/rotes/apiformdata.rs
use tuono_lib::{axum::response::Redirect, Request};
use serde::Deserialize;
use with_tailwind::lib::utils::connect_to_db;

#[derive(Deserialize)]
struct FormData {
    name: Option<String>,
    age: Option<i8>,
    email: Option<String>,
    password: Option<String>,
    action: String,
}


#[tuono_lib::api(POST)]
async fn send_data(_req: Request) -> Redirect {
    let data: FormData = _req.form_data::<FormData>().unwrap();
    let mut message: String= "/?message=Hello ".to_string();
    println!("Name: {:?}, Age: {:?}, Email: {:?}, Password: {:?}, Action: {}", data.name, data.age, data.email, data.password, data.action);
    
    let conn: libsql::Connection = connect_to_db().await; 
    
    if data.action == "create" {
        let mut _rows: u64 = conn.execute("INSERT INTO users (name, age, email, password, salt) VALUES (:name, :age, :email, :password, :salt)", libsql::named_params! {":name": data.name.clone(), ":age": data.age.clone(), ":email": data.email.clone(), ":password": data.password.clone(), ":salt": "sssss".to_string()}).await.expect("Problem with query");
        message.push_str(&data.name.as_deref().unwrap_or(""));
        message.push_str(", your registered!");
    }
    
    if data.action == "update" {
        let mut _rows = conn.execute("UPDATE users SET name=?, age=? WHERE email = ?",&[data.name.clone(), Some(data.age.unwrap_or(0).to_string().clone()), data.email.clone()])
        .await.expect("Problem with query");
        message.push_str(&data.name.as_deref().unwrap_or(""));
        message.push_str(", data were updated!");
    }

    if data.action == "delete" {
        let mut _rows = conn.execute("DELETE FROM users WHERE email = ?",&[data.email.clone()])
        .await.expect("Problem with query");
        message.push_str("Data were deleted!");
    }
    
    Redirect::to(&message)
}

I added an action of type String to the interface. At the same time, I changed all the attributes to Option types because sometimes you receive all the data (create), other times you only need the email (delete where email = ?), and in some cases you don't need the password at all (update). On the backend, after receiving the data, I connect to the database client and, based on the action, either create, update, or delete the data.

Reading data!

Updating and deleting

Subscribe to my newsletter !