Read queries

Queries in mode: read go in the generated Read interface. PlainSQL also checks that they do not write data. For example, add this query to queries/posts_read.sql:

-- plainsql: query ListPostTitles returns many
SELECT title FROM posts WHERE user_id = $1 ORDER BY id;

Then select the file in a read entry in plainsql.yaml. The configuration sets the mode. The _read.sql suffix is only a naming convention:

queries:
  - dir: queries
    mode: read
    include:
      - "**/*_read.sql"
  - dir: queries
    mode: readwrite
    include:
      - "posts.sql"

SELECT FOR UPDATE

This query fails in a read entry:

-- plainsql: query LockPost returns one
SELECT id FROM posts WHERE id = $1 FOR UPDATE;

FOR UPDATE locks the matching rows, which counts as a write. Move the query to posts.sql, which the configuration above puts in readwrite mode.

Use a read replica

A read replica is a copy of the database that only serves reads. To send read queries to it, create a query set with the replica’s pool:

reads := db.New(replicaPool)
titles, err := reads.ListPostTitles(ctx, userID)