Table of Contents

Dialects

If your database is PostgreSQL or SQL Server, set the dialect to postgres or mssql and stop reading. The rest of this page is for the output sql on another database, and for SQL that SqlSource reads differently from your database.

The dialect matters because databases disagree about where a comment or a string ends. SqlSource removes comments from your SQL, so it has to know which rules your SQL follows. This line, read by three dialects, is three different statements:

SELECT 1 # x [y--z] -- c
Dialect The SQL that is kept Why
ansi SELECT 1 # x [y # is SQL, [ is SQL, and the first -- starts a comment
mysql SELECT 1 # starts a comment
mssql SELECT 1 # x [y--z] [y--z] is a quoted identifier, and the last -- starts a comment

The dialect is the set of rules by which SqlSource reads a .sql file. It connects to nothing, and it is not the logical database. This page covers the dialect names, the options, what each dialect changes, and what SqlSource still reads differently from the database. The settings page says where you set the dialect, and which place wins.

Dialect names

Dialect Also accepted Use it for
ansi The default. Any database without a dialect of its own, such as Db2
mssql sqlserver, tsql SQL Server, Azure SQL
postgres postgresql PostgreSQL, DuckDB
cockroachdb cockroach CockroachDB
mysql MySQL
mariadb MariaDB
sqlite SQLite
oracle Oracle, Firebird

Names are not case-sensitive.

Set the dialect named for your database. A database that has no dialect of its own can be read correctly by one that has: the last column says which. For example, postgres reads DuckDB, whose files then need the output sql (see database support). For any other database, use ansi.

  • ansi is the default. A project that sets nothing is read by ansi.
  • ansi reads the SQL of every database by one set of rules. So it misreads each construct of the table below where its column says No and the column of your database says Yes.
  • ansi also reads the quoting forms of PostgreSQL and MySQL that are rarely anything else: `...`, $tag$...$tag$ and E'...'.
  • The fix for a misread is to set the dialect.

The dialect also decides which databases the tool can ask about a query: see database support.

Options

-- dialect: mysql, no-backslash-escapes

-- name: GetPath
SELECT 'C:\temp\' AS path;

MySQL and MariaDB have SQL modes that change how a string is read. If your server runs with one, write its option after the dialect, with a comma:

Option Also accepted SQL mode What it changes
ansi-quotes ansi_quotes ANSI_QUOTES "..." is a quoted identifier, and a backslash does not escape in it
no-backslash-escapes no_backslash_escapes NO_BACKSLASH_ESCAPES A backslash does not escape in '...' or in "..."
// sqlsource.json, in the project's folder
{
    "root": true,
    "dialect": "mysql,ansi-quotes",
}
// Legacy/sqlsource.json
{
    "dialect": "mariadb,ansi-quotes,no-backslash-escapes",
}
  • Options are not case-sensitive, and come in any order. Spaces around the name and each option are ignored.
  • Only mysql and mariadb have options. An option of another dialect, or one that does not exist, is the same error as a name that is not a dialect. So is an empty option, as in mysql,. The settings page gives the errors by place.
  • In a -- dialect: marker, the value is the rest of the line: the name, then the options, separated by commas. Nothing else can follow it on the line, not even a comment.
  • A value written in several places, options included, follows the rules of the dialect setting.

What each dialect changes

ansi mssql postgres cockroachdb mysql mariadb sqlite oracle
A backslash escapes in '...' and "..." No No No No Yes Yes No No
A backslash escapes in E'...' Yes No Yes Yes Yes Yes No No
`...` is a quoted identifier Yes No No No Yes Yes Yes No
[...] is a quoted identifier No Yes No No No No Yes No
$tag$...$tag$ is a string Yes No Yes Yes Yes No No No
q'[...]' is a string No No No No No No No Yes
A /* inside a block comment needs its own */ Yes Yes Yes Yes No No No No
-- is a comment with no white space after it Yes Yes Yes Yes No No Yes Yes
# starts a comment No No No No Yes Yes No No
Kept as hints, besides /*+ ... */ and /*! ... */ /*M! ... */ --+ ...
A name declared with DECLARE @name is a local variable, not a parameter (declared variables) No Yes No No No No No No

Five details:

  • In mssql, a ]] inside brackets stands for one ]. In sqlite, the first ] ends the identifier.
  • In mysql and mariadb, an option changes the first row (see options).
  • In postgres and cockroachdb, an E'...' string that is continued on the next line is one string. Each later part takes backslash escapes too.
  • In postgres, -- comments can stand between the parts of such a string, as PostgreSQL allows. They are removed like any other comment. In cockroachdb, only white space can stand there.
  • In cockroachdb, a bytes literal, b'...', takes backslash escapes as an E'...' string does. So does b'...' in mysql and mariadb, where every string does.

Markers under mysql and mariadb

Under mysql and mariadb, -- name: GetUser is a marker, and --name: GetUser is not a comment at all. A marker is a -- comment, so under these dialects it needs white space after the two dashes, as these databases need it.

A # comment is never a marker, even where # starts a comment: # name: GetUser is a comment.

What is still read differently

SqlSource reads these differently from the database, whatever the dialect:

Construct Database How SqlSource reads it
A versioned comment whose body holds a string that contains */, such as /*!50700 SELECT '*/' */ MySQL, MariaDB The comment ends at the first */, so the quote after it opens a string that is never closed: the error SQLSRC101. Where the server ends the comment depends on the server's version
A block comment that is still open at the end of the file SQLite The error SQLSRC102. SQLite accepts it, but it would take every later query of the file with it
A file whose lines end with a carriage return alone, with no line feed MySQL, SQLite, CockroachDB A line ends there, as in every dialect. These databases end a -- comment only at a line feed
A client command that is not SQL: DELIMITER, GO, SQL*Plus PROMPT and REM, a psql \ command All As SQL, so a quote in it can open a string
A name that is not a parameter to the database: a user variable, @x for an absolute value, the parameter of a procedure that the SQL creates MySQL, MariaDB, PostgreSQL, SQL Server As a parameter: see names that are read as parameters

A misread has one of two results:

  • A build error that reports an unclosed quote or comment (SQLSRC101, SQLSRC102) in SQL that is valid for your database. keep-comments does not help.
  • Missing SQL. The misread quotes happen to balance, and the SQL after them is taken for a comment and removed. Under ansi, SELECT 'a\'b -- c', 2 becomes SELECT 'a\'b. Nothing is reported. keep-comments on the query prevents the removal.

If your SQL uses one of these constructs, check the generated SQL: hover over the member, or read its documentation.