Writing a Cross-Database .NET Application

Most .NET applications target a single database, and the tooling is built on that assumption. A provider is chosen near the start of a project, migrations are generated against it, queries are written in LINQ, and nothing that happens later requires the choice to be reconsidered. Where the assumption holds, that is the right way to work and there is no reason to complicate it.

The assumption fails for software that has to run against whatever database the customer already operates. Products sold into enterprises encounter one client standardized on SQL Server, another on Oracle, and a third with a warehouse it has no intention of replacing. Internal systems begin on PostgreSQL and inherit a DB2 estate through an acquisition. Desktop and edge builds need an embedded database locally and a server engine in production. In each case the number of target engines is a commercial fact rather than an architectural preference, and the data layer has to accommodate it.

The usual answer is that an object-relational mapper abstracts the database, which holds for a narrow definition of what a database does. This article sets out where that abstraction ends and what has to be built once it does.

What does supporting several databases actually mean?

Two arrangements are commonly described in the same terms, and they behave very differently once a product is in maintenance. Telling them apart at the outset is straightforward, whereas converting from the second to the first after a few years of accumulated code is not.

The first is one compiled data layer, containing no vendor branches, running unchanged against several engines, with the target chosen by configuration when the application is deployed. Adding an engine to the supported list means testing against it rather than writing against it.

The second is a repository per vendor behind a common interface, which is the arrangement most teams arrive at by increments rather than by decision. Every schema change is then made once per vendor, and so is every defect fix. Building it is not the expensive part. Everything after that is.

The second arrangement also shapes the roadmap. The engine the team knows least well accumulates the most defects and receives features last, so support degrades where it was hardest to establish in the first place. That follows from the structure rather than from anyone's decision, and it is easier to avoid at the outset than to unwind later.

Can Entity Framework Core do this already?

Entity Framework Core's provider model allows a single DbContext to target more than one engine, and for a data layer built from entity reads and writes it does that job well. Basic CRUD, LINQ queries, relationships and change tracking all behave consistently against SQL Server, PostgreSQL or SQLite, so an application whose data access consists of those operations can add a second provider and largely expect it to work. The framework is competent at what it was designed for, and it costs nothing to adopt.

The limitation is where the abstraction is drawn. It sits at the level of entities rather than the database itself. Capabilities beyond entity access therefore fall outside it, and five of those account for most of the vendor-specific code that results.

1. Migrations are generated per provider, because the SQL they emit is provider-specific, and Microsoft's own guidance for multi-provider applications is to maintain a separate migrations assembly for each one. Supporting two engines therefore means maintaining two sets of migrations that describe a single model.

2. Column type mapping is automatic only while the defaults are accepted. Any column given an explicit type has to be given a different one per provider, since the type names and their size and precision rules differ, and those declarations accumulate conditions as engines are added.

3. Spatial support arrives as a per-provider plugin, so it exists for some engines and not for others. Where a plugin exists the API is consistent. Where none exists there is nothing underneath it, so the application either drops the feature on that engine or writes raw SQL for it.

4. Full-text search has no abstraction at all. Each provider exposes a different method, with a different query syntax and a different relevance scale, and none of them are interchangeable, so a search feature built against one provider is rewritten rather than adapted when a second engine appears.

5. Bulk loading has no built-in answer either. Adding a range of entities and saving them issues INSERT statements rather than using the engine's own loading path, which is adequate for hundreds of rows and impractical for hundreds of thousands. The usual remedy is a third-party extension, and those support their own subset of providers, which reintroduces the original problem one level further down.

None of that makes Entity Framework Core a poor choice. It makes it a choice whose remaining work scales with the number of engines supported, because each of those five gaps is filled per provider rather than once.

What breaks first when the second database arrives?

Schema management fails before any query does, which is not where most teams expect the first difficulty to appear. Queries are the visible part of a data layer and therefore the part that gets planned for, while the schema is treated as settled because a single-provider project makes it settled.

Two providers produce two migration histories describing one model, and those histories drift. Somebody adds a column and regenerates for the provider running on their machine. The other history is now behind, and nothing detects the discrepancy because each is internally consistent. The failure surfaces in a customer environment, at the point where the application queries a column that exists in one deployment and not another.

The alternative is a single declarative description of the schema, with the data layer working out how the database differs from it. That description is a file:

<table name="customer">
    <columns>
        <column type="long" autogenerate="true" notnull="true">customer_id</column>
        <column type="varchar" size="255" notnull="true">email</column>
        <column type="varchar" size="100" notnull="true">first_name</column>
        <column type="varchar" size="100" notnull="true">last_name</column>
        <column type="boolean" notnull="true">subscription_active</column>
        <column type="datetime">creation_stamp</column>
    </columns>
