Your Data Access Layer Doesn’t Understand Databases

Here’s what nobody in the data access space wants to admit: the tools built to simplify database work have quietly offloaded the hardest parts back onto your application. Not by accident — by design. They model a pleasant fiction of what a database is, and when reality diverges, you pay for it in conditionals, workarounds, retries, and production incidents.

This is not a complaint about generated SQL. SQL quality is a separate argument, and an old one. The problem runs deeper.

Connection behavior is not a performance concern. It is a correctness concern.

Most data access layers don’t model that. Your application does — in feature flags, special cases, and debugging sessions you didn’t budget for.

Connection lifetime, concurrency, and identity are not independent concerns. They are the database.

Not all databases are the same machine

Most data access libraries are built around one mental model:

• The server is already running
• You connect
• You execute commands
• You commit or roll back
• You disconnect
• The server waits for the next client

SQL Server, PostgreSQL, Oracle, and MySQL broadly fit that model. For those databases, the standard approach works well enough that the cracks stay hidden for a while.

But not every database works that way.

In-memory embedded databases

SQLite and DuckDB in :memory: mode have a fundamental behavioral difference that most abstractions ignore entirely: the database is the connection.

Open one connection, you have a database. Open a second connection, you don’t get another connection to the same database. You get a different database. Empty. Gone.

That’s not a quirk. That’s the operational model.

A library built around “open and close connections freely per operation” will silently destroy your in-memory database between calls. No error. No warning. Just an empty database where your data used to be.

File-based embedded databases

File-based SQLite and DuckDB are not miniature SQL Servers. Their write behavior is fundamentally different:

• One writer at a time, enforced at the engine level
• Concurrent write attempts result in lock contention, busy timeouts, and failures
• The journal mode and transaction settings interact with connection behavior in ways that bite you if you get them wrong

The standard advice — “open a connection, run a command, close it, repeat” — actively works against you here. Under concurrent load you’ll see lock errors. Under sequential load you’ll see busy timeouts. The “simple” connection pattern creates exactly the contention the database can’t handle.

This is so commonly mishandled that most dedicated SQLite tooling gets it wrong too.

LocalDB

Microsoft’s LocalDB was a genuinely good idea: full SQL Server semantics, no server install, attach a file and go. Great for local development and testing.

Until idle unload enters the picture.

If no connection is held for a configurable period, LocalDB unloads the database. Not an error — an unload. Your next operation reconnects and reattaches, which adds latency and can cause failures during test runs where operations are spaced out.

The fix is a sentinel connection: one persistent connection held open specifically to prevent idle unload. Not for running queries. Just to keep the database alive.

No mainstream data access library models this. You find out about it from a Stack Overflow answer at 11pm.

What the libraries actually do

This is not speculation. Here’s what each major option in the .NET ecosystem actually models.

Entity Framework Core opens and closes connections per query and per SaveChanges() call by default. It delegates pooling entirely to the ADO.NET provider — EF has no awareness of pool pressure or saturation. When you use an in-memory SQLite database with EF, the official workaround documented by Microsoft is to manually open the connection on the DbContext and hold it open for the lifetime of the context. The developer absorbs the problem the library doesn’t solve. There is no connection policy. There is a workaround in the docs.

NHibernate ties connection lifetime to the ISession. It has a more explicit unit-of-work model than EF, which helps with some lifetime issues, but the underlying assumption is unchanged: stable, server-style backend, many connections available, connection lifetime is a performance concern. No single-writer enforcement, no keepalive, no concept of connection-bound database identity.

Dapper doesn’t touch connection lifetime at all. You open it, you pass it in, Dapper runs the command, you close it. That’s the design — deliberately minimal. It’s not a flaw in Dapper. But it means connection policy is 100% on the caller, every time, with no structure and no guardrails.

Raw ADO.NET is the same. The documented guidance is to use using blocks and dispose promptly. That’s the policy: a coding convention. Not a library feature, not an enforced invariant — a convention you either follow or don’t.

None of them model connection policy as a function of database behavior. They model lifetime. They do not model constraints. Connection policy is either delegated to the provider, left to convention, or documented as a workaround.

