Specification Pattern using SQL without an ORM, with the repository pattern

Question

I have been looking into the specification pattern that is briefly described in martin fowler's patterns of enterprise architecture under the repository pattern section, as well as several examples on the web. However, almost all of the examples/descriptions are created by utilizing an ORM and methods such as IsSatisfiedBy which are executed by the specification objects(and probably converted into SQL by the ORM).

I can see how you might adapt it to work with SQL, but due to a general lack of SQL examples, I was wondering if people are using this pattern with a SQL data access layer and the repository pattern, and their experience/approach with it if they are, or any alternatives that may be better suited to the task if there are any.

Answer 1

With Clean Architecture, you have these restrictions:

• Specifications are written in the application or domain layer
• Database entities and SQL are only allowed in the infrastructure layer

Therefore, you need to write your specifications

• without using SQL, and
• based on the domain entities.

If you are not using an ORM, the missing piece to making Specification Pattern work is the mapping from specifications and domain entities to SQL and database tables.

Entity Framework does this out of the box, based on either conventions, attributes or the Fluent API.

You can either

• look into other libraries that provide this feature set, or
• implement some kind of abstraction yourself, or
• break Clean Architecture and use SQL in the specifications.

Unfortunately, I haven't explored these options further yet.