.sql files
-- name: GetUser
-- summary: Loads one user by id.
SELECT id, name
FROM users
WHERE id = @id;
-- name: ListUsers
SELECT id, name FROM users ORDER BY name;
A .sql file holds one or more queries. Each query starts with a -- name: line, and the markers that describe the query stand under that line.
Save the file as UTF-8, which current editors write. A file in another encoding reads differently on different systems. The tool reports such a file when it must describe the file's queries (SQLSRC271).
What a marker is
-- name: GetUser and -- summary: Loads one user. are markers. A marker is a line comment that starts its line and has the form -- word: rest, where the word is one that SqlSource knows. SqlSource reads nothing else in a comment. The marker index lists every marker.
- The word is matched ignoring case, and the blanks after
--are optional. So--NAME:GetUseris a marker too. - The colon follows the word directly.
-- name : GetUseris a comment, not a marker. - A comment after SQL on the same line is not a marker. A comment inside a block comment or a string is not a marker either.
- Under
mysqlandmariadb, a marker needs white space after the two dashes. There, the two dashes start a comment only when white space follows them. See markers undermysqlandmariadb.
Marker index
A marker stands in one of two places. A marker that describes one query stands inside the query, under its -- name: line. A marker that describes the whole file stands in the file header, before the first -- name: line. A file with no -- name: line is one query, and it takes both kinds.
| Marker | Purpose | File header | Inside a query | Documented in |
|---|---|---|---|---|
-- name: |
Starts a query and names it, with an optional result shape | no | starts one | Queries and names |
-- summary: |
Documents the query's generated constant or method, and its models | no | yes | Summaries |
-- dialect: |
The dialect of the file, which you set to your database: postgres or mssql. The other names, the options and the reading rules are on the dialects page |
yes | no | Dialect |
-- generator: |
Generator parameters | yes | yes | Generator parameters |
-- param: |
Gives a query parameter its database type, whether it can be null, and, after ->, a C# type of your choice: -- param: @id int -> UserId, or -> json OrderMetadata for a parameter of a string type that holds JSON |
no | yes | Giving a parameter a type, the JSON opt-in |
-- token: |
Gives a token a default | no | yes | Defaults |
-- token-ignore: |
Keeps text that looks like a token as it is written | no | yes | Text that looks like a token |
-- database: |
The logical database a query belongs to | yes | yes | Database |
-- output: |
What is generated for a query: its models and an instance method of the type that gets its own connection and runs it (repository, the default), its models and a method that takes a connection and runs it (methods), its SQL and its models (models), or its SQL alone (sql). It also decides which queries must be described, by the tool and in the build's check of the sidecars |
yes | yes | Output |
-- input-model-suffix:, -- output-model-suffix: |
What ends the name of a model | yes | no | Suffix of a model's name |
-- model-namespace: |
The namespace of the models | yes | no | Namespace of the models |
-- input-model-type:, -- output-model-type: |
The shape of a model | yes | yes | Shape of a model |
-- input-model:, -- output-model: |
The name of one query's model | no | yes | Name of one query's model |
-- collection-type: |
The type that a query's method returns many rows in | yes | yes | Collection type |
-- member-location: |
Where a query's members go: in a class nested in the type (nested-class), on the type (class), or, for a methods query's method, in the extension class (extension-class) |
yes | yes | Where the members go |
-- visibility: |
How visible a query's members are, or its nested class: public, internal, protected or private |
yes | yes | Visibility |
-- nested-class: |
The name of the class nested in the type that holds a query's members | yes | yes | Name of the nested class |
-- type-override: |
Gives a database type the C# type of your choice, one entry for each line | yes | yes | Type overrides |
-- column: |
Inside a query, says whether one column of the query's result can be null, -- column: total not null, or gives it a C# type after ->, -- column: total -> PostCount, for its property in the output model, and leaves the column's name alone. In the file header, gives a column of a table or view a C# type in every query of the file, -- column: users.id -> UserId, one entry for each line. -> json OrderMetadata gives a column of a string type that holds JSON a class of yours |
yes | yes | Setting a column's nullability or type with a marker, column types, the JSON opt-in |
-- library-types: |
Turns a library's types, such as NodaTime's or System.Text.Json's, on or off for the query methods, -- library-types: nodatime=off, one entry or more for each line |
yes | yes | Library types |
A marker that is a setting links to its section. The settings page says where else you can give the setting, and which place wins. A marker in a place whose column says "no" is an error: see markers in the wrong place.
Queries and names
-- name: GetUser
SELECT id, name FROM users WHERE id = @id;
-- name: DeleteUser
DELETE FROM users WHERE id = @id;
A query is the SQL that follows a -- name: line, up to the next -- name: line or the end of the file. A file with no -- name: line is one query: see a file with no -- name:. Each query becomes one or more members of the generated code, named after the query. So the name of a query follows these rules:
- The name is a C# identifier that is not a reserved keyword, such as
class. Any other name is SQLSRC103. Soget-useris not a name. - Names are case-sensitive.
GetUserandgetUserare two queries. - A file uses each name once. A name that a file uses twice is SQLSRC104.
- The files of one type use each name once. A name that two files of one type use is SQLSRC008.
- The name of a query is not the name of the class that holds its members (SQLSRC009). That class is one of these:
- for a query of any output, its nested class:
Sql, unless you name it otherwise; - the type itself, when the constant or token method of a query whose output is
modelsorsqlgoes on it.
- for a query of any output, its nested class:
- A query method cannot have some other names. They are listed with a repository method's signature, and with where the
methodsmethods go. See where the members go. - A query has SQL in it. A query that holds only comments and markers is SQLSRC113.
A -- name: marker can be followed by -> and a result shape: see result shapes. The name ends at the ->.
Result shapes
-- name: GetUser -> one-optional
SELECT id, name FROM users WHERE id = @id;
-- name: RenameUser -> none
UPDATE users SET name = @name WHERE id = @id;
A result shape is one of five words after -> on a -- name: line. It says what the method of a query whose output is repository or methods returns:
| Shape | The method returns |
|---|---|
many |
Every row, in the collection type, an array by default |
one |
The one row, and throws when there are none or more than one |
one-optional |
The one row, or null when there is none, and throws when there are more than one |
none |
Nothing |
rowcount |
The number of rows the statement affected |
What each shape returns has the details.
- Without a shape, a query that returns rows is
many. A query that returns no rows isrowcount. one,one-optionalandmanyneed a statement that returns rows. On a query whose statement returns no rows, each of them is SQLSRC272.noneandrowcountgo on any query.- A shape changes nothing under the output
modelsorsql. - Adding or changing a shape does not make the query's sidecar out of date.
- The case of a shape is ignored.
- After
->, a word that is not one of the five is SQLSRC111. So is a->with no word after it. - Text after a name with no
->is part of the name. So-- name: Get Usergives a name with a space in it, which is SQLSRC103. - A file with no
-- name:line has no place to write a shape.
Summaries
-- name: GetUser
-- summary: Loads one user by id.
-- summary: Returns nothing when there is no such user.
SELECT id, name FROM users WHERE id = @id;
A -- summary: line inside a query becomes the documentation of the query's member, and of its models. IntelliSense shows the documentation of the member beside the query's SQL.
- Several summaries are joined with a space.
- A summary with no text is skipped.
- The text is written as it is. So
<and&appear as you typed them. - A query without a summary is documented with its name and its file: "The
GetUserquery fromUsers.sql." - A
-- summary:before the first-- name:line belongs to no query. It is SQLSRC107.
The file header
-- Licensed under the MIT licence.
-- dialect: postgres
-- generator: keep-comments
-- name: GetUser
SELECT id, name FROM users WHERE id = @id;
The file header is the part of a .sql file before its first -- name: line. It can hold comments and markers:
- Comments, such as a licence. A comment in the header is never part of the SQL of a query, even of a query that keeps its comments.
- The markers whose "File header" column in the index says yes. A marker that is a setting applies to every query in the file, unless a query gives its own. The settings page says which place wins when you give a setting in several places.
SQL does not belong in the header. SQL before the first -- name: line belongs to no query, and is SQLSRC106.
The -- dialect: marker also goes before the file's first SQL. A file has one dialect, and the marker changes how SqlSource reads the text after it: see dialects.
A file with no -- name:
-- summary: Counts the users.
SELECT COUNT(*) FROM users;
A file with no -- name: line is one query, named after the file: CountUsers.sql holds the query CountUsers.
- The name is the file's name without its extension. It must be a name as queries and names describes.
get-user.sqland001_init.sqlare not names, and are SQLSRC105. To fix it, do one of these:- add a
-- name:line; - rename the file;
- keep the file out of the files that the type claims.
- add a
- A file that has a
-- name:line ignores its own name. - The file is its own header and its own query, so it takes every marker. The markers stand above the SQL or between its lines.
- A marker after the last SQL of the file is SQLSRC108.
What reaches the generated SQL
Hover over a generated member to see the SQL that it holds. It is the SQL you wrote, with these changes:
- Every marker line is removed.
- Comments are removed. A line comment is deleted, and a block comment becomes one space.
- Blank lines are removed, and so are the blanks at the end of a line. Indentation and the spacing inside a line stay.
- The
keep-commentsgenerator parameter keeps comments, and the blank lines between lines of SQL. See generator parameters. - Optimizer hints are kept:
/*+ ... */and/*! ... */. Where the dialect is theirs, MariaDB's/*M! ... */and Oracle's--+ ...are kept too. - Strings and quoted identifiers are copied exactly as written. Where one starts and ends depends on the dialect: see dialects.
- Every line ending becomes
\n. So the SQL does not depend on how the file was checked out. - A query parameter such as
@idstays as written. - A token such as
{{table}}is replaced when your code asks for the SQL, or when the query's method runs it. - A list,
IN @ids, is written as one parameter for each value when the query's method runs it. See list parameters.
Markers in the wrong place
-- name: GetUser
SELECT 1;
-- summary: Lists every user.
-- name: ListUsers
SELECT 2;
The summary was meant for ListUsers, but it follows the last SQL of GetUser. To a reader, a marker after the last SQL of a query looks as if it belongs to the next query, so it is refused. Move the summary under the -- name: line of ListUsers.
A marker goes before the SQL that it describes. These are the mistakes, and the error that each one is:
| Mistake | Error |
|---|---|
| A marker where its column in the index says "no", other than the two cases below: one for the whole file inside a query, or one for a query in the file header | SQLSRC116 |
A -- summary: before the first -- name: line |
SQLSRC107 |
A -- dialect: inside a query or after SQL |
SQLSRC115 |
SQL before the first -- name: line |
SQLSRC106 |
| A marker after the last SQL of its query | SQLSRC108 |
| A marker whose value its setting does not take | SQLSRC111 |
| The same marker twice in one place with two different values | SQLSRC112 |
- The same marker twice in one place with the same value is not an error.
- The words of several
-- generator:lines in one place add up. A query's words also add to those of its file header: see generator parameters. - A comment of yours can be read as a marker. A comment that starts with one of the words and a colon, such as
-- summary: see the ticket, is a marker. Reword it.
An error in how a .sql file is written, such as a marker in the wrong place, is reported at its line and column in that file. Until you fix it, the file gives no members. An error about describing a query, or about its sidecar, costs that query its models. See what an error costs, which queries get a method and diagnostics.