Multi-Host Connection Support · sdaves/NpgsqlRest@fe71100 · GitHub
Skip to content

Commit fe71100

Browse files
vbilopavvbilopav
committed
Multi-Host Connection Support
1 parent 3a5f02a commit fe71100

11 files changed

Lines changed: 518 additions & 61 deletions

‎NpgsqlRest/Extensions.cs‎

Lines changed: 26 additions & 4 deletions

‎NpgsqlRest/NpgsqlRestEndpoint.cs‎

Lines changed: 14 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -46,18 +46,28 @@ public async Task InvokeAsync(HttpContext context, IServiceProvider serviceProvi
4646
{
4747
if (endpoint.ConnectionName is not null)
4848
{
49-
if (Options.ConnectionStrings?.TryGetValue(endpoint.ConnectionName, out var connectionString) is true)
49+
// First check if there's a data source for this connection name (for multi-host support)
50+
if (Options.DataSources?.TryGetValue(endpoint.ConnectionName, out var namedDataSource) is true)
51+
{
52+
connection = namedDataSource.CreateConnection();
53+
}
54+
else if (Options.ConnectionStrings?.TryGetValue(endpoint.ConnectionName, out var connectionString) is true)
5055
{
5156
connection = new(connectionString);
5257
}
5358
else
5459
{
55-
await ReturnErrorAsync($"Connection name {endpoint.ConnectionName} could not be found in options ConnectionStrings dictionary.", true, context);
60+
await ReturnErrorAsync($"Connection name {endpoint.ConnectionName} could not be found in options DataSources or ConnectionStrings dictionaries.", true, context);
5661
return;
5762
}
5863
}
5964
else if (Options.ServiceProviderMode != ServiceProviderObject.None)
6065
{
66+
if (serviceProvider is null)
67+
{
68+
await ReturnErrorAsync($"ServiceProvider must be provided when ServiceProviderMode is set to {Options.ServiceProviderMode}.", true, context);
69+
return;
70+
}
6171
if (Options.ServiceProviderMode == ServiceProviderObject.NpgsqlDataSource)
6272
{
6373
connection = serviceProvider.GetRequiredService<NpgsqlDataSource>().CreateConnection();
@@ -390,50 +400,6 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
390400
}
391401
}
392402

393-
if (Options.HttpClientOptions.Enabled)
394-
{
395-
if (parameter.TypeDescriptor.CustomType is not null)
396-
{
397-
if (HttpClientTypes.Definitions.ContainsKey(parameter.TypeDescriptor.CustomType))
398-
{
399-
customHttpTypes.Add(parameter.TypeDescriptor.CustomType);
400-
}
401-
}
402-
}
403-
404-
if (Options.HttpClientOptions.Enabled)
405-
{
406-
if (parameter.TypeDescriptor.CustomType is not null)
407-
{
408-
if (HttpClientTypes.Definitions.ContainsKey(parameter.TypeDescriptor.CustomType))
409-
{
410-
customHttpTypes.Add(parameter.TypeDescriptor.CustomType);
411-
}
412-
}
413-
}
414-
415-
if (Options.HttpClientOptions.Enabled)
416-
{
417-
if (parameter.TypeDescriptor.CustomType is not null)
418-
{
419-
if (HttpClientTypes.Definitions.ContainsKey(parameter.TypeDescriptor.CustomType))
420-
{
421-
customHttpTypes.Add(parameter.TypeDescriptor.CustomType);
422-
}
423-
}
424-
}
425-
426-
if (Options.HttpClientOptions.Enabled)
427-
{
428-
if (parameter.TypeDescriptor.CustomType is not null)
429-
{
430-
if (HttpClientTypes.Definitions.ContainsKey(parameter.TypeDescriptor.CustomType))
431-
{
432-
customHttpTypes.Add(parameter.TypeDescriptor.CustomType);
433-
}
434-
}
435-
}
436-
437403
if (Options.HttpClientOptions.Enabled)
438404
{
439405
if (parameter.TypeDescriptor.CustomType is not null)
@@ -945,7 +911,8 @@ await Options.ValidateParametersAsync(new ParameterValidationValues(
945911
}
946912
else if (parameter.TypeDescriptor.HasDefault is false)
947913
{
948-
transaction?.RollbackAsync();
914+
shouldCommit = false;
915+
uploadHandler?.OnError(connection, context, null);
949916
context.Response.StatusCode = StatusCodes.Status404NotFound;
950917
await context.Response.CompleteAsync();
951918
return;

‎NpgsqlRest/Options/NpgsqlRestOptions.cs‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,14 @@ public NpgsqlRestOptions(NpgsqlDataSource dataSource)
6666
/// For example, some routines might use the primary database connection string, while others might use a read-only connection string from the replica servers.
6767
/// </summary>
6868
public IDictionary<string, string>? ConnectionStrings { get; set; }
69+
70+
/// <summary>
71+
/// Dictionary of data sources by connection name. This is used for multi-host connection support.
72+
/// When a connection name is specified in a routine endpoint, the middleware will first check this dictionary for a data source.
73+
/// If not found, it falls back to the ConnectionStrings dictionary.
74+
/// Use this for multi-host failover/load-balancing scenarios where you need NpgsqlMultiHostDataSource with specific target session attributes.
75+
/// </summary>
76+
public IDictionary<string, NpgsqlDataSource>? DataSources { get; set; }
6977

7078
/// <summary>
7179
/// The connection name in ConnectionStrings dictionary that will be used to execute the metadata query. If this value is null, the default connection string or data source will be used.

‎NpgsqlRestClient/Builder.cs‎

Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1203,4 +1203,102 @@ public HttpClientOptions BuildHttpClientOptions()
12031203

12041204
return options;
12051205
}
1206+
1207+
/// <summary>
1208+
/// Checks if a connection string is a multi-host connection string (has comma-separated hosts).
1209+
/// </summary>
1210+
public static bool IsMultiHostConnectionString(string connectionString)
1211+
{
1212+
var builder = new NpgsqlConnectionStringBuilder(connectionString);
1213+
return builder.Host?.Contains(',') ?? false;
1214+
}
1215+
1216+
/// <summary>
1217+
/// Gets the target session attribute for a connection name from configuration.
1218+
/// </summary>
1219+
public TargetSessionAttributes GetTargetSessionAttribute(string? connectionName)
1220+
{
1221+
var multiHostCfg = _config.ConnectionSettingsCfg.GetSection("MultiHostConnectionTargets");
1222+
if (_config.Exists(multiHostCfg) is false)
1223+
{
1224+
return TargetSessionAttributes.Any;
1225+
}
1226+
1227+
// Check per-connection override first
1228+
if (connectionName is not null)
1229+
{
1230+
var byName = multiHostCfg.GetSection("ByConnectionName");
1231+
var overrideValue = _config.GetConfigStr(connectionName, byName);
1232+
if (overrideValue is not null && Enum.TryParse<TargetSessionAttributes>(overrideValue, true, out var result))
1233+
{
1234+
Logger?.LogDebug("Using target session attribute override '{TargetSession}' for connection '{ConnectionName}'", result, connectionName);
1235+
return result;
1236+
}
1237+
}
1238+
1239+
// Fall back to default
1240+
var defaultValue = _config.GetConfigStr("Default", multiHostCfg) ?? "Any";
1241+
if (Enum.TryParse<TargetSessionAttributes>(defaultValue, true, out var defaultResult))
1242+
{
1243+
return defaultResult;
1244+
}
1245+
return TargetSessionAttributes.Any;
1246+
}
1247+
1248+
/// <summary>
1249+
/// Builds data sources dictionary for multi-host connections.
1250+
/// </summary>
1251+
public Dictionary<string, NpgsqlDataSource> BuildDataSources(string mainConnectionString)
1252+
{
1253+
var result = new Dictionary<string, NpgsqlDataSource>();
1254+
1255+
// Build main data source if it's multi-host
1256+
if (IsMultiHostConnectionString(mainConnectionString))
1257+
{
1258+
var target = GetTargetSessionAttribute(ConnectionName);
1259+
var multiHost = new NpgsqlDataSourceBuilder(mainConnectionString).BuildMultiHost();
1260+
result["_default"] = multiHost.WithTargetSession(target);
1261+
Logger?.LogDebug("Built multi-host data source for main connection with target session '{TargetSession}'", target);
1262+
}
1263+
1264+
// Build additional connection data sources
1265+
if (_config.GetConfigBool("UseMultipleConnections", _config.NpgsqlRestCfg, false))
1266+
{
1267+
foreach (var section in _config.Cfg.GetSection("ConnectionStrings").GetChildren())
1268+
{
1269+
if (section?.Key is null || string.Equals(ConnectionName, section.Key, StringComparison.OrdinalIgnoreCase))
1270+
{
1271+
continue;
1272+
}
1273+
1274+
var (connStr, _) = BuildConnection(section.Key, section.Value!, isMain: false, skipRetryOpts: true);
1275+
if (connStr is null || !IsMultiHostConnectionString(connStr))
1276+
{
1277+
continue;
1278+
}
1279+
1280+
var target = GetTargetSessionAttribute(section.Key);
1281+
var multiHost = new NpgsqlDataSourceBuilder(connStr).BuildMultiHost();
1282+
result[section.Key] = multiHost.WithTargetSession(target);
1283+
Logger?.LogDebug("Built multi-host data source for connection '{ConnectionName}' with target session '{TargetSession}'", section.Key, target);
1284+
}
1285+
}
1286+
1287+
return result;
1288+
}
1289+
1290+
/// <summary>
1291+
/// Builds the main data source (handles both single-host and multi-host).
1292+
/// </summary>
1293+
public NpgsqlDataSource BuildMainDataSource(string connectionString)
1294+
{
1295+
if (IsMultiHostConnectionString(connectionString))
1296+
{
1297+
var target = GetTargetSessionAttribute(ConnectionName);
1298+
var multiHost = new NpgsqlDataSourceBuilder(connectionString).BuildMultiHost();
1299+
Logger?.LogDebug("Using multi-host data source with target session '{TargetSession}'", target);
1300+
return multiHost.WithTargetSession(target);
1301+
}
1302+
return new NpgsqlDataSourceBuilder(connectionString).Build();
1303+
}
12061304
}

‎NpgsqlRestClient/Program.cs‎

Lines changed: 16 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -193,13 +193,22 @@
193193
}
194194
appInstance.ConfigureStaticFiles(app, authenticationOptions);
195195

196-
//await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
197-
//TODO: multi-host support and switch to data sources for all connections
198-
199-
await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).BuildMultiHost().WithTargetSession(TargetSessionAttributes.Primary);
200-
//var primaryDataSource = dataSource.WithTargetSession(TargetSessionAttributes.Primary);
201-
//dataSource.OpenConnectionAsync(TargetSessionAttributes.Primary);
196+
// Build data source (handles both single-host and multi-host connections)
197+
await using var dataSource = builder.BuildMainDataSource(connectionString);
202198

199+
// Build additional multi-host data sources for named connections
200+
var dataSources = builder.BuildDataSources(connectionString);
201+
if (dataSources.Count > 0)
202+
{
203+
// Register disposal of additional data sources
204+
app.Lifetime.ApplicationStopping.Register(() =>
205+
{
206+
foreach (var ds in dataSources.Values)
207+
{
208+
ds.Dispose();
209+
}
210+
});
211+
}
203212

204213
var logConnectionNoticeEventsMode = config.GetConfigEnum<PostgresConnectionNoticeLoggingMode?>("LogConnectionNoticeEventsMode", config.NpgsqlRestCfg) ?? PostgresConnectionNoticeLoggingMode.FirstStackFrameAndMessage;
205214

@@ -208,6 +217,7 @@
208217
NpgsqlRestOptions options = new()
209218
{
210219
DataSource = dataSource,
220+
DataSources = dataSources.Count > 0 ? dataSources : null,
211221
ServiceProviderMode = ServiceProviderObject.None,
212222
ConnectionStrings = connectionStrings,
213223
ConnectionRetryOptions = retryOpts,

‎NpgsqlRestClient/appsettings.json‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -112,7 +112,21 @@
112112
// This is needed when using non superuser connection roles with limited schema access and mapping the metadata function to a specific schema.
113113
// If the connection string contains the same "Search Path=" it will be skipped.
114114
//
115-
"MetadataQuerySchema": null
115+
"MetadataQuerySchema": null,
116+
// Any: Any successful connection is acceptable.
117+
// Primary: Server must not be in hot standby mode (pg_is_in_recovery() must return false).
118+
// Standby: Server must be in hot standby mode (pg_is_in_recovery() must return true).
119+
// PreferPrimary: First try to find a primary server, but if none of the listed hosts is a primary server, try again in Any mode.
120+
// PreferStandby: First try to find a standby server, but if none of the listed hosts is a standby server, try again in Any mode.
121+
// ReadWrite: Session must accept read-write transactions by default (that is, the server must not be in hot standby mode and the default_transaction_read_only parameter must be off).
122+
// ReadOnly: Session must not accept read-write transactions by default (the converse).
123+
// see https://www.npgsql.org/doc/failover-and-load-balancing.html
124+
"MultiHostConnectionTargets": {
125+
// all connections use the same target mode
126+
"Default": "Any",
127+
// per connection overrides { "name": "Primary|Standby|Any|PreferPrimary|PreferStandby|ReadWrite|ReadOnly" }
128+
"ByConnectionName": { }
129+
}
116130
},
117131

118132
//

‎NpgsqlRestTests/ConnectionTests/ConnectionNameTests.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,6 @@ public async Task Test_get_conn3_connection_name()
8686
using var response = await test.Client.GetAsync("/api/get-conn3-connection-name/");
8787
var content = await response.Content.ReadAsStringAsync();
8888
response?.StatusCode.Should().Be(HttpStatusCode.InternalServerError);
89-
content.Should().Be("Connection name conn3 could not be found in options ConnectionStrings dictionary.");
89+
content.Should().Be("Connection name conn3 could not be found in options DataSources or ConnectionStrings dictionaries.");
9090
}
9191
}

‎NpgsqlRestTests/ConnectionTests/ConnectionNameUsingParamsTests.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,6 @@ public async Task Test_get_conn3_connection_name_p()
6868
using var response = await test.Client.GetAsync("/api/get-conn3-connection-name-p/");
6969
var content = await response.Content.ReadAsStringAsync();
7070
response?.StatusCode.Should().Be(HttpStatusCode.InternalServerError);
71-
content.Should().Be("Connection name conn3 could not be found in options ConnectionStrings dictionary.");
71+
content.Should().Be("Connection name conn3 could not be found in options DataSources or ConnectionStrings dictionaries.");
7272
}
7373
}

‎NpgsqlRestTests/ConnectionTests/CreateAndOpenSourceConnectionTests.cs‎

Lines changed: 1 addition & 1 deletion

0 commit comments

Comments
 (0)