Blog announcement

July 26, 2023

Announcing Fermyon Cloud’s SQLite Database

MacKenzie Olson

cloud sql database

Private beta is now open beta.

We’re excited to announce Fermyon Cloud’s SQLite Database using SQLite is now in private beta! Serverless workloads often need to store state in between application invocations, and Spin applications are no exception to this rule. Workloads ranging from Extract Transfer and Load (ETL) pipelines to full-stack applications benefit from storing and retrieving data from a relational database.

With Fermyon Cloud’s SQLite Database, developers don’t need to worry about database configuration. Instead, they can use the Spin SDK in their programming language of choice to store and retrieve data from a database that’s provisioned and managed by Fermyon Cloud on their behalf. Let’s take a look into how we’ve built the feature, and take a specific workload for a Spin.

To request access to the private beta, please fill out this short form. We will try our best to accommodate your request.

Spin and SQLite

In Spin 1.4, we announced support for the Spin SQLite interface, which provides developers with an API to persist data in an SQLite database. When building SQLite support, we had a few core principles in mind -

• Available in your programming language of choice Across all SDKs, developers can open a connection to their database, execute a command, and close the connection. Read more about the set of operations in the Spin API Guide for SQLite storage.

• Default Storage Ready Out of Box - no ops required By using the default SQLite database, developers communicate to Spin that they would like the store created on their behalf. This is what we mean when we say “NoOps database.” Spin takes the hassle out of database operations by provisioning and managing the instance for you.

• Following Principle Of Least Privilege By default, Spin components do not have access to the SQLite database. Developers must grant access to specific Spin components with the following line in the application manifest. This is the only line required to get started with the SQLite database

  [component]
  sqlite_databases = ["default"]
  

• No connection strings attached Developers never have to worry about credentials or connection strings when connecting to their SQLite database thanks to Spin. The connection between the Spin application and the SQLite database is handled securely by Spin on the developer’s behalf once they give a component sufficient privilege to access the SQLite database.

Bringing SQLite to The Cloud

At Fermyon, we wanted to provide the same great SQLite experience Spin developers enjoy locally at a Cloud scale. That’s why we partnered with Turso to build out Fermyon Cloud’s SQLite Database. Turso’s edge database fits our requirements of being a performant database that supports libSQL (an OSS fork of SQLite). We’re proud to add Turso to our technology stack. Read more in our Fermyon Cloud Explained documentation.

Using SQLite Database on Fermyon Cloud

As you take your Spin application from local development to the Cloud, you won’t have to worry about any configuration changes. SQLite Database using SQLite has been built in a manner that allows you to seamlessly push your application to Fermyon Cloud — without needing to make any configuration changes — which frees up your time to focus on app development rather than config management.

Video: What do we mean by “NoOps” Database:

To make the concept a bit more concrete, let’s walk through the process of writing a to-do application that uses SQLite to persist its data.

Building A “To Do” Application With SQLite Database

The to-do application will be composed of two components: todo-api which is an API to get, create, update, and delete “to do” work items; filserver which renders the UI for our application.

spin_manifest_version = "1"
authors = ["Ryan Levick <me@ryanlevick.com>"]
description = "A todo application written in spin"
name = "todo"
trigger = { type = "http", base = "/" }
version = "0.1.0"

[[component]]
id = "todo-api"
source = "target/wasm32-wasi/release/todo.wasm"
allowed_http_hosts = []
# This allows the component to access the default SQLite Database
sqlite_databases = ["default"]
[component.trigger]
route = "/api/..."
[component.build]
command = "cargo build --target wasm32-wasi --release"
watch = ["src/**/*", "Cargo.toml"]

[[component]]
source = { url = "https://github.com/fermyon/spin-fileserver/releases/download/v0.0.2/spin_static_fs.wasm", digest = "sha256:65456bf4e84cf81b62075e761b2b0afaffaef2d0aeda521b245150f76b96421b" }
id = "fileserver"
files = [{ source = "static", destination = "/" }]
[component.trigger]
route = "/..."

Notice that we’ve granted the todo-api component access to the default SQLite Database. The fileserver component, however, will not be able to run transactions against the store.

Now that we have our application manifest set up, let’s dive into the source code. First matter of business, we’ll want to decide on a table structure to store our ‘to-do’ work items. For now, we’ll keep things simple and track the work item’s ID, description, due date, whether it was starred, and whether it’s complete.

First, in src/lib.rs we must add an initialization function that checks if our table exists inside the default database. If it doesn’t, we’ll create it.

