Pragmatic connection string management for small Dapper APIs

Right now our connection strings sit in appsettings.json as plain text, and I'm worried about secrets leaking into source control or container images. We're not ready to adopt Azure Key Vault because the team wants to keep dependencies light.

What's the most practical approach for a small ASP.NET Core API using Dapper? I've heard about User Secrets for local development and environment variables for staging, but I'm unsure how to wire that cleanly into IConfiguration without scattering GetConnectionString calls everywhere. Is there a standard helper or pattern you'd recommend?

Keep the string in configuration

Use a named entry such as ConnectionStrings:ApiDatabase. A local appsettings.Development.json can hold non-sensitive development settings, while passwords and production credentials should come from .NET User Secrets or a deployment secret store. Environment variables can override the setting with the name ConnectionStrings__ApiDatabase. Don’t commit real credentials or log the connection string.

Use a small connection factory

A singleton factory is fine because it stores only the immutable string. It should create a new connection each time; don’t register one open SqlConnection for the whole application. SQL Server’s built-in connection pooling makes opening and disposing connections per operation practical.

using Dapper; using Microsoft.Data.SqlClient; builder.Services.AddSingleton<IDbConnectionFactory>(services => { var configuration = services.GetRequiredService<IConfiguration>(); // Fail during startup if the required setting is missing. var connectionString = configuration.GetConnectionString("ApiDatabase") ?? throw new InvalidOperationException( "Connection string 'ApiDatabase' is not configured."); return new SqlConnectionFactory(connectionString); }); public interface IDbConnectionFactory { SqlConnection CreateConnection(); } public sealed class SqlConnectionFactory : IDbConnectionFactory { private readonly string _connectionString; public SqlConnectionFactory(string connectionString) { _connectionString = connectionString; } public SqlConnection CreateConnection() { // Return a new connection; do not share connection instances across requests. return new SqlConnection(_connectionString); } } public sealed class CustomerRepository { private readonly IDbConnectionFactory _connections; public CustomerRepository(IDbConnectionFactory connections) { _connections = connections; } public async Task<IEnumerable<Customer>> GetActiveAsync( CancellationToken cancellationToken) { await using var connection = _connections.CreateConnection(); const string sql = "SELECT Id, Name FROM Customers WHERE IsActive = 1"; // Dapper opens the connection as needed; disposal also handles exceptions. return await connection.QueryAsync<Customer>( new CommandDefinition(sql, cancellationToken: cancellationToken)); } }

This keeps configuration separate from connection lifetime without adding much machinery. If the API grows, the same factory can be replaced or extended without changing how repositories obtain connections.

forum

Pragmatic connection string management for small Dapper APIs

Can you answer this question?

1 Answers

Keep the string in configuration

Use a small connection factory