SQLite Storage

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},
    http_component,
    sqlite::{Connection, Error, ValueParam},
};

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

    let execute_params = [
        ValueParam::Text("Try out Spin SQLite"),
        ValueParam::Text("Friday"),
    ];
    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(http::Response::builder().status(200).body(Some(body.into()))?)
}

// 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 ValueParam 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 ValueResult 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.

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

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

const conn = Sqlite.openDefault();
const result = conn.execute("SELECT * FROM todos WHERE id > (?);", [1]);
const json = JSON.stringify(result.rows);

General Notes

• The spinSdk object is always available at runtime. Code checking and completion are available in TypeScript at design time if the module imports anything from the @fermyon/spin-sdk package.
• Parameters are JavaScript values (numbers, strings, byte arrays, or nulls). Spin infers the underlying SQL type.
• 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 array of JavaScript values (as above) in the same order as columns.
• The Connection object doesn’t surface the close function.
• If a Spin SDK function fails, it throws an Error.

To use SQLite functions, use the spin_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_http import Response
from spin_sqlite import sqlite_open_default

def handle_request(request):
    conn = sqlite_open_default()
    result = conn.execute("SELECT * FROM todos WHERE id > (?);", [1])
    rows = result.rows()
    return Response(200,
                    {"content-type": "application/json"},
                    bytes(str(rows), "utf-8"))

General Notes

• Parameters are Python values (numbers, strings, and lists). Spin infers the underlying SQL type.
• The execute method returns an 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 Python values (as above) in the same order as columns.
• The connection object doesn’t surface the close function.
• Errors are surfaced as exceptions.

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/http"
	"github.com/fermyon/spin/sdk/go/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.