List rows from the database
Show a list read from the app's own SQL database. The example is the list of users in the people
example, at /users: its deps package holds the table and the queries, and the page shows the
rows.
Steps
-
Declare the database in the permission list, under
data:- source: sqldbwithclasses: [internal]. Open it once, inDeps.Start, withsqldb.Open(ctx), as Start a new app shows. There is no DSN: the runner logs in as the app's own database user. -
Create the table in the app's draft of schema changes,
.aicoded/migration.sql, never in app code (E-LINT-013). The draft stays out of git, andaicoded publishsends it with the commit; Schema changes tells what it may hold. people's draft:-- The contacts tab compares a login with the viewer's Subject, so the column holds any Subject, of -- at most 512 bytes, and compares exactly, with no case folding and no trailing-space padding, so -- "Alice" and "alice " are not "alice". CREATE TABLE users ( id BIGINT AUTO_INCREMENT PRIMARY KEY, login VARCHAR(512) COLLATE utf8mb4_0900_bin NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, age TINYINT UNSIGNED NOT NULL, team VARCHAR(40) NOT NULL, languages VARCHAR(100) NOT NULL, on_site BOOL NOT NULL, remote BOOL NOT NULL, shift TINYINT UNSIGNED NOT NULL, bio TEXT NOT NULL );aicoded devapplies it to the app's dev database before the app starts. Fill the table inStart, before the app serves; people fillsusersin one transaction when it is empty:// fill adds seed to the users table, which .aicoded/migration.sql creates, when the table is empty. func fill(ctx context.Context, db *sql.DB) error { tx, err := db.BeginTx(ctx, nil) if err != nil { return err } defer func() { _ = tx.Rollback() }() var n int if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM users").Scan(&n); err != nil { return err } if n > 0 { return nil } for _, u := range seed { if err := insert(ctx, tx, u); err != nil { return err } } return tx.Commit() }// insert adds u to the users table. func insert(ctx context.Context, tx *sql.Tx, u User) error { _, err := tx.ExecContext(ctx, "INSERT INTO users ("+columns+") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", u.Login, u.Name, u.Age, u.Team, u.Languages, u.OnSite, u.Remote, u.Shift, u.Bio) return err }Values always go into the statement with
?placeholders, never into its text. One statement runs per call. -
Read the rows in
deps, into a type of their own:// User is one person in the users table. type User struct { Login string Name string Age int Team string // Languages lists the languages the person speaks, joined with ", ". Languages string OnSite bool Remote bool // Shift is 1 for the day shift and 2 for the night shift. Shift int Bio string }const columns = "login, name, age, team, languages, on_site, remote, shift, bio"// Users returns every user, in the order they were added. func (d *Deps) Users(ctx context.Context) ([]User, error) { rows, err := d.DB.QueryContext(ctx, "SELECT "+columns+" FROM users ORDER BY id") if err != nil { return nil, err } defer rows.Close() var users []User for rows.Next() { u, err := scan(rows) if err != nil { return nil, err } users = append(users, u) } return users, rows.Err() }// scan reads a user from a row that holds the columns. func scan(row interface{ Scan(...any) error }) (User, error) { var u User err := row.Scan(&u.Login, &u.Name, &u.Age, &u.Team, &u.Languages, &u.OnSite, &u.Remote, &u.Shift, &u.Bio) return u, err } -
Name the row's type in the page's package, in a
.gofile next to the template:package users import "people/deps" // User is a user of the list. type User = deps.User -
Show the rows with
ssr:for. This page is also the layout of the user pages: the list stays, and<ssr:content/>shows the user that is open.<ssr:access role="staff"/> <ssr:var name="users" type="[]User"/> <ssr:var name="current" type="string"/> <ssr:var name="canAdd" type="bool"/> <ssr:var name="userCount" type="int" reactive="true"/> <div class="users"> <aside> <h2>Users</h2> <p class="muted"><span id="user-count">{{ userCount }}</span> people</p> <a id="add-user" ssr:if="canAdd" href="/users/add">Add a user</a> <ul id="user-list"> <li ssr:for="u in users"><a class="{{ u.Login == current ? 'active' : '' }}" href="/users/{{ u.Login }}">{{ u.Name }}</a></li> </ul> </aside> <section> <ssr:content/> </section> </div> -
Fill the list in
Data:// Data lists the users, marks the one open, and counts them. func (p *DP) Data(ctx context.Context, r *web.Request, _ web.ResponseWriter, data *RouteData) error { users, err := p.d.Users(ctx) if err != nil { return err } data.Users = users data.Current = r.URLParam("login") data.CanAdd = auth.Viewer(ctx).HasRole("hr") data.UserCount = len(users) return nil }
Check it
aicoded checkgenerates, builds, vets, lints and tests the app.aicoded devmakes the app's own database,ac-dev-people, on the MySQL server thatdev.yamlnames, and applies the draft to it. Openhttp://people.localhost:8080/usersas a persona with thestaffrole.- The app's tests open the test database that
aicoded checkmakes withsqltest.Open(t), fromaicoded.dev/framework/sqldb/sqltest. - The dev UI's Traces page shows each statement as a span, such as
sql.query, which records only the statement's first word, never its text or its values.