The connection-per-command trap

Standard advice across almost every data access library is “use one connection per command.” Open, execute, consume results, close. Your DBA will approve. Your server database will be healthier.

That advice has real merit for server databases. It reduces leaks. It shortens connection lifetime. It keeps the pool healthy under normal load.

But follow it everywhere and you create three new problems:

You can’t use in-memory databases for testing. One connection per command means a new empty database on every operation. Your tests pass against nothing.

File-based embedded databases become unreliable. The write serialization problem doesn’t go away because you’re closing connections quickly — it gets worse, because you’re now racing to reacquire write locks constantly.

LocalDB unloads between operations. No persistent connection means no sentinel. Your test suite reconnects and reattaches on every run. Or fails partway through when the unload window closes before your next operation.

So you add feature flags. Database-specific code paths. If-SQLite-do-this, if-LocalDB-do-that. Your business logic now contains your database topology.

That is the abstraction failing at its core job.

Pool saturation: when “doing it right” still breaks

Say you got through all of the above. Feature flags in place. Connection handling tuned per database type. DBA is satisfied, cloud bill is down, everything works.

Then you get a traffic spike.

Requests pile up. Each one opens a connection. The pool has a maximum size — every pool does. Requests start waiting. Wait times compound. At this point you don’t get slow queries — you get connection acquisition failures. SQL Server throws Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. PostgreSQL reports remaining connection slots are reserved. SQLite escalates busy errors into lock failures.

You need something that governs connections. Not just “open and close promptly” — actual enforcement: when the pool is full, make everything else wait. Bounded concurrency tied to pool size, not just etiquette.

None of the libraries above have this. ADO.NET pooling will queue and timeout, but it doesn’t expose the control surface you need to shape behavior under load. You find out your limits when production hits them.

What you actually need: routing by intent

Databases already differentiate reads and writes. Your access layer doesn’t.

That missing distinction has consequences. If the layer exposed it, it could enforce:

• Read-only sessions routed to a read replica, drawing from a separate pool
• Write operations serialized where the database requires it
• Each pool governed independently, sized to its workload
• For embedded databases, the write limit set to one — enforced by the layer, not scattered across application code

This isn’t new capability. It’s the modeling that’s missing. Databases have always had these constraints. The access layer just never encoded them.

What modeling it correctly looks like

Connection behavior is a property of the database. It belongs in the data access layer, encoded explicitly, not left to convention or absorbed by the application.

Here’s what that looks like in practice. pengdows.crud exposes connection policy as a first-class configuration decision through DbMode:

Best maps automatically: SQLite/DuckDB :memory: gets SingleConnection; file-based SQLite/DuckDB gets SingleWriter; LocalDB gets KeepAlive; everything else gets Standard.

Configuration looks like this:

// File-based SQLite — enforces single writer, concurrent readers 
new DatabaseContext(
     new DatabaseContextConfiguration { ConnectionString = connStr, DbMode = DbMode.SingleWriter },
     SqliteFactory.Instance);

// Or let the library decide
new DatabaseContext(connStr, "Microsoft.Data.SqlClient", DbMode.Best);

Correctness is invariant. Performance is tunable. No conditionals. No feature flags. No if-SQLite-else-SqlServer code paths. The layer absorbs the difference because it models the difference.

Pool governance is built in separately from DbMode — a turnstile-based reader/writer governor with bounded permits, drain support, and a telemetry snapshot. When you’re at pool capacity, operations wait in an orderly queue rather than failing. Under contention, the system degrades predictably instead of falling off a cliff.

The real failure

Writing your own SQL does not fix a data access layer that has no opinion about connection policy.

You can drop EF entirely. You can use Dapper. You can write raw ADO.NET. The connection problem follows you. Because the problem isn’t the SQL layer — it’s the operational model underneath it.

The databases have rules. Different rules, depending on the database. Those rules affect correctness, not just performance. A library that doesn’t model them pushes that complexity into your application, where it’s harder to see, harder to test, and easier to get wrong.

Most data access layers don’t understand databases.

They model the happy path of one class of database.

Everything else is your problem.