How to organize database access logic for the infrastructure and application layer when avoiding ORM tools?

Question

I'm trying create a .NET Core project and followed some guides to create a basic architecture

• Jason Taylor - Clean Architecture with ASP.NET Core 3.0
• Sample project
• Clean Architecture Template

I'm not using an ORM like Entity Framework, I want to use raw SQL for my Maria database so I'm using the official MySQL Connector. I set up the database connection and dependency injection parts.

As you can see in the sample repositories linked above (sample here) the database access happens in the Application layer and is coupled to the business logic. The persistence layer only acts like a database configuration container.

I want to know about best practises on how to organize the database access logic.

• Before executing queries I have to open a connection and close it afterwards. Should I do this in my Application command / query because then I can fire multiple queries with one connection?
• How should I structure the database logic? Should I create one file per SQL statement which handles the sql query and the mapping from database result to domain object?

It would be awesome if you could also provide a sample folder structure for visual purposes.

Answer 1

ORMs like Entity Framework do not preclude you from using Raw SQL queries.

If Entity Framework is a bit too much for your liking, consider using Dapper or any of a number of different micro-frameworks. These tools remove a lot of the hassle of making a connection to the database and managing query parameters without encountering Little Bobby Tables, allowing you to focus your time and effort on writing the SQL queries.

ORMs also serve as your Data Access Layer, abstracting CRUD operations away from the more interesting Service Layer of your application. If you still feel like writing your own Data Access Layer, you can still do so. You will need one class and four methods for each table in your database (Create, Read, Update & Delete).

The way you manage your connection can vary. Depending on how quickly MariaDB can open connections and whether or not it caches connections, you might want to just open a connection for each query. You could also do it at the Aggregate level if you're practicing DDD. Or, you could simply open a connection and leave it open. It really all depends on your application and what you want to do with it.

One file per SQL statement sounds like a bit much. Try putting your SQL statements inside your entity classes; it's a very convenient place to stash them.

Folder structures are largely a matter of taste. There's no "standard," and everyone does them differently.

Further Reading
P of EAA : Data Mapper
P of EAA : Repository
P of EAA : Service Layer

Answer 2

If you are looking for best practices, well, the linked resources are pretty much a summary of the worst practices of our industry at the moment.

Unmaintainable anemic-centered "domain" with no behavior whatsoever, technical organization and package naming, devoid of any business-related knowledge. Complete regression into full procedural design. The sample app could have been written in COBOL (no offense), and would have made absolutely no difference to the design.

That being said, ditching the ORM is generally a good idea, helps make better design decisions. But to arrive at a sensible architecture, you'll have to move back the logic into the objects they belong to. Then, there are multiple options you can do:

• Pass a transaction-type parameter around, which executes outside the objects, but objects can add database operations to it. (So open-close is not an issue of the objects themselves)
• Return a transaction-type object that can be combined with others and executed later. In fp this would be a "monad", but that is just a fancy word for "it can be combined with others".
• There should be no separate "persistence" structure, there is only the business structure, of which the persistence is just a feature of.