Table of Contents

Use several databases

Query a second database from one project, and give the tool two connection strings, one for each database.

Use this when the queries of one project run against two or more databases. Use it too when the files of one folder are written for another kind of database.

Use several databases

This PostgreSQL project sets "dialect": "postgres" in its sqlsource.json. Name the logical database of each file with a line such as -- database: billing at its top:

-- database: billing

-- name: ListInvoices
SELECT id, total FROM invoices;

The files of reports start with -- database: reports. Give the tool a connection for each name. Then describe the queries:

$ export SQLSOURCE_CONNECTION_BILLING="Host=localhost;Database=billing;Username=app;Password=secret"
$ export SQLSOURCE_CONNECTION_REPORTS="Host=localhost;Database=reports;Username=app;Password=secret"
$ dotnet sqlsource describe
billing (postgres): 4 described, 0 skipped, 0 failed
reports (postgres): 2 described, 0 skipped, 0 failed

A repository that claims files of both databases marks the connection of each:

[SqlSourceGenerate(Source = ".")]
public partial class Accounting(
    [SqlSourceConnection(Database = "billing")] NpgsqlDataSource billing,
    [SqlSourceConnection(Database = "reports")] NpgsqlDataSource reports);

What you get. The tool describes the files of each database through the connection of its name. Its summary has a line for each database. Accounting runs the queries of each database on the data source marked for it.

The rules you need.

  • Several databases can share one dialect. Here billing and reports are both postgres. See database.
  • The variable of a name is SQLSOURCE_CONNECTION_ and the name in upper case, with each character that is not a letter or a digit written as _: SQLSOURCE_CONNECTION_BILLING_V2 for billing-v2. See connections.
  • The build reads the database of each query too. It chooses the connection of each repository method by it. See database.
  • A repository that uses several databases marks one connection for each, as [SqlSourceConnection(Database = "billing")], or uses the connections that the project marks for all its repositories. See One repository on two databases, Share the connections of several databases and a repository's connection.
  • For files written for another kind of database, set their dialect. See Read one folder with another dialect.

One repository on two databases

If one repository class runs queries of two databases, mark a connection for each database. Here Users.sql and Invoices.sql are in one folder:

-- Users.sql
-- database: main

-- name: GetUser -> one-optional
SELECT id, name FROM users WHERE id = @id;
-- Invoices.sql
-- database: billing

-- name: GetInvoice
SELECT id, number, amount, paid_at
FROM billing.invoices
WHERE id = @invoiceId;
[SqlSourceGenerate(Source = ".")]
public partial class AccountRepository(
    [SqlSourceConnection(Database = "main")] NpgsqlDataSource main,
    [SqlSourceConnection(Database = "billing")] NpgsqlDataSource billing);
SQLSOURCE_CONNECTION_MAIN="Host=localhost;Database=main;Username=app;Password=secret"
SQLSOURCE_CONNECTION_BILLING="Host=localhost;Database=billing;Username=app;Password=secret"

What you get. GetUser runs on a connection of main, and GetInvoice on a connection of billing. The tool describes each file through the connection of its database.

The rules you need.

  • A -- database: line in the file header names the logical database of each query of the file. A file with no such line belongs to the database named after its dialect, postgres or mssql. See Database.
  • If the repository queries of a class use more than one database, mark one member for each database. The build then finds no member by its name or type. See The connection.
  • On SQL Server, which has no data source, mark a connection string, a method that returns a connection, or a connection. See The connection.
  • The tool needs a connection for each name. SQLSOURCE_CONNECTION alone serves no database when the run has more than one. See Connections.

Share the connections of several databases

If many repositories use the same databases, mark their connections once, as static members of a class of your project. Mark the connection of the main database without Database, and each other connection with its name:

public static class Db
{
    [SqlSourceConnection]
    public static NpgsqlDataSource? Main { get; set; }

    [SqlSourceConnection(Database = "billing")]
    public static NpgsqlDataSource? Billing { get; set; }
}

[SqlSourceGenerate(Source = ".")]
public partial class AccountRepository;
// Program.cs
Db.Main = NpgsqlDataSource.Create(builder.Configuration.GetConnectionString("Main")!);
Db.Billing = NpgsqlDataSource.Create(builder.Configuration.GetConnectionString("Billing")!);
builder.Services.AddSingleton<IAccountRepository, AccountRepository>();

What you get. GetInvoice runs on a connection of Db.Billing, and GetUser on one of Db.Main. Each other repository of the project with no connection of its own uses them in the same way. Your application sets both members once, at startup, before a repository method runs.

The rules you need.

Read one folder with another dialect

A project of PostgreSQL files keeps its reports for SQL Server in Reporting/. A settings file in that folder sets the dialect of its files:

// sqlsource.json, in the project's folder
{
    "root": true,
    "dialect": "postgres",
}
// Reporting/sqlsource.json
{
    "dialect": "mssql",
}

To set the dialect of one file, write a -- dialect: marker in its file header:

-- dialect: mssql

-- name: MonthlyTotals
SELECT [month], SUM(total) AS total FROM invoices GROUP BY [month];

Give the tool a connection for each of the two databases, postgres and mssql:

$ export SQLSOURCE_CONNECTION_POSTGRES="Host=localhost;Database=app;Username=app;Password=secret"
$ export SQLSOURCE_CONNECTION_MSSQL="Server=localhost,1433;Database=reports;User ID=app;Password=secret;TrustServerCertificate=True"
$ dotnet sqlsource describe

What you get. SqlSource reads the files under Reporting/ as mssql, and every other file of the project as postgres. It reads a file with the marker as mssql too. The tool describes the reports against SQL Server, as the database mssql, beside the database postgres of the other files.

The rules you need.

  • A settings file sets the dialect of the .sql files of its folder and of the folders below it. See settings files and configure one folder differently.
  • A -- dialect: marker in the file header wins over every settings file. See dialect and which place wins.
  • If the reports need only their SQL, set their output to sql, as "output": "sql" in Reporting/sqlsource.json. The tool then leaves them out, and needs no connection for them. See output.

If it does not work

  • SQLSRC030. A member of the repository is marked [SqlSourceConnection] with no Database, and the queries use more than one database. Set its Database. See SQLSRC030.
  • SQLSRC031, a warning. No repository query of the class uses the database of a mark. Its Database is misspelt, or the queries of that database have another output. See SQLSRC031.
  • SQLSRC028. No member is marked for a database that the repository queries use. Mark one, with its Database, on the repository or as a shared mark. See SQLSRC028.
  • SQLSRC033 at the repository's [SqlSourceGenerate]. A static member of the project names the database, and is of the other engine's driver. See SQLSRC033.
  • SQLSRC213, from the tool. A database has a query to describe and no connection. Set the variable of its name. See SQLSRC213.