#[http_component]
fn handle_todo(req: Request) -> anyhow::Result<Response> {
    let connection = Connection::open_default()?;
		connection.execute("CREATE TABLE IF NOT EXISTS todos (
			  id INTEGER PRIMARY KEY AUTOINCREMENT,
			  description TEXT NOT NULL,
			  due_date DATE,
			  starred BOOLEAN DEFAULT 0,
			  is_completed BOOLEAN DEFAULT 0
				);", &[])?;

    let router = http_router! {
        GET "/api/todos" => get_todos,
        POST "/api/todos/create" => create_todo,
        PATCH "/api/todos/:id" => update_todo,
        DELETE "/api/todos/:id" => delete_todo,
        _   "/*"             => |req, _params| {
            println!("No handler for {} {}", req.uri(), req.method());
            Ok(http::Response::builder()
                .status(http::StatusCode::NOT_FOUND)
                .body(Some(serde_json::json!({"error":"not_found"}).to_string().into()))
                .unwrap())
        }
    };
    router.handle(req)
}

Let’s take a look at a specific function that creates a to-do line item. In the snippet below, we’ll open our connection to the default database, execute a query to store the to-do item, and then close the connection. Since we specified we needed a default store in our application manifest, we don’t have to worry about infrastructure provisioning - Fermyon Cloud will handle that on our behalf.

<...>
pub fn create_todo(req: Request, _params: Params) -> anyhow::Result<Response> {
    let create: CreateParams = serde_json::from_slice(
        req.body()
            .as_ref()
            .map(|b| -> &[u8] { &*b })
            .unwrap_or_default(),
    )?;
    let format = time::format_description::parse(DATE_FORMAT)?;
    let format = create.due_date.map(|d| d.format(&format).unwrap());
    let params = [
        sqlite::ValueParam::Text(&create.description),
        format
            .as_deref()
            .map(|s| sqlite::ValueParam::Text(s))
            .unwrap_or(sqlite::ValueParam::Null),
    ];

    // Opens a connection to SQLite Database
    let conn = Connection::open_default()?;

    // Inserts new To DO work item by executing SQLite command
    let response = &conn
        .execute(
            "INSERT INTO todos (description, due_date) VALUES(?, ?) RETURNING id;",
            params.as_slice(),
        )?
        .rows;
    let Some(id) = response.get(0) else { anyhow::bail!("Expected number got {response:?}")};
    let todo = Todo {
        id: id.get(0).unwrap(),
        description: create.description,
        due_date: create.due_date,
        starred: false,
        is_completed: false,
    };

    Ok(http::Response::builder()
        .status(http::StatusCode::OK)
        .body(Some(serde_json::to_vec(&todo)?.into()))
        .unwrap())
}

If you’re interested in checking out how the remaining APIs were written, please visit the fermyon/finicky-whiskers GitHub repo.

For our frontend component, we have a simple HTML and JS application that will render our todo list, thanks to Spin’s static fileserver template. You can take a closer look in the static directory of the example repository if you’re curious.

We’ll test our Spin application’s behavior locally by running the following command:

$ spin build --up
Building component todo-api with `cargo build --target wasm32-wasi --release`
   Compiling todo v0.1.0 (/Users/mackenzieolson/Documents/test/spin-todo)

Managing SQLite Databases on Fermyon Cloud

After validating that our Spin application works as expected by creating, updating, and deleting a to-do item via the UI, we’ll go ahead and migrate the Spin application to the Cloud with one easy step:

$ spin cloud deploy

Just like that, we have a Spin application using SQLite Database with SQLite on Fermyon Cloud 🎉  We will automatically generate a random name for your default database. You can use the Spin Cloud plugin to check which Spin application is associated with your SQLite Store instance; no ops are required! As long as your Spin application is running, you’ll be able to persist data in your SQLite database without worrying about database management.

$ spin cloud sql list
inspirational-pig (default) (currently used by "todo")

Lastly, when we’re ready to clean up our resources, we will first delete our to-do application and then follow up by removing your SQLite Database (Fermyon Cloud prevents you from deleting your SQLite store when it’s tied to an active application):

$ spin cloud sql delete default
inspirational-pig has been deleted

Conclusion

We’re looking forward to hearing your feedback on Fermyon Cloud’s SQLite Database. Let us know what you think on Discord, or join us in our Fermyon Cloud Office Hours which takes place 3:00 pm EDT on August 2nd, 2023.

Read more about today’s announcements: