sqldb

Open the database once, in OnStart, and keep the *sql.DB in the app's deps for every page. Its tables come from the app's draft of schema changes, .aicoded/migration.sql, such as

CREATE TABLE notes (
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	owner VARCHAR(512) COLLATE utf8mb4_0900_bin NOT NULL,
	title VARCHAR(200) NOT NULL,
	created DATETIME NOT NULL
);

package main

import (
	"context"
	"database/sql"

	"aicoded.dev/framework/sqldb"
)

var db *sql.DB // the database Open returned in OnStart

func main() {
	onStart := func(ctx context.Context) error {
		var err error
		db, err = sqldb.Open(ctx)
		return err
	}
	_ = onStart
}

Output:

Pass every value with a ? placeholder, here to list the viewer's notes in a page's Data.

package main

import (
	"context"
	"database/sql"
	"fmt"

	"aicoded.dev/framework/auth"
)

var db *sql.DB // the database Open returned in OnStart

func main() {
	data := func(ctx context.Context) error {
		rows, err := db.QueryContext(ctx, "SELECT id, title FROM notes WHERE owner = ? ORDER BY id", auth.Viewer(ctx).Subject)
		if err != nil {
			return err
		}
		defer rows.Close()
		for rows.Next() {
			var id int64
			var title string
			if err := rows.Scan(&id, &title); err != nil {
				return err
			}
			fmt.Println(id, title)
		}
		return rows.Err()
	}
	_ = data
}

Output:

Run statements that belong together in a transaction, here in a form's Process hook.

package main

import (
	"context"
	"database/sql"
	"time"

	"aicoded.dev/framework/auth"
)

var db *sql.DB

func main() {
	process := func(ctx context.Context) error {
		owner := auth.Viewer(ctx).Subject
		tx, err := db.BeginTx(ctx, nil)
		if err != nil {
			return err
		}
		defer func() { _ = tx.Rollback() }() // does nothing after Commit
		_, err = tx.ExecContext(ctx, "INSERT INTO notes (owner, title, created) VALUES (?, ?, ?)", owner, "Quarterly review", time.Now())
		if err != nil {
			return err
		}
		_, err = tx.ExecContext(ctx, "UPDATE owners SET notes = notes + 1 WHERE owner = ?", owner)
		if err != nil {
			return err
		}
		return tx.Commit()
	}
	_ = process
}

Output: