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
billingandreportsare bothpostgres. 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_V2forbilling-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,postgresormssql. 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_CONNECTIONalone 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.
- A shared mark without
Databaseserves each database that no other mark names, on a repository of one database or of several. See which database a shared mark is for. - A shared mark for a database that a repository does not use is ignored, with no warning. See which database a shared mark is for.
- A shared mark of an Npgsql type without
Databasedoes not serve a SQL Server database, and one of a Microsoft.Data.SqlClient type does not serve a PostgreSQL database. So one class can hold the connections of both engines. See which database a shared mark is for. - A repository's own members win. A repository that marks a member for a database uses it. See where the build looks.
- A factory that a repository holds can mark its members in the same way. See a factory that a repository holds.
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
.sqlfiles 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"inReporting/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 noDatabase, and the queries use more than one database. Set itsDatabase. See SQLSRC030. - SQLSRC031, a warning. No repository query of the class uses the database of a mark. Its
Databaseis 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.