SqlSource

SqlSource keeps the SQL of your C# project in .sql files, out of your code files, and generates the C# that runs it. For each query, the build gives you a method, a record for its parameters and a record for its rows. SQL in a file of its own is easier to work with than SQL in a C# string. Your editor highlights it, SQL tools format it, and a search finds it. A renamed or removed query, or a column that your code reads and the query no longer returns, is a compile error. With PostgreSQL or SQL Server, the sqlsource tool checks every query against your database's schema.

-- name: GetUser -> one-optional
-- summary: One user, or null.
SELECT
    id,
    name,
    email
FROM users
WHERE id = @userId;
await using var dataSource = NpgsqlDataSource.Create("Host=localhost;Database=app;Username=app;Password=secret");
var users = new UserRepository(dataSource);
GetUserDto? user = await users.GetUser(new GetUserParams(1));

Does it work with my database?

Your database What you get
PostgreSQL 14 or later Typed models and methods for your queries
SQL Server 2017 or later Typed models and methods for your queries
Azure SQL Accepted, and not tested
Any other database Your SQL as constants

The versions are those of supported versions.

Start here: Install

You use Dapper: start with Use Dapper. Your database is not PostgreSQL or SQL Server: start with another database.

What do you want to do?

I want to Go to
Run queries in a transaction, or with a timeout Run queries in a transaction (or with a timeout)
Pass a list to IN Pass a list of values (IN)
Stop a property being nullable Fix a nullable property
Mock a repository in a test Test code that uses a repository (mock or fake)
Insert many rows in one call Insert or update many rows in one call (bulk)
Use an enum or an id type of my own Use your own types: ids, enums, value objects
Read a JSON column Read and write JSON columns
Use a second database Use several databases
Keep the sidecars right in CI Check the sidecars in CI
Describe as I save Describe queries as you save (watch)
Choose a table name at run time Choose a table or a clause at run time
Use Dapper Use Dapper or your own ADO.NET code
Lint or format my SQL Lint and format SQL with sqlfluff
Look up a setting and where to write it All settings
Look up a -- word: line The marker index
Look up a command or an option The command reference
It built, but something is wrong Troubleshooting: it built, but something is wrong

A build error?

The three parts

  • Get started is read in order, from the install to a tested application. How SqlSource works gives the words that the manual uses.
  • Guides give one task on each page, such as a list after IN or a JSON column.
  • Reference has one page for each thing, with all of its rules, for when you look something up.

To see whole projects with their databases, open the PostgreSQL example and the SQL Server example.