Common Sense Software Design

This diagram is a representation of a design approach I refer to as the Common Sense Software Design for RDBMS-backed type of software.

1) Service Class

Service class does stuff.

It is the complete feature implementation that fulfills functional requirements:

• Use parameters or requests to:
  • Create database queries, either with SQL or ORM, and execute and retrieve the data.
  • Call stored procedures or database functions, and retrieve the data.
• Process the database results and form results or responses.

Pseudo example:

class MyFeature(Database db /*, other dependencies*/)
{
    int ServiceMethod1(int p1, int p2)
    {
        // do stuff with p1 and p2
        // create query with sql or orm
        // call database
        // retrive results
        // return results
    }

    Method2Response ServiceMethod2(Method2Request request)
    {
        // result values are complicated (more values) so we return a response structure (class or record or named tuple) 
        // parameters are complicated (more then three values usually) so we accept a request structure (class or record or named tuple) 
        // everything else is the same as ServiceMethod1 pseudo example
    }
}

class MyFeature(Database db /*, other dependencies*/)
{
    int ServiceMethod1(int p1, int p2)
    {
        // do stuff with p1 and p2
        // create query with sql or orm
        // call database
        // retrive results
        // return results
    }

    Method2Response ServiceMethod2(Method2Request request)
    {
        // result values are complicated (more values) so we return a response structure (class or record or named tuple) 
        // parameters are complicated (more then three values usually) so we accept a request structure (class or record or named tuple) 
        // everything else is the same as ServiceMethod1 pseudo example
    }
}

There are no:

• Repository patterns.
• Unit Of Work patterns.
• No generic patterns.
• No domain model, no domain model, no value object, no aggregate roots, none of that.
• No needless mapping or useless abstractions.

Most of the logic is executed in the RDBMS, closer to actual data, either with raw SQL or crafted with ORM, or ideally via Stored Procedures/Functions.

Logic may be executed on the RDBMS client (service classes) as needed and if needed, while service public interfaces remain the same and unchanged.

2) REST API / gRPC / UI

This layer is a concrete implementation that calls the service MyFeature methods.

Sometimes this is just a REST API that returns a value from a service or entire service response (as is, no mapping).

Sometimes this layer facilitates server-side rendering and uses a service response (as is, no mapping) to render some UI template on the server.

Sometimes we want to be able to use some different type of service like gRPC.

It's common sense to keep those layers separated.

3) RDBMS

RDBMS holds the physical implementation of the logical data model in relational form. For example:

begin;

create table addresses (
    address_id int generated always as identity primary key,
    street text not null,
    city text not null
);
create table customers (
    customer_id int generated always as identity primary key,
    name text not null,
    address_id int not null references addresses
);
create table customer_addresses (
    customer_id int not null references customers,
    address_id int not null references addresses,
    primary key (customer_id, address_id)
);
alter table customers add constraint fk_customer_addresses
foreign key (customer_id, address_id) 
references customer_addresses;

end;

begin;

create table addresses (
    address_id int generated always as identity primary key,
    street text not null,
    city text not null
);
create table customers (
    customer_id int generated always as identity primary key,
    name text not null,
    address_id int not null references addresses
);
create table customer_addresses (
    customer_id int not null references customers,
    address_id int not null references addresses,
    primary key (customer_id, address_id)
);
alter table customers add constraint fk_customer_addresses
foreign key (customer_id, address_id) 
references customer_addresses;

end;

This is a general-purpose data model that uses relational notation to implement a conceptual model of reality in a database.

Use appropriate tools and techniques to maintain this data model such as:

• Handcrafted migrations, written with SQL.
• ORM migration tools.
• External tools such as Flyway, and other (schema sync tools, etc).

4) Unit Tests

A set of unit tests that test and assert features implemented in the service classes.

These tests will use a test database that is created on every test session, for example:

• Using Docker containers.
• Using local database server instance.

As needed, tests will either clone the test database as a template or create an empty database and run all migrations from zero

Also, to ensure test isolation as needed every test may or may not be in a separate transaction that is rolled back after the test.

There are many different approaches to this type of testing and it may depend on the system requirements. One of the possible approaches when using the PostgreSQL database can be the XUnit.Npgsql unit test utility library but there are many others, the common one is to use Docker containers.

The point is that the RDBMS should not be mocked or faked ever.

If you are not testing stuff in the RDBMS, what are you testing then?

Other than that, mocks and fakes for other external systems such as file systems or external services are a normal part of the testing process, and they can be injected through the constructor injection.

5) Integration Tests

These can be anything from simple REST API tests to complicated automated UI testing systems such as Puppeteer, Playwright, Selenium or others.