Write queries
At the end of this page, your .sql file holds queries with parameters, an optional filter, an IN list, and one row or no rows.
Several queries in one file
Add these two queries to UserRepository.sql, below GetUser:
-- name: ListUserNames
-- summary: The name of every user, in the order of their ids.
SELECT
id,
name
FROM users
ORDER BY id;
-- name: RenameUser -> rowcount
-- summary: Changes the name of one user.
UPDATE users
SET name = @name
WHERE id = @userId;
Each -- name: line starts a new query. The query ends at the next -- name: line, or at the end of the file.
Build the project. With the .env file of Your first query, the build describes the two new queries first. UserRepository now has three methods: GetUser, ListUserNames and RenameUser.
One row, or no rows
The word after -> is the result shape of the query. It says what the query's method returns:
- No word, for a query that returns rows: every row, in an array.
ListUserNamesreturnsListUserNamesDto[]. many: every row, as with no word.one: the one row. The method throws when the query returns no row, or more than one.one-optional: the one row, or null when there is none.GetUserreturnsGetUserDto?. The method throws when the query returns more than one row.none: nothing. The method runs the statement and returns.rowcount: the number of rows that the statement changed.RenameUserreturns anint.
A parameter
A query parameter is @ and a name, such as @userId. It stays in the SQL as you wrote it. Its value comes from a property of the query's parameters model: GetUserParams.UserId. The properties are in the order that the parameters first appear in the SQL. So you call RenameUser with the name first:
int renamed = await users.RenameUser(new RenameUserParams("Ada Lovelace", 1));
An optional filter
To filter by a value only when the caller gives one, test the parameter for null. Add this query to UserRepository.sql:
-- name: FindUsers
-- summary: Finds the users with a name, or every user when the name is null.
-- param: @name text null
SELECT
id,
name,
email
FROM users
WHERE @name IS NULL OR name = @name
ORDER BY name;
The -- param: line gives @name a type, in the words of your database. The word null says that the value can be null. So FindUsersParams.Name is a string?, and new FindUsersParams(null) finds every user.
If your database cannot work out the type of a parameter, the tool reports SQLSRC226. On PostgreSQL, @name IS NULL before name = @name gives SQLSRC227. Add a -- param: line with the type, as above. Until you fix it, the other new queries of the file get SQLSRC241 too.
A list after IN
To filter by a list of values, write the parameter directly after IN, with no parentheses. Add this query to UserRepository.sql:
-- name: ListUsersByIds
-- summary: The users whose ids are in the list.
SELECT
id,
name
FROM users
WHERE id IN @ids
ORDER BY id;
The property ListUsersByIdsParams.Ids is an IEnumerable<int>, so it takes an array or a List<int>:
ListUsersByIdsDto[] found = await users.ListUsersByIds(new ListUsersByIdsParams([1, 2]));
The method sends one parameter for each value. An empty list finds no rows.
A column that is never null
A property is nullable when your database cannot show that its column is never null. The result of a function is often such a column. If you know that the column is never null, add a -- column: line with not null. Add this query to UserRepository.sql:
-- name: ListDisplayNames
-- summary: The name of every user, with each word capitalized.
-- column: display_name not null
SELECT
id,
initcap(name) AS display_name
FROM users
ORDER BY id;
Or end the column's alias with !, in double quotes: AS "display_name!". Either way, ListDisplayNamesDto.DisplayName is a string, not a string?. The build does not check what you say. If the column is null after all, the method throws when it reads the row.
Document a query
A -- summary: line documents its query. In your editor, point at a call of GetUser. The editor shows the summary, One user, or null., and the SQL of the query.
If it does not work
- SQLSRC226 or SQLSRC227: your database cannot work out the type of a parameter. Add
-- param: @name <type>, withnullif the value can be null. - A property is nullable, but its column never is: add
-- column: <name> not nullto the query. - SQLSRC244: a query changed after it was described. Build with your
.envfile in place, or rundotnet sqlsource describe, as The build describes for you says.
What you have now
A .sql file with several queries, and the forms for a parameter, an optional filter, a list and a column that is never null.
Rules: queries and names, result shapes, what each shape returns, query parameters, -- param:, list parameters, which properties can be null, -- column:, summaries, what your editor shows. Guides: Pass a list of values (IN), Fix a nullable property.
Next: Call your queries