Table of Contents

Use MySQL, SQLite, Oracle or another database

Keep the SQL of a MySQL, MariaDB, SQLite, Oracle, CockroachDB or DuckDB project in .sql files, as C# constants.

Use this when your database is not PostgreSQL or SQL Server. You get the SQL alone, and run it with your own code.

  1. Add the package.
  2. Write a query.
  3. Mark a class.
  4. Set the dialect and the output sql.
  5. Run the constant.

Add the package

Step 1. In your project's folder, add the package, and your database's driver:

$ dotnet add package SqlSource
$ dotnet add package Microsoft.Data.Sqlite

A project whose queries all have the output sql needs no sqlsource tool. See The tool.

Write a query

Step 2. Save your queries as Queries/UserQueries.sql:

-- name: CountUsers
-- summary: The number of users.
SELECT count(*) FROM users;

-- name: CountRows
-- summary: The number of rows of a table that the code chooses.
SELECT count(*) FROM {{table}};

The rules you need.

  • -- name: starts a query, and its name is a C# identifier. See Queries and names.
  • On MySQL and MariaDB, a marker needs white space after --, and a # comment is never a marker. See Markers under mysql.

Mark a class

Step 3. Save a partial class of the file's name as Queries/UserQueries.cs:

// Queries/UserQueries.cs
using SqlSource;

[SqlSourceGenerate]
public sealed partial class UserQueries;

What you get. The class gets UserQueries.sql, the file named for it in its folder. The class must be partial. See The attribute and Source.

Set the dialect of your database and the output sql

Step 4. Save this sqlsource.json:

// sqlsource.json, beside your project file
{
    "root": true,
    "dialect": "sqlite",
    "output": "sql",
}

What you get. SqlSource reads every .sql file of the project as SQLite. Each query gets a constant or a method that returns its SQL, which step 5 runs.

Use mysql, mariadb, sqlite, oracle (also for Firebird) or cockroachdb for that database, postgres for DuckDB, and ansi for any other.

The rules you need.

  • Any dialect works with the output sql. Set the one named for your database, such as mysql or sqlite. See Choosing a dialect.
  • The dialect tells SqlSource where a comment or a string ends. See Choosing a dialect.
  • The output sql gives each query its SQL alone, and needs no sidecar and no connection. See Output.

Run the constant

Step 5. Run the SQL with your driver:

// Queries/UserQueries.cs
using Microsoft.Data.Sqlite;
using SqlSource;

[SqlSourceGenerate]
public sealed partial class UserQueries(SqliteConnection connection)
{
    public long CountUsers() => Count(Sql.CountUsers);

    public long CountRows(string table) => Count(Sql.CountRows(table));

    private long Count(string sql)
    {
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        return (long)command.ExecuteScalar()!;
    }
}

What you get. Sql.CountUsers is a const string. Sql.CountRows(table) returns the SQL with the token filled in.

The rules you need.

  • Sql is a private class nested in UserQueries, so only its own code can use it. See Where the members go.
  • The SQL is as you wrote it, without its markers and comments. A query parameter such as @id stays in it: give the database its value yourself. See Constants and What reaches the generated SQL.
  • Nothing in a token is escaped. Pass only text that your own code chooses. See Token methods.

What you do not get

You get no models and no method that runs the query. Nothing checks a query against your schema, so a wrong table or column fails only when the query runs.

The rules you need.

  • Only PostgreSQL and SQL Server can be described. On another dialect, an output other than sql is SQLSRC209, models included. See Databases that are not described.
  • If your database is DuckDB, set the dialect to postgres and the output to sql. The tool cannot describe a DuckDB query. See DuckDB.

If it does not work

  • SQLSRC209 for each file. The output of a query in the file is not sql, and only PostgreSQL and SQL Server can be described. Set the output to sql, as step 4 does. See SQLSRC209.
  • SQLSRC101 or SQLSRC102 on valid SQL, or SQL missing from a constant. SqlSource reads a few constructs differently from your database. See What is still read differently.