Onion Architecture: How to prevent domain knowledge from creeping into infra layer

onion_architecture_how_to_avoid_domain_knowledge_in_sql.md

One of the headaches I have been bugged by is domain knowledge in SQL queries when it comes to configure code base with Onion Architecture.

If you want to filter data, it is not realistic to use Array.filter to filter the data after retrieving it by SQL, as it consumes memory and is bad for performance.

I have no choice but to write the filter conditions in the WHERE clause of SQL, but I thought it might be a good idea to define the conditions in the WHERE clause as constants in the domain layer.

Example: Extracting customers who have purchased more than 1,000,000 yen in the past year as valued customers

Problem

To get valued customers you might write a query like:

SELECT * FROM customers JOIN order ON customers.id = orders.customer_id WHERE (SELECT SUM(total) FROM orders WHERE created_at > "2024-01-24" GROUP BY cutomer_id) > 1000000

But then you have to put the domain knowledge, "purchases over 1 million yen in 1 year", in the infrastructure layer.

Solution

1. Write the domain knowledge values as a constant in the domain layer, and define the repository type that receives it and issues SQL.

2. When the application layer calls the infrastructure layer code, pass the domain layer constant as arguments.

The code is as follows.

type CustomerId = Branded('CustomerId', number);

type Customer = {
	id: CutomerId;
}

const ValuedCustomerCriteria = {
	period: 'one_year',
	totalAmount: 1000000,
} as const;
type ValuedCustomerCriteriaType = typeof ValuedCustomerCriteria;

type CustomerRepository = {
	selectValuedCustomer: (x: ValuedCustomerCriteriaType) => Promise<Customer[]>
}

import { CustomerRepository, ValuedCustomerCriteria } from './domain.ts';

const campaignForValuedCustomers = async (repo: CustomerRepository) => {
	return repo.selectValuedCustomer(ValuedCustomerCriteria);
}

import { db } from './db';
import { CustomerRepository, ValuedCustomerCriteriaType } from './domain.ts';

export const repo: CustomerRepository  = {
	selectValuedCustomer: async (criteria: ValuedCustomerCriteriaType) => {
		const date = calcDate(criteria.period); // get the date which is one_year ago
		return db.sql("SELECT * FROM customers JOIN orders ON cutomers.id = orders.customer_id WHERE (SELECT SUM(total) FROM orders WHERE created_at > '?' GROUP BY cutomer_id) > ?", date, criteria.totalAmount).execute();
	}
}

The “sum of total purchase amount” part is left in the SQL, but the selectValuedCustomer function does not work without the ValuedCustomerCriteria, so you can see that the domain layer has domain knowledge about its valued customers.

This is just my two cents so if you have a better solution to this problem, please let me know.