aicoded.dev

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

  1. Declare the database in the permission list, under data: - source: sqldb with classes: [internal]. Open it once, in Deps.Start, with sqldb.Open(ctx), as Start a new app shows. There is no DSN: the runner logs in as the app's own database user.

  2. 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, and aicoded publish sends 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 dev applies it to the app's dev database before the app starts. Fill the table in Start, before the app serves; people fills users in 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.

  3. 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
    }
    
  4. Name the row's type in the page's package, in a .go file next to the template:

    package users
    
    import "people/deps"
    
    // User is a user of the list.
    type User = deps.User
    
  5. 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>
    
  6. 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

See also

Markdown · Source on GitHub · framework f46741f