SQLite Storage
- Granting SQLite Database Permissions to Components
- Using SQLite Storage From Applications
- Preparing an SQLite Database
- Custom SQLite Databases
Spin provides an interface for you to persist data in an SQLite Database managed by Spin. This database allows Spin developers to persist relational data across application invocations.
Why do I need a Spin interface? Why can't I just use my own external database?
You can absolutely still use your own external database either with the MySQL or Postgres APIs. However, if you’re interested in quick, local relational storage without any infrastructure set-up then Spin’s SQLite Database is a great option.
Granting SQLite Database Permissions to Components
By default, a given component of an app will not have access to any SQLite Databases. Access must be granted specifically to each component via the component manifest. For example, a component could be given access to the default store using:
[component.example]
sqlite_databases = ["default"]
Note: To deploy your Database application to Fermyon Cloud using
spin cloud deploy
, see the SQLite Database section in the documentation. It covers signing up for the private beta and setting up your Cloud database tables and initial data.
Using SQLite Storage From Applications
The Spin SDK surfaces the Spin SQLite Database interface to your language.
The set of operations is common across all SDKs:
Operation | Parameters | Returns | Behavior |
---|---|---|---|
open | name | connection | Open the database with the specified name. If name is the string “default”, the default database is opened, provided that the component that was granted access in the component manifest from spin.toml . Otherwise, name must refer to a store defined and configured in a runtime configuration file supplied with the application. |
execute | connection, sql, parameters | database records | Executes the SQL statement and returns the results of the statement. SELECT statements typically return records or scalars. INSERT, UPDATE, and DELETE statements typically return empty result sets, but may return values in some cases. The execute operation recognizes the SQLite dialect of SQL. |
close | connection | - | Close the specified connection . |
The exact detail of calling these operations from your application depends on your language:
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 aQueryResult
. To iterate over the rows use therows()
function. This returns an iterator; usecollect()
if you want to load it all into a collection. - The values in rows are instances of the
Value
enum. However, you can userow.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, writerow.get::<&str>(column_name)
(or whatever the desired type is). - All functions wrap the return in
Result
, with the error type beingspin_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 withrows
andcolumns
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 theclose
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 aQueryResult
object withrows
andcolumns
methods.columns
returns a list of strings representing column names.rows
is an array of rows, each of which is an array ofRowResult
in the same order ascolumns
. - 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.
Preparing an SQLite Database
Although Spin provides SQLite as a built-in database, SQLite still needs you to create its tables. In most cases, the most convenient way to do this is to use the spin up --sqlite
option to run whatever SQL statements you need before your application starts. This is typically used to create or alter tables, but can be used for whatever other maintenance or troubleshooting tasks you need.
You can run a SQL script from a file using the @filename
syntax:
spin up --sqlite @migration.sql
Or you can pass SQL statements directly on the command line as a (quoted) string:
spin up --sqlite "CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT NOT NULL, due TEXT NOT NULL)"
As with runtime operations, this flag uses the SQLite dialect of SQL.
You can provide the --sqlite
flag more than once; Spin runs the statements (or files) in the order you provide them, and waits for each to complete before running the next.
It’s also possible to create tables from your Wasm components using the usual
execute
function. That can end up mingling your “hot path” application logic with database maintenance code; decide which approach is best based on your application’s needs.
Custom SQLite Databases
Spin defines a database named "default"
and provides automatic backing storage. If you need to customize Spin with additional databases, or to change the backing storage for the default database, you can do so via the --runtime-config-file
flag and the runtime-config.toml
file. See SQLite Database Runtime Configuration for details.
Granting Access to Custom SQLite Databases
As mentioned above, by default, a given component of an app will not have access to any SQLite Databases. Access must be granted specifically to each component via the component manifest, using the component.sqlite_databases
field in the manifest.
Components can be given access to different databases, and may be granted access to more than one database. For example:
# c1 has no access to any databases
[component.example]
name = "c1"
# c2 can use the default database, but no custom databases
[component.example]
name = "c2"
sqlite_databases = ["default"]
# c3 can use the custom databases "marketing" and "sales", which must be
# defined in the runtime config file, but cannot use the default database
[component.example]
name = "c3"
sqlite_databases = ["marketing", "sales"]