SQLITE in the cloud with TURSO

Source of the Orontes river

This is a short tutorial on using Turso with Rust. **Turso is a database-as-a-service company offering to host libsql**-based databases, and libsql is an open source fork of SQLite.

While this tutorial is based on Rust, Turso is also available for Javascript, Golang, Python, PHP or any language with HTTP request capacity.

The tutorial assumes you know some Rust (including pattern matching) and have Rust installed on your computer.

1. Prepare the database

Our database will include only one table: usersfor which we prepare the following SQL script, tuto.sql :

DROP TABLE IF EXISTS users;
CREATE TABLE users ( "id" INTEGER PRIMARY KEY, 
    "name" varchar(11) NOT NULL, "email" varchar(30) NOT NULL );
INSERT INTO users VALUES(1,'John Doe', 'john.doe@somewhere.com');
INSERT INTO users VALUES(2,'Mike Jones', 'mike.jones@elsewhere.com');

2. Create the database onTurso

• Follow the Turso quickstart to *install the Turso CLI and sign up to Turso*. Turso offers a ‘Starter’ plan which — at the time of writing this article — is free for individual developers, including up to 500 Databases, 9GB of total storage, 1 billion row reads, unlimited embedded replicas. From now on we assume that you have the Turso CLI installed and did sign up.
• Create the my-tuto database on Turso:

$ turso db create my-tuto

• Execute the tuto.sql script on the new database:

  $ turso db shell my-tuto < tuto.sql

• Check that your table has been created:

$ turso db shell my-tuto
Connected to my-tuto at libsql://my-tuto-<your-turso-account>.turso.io

Welcome to Turso SQL shell!

Type ".quit" to exit the shell and ".help" to list all available commands.

→  SELECT * FROM users;
ID     NAME           EMAIL                    
1      John Doe       john.doe@somewhere.com       
2      Mike Jones     mike.jones@elsewhere.com     
→  .quit

• Get the database url and save a copy of it:

$ turso db show --url my-tuto

• Get the database authentication token and save a copy of it:

$ turso db tokens create my-tuto

3. Prepare a rust program to read the database

• Create a new rust project

$ cargo new tursotutorial
$ cd tursotutorial

• Create a .env file with the two database properties you just saved (end of section 2 here above):

TURSO_DATABASE_URL=
TURSO_AUTH_TOKEN=

• Add the necessary dependencies: libsql to access the database, dotenv to read the .env file, serde to deserialize the content of each database row, and tokio to provide the asynchronous runtime

$ cargo add libsql
$ cargo add dotenv
$ cargo add serde
$ cargo add tokio

• and edit src/main.rs:

use dotenv::dotenv;
use libsql::{de, params, Builder};
use serde::Deserialize;
use std::env;

#[derive(Debug, Deserialize)]
pub struct User {
    id : i64,
    name : String,
    email : String,
}

#[tokio::main]
async fn main() {
    dotenv().ok();
    let url = env::var("TURSO_DATABASE_URL").expect("LIBSQL_URL must be set");
    let token = env::var("TURSO_AUTH_TOKEN").unwrap_or_default();

    let db = Builder::new_remote(url, token).build().await.unwrap();
    let conn = db.connect().unwrap();

    let mut rows = conn.query("SELECT * FROM users;", params![]).await.unwrap();

    loop {
        let row = rows.next().await.unwrap();
        match row {
            None => break,
            Some(row) => match de::from_row::<User>(&row) {
                Ok(user) => {
                    println!("{:?}", user);
                }
                Err(_) => {}
            }
        }
    }
}

The main aspects of this code are:

• The User struct, which implements Deserialize (see below) and Debug (for printing). The identifiers of this struct are the same as the columns of the users table, and their type matches the corresponding types in the table.
• The dotenv line and following, where the database url and auth token are input (from the .env file)
• The conn variable holds the database connection
• The rows variable (of type Rows) contains the rows after executing the query
• We access the elements in each row through an open loop
• The [next](https://docs.rs/libsql/latest/libsql/struct.Rows.html) function has the following signature:

pub async fn next(&mut self) -> Result<Option<Row>>

• After we unwrap the Result with unwrap(), we get an Option, which can be eithe Ok(row:Row) or None.
• Through pattern matching, we destructure the row with Ok(row)
• We then do a second pattern matching, based on the from_row function, which has the following signature (see here) :

pub fn from_row<'de, T: Deserialize<'de>>(row: &'de Row) -> Result<T, Error>

• This deserves to be considered more closely: we do not need the ‘de lifetime annotation here, so we just forget it. The T type is the struct to which we want the data to be deserialized, which a struct of type User. It must implement Deserialize, this is why we specified Derive(Deserialize) for the User struct. This translates as follows in our code:

Some(row) => match de::from_row::<User>(&row)

• The result of de::from_row is of type Result(User, Error) in our case, with two branches in the match: the Ok branch, where we get a user (of type User) by destructuring the result Ok(user), and the Error branch, which we chose not to use.

4. Running the program

We may run the program with cargo run, and it works:

$ cargo run
   Compiling tursotutorial v0.1.0 (/Users/francisstephan/dev/rust/tursotutorial)
warning: fields `id`, `name`, and `email` are never read
  --> src/main.rs:10:5
   |
9  | pub struct User {
   |            ---- fields in this struct
10 |     id: i64,
   |     ^^
11 |     name: String,
   |     ^^^^
12 |     email: String,
   |     ^^^^^
   |
   = note: `User` has a derived impl for the trait `Debug`, but this is intentionally ignored during dead code analysis
   = note: `#[warn(dead_code)]` on by default

warning: `tursotutorial` (bin "tursotutorial") generated 1 warning
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 2.97s
     Running `target/debug/tursotutorial`
User { id: 1, name: "John Doe", email: "john.doe@somewhere.com" }
User { id: 2, name: "Mike Jones", email: "mike.jones@elsewhere.com" }

The warning is a minor nuisance here; we get it because we did not use the elements of the User struct. However, we need to provide these elements for the Deserialization to work. You may consider this as a (minor) compiler bug.

This is a local program using a cloud database. In a further article we shall discuss the case of a web based program using Turso. See a preliminary version of the program here.

5. Conclusion

Our experience with the Turso platform was excellent from the start.

The libsql documentation is quite ok but a bit terse, as shown in the two examples given above (libsql::rows and libsql::de::from_row). Some examples would be useful for beginners.