</table>

The same file describes the table on every supported engine. It does not state the syntax an engine uses to declare an auto-incrementing key, the type it stores a boolean in, or how it expresses a string length, since those are exactly the parts that differ between engines. There is no per-vendor artifact to keep synchronized, since there is only one artifact. ConfigType accepts JSON and YAML equally, for teams whose tooling prefers them.

How is a schema change applied?

Changes are expressed as flags in the file rather than as an ordered list of operations to replay. One file can therefore serve a new database and an existing one alike.

Columns and tables new in a version are marked with schemaupdate, and a column whose type or size has moved is marked with columnupdate:

<column type="varchar" size="150" schemaupdate="true">utm_campaign</column>

Applying it takes two calls, one to read the file and one to reconcile it against the database:

using var manager = new Manager(connection);

Schema schema = manager.ReadSchemaFromFile("schema-v12.xml", ConfigType.Xml);

manager.UpdateSchema(schema);

Anything without a flag is treated as already present, so each file remains a single reviewable increment rather than an accumulating history. UpdateSchema compares what the file declares against what the database contains and issues only the differences, in the dialect of whichever engine is connected. Databases being created for the first time take BuildSchema and the same file.

The same operations are available in code, for changes better expressed that way:

Schema schema = manager.LoadSchema("app");

Table customers = schema["customer"];
customers.AddColumn("phone", DataType.VarChar, size: 20);
customers["email"].ModifySize(320);

manager.UpdateSchema(schema);

AddColumn and the Modify methods set the same flags the file would have carried, so both routes converge on one mechanism. Deploying the identical build against a different engine changes nothing about either.

How does the application choose an engine?

Selection happens once, when the connection object is constructed. Only the vendor-specific type differs between the cases, since the settings themselves are the same on every engine.

IDatasourceConnection connection = vendor switch
{
    "postgres"  => new PostgreSqlDatasourceConnection(),
    "mysql"     => new MySqlDatasourceConnection(),
    "sqlserver" => new SqlServerDatasourceConnection(),
    "oracle"    => new OracleDatasourceConnection(),
    "teradata"  => new TeradataDatasourceConnection(),
    "db2"       => new DB2DatasourceConnection(),
    "sqlite"    => new SqliteDatasourceConnection(),
    _           => throw new ArgumentOutOfRangeException(nameof(vendor))
};

connection.Hostname = host;
connection.Database = database;
connection.Username = user;
connection.Password = password;

File-based engines use only Database and ignore the server settings, which is why all four can be assigned unconditionally rather than per vendor.

Everything after that statement is written once: queries, joins, aggregates, schema updates, spatial predicates, full-text searches and bulk loads. In a dependency-injected application the switch belongs in startup configuration, with everything downstream receiving an IDatasourceConnection. The engine then sits in configuration alongside the connection timeout.

The rest of this article covers the features where that arrangement is normally lost: queries, spatial data, full-text search, bulk loading and testing.

How are queries expressed without vendor SQL?

Queries are composed as objects rather than assembled as strings, which is what allows one query to be rendered into different dialects. The structure is stated in C# and the SQL is generated from it when the query runs, so no part of the application holds a fragment of any engine's syntax.

Query query = new Query()
    .Select([
        customerTable["customer_id"],
        customerTable["email"],
        new Expression("order_total", new Aggregate(AggregateType.Sum, orderTable["total"]))
    ])
    .From(new Join(customerTable, orderTable, "customer_id"))
    .Where([
        new Criterion<bool>(customerTable["subscription_active"], true),
        new BooleanItem(BooleanType.And),
        new Criterion<DateTime>(orderTable["placed"], ConditionalType.GreaterThanOrEqualTo, since)
    ])
    .GroupBy([ customerTable["customer_id"], customerTable["email"] ])
    .OrderBy(customerTable["email"], OrderClauseType.Ascending);

Nothing in that expresses a dialect. Criterion states a comparison, Join states a relationship, and the aggregate states an operation, leaving the rendering of each to whichever engine receives it. Parameterization is handled as part of that rendering, so the question of how a particular database expects parameters to be named does not arise.

The practical consequence is that filtering happens in the database rather than in application code. Composing conditions as objects makes it as convenient to add a criterion to a query as it is to filter a list afterwards, which removes the most common reason for pulling more rows than necessary across the network.

What happens to the features that usually force vendor code?

Three capabilities account for most of the vendor-specific code in data layers that contain any, and all three behave differently from ordinary queries. Each is implemented by a different mechanism in each engine, and in every case the differences reach into the results rather than staying in the syntax.

Spatial data

Type systems, function names, indexing models and unit conventions all differ between engines, and those differences produce wrong answers rather than errors. Distances asked for without a stated unit come back in whatever each engine treats as native, which on more than one of them is degrees:

var query = new Query()
    .SelectAll(storeTable)
    .Where(new Criterion<double>(
        new SpatialDistance(storeTable["location"], here, UnitOfMeasure.Mile),
        ConditionalType.LessThanOrEqualTo,
        25));

Naming the unit inside the query means twenty-five miles is twenty-five miles wherever it runs. Spatial columns, indexing, the OGC predicates and shape arithmetic are covered in Spatial Data Across Relational Databases.

Full-text search

No free cross-vendor equivalent exists for this one. Every engine ranks differently, and the scores it produces are not comparable with any other engine's:

var index = articles.CreateFullTextIndex("idx_fts_articles", ["title", "content"]);
manager.CreateFullTextIndex(index);

using ResultReader reader = manager.FullTextSearch(articles, columns, "quantum computing");

foreach (Result row in reader)
{
    double score = row.GetFieldDouble("score");
}

The score column is normalized to a range of 0 to 100 regardless of engine, which is what makes a relevance threshold portable. Writing score >= 60 in a search feature means the same thing everywhere, whereas a threshold expressed against a native ranking function means something different on each engine and cannot be moved.

Bulk data movement

This defeats most abstractions, because each engine reaches its throughput by a different route:

manager.ImportData(usersTable, "users.csv", config);

manager.ExportData(query, "user_spending.csv", new ExportConfig { WriteHeaders = true });

ExportData accepts a query rather than only a table, so an extract can be a join with aggregates rather than a raw dump. Whole schemas and whole databases export the same way through ExportSchema and ExportDatabase, structure and data together, which turns reproducing a customer's environment locally into a command rather than a project.

Underneath each of those, the engine receives the loading strategy that performs well on it, and the batch size is adjustable through ImportBatchSize on the connection where a particular file needs it.

How do you test against several engines?

Whether supporting more than one database stays manageable depends largely on what a full test run requires. If every run needs infrastructure, the suite is run less often and the engines drift apart.

The approach that scales is to make the common case free. Because SQLite requires no server and, with the spatial extension already inside the package, no additional installation either, a test suite can construct a database as a file, build the schema into it from the same definition the application ships, exercise the data layer, and delete it afterwards:

var connection = new SqliteDatasourceConnection { Database = "test-run.db" };

using var manager = new Manager(connection);

Schema schema = manager.ReadSchemaFromFile("schema-v12.xml", ConfigType.Xml);

manager.BuildSchema(schema);

That runs on a developer machine and on a build agent identically, with no container to start and no credentials to manage, which means the suite can run on every commit rather than nightly.

Integration runs against the server engines are then a separate, slower stage, executing the same tests against a different connection object. The tests themselves do not change, because the data layer they exercise does not change, so the marginal cost of adding an engine to that stage is a connection string and pipeline time rather than a body of new test code.

Two kinds of test are involved, and separating them keeps the fast stage fast. Tests verifying your own logic are numerous, run constantly, and any engine satisfies them. The second kind are few. They target the specific behaviours engines disagree about, and they belong in the release stage rather than the commit stage.

What does deployment look like?

One build artifact serves every customer regardless of which engine they run, and that changes what a release involves. Nothing about the package varies by database, so there is no per-vendor variant to produce, sign, test or ship.

Two values decide behaviour, and both live in configuration rather than in code. The first identifies the engine and supplies its connection details, feeding the switch shown earlier. The second states which schema version the build expects, which lets the application reconcile the database on startup rather than requiring a separate migration step to be run by hand at the right moment:

{
  "Datasource": {
    "Vendor": "sqlserver",
    "Hostname": "db01",
    "Database": "app",
    "SchemaVersion": 12
  }
}

Reading that version and applying the matching file is the whole of the upgrade procedure. Databases several versions behind are brought forward by applying each intervening file in order, and one already current receives nothing, since a file whose declarations all match what is present produces no changes.

The operational consequence is that the same installer, container image or deployment package goes to every customer. Supporting an additional engine does not produce an additional build, an additional artifact to sign, or an additional pipeline to maintain, and the matrix that usually grows with the number of supported databases stays a single column.

For teams shipping on-premises software this often matters more than anything about query syntax. The engineering cost of a per-vendor data layer is visible and can be budgeted for. The release engineering that accompanies a per-vendor build is harder to see in advance and tends to consume more time than the data layer itself.

How do you write for the differences that remain?

Abstraction removes syntax differences between engines. It cannot remove behavioural ones, since a function meaning something slightly different on two databases has no single correct translation. Those cases are few, but knowing how to write for them is the difference between code that ports and code that appears to port.

The most instructive example is what GREATEST and LEAST do when one of their arguments is null. PostgreSQL and SQL Server ignore the null and compare the remaining values. Oracle, MySQL, DB2, SQLite and Teradata return null for the whole expression. Both behaviours are documented, and neither is wrong. Emulating one of them everywhere would mean the function no longer did what the database it ran on says it does.

Velocity passes those through rather than normalizing them, on the principle that a documented divergence is easier to work with than a hidden one. The portable form states the intent explicitly:

var counts = raceColumns
    .Select(c => (IElement)new Coalesce([ c, new Literal<int>(0) ]))
    .ToList();

var greatest = new Greatest(counts);

Wrapping each argument in Coalesce gives a defined value where one was absent, which produces identical results on every engine because there is no longer a null for them to disagree about.

The general technique generalizes past this one function. Divergences between engines almost always concern behaviour at an edge. Stating what should happen at that edge removes the disagreement. Deciding that a missing count is zero, that an absent date sorts last, or that an empty string and a null mean the same thing are all decisions your application is better placed to make than any of the engines, and making them explicitly costs one function call.

Two habits follow from this. Design against the constraints of the most restrictive engine you intend to support rather than the most capable, since a data model that works on all of them is easier to arrive at than one retrofitted later. Keep a small suite of tests aimed at those edges and run it against every target engine. Those assertions catch a divergence before a customer does.

How do results become objects?

Rows return as typed objects through the same mechanism regardless of engine, so the classes describing your data are unaffected by which database produced the rows.

IList<Customer> customers = manager.Retrieve<Customer>(query, BindingType.SnakeCase);

One argument states the naming convention connecting properties to columns. Schemas that follow a convention need nothing further: no mapping file, no base class, no attributes. Where a legacy schema follows none, attributes state the mapping per property, which is a per-table cost rather than a per-engine one. Naming conventions, nullability, streaming large results and binding joins are covered in Data Binding for Relational Tables.

Putting it together

The complete arrangement, from an empty database to a query returning objects, is short enough to read in one piece.

Selecting the engine, which happens once:

IDatasourceConnection connection = vendor switch
{
    "postgres"  => new PostgreSqlDatasourceConnection(),
    "sqlserver" => new SqlServerDatasourceConnection(),
    _           => throw new ArgumentOutOfRangeException(nameof(vendor))
};

connection.Hostname = host;
connection.Database = database;
connection.Username = user;
connection.Password = password;

Bringing the database up to the version the build expects:

using var manager = new Manager(connection);

Schema schema = manager.ReadSchemaFromFile("schema-v12.xml", ConfigType.Xml);

manager.UpdateSchema(schema);

Querying it:

Query query = new Query()
    .Select([
        customerTable["customer_id"],
        customerTable["email"],
        customerTable["join_date"]
    ])
    .From(customerTable)
    .Where(new Criterion<bool>(customerTable["subscription_active"], true))
    .OrderBy(customerTable["join_date"], OrderClauseType.Descending);

IList<Customer> active = manager.Retrieve<Customer>(query, BindingType.SnakeCase);

And extracting from it:

manager.ExportData(query, "active_customers.csv", new ExportConfig { WriteHeaders = true });

Every call above is identical on every supported engine, and the only line aware of which one is in use is the connection object at the top. Adding an engine to the supported list means one more case in a switch statement, and a test run against it. No repository is implemented.

Both arrangements described at the start of this article can be made to work, and building either takes roughly the same initial effort. They differ in what happens next. The cost of the first stays flat as engines are added. The cost of the second multiplies by the number of engines, and does so again for every subsequent change.

Where to start

The free Community Edition is published on nuget.org and covers MySQL and PostgreSQL, which is enough to evaluate the query and schema API without a licence:

dotnet add package YndigoBlue.Velocity.Community

The full edition adds the remaining engines along with spatial functionality, full-text search and the bulk paths.

As a first exercise, take whichever query in your existing data layer is most obviously tied to one vendor and express it through the query builder. Then run it against a second engine. Whatever a cross-vendor abstraction is going to cost you, that exercise reveals it in an afternoon rather than at the point where the second customer has already signed.