SQLite Storage

Please note, we use serde in this Rust example, so please add serde as a dependency in your application’s Cargo.toml file:

[dependencies]
serde = {version = "1.0", features = ["derive"]}
serde_json = "1.0"

Want to go straight to the reference documentation? Find it here.

SQLite functions are available in the spin_sdk::sqlite module. The function names match the operations above. For example:

use anyhow::Result;
use serde::Serialize;
use spin_sdk::{
    http::{Request, Response, IntoResponse},
    http_component,
    sqlite::{Connection, Value},
};

#[http_component]
fn handle_request(req: Request) -> Result<impl IntoResponse> {
    let connection = Connection::open_default()?;

    let execute_params = [
        Value::Text("Try out Spin SQLite".to_owned()),
        Value::Text("Friday".to_owned()),
    ];
    connection.execute(
        "INSERT INTO todos (description, due) VALUES (?, ?)",
        execute_params.as_slice(),
    )?;

    let rowset = connection.execute(
        "SELECT id, description, due FROM todos",
        &[]
    )?;

    let todos: Vec<_> = rowset.rows().map(|row|
        ToDo {
            id: row.get::<u32>("id").unwrap(),
            description: row.get::<&str>("description").unwrap().to_owned(),
            due: row.get::<&str>("due").unwrap().to_owned(),
        }
    ).collect();

    let body = serde_json::to_vec(&todos)?;
    Ok(Response::builder()
        .status(200)
        .header("content-type", "text/plain")
        .body(body)
        .build())
}

// Helper for returning the query results as JSON
#[derive(Serialize)]
struct ToDo {
    id: u32,
    description: String,
    due: String,
}

General Notes

• All functions are on the spin_sdk::sqlite::Connection type.
• Parameters are instances of the Value enum; you must wrap raw values in this type.
• The execute function returns a QueryResult. To iterate over the rows use the rows() function. This returns an iterator; use collect() if you want to load it all into a collection.
• The values in rows are instances of the Value enum. However, you can use row.get(column_name) to extract a specific column from a row. get casts the database value to the target Rust type. If the compiler can’t infer the target type, write row.get::<&str>(column_name) (or whatever the desired type is).
• All functions wrap the return in Result, with the error type being spin_sdk::sqlite::Error.

Want to go straight to the reference documentation? Find it here.

To use SQLite functions, use the Sqlite.open or Sqlite.openDefault function to obtain a SqliteConnection object. SqliteConnection provides the execute method as described above. For example:

import { ResponseBuilder, Sqlite } from "@fermyon/spin-sdk";

export async function handler(req: Request, res: ResponseBuilder) {
    let conn = Sqlite.openDefault();
    let result = conn.execute("SELECT * FROM todos WHERE id > (?);", [1]);

    res.send(JSON.stringify(result));
}

General Notes

• The execute function returns an object with rows and columns properties. columns is an array of strings representing column names. rows is an array of rows, each of which is an object containing Javascript values keyed using the column names.
• The SqliteConnection object doesn’t surface the close function.

Want to go straight to the reference documentation? Find it here.

To use SQLite functions, use the sqlite module in the Python SDK. The sqlite_open and sqlite_open_default functions return a connection object. The connection object provides the execute method as described above. For example:

from spin_sdk import http, sqlite
from spin_sdk.http import Request, Response
from spin_sdk.sqlite import ValueInteger

class IncomingHandler(http.IncomingHandler):
    def handle_request(self, request: Request) -> Response:
        with sqlite.open_default() as db:
            result = db.execute("SELECT * FROM todos WHERE id > (?);", [ValueInteger(1)])
            rows = result.rows
        
        return Response(
            200,
            {"content-type": "text/plain"},
            bytes(str(rows), "utf-8")
        )

General Notes

• The execute method returns a QueryResult object with rows and columns methods. columns returns a list of strings representing column names. rows is an array of rows, each of which is an array of RowResult in the same order as columns.
• The connection object doesn’t surface the close function.
• Errors are surfaced as exceptions.

Want to go straight to the reference documentation? Find it here.

The Go SDK is implemented as a driver for the standard library’s database/sql interface.

package main

import (
	"encoding/json"
	"net/http"

	spinhttp "github.com/fermyon/spin/sdk/go/v2/http"
	"github.com/fermyon/spin/sdk/go/v2/sqlite"
)

type Todo struct {
	ID          string
	Description string
	Due         string
}

func init() {
	spinhttp.Handle(func(w http.ResponseWriter, r *http.Request) {
		db := sqlite.Open("default")
		defer db.Close()

		_, err := db.Exec("INSERT INTO todos (description, due) VALUES (?, ?)", "Try out Spin SQLite", "Friday")
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		rows, err := db.Query("SELECT id, description, due FROM todos")
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		var todos []*Todo
		for rows.Next() {
			var todo Todo
			if err := rows.Scan(&todo.ID, &todo.Description, &todo.Due); err != nil {
				http.Error(w, err.Error(), http.StatusInternalServerError)
				return
			}
			todos = append(todos, &todo)
		}
		json.NewEncoder(w).Encode(todos)
	})
}

func main() {}

General Notes

A convenience function sqlite.Open() is provided to create a database connection. Because the http.Handle function is inside the init() function the Spin SQLite driver cannot be initialized the same way as other drivers using sql.Open.