Relational Database Storage

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

MySQL functions are available in the spin_sdk::mysql module, and PostgreSQL functions in the spin_sdk::pg module.

The function names match the operations above. This example shows MySQL (blocking):

use spin_sdk::mysql::{self, Connection, Decode, ParameterValue};

let connection = Connection::open(&address)?;

let params = vec![ParameterValue::Int32(id)];

let rowset = connection.query("SELECT id, name FROM pets WHERE id = ?", &params)?;

// MySQL returns the rows as a vector
match rowset.rows.first() {
    None => /* no rows matched query */,
    Some(row) => {
        let name = String::decode(&row[1])?;
    }
}

PostgreSQL (async) uses a QueryResult struct to encapsulate the streaming results:

use spin_sdk::pg::{Connection, Decode, ParameterValue};

let connection = Connection::open(&address).await?;

let mut query_result = connection.query(
    "SELECT id, name FROM pets WHERE id = $1",
    &[ParameterValue::Int32(id)]
).await?;

// PostgreSQL returns the rows asynchronously (stream-like)
while let Some(row) = query_result.next().await {
    let name = row.get::<String>("name")?;
    // ... more processing ...
}

// Check if the row stream ended due to completion or an error
query_result.result().await?;

If you are querying for a small result set, you can load all the rows into a Vec by calling QueryResult::collect(). This can be more convenient for some operations.

Notes

• Parameters are instances of the ParameterValue enum; you must wrap raw values in this type.
• A row is a vector of the DbValue enum. Use the Decode trait to access conversions to common types.
• Modified row counts are returned as u64.
• All functions wrap the return in anyhow::Result.

You can find complete examples for using relational databases in the Spin repository on GitHub (MySQL, PostgreSQL).

For full information about the MySQL and PostgreSQL APIs, see the Spin SDK reference documentation.

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

The code below is an Outbound MySQL example. There is also an outbound PostgreSQL example available.

// https://itty.dev/itty-router/routers/autorouter
import { AutoRouter } from 'itty-router';
import { open } from '@spinframework/spin-mysql';

// Connects as the root user without a password 
const DB_URL = "mysql://root:@127.0.0.1/spin_dev"

/*
 Run the following commands to setup the instance:
 create database spin_dev;
 use spin_dev;
 create table test(id int, val int);
 insert into test values (4,4);
*/

let router = AutoRouter();

router
    .get("/", () => {
        let conn = open(DB_URL);
        conn.execute('delete from test where id=?', [4]);
        conn.execute('insert into test values (4,5)', []);
        let ret = conn.query('select * from test', []);

        return new Response(JSON.stringify(ret, null, 2));
    })

//@ts-ignore
addEventListener('fetch', async (event: FetchEvent) => {
    event.respondWith(router.fetch(event.request));
});

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

The code below shows use of the Postgres module and its open function for opening a connection to the database:

from spin_sdk import http, postgres
from spin_sdk.http import Request, Response

class HttpHandler(http.Handler):
    async def handle_request(self, request: Request) -> Response:
        with await postgres.open("user=postgres dbname=spin_dev host=localhost sslmode=disable password=password") as db:
            print(db.query("SELECT * FROM test", []))

        return Response(
            200,
            {"content-type": "text/plain"},
            bytes("Hello from Python!", "utf-8")
        )

You can find a complete outbound PostgreSQL example in the Spin Python SDK repository on GitHub. There is also an Outbound MySQL example available.

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

MySQL functions are available in the github.com/spinframework/spin-go-sdk/v2/mysql package, and PostgreSQL in github.com/spinframework/spin-go-sdk/v2/pg. See Go Packages for reference documentation.

The package follows the usual Go database API. Use Open to return a connection to the database of type *sql.DB - see the Go standard library documentation for usage information. For example:

package main

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

	spinhttp "github.com/spinframework/spin-go-sdk/v2/http"
	"github.com/spinframework/spin-go-sdk/v2/pg"
)

type Pet struct {
	ID        int64
	Name      string
	Prey      *string // nullable field must be a pointer
	IsFinicky bool
}

func init() {
	spinhttp.Handle(func(w http.ResponseWriter, r *http.Request) {

		// addr is the environment variable set in `spin.toml` that points to the
		// address of the Mysql server.
		addr := os.Getenv("DB_URL")

		// For MySQL, use `mysql.Open`
		db := pg.Open(addr)
		defer db.Close()

		// For MySQL, use `?` placeholder syntax
		_, err := db.Query("INSERT INTO pets VALUES ($1, 'Maya', $2, $3);", int32(4), "bananas", true)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		rows, err := db.Query("SELECT * FROM pets")
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}

		var pets []*Pet
		for rows.Next() {
			var pet Pet
			if err := rows.Scan(&pet.ID, &pet.Name, &pet.Prey, &pet.IsFinicky); err != nil {
				fmt.Println(err)
			}
			pets = append(pets, &pet)
		}
		json.NewEncoder(w).Encode(pets)
	})
}