JPHConseil.DataAdapters 10.0.0.2

JPHConseil.DataAdapters

This library is built over Dapper to provide data adapter style CRUD operations. It is built as an ORM-like library to facilitate data access and guarantee data isolation between tenants.

Several SQL dialects are supported:

• MySQL: tested.
• PostgreSQL: tested.
• SQLite: tested.
• SQL Server: not yet tested.

Why another library?

We have been building SaaS applications for a long time. Data isolation between tenants is a key requirement for such applications. There are several approaches to achieve this goal. One of the most common ones is to use a partitioned database, where each record is linked to a tenant. When using such an approach, it is important to guarantee that all data access is done in the context of a given tenant, to avoid data leaks between tenants.

Even if there are already several libraries that provide data access over Dapper, none of them provide partitioned access to data in a manner that guarantees data isolation between tenants by design.

This library is designed to provide such a feature, while keeping the flexibility and performance of Dapper.

Another important feature of this library is to support a FIQL query language to filter records when retrieving data. FIQL is a simple and powerful query language that can be used to filter records based on their properties. As SaaS applications often rely on RESTful APIs, FIQL is a great fit for such applications.

Main components

SqlConnectionFactory

The SqlConnectionFactory class will be used to create instances of DbConnection based on the provided configuration. It relies on a configuration object and a section name. This section will have to contain the necessary information to connect to the database:

• ProviderName: the database provider as defined in the Microsoft documentation.
• ConnectionString: the connection string to connect to the database.
• Pwd: the password that will be used to replace the placeholder {pwd} in the connection string.

ProviderName and ConnectionString are mandatory. Pwd is optional.

IDataAdapterConnection and SqlDataAdapterConnection

With the view of supporting these different SQL dialects, the library will rely on the IDataAdapterConnection interface that will expose the following methods:

• OpenAsync: to open a connection to the database.
• CloseAsync: to close the connection to the database.
• BeginAsync: to begin a transaction.
• CommitAsync: to commit a transaction.
• RollbackAsync: to rollback a transaction.
• InsertReturningLastInsertedIdAsync: to insert a record and return the last inserted id.

A IDataAdapterConnection is needed to ensure that all operations are executed within the same connection and transaction context. More precisely, some databases (like MySQL) require that the LAST_INSERT_ID() method is called in the same transaction as the INSERT statement.

DataAdapters

DataAdapters are used to provide SQL dialect specific implementations for CRUD operations. Each DataAdapter will inherit from DataAdapter<T>.

Different types of DataAdapters are provided:

• DataAdapter<T>: the base class for all DataAdapters. It provides only the GetAllAsync method to retrieve all records from the given table, or to retrieve all records corresponding to the given FIQL query.
• DataAdapter<T, TId>: inherits from DataAdapter<T> and adds methods to retrieve a record by its identifier, to insert, update and delete records. The constructor allows specifying the name of the id and its behaviour (auto-incremented or not).
• DataAdapter<T, TId1, ..., TId4>: all the same as above, but for entities with composite keys of 2, 3 or 4 identifiers. Please note that no auto-increment is supported for composite keys.
• PartitionDataAdapter<T, TPId>: provides the same GetAllAsync methods as DataAdapter<T>, scoped to the given partition. The partition identifier is expected to be present in the entity as a property whose name is defined by partitionIdName. Note that IPartitionDataAdapter does not inherit from IDataAdapter — they are independent interface hierarchies sharing a common base.
• PartitionDataAdapter<T, TPId, TId>: provides the same CRUD methods as DataAdapter<T, TId>, but scoped to the given partition.
• PartitionDataAdapter<T, TPId, TId1, TId2>: provides the same composite-key CRUD methods as DataAdapter<T, TId1, TId2>, but scoped to the given partition.

DataAdapter, PartitionDataAdapter and IDataAdapterConnection

DataAdapter and PartitionDataAdapter instances rely on an IDataAdapterConnection to execute all operations within the same connection and transaction context. With the perspective of being able to change the underlying connection depending on the needs, the library provides two separate methods to retrieve the correct adapter instance:

• GetDataAdapter<T>() where T implements IDataAdapter: returns a non-partitioned adapter.
• GetDataAdapter<T, TPId>(TPId pId) where T implements IPartitionDataAdapter<TPId>: returns a partitioned adapter.

By default, each method searches the assembly that defines T for the first concrete class implementing it, then instantiates it. If no such class is found, an exception is thrown.

If this strategy doesn't work for you, you can implement IDataAdapterConnection and override the GetDataAdapter() methods to provide your own logic.

Tests

Unit testing for this library supports MySQL, PostgreSQL and SQLite. Tests have been written recently, and may not cover all edge cases.

MySQL

To run the tests, you need a local server (localhost) with a database named unittests, and a user unittests / unittests with all privileges on the unittests database.

The test suite will then create all the entities required for the tests to run.

The script to create the database is the following:

CREATE DATABASE IF NOT EXISTS `unittests` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE USER 'unittests'@'localhost' IDENTIFIED BY 'unittests';
GRANT ALL ON unittests.* TO 'unittests'@'localhost';

PostgreSQL

It works the same way as MySQL. You need a local server (localhost) on which you'll use the following script to create the database:

CREATE DATABASE IF NOT EXISTS unittests;
CREATE USER unittests WITH PASSWORD 'unittests';
GRANT ALL PRIVILEGES ON DATABASE unittests to unittests;
GRANT ALL ON SCHEMA unittests TO unittests;

SQLite

SQLite tests will create a local database file in memory.

The test suite will then create all the entities required for the tests to run.

Using with AI agents

The file docs/Using-DataAdapters.md provides a comprehensive reference of the library's API, patterns, and usage examples specifically written to help AI coding assistants (such as GitHub Copilot, Claude, or similar tools) generate correct code when working with this library. If you are using an AI agent in your project, point it to that file so it can understand the adapter hierarchy, partition protection rules, FIQL syntax, and transaction conventions without having to infer them from source code.

Authors

• Fabien Philippe - Initial work - In