Database
SwiftlyS2's database service lets plugins fetch named connections from one global configuration, through APIISwiftlyCore.Database (APIIDatabaseService).
Service Members
Type is the return type of the method:
| Member | Type | Description |
|---|---|---|
| APIIDatabaseService.GetConnectionString | string | Connection string of a connection, for (string connectionName) |
| APIIDatabaseService.GetConnectionInfo | APIDatabaseConnectionInfo | Parsed metadata of a connection, for (string connectionName) |
| APIIDatabaseService.GetConnection | IDbConnection | A connection to the database, for (string connectionName) |
All three resolve connectionName from the global configuration and return the default connection when the name isn't found.
Global Database Configuration
Connections are defined in configs/database.jsonc and referenced by name from plugins:
{
// Default connection name
"default_connection": "default",
"connections": {
"default": "mysql://root:@localhost:3306/swiftlys2",
"postgres_uri": "postgresql://postgres:@localhost:5432/swiftlys2",
"local": "sqlite://swiftlys2-local.db"
}
}default_connection is the connection name a lookup falls back to when the requested name isn't found. Supported URI formats: driver://user:password@host:port/database for mysql and postgresql, and sqlite://path/to/database.db for sqlite.
Object Format and Options
A connection can also be an object. Besides driver, host, port, database, user, pass and timeout, it takes an options field with the connection string keywords of the driver:
{
"connections": {
"mysql_detailed": {
"driver": "mysql",
"host": "localhost",
"database": "swiftlys2",
"user": "root",
"pass": "",
"port": 3306,
"timeout": 30,
"options": {
"SslMode": "Preferred",
"CharacterSet": "utf8mb4",
"MaximumPoolSize": 50,
"DefaultCommandTimeout": 60
}
},
"postgres_detailed": {
"driver": "postgresql",
"host": "localhost",
"database": "swiftlys2",
"user": "postgres",
"pass": "",
"port": 5432,
"timeout": 30,
"options": {
"SSL Mode": "Prefer",
"Maximum Pool Size": 50,
"Command Timeout": 60,
"Search Path": "public"
}
},
"sqlite_detailed": {
"driver": "sqlite",
"database": "data/swiftlys2-detailed.db",
"options": {
"Journal Mode": "Wal",
"Foreign Keys": true,
"Cache Size": 4000,
"Default Timeout": 30
}
}
}
}options takes the connection string keywords of the driver used for that connection - MySqlConnector for mysql, Npgsql for postgresql and System.Data.SQLite for sqlite:
| Driver | Library | Keywords |
|---|---|---|
mysql | MySqlConnector | Connection options |
postgresql | Npgsql | Connection string parameters |
sqlite | System.Data.SQLite | The properties of SQLiteConnectionStringBuilder, such as Journal Mode, Foreign Keys, Cache Size, Default Timeout and Pooling |
Every keyword that the driver supports works, with the same names and synonyms as in its own connection string. Values can be strings, numbers or booleans. Options are applied after the other fields, so an option can override one of them, for example Server.
A keyword or a value that the driver doesn't know is an error when the connection is requested.
Getting a Connection
APIIDatabaseService.GetConnection returns an
IDbConnection for the named connection, falling back to the default connection
if the name isn't found. APIIDatabaseService.GetConnectionString and
APIIDatabaseService.GetConnectionInfo share the same
fallback and give you the connection string / the parsed metadata instead (see
APIDatabaseConnectionInfo). The returned connection is not
opened - call Open()/OpenAsync() yourself:
using var connection = Core.Database.GetConnection("default");
connection.Open();
string connectionString = Core.Database.GetConnectionString("default");
DatabaseConnectionInfo info = Core.Database.GetConnectionInfo("default");
Console.WriteLine($"{info.Driver} -> {info.Host}:{info.Port}/{info.Database}");
Console.WriteLine(info.ToString()); // connection string built from the infoAPIDatabaseConnectionInfo is a record struct, so its properties are positional and it can be deconstructed:
| Member | Type | Description |
|---|---|---|
Driver | string | Driver of the connection, e.g. mysql, postgresql or sqlite |
Host | string | Host of the database server |
Database | string | Database name, or the file path for sqlite |
User | string | User name |
Pass | string | Password |
Timeout | uint | Timeout value of the connection |
Port | ushort | Port of the database server |
RawUri | string | The raw connection URI, when one is available |
Options | IReadOnlyDictionary<string, string> | The parsed options of the connection, empty when it has none |
Options is the one member outside the positional parameter list: it is an init-only property, set with an object initializer, and it holds the configured values as strings - including those written as numbers or booleans in the configuration.
APIDatabaseConnectionInfo.ToString renders the connection string: it returns RawUri unchanged when that is set, otherwise it builds the driver's own format - Data Source=... for sqlite, Server=...;Port=...;Database=...;User ID=...;Password=... (plus Timeout when it is greater than 0) for mysql and postgresql, and driver://user:pass@host:port/database for any other driver - and appends every entry of Options. When Port is 0 on a mysql or postgresql connection, the default port is used (3306 and 5432). Values with leading or trailing whitespace, or containing ;, ", ' or =, are quoted.
Avoid logging secrets like Pass in production - a rendered connection string contains it.
services.AddSwiftly(Core) registers APIIDatabaseService as a singleton next to the other core services, so it can be injected like any other dependency - see Dependency Injection.
Using ADO.NET or an ORM
GetConnection returns IDbConnection, so plain ADO.NET or any compatible ORM works - Dapper, Dommel, FreeSql, Entity Framework Core:
using var connection = Core.Database.GetConnection("default");
var players = await connection.QueryAsync<PlayerRow>(
"SELECT steam_id, name FROM players WHERE is_active = @IsActive",
new { IsActive = true }
);