Documentation
¶
Overview ¶
Package sqldb opens the app's SQL database, a MySQL database of its own. The app never holds a credential: Open connects to the runner's socket, and the runner logs in to the database as the app's own user, whose grants reach only the app's database. There is no DSN and no password anywhere in the app.
The permission list declares the database as a data source:
data:
- source: sqldb
classes: [internal]
Rules:
- Open the database once, in OnStart, create the app's tables there with CREATE TABLE IF NOT EXISTS, and share the *sql.DB with the pages through the app's deps package.
- Pass every value with a ? placeholder; never join values into SQL.
- Run statements that belong together in a transaction, and send mail about them only after it commits.
- One statement runs per call, and times are read and written in UTC.
Every query and transaction gets a trace span that names only the statement's first keyword, never its text or its values.
Read more in the guide docs/guides/sql-database.md and the task docs/tasks/list-from-database.md, which aicoded explain and the MCP tool howto print as guides/sql-database and tasks/list-from-database.
Index ¶
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func Open ¶
Open returns the pool of connections to the app's database. Times are read and written in UTC, one statement runs per call, and statements with arguments are always prepared. Open fails with E-MAN-010 when aicoded.yaml does not declare the data source sqldb.
Example ¶
Open the database once, in OnStart, and create the app's tables there. Keep the *sql.DB in the app's deps for every page.
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)
if err != nil {
return err
}
_, err = db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS notes (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
owner VARCHAR(255) NOT NULL,
title VARCHAR(200) NOT NULL,
created DATETIME NOT NULL)`)
return err
}
_ = onStart
}
Output:
Example (Query) ¶
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:
Example (Transaction) ¶
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:
Types ¶
This section is empty.
Source Files