Which is the Better Approach? Transaction Script, Domain Model or PostgreSQL Functions with TDD?
And why?
- Readability?
- Maintainability?
- Testability?
- Debugging?
- Performances?
Transaction Script
internal sealed class AddExercisesCommandHandler(
IWorkoutRepository workoutRepository,
IUnitOfWork unitOfWork)
: ICommandHandler<AddExercisesCommand>
{
public async Task<Result> Handle(
AddExercisesCommand request,
CancellationToken cancellationToken)
{
Workout? workout = await workoutRepository.GetByIdAsync(
request.WorkoutId,
cancellationToken);
if (workout is null)
{
return Result.Failure(WorkoutErrors.NotFound(request.WorkoutId));
}
List<Error> errors = [];
foreach (ExerciseRequest exerciseDto in request.Exercises)
{
if (exerciseDto.TargetType == TargetType.Distance &&
exerciseDto.DistanceInMeters is null)
{
errors.Add(ExerciseErrors.MissingDistance);
continue;
}
if (exerciseDto.TargetType == TargetType.Time &&
exerciseDto.DurationInSeconds is null)
{
errors.Add(ExerciseErrors.MissingDuration);
continue;
}
var exercise = new Exercise(
Guid.NewGuid(),
workout.Id,
exerciseDto.ExerciseType,
exerciseDto.TargetType,
exerciseDto.DistanceInMeters,
exerciseDto.DurationInSeconds);
workouts.Exercises.Add(exercise);
if (workouts.Exercise.Count > 10)
{
return Result.Failure(
WorkoutErrors.MaxExercisesReached(workout.Id));
}
}
if (errors.Count != 0)
{
return Result.Failure(new ValidationError(errors.ToArray()));
}
await unitOfWork.SaveChangesAsync(cancellationToken);
return Result.Success();
}
}internal sealed class AddExercisesCommandHandler(
IWorkoutRepository workoutRepository,
IUnitOfWork unitOfWork)
: ICommandHandler<AddExercisesCommand>
{
public async Task<Result> Handle(
AddExercisesCommand request,
CancellationToken cancellationToken)
{
Workout? workout = await workoutRepository.GetByIdAsync(
request.WorkoutId,
cancellationToken);
if (workout is null)
{
return Result.Failure(WorkoutErrors.NotFound(request.WorkoutId));
}
List<Error> errors = [];
foreach (ExerciseRequest exerciseDto in request.Exercises)
{
if (exerciseDto.TargetType == TargetType.Distance &&
exerciseDto.DistanceInMeters is null)
{
errors.Add(ExerciseErrors.MissingDistance);
continue;
}
if (exerciseDto.TargetType == TargetType.Time &&
exerciseDto.DurationInSeconds is null)
{
errors.Add(ExerciseErrors.MissingDuration);
continue;
}
var exercise = new Exercise(
Guid.NewGuid(),
workout.Id,
exerciseDto.ExerciseType,
exerciseDto.TargetType,
exerciseDto.DistanceInMeters,
exerciseDto.DurationInSeconds);
workouts.Exercises.Add(exercise);
if (workouts.Exercise.Count > 10)
{
return Result.Failure(
WorkoutErrors.MaxExercisesReached(workout.Id));
}
}
if (errors.Count != 0)
{
return Result.Failure(new ValidationError(errors.ToArray()));
}
await unitOfWork.SaveChangesAsync(cancellationToken);
return Result.Success();
}
}Domain Model
public sealed class Workout
{
private readonly List<Exercise> _exercises = [];
// Omitting the constructor and other propreties for brevity.
public Result AddExercises(ExerciseModel[] exercises)
{
List<Error> errors = [];
foreach (var exerciseModel in exercises)
{
if (exerciseModel.TargetType == TargetType.Distance &&
exerciseModel.DistanceInMeters is null)
{
errors.Add(ExerciseErrors.MissingDistance);
continue;
}
if (exerciseModel.TargetType == TargetType.Time &&
exerciseModel.DurationInSeconds is null)
{
errors.Add(ExerciseErrors.MissingDuration);
continue;
}
var exercise = new Exercise(
Guid.NewGuid(),
workout.Id,
exerciseDto.ExerciseType,
exerciseDto.TargetType,
exerciseDto.DistanceInMeters,
exerciseDto.DurationInSeconds);
workouts.Exercises.Add(exercise);
if (workouts.Exercise.Count > 10)
{
return Result.Failure(
WorkoutErrors.MaxExercisesReached(workout.Id));
}
}
if (errors.Count != 0)
{
return Result.Failure(new ValidationError(errors.ToArray()));
}
return Result.Success();
}
}
internal sealed class AddExercisesCommandHandler(
IWorkoutRepository workoutRepository,
IUnitOfWork unitOfWork)
: ICommandHandler<AddExercisesCommand>
{
public async Task<Result> Handle(
AddExercisesCommand request,
CancellationToken cancellationToken)
{
Workout? workout = await workoutRepository.GetByIdAsync(
request.WorkoutId,
cancellationToken);
if (workout is null)
{
return Result.Failure(WorkoutErrors.NotFound(request.WorkoutId));
}
var exercises = request.Exercises.Select(e => e.ToModel()).ToArray();
var result = workout.AddExercises(exercises);
if (result.IsFailure)
{
return result;
}
await unitOfWork.SaveChangesAsync(cancellationToken);
return Result.Success();
}
}public sealed class Workout
{
private readonly List<Exercise> _exercises = [];
// Omitting the constructor and other propreties for brevity.
public Result AddExercises(ExerciseModel[] exercises)
{
List<Error> errors = [];
foreach (var exerciseModel in exercises)
{
if (exerciseModel.TargetType == TargetType.Distance &&
exerciseModel.DistanceInMeters is null)
{
errors.Add(ExerciseErrors.MissingDistance);
continue;
}
if (exerciseModel.TargetType == TargetType.Time &&
exerciseModel.DurationInSeconds is null)
{
errors.Add(ExerciseErrors.MissingDuration);
continue;
}
var exercise = new Exercise(
Guid.NewGuid(),
workout.Id,
exerciseDto.ExerciseType,
exerciseDto.TargetType,
exerciseDto.DistanceInMeters,
exerciseDto.DurationInSeconds);
workouts.Exercises.Add(exercise);
if (workouts.Exercise.Count > 10)
{
return Result.Failure(
WorkoutErrors.MaxExercisesReached(workout.Id));
}
}
if (errors.Count != 0)
{
return Result.Failure(new ValidationError(errors.ToArray()));
}
return Result.Success();
}
}
internal sealed class AddExercisesCommandHandler(
IWorkoutRepository workoutRepository,
IUnitOfWork unitOfWork)
: ICommandHandler<AddExercisesCommand>
{
public async Task<Result> Handle(
AddExercisesCommand request,
CancellationToken cancellationToken)
{
Workout? workout = await workoutRepository.GetByIdAsync(
request.WorkoutId,
cancellationToken);
if (workout is null)
{
return Result.Failure(WorkoutErrors.NotFound(request.WorkoutId));
}
var exercises = request.Exercises.Select(e => e.ToModel()).ToArray();
var result = workout.AddExercises(exercises);
if (result.IsFailure)
{
return result;
}
await unitOfWork.SaveChangesAsync(cancellationToken);
return Result.Success();
}
}TDD PostgreSQL Functions
(source: me)
-- model
create table if not exists workout(
workout_id int generated always as identity primary key,
name text not null
);
create table if not exists exercise_types(
exercise_type_id int generated always as identity primary key,
name text not null
);
create table if not exists target_types(
target_type_id int generated always as identity primary key,
name text not null
);
create table if not exists exercise(
exercise_id int generated always as identity primary key,
workout_id int not null references workout,
exercise_type_id int not null references exercise_types deferrable initially deferred,
target_type_id int not null references target_types deferrable initially deferred,
distance_in_meters int,
duration_in_seconds int
);
-- function
create or replace function add_exercises(
_request json
)
returns text[]
language plpgsql as
$$
declare
_workout_id int;
_distance_type constant int = 1;
_duration_type constant int = 2;
_max_exercises constant int = 10;
_err_not_found constant text = 'Workout not found';
_err_exercises constant text = 'Exercises request is invalid';
_err_distance constant text = 'Missing distance';
_err_duration constant text = 'Missing duration';
_err_too_many constant text = 'Too many exercises';
begin
_workout_id = (_request->>'workoutId')::int;
if _workout_id is null then
return array[_err_not_found];
end if;
if not exists(select 1 from workout where workout_id = _workout_id) then
return array[_err_not_found || ' with id: ' || _workout_id];
end if;
create temp table _exercises on commit drop as
select
"exerciseType" as exercise_type_id,
"targetType" as target_type_id,
"distanceInMeters" as distance_in_meters,
"durationInSeconds" as duration_in_seconds
from
json_to_recordset(_request->'exercises') as x(
"exerciseType" int,
"targetType" int,
"distanceInMeters" int,
"durationInSeconds" int
);
create temp table _errors on commit drop as
select
_err_distance as error
from _exercises
where
target_type_id = _distance_type and distance_in_meters is null;
insert into _errors
select
_err_duration as error
from _exercises
where
target_type_id = _duration_type and duration_in_seconds is null;
if exists(select 1 from _errors) then
return array(select error from _errors);
end if;
if (select count(*) from _exercises) > _max_exercises then
return array[_err_too_many];
end if;
insert into exercise(
workout_id,
exercise_type_id,
target_type_id,
distance_in_meters,
duration_in_seconds
)
select
_workout_id,
exercise_type_id,
target_type_id,
distance_in_meters,
duration_in_seconds
from _exercises;
-- return emtpy array, no errors
return array[]::text[];
end;
$$;
-- tests
create or replace procedure test.add_exercises_workout_not_found() language plpgsql as $$
declare _result text[] = add_exercises('{}');
begin
if _result is null or cardinality(_result) <> 1 or _result[1] <> 'Workout not found' then
raise exception 'Expected: [Workout not found]';
end if;
rollback;
end;
$$;
call test.add_exercises_workout_not_found();
create or replace procedure test.add_exercises_workout_with_id_not_found() language plpgsql as $$
declare _result text[] = add_exercises('{"workoutId": 1}');
begin
if _result is null or cardinality(_result) <> 1 or _result[1] <> 'Workout not found with id: 1' then
raise exception 'Expected: [Workout not found with id: 1]';
end if;
rollback;
end;
$$;
call test.add_exercises_workout_with_id_not_found();
create or replace procedure test.add_exercises_workout_found_empty_exercises() language plpgsql as $$
declare _result text[];
begin
insert into workout(workout_id, name) overriding system value values (1, 'Workout 1');
_result = add_exercises('{"workoutId": 1, "exercises": []}');
if cardinality(_result) = 0 is false then
raise exception 'Expected result to be null, got: %', _result;
end if;
if exists(select 1 from exercise where workout_id = 1) is true then
raise exception 'Expected no exercises to be added';
end if;
rollback;
end;
$$;
call test.add_exercises_workout_found_empty_exercises();
create or replace procedure test.add_exercises_exercises_errors() language plpgsql as $$
declare _result text[];
begin
insert into workout(workout_id, name) overriding system value values (1, 'Workout 1');
_result = add_exercises('{"workoutId": 1, "exercises": [
{"exerciseType": 1, "targetType": 1, "distanceInMeters": null, "durationInSeconds": 10},
{"exerciseType": 1, "targetType": 2, "distanceInMeters": 100, "durationInSeconds": null}
]}');
if (cardinality(_result) = 2 and _result[1] = 'Missing distance' and _result[2] = 'Missing duration') is false then
raise exception 'Expected result %, got: %', array['Missing distance','Missing duration'], _result;
end if;
if exists(select 1 from exercise where workout_id = 1) is true then
raise exception 'Expected no exercises to be added';
end if;
rollback;
end;
$$;
call test.add_exercises_exercises_errors();
create or replace procedure test.add_exercises_exercises_errors() language plpgsql as $$
declare _result text[];
begin
insert into workout(workout_id, name) overriding system value values (1, 'Workout 1');
_result = add_exercises('{"workoutId": 1, "exercises": [
{"exerciseType": 1, "targetType": 1, "distanceInMeters": 100, "durationInSeconds": 10},
{"exerciseType": 1, "targetType": 2, "distanceInMeters": 200, "durationInSeconds": 20}
]}');
if cardinality(_result) = 0 is false then
raise exception 'Expected result to be null, got: %', _result;
end if;
if (select count(*) from exercise where workout_id = 1) <> 2 then
raise exception 'Expected number of new exercises to be 2, got: %', (select count(*) from excercies where workout_id = 1);
end if;
if (select count(*) from exercise where workout_id = 1 and exercise_type_id = 1 and target_type_id= 1 and distance_in_meters = 100) <> 1 then
raise exception 'Expected coount for {"exerciseType": 1, "targetType": 1, "distanceInMeters": 100, "durationInSeconds": 10} to be 1';
end if;
if (select count(*) from exercise where workout_id = 1 and exercise_type_id = 1 and target_type_id = 2 and duration_in_seconds = 20) <> 1 then
raise exception 'Expected coount for {"exerciseType": 1, "targetType": 2, "distanceInMeters": 200, "durationInSeconds": 20} to be 1';
end if;
rollback;
end;
$$;
call test.add_exercises_exercises_errors();-- model
create table if not exists workout(
workout_id int generated always as identity primary key,
name text not null
);
create table if not exists exercise_types(
exercise_type_id int generated always as identity primary key,
name text not null
);
create table if not exists target_types(
target_type_id int generated always as identity primary key,
name text not null
);
create table if not exists exercise(
exercise_id int generated always as identity primary key,
workout_id int not null references workout,
exercise_type_id int not null references exercise_types deferrable initially deferred,
target_type_id int not null references target_types deferrable initially deferred,
distance_in_meters int,
duration_in_seconds int
);
-- function
create or replace function add_exercises(
_request json
)
returns text[]
language plpgsql as
$$
declare
_workout_id int;
_distance_type constant int = 1;
_duration_type constant int = 2;
_max_exercises constant int = 10;
_err_not_found constant text = 'Workout not found';
_err_exercises constant text = 'Exercises request is invalid';
_err_distance constant text = 'Missing distance';
_err_duration constant text = 'Missing duration';
_err_too_many constant text = 'Too many exercises';
begin
_workout_id = (_request->>'workoutId')::int;
if _workout_id is null then
return array[_err_not_found];
end if;
if not exists(select 1 from workout where workout_id = _workout_id) then
return array[_err_not_found || ' with id: ' || _workout_id];
end if;
create temp table _exercises on commit drop as
select
"exerciseType" as exercise_type_id,
"targetType" as target_type_id,
"distanceInMeters" as distance_in_meters,
"durationInSeconds" as duration_in_seconds
from
json_to_recordset(_request->'exercises') as x(
"exerciseType" int,
"targetType" int,
"distanceInMeters" int,
"durationInSeconds" int
);
create temp table _errors on commit drop as
select
_err_distance as error
from _exercises
where
target_type_id = _distance_type and distance_in_meters is null;
insert into _errors
select
_err_duration as error
from _exercises
where
target_type_id = _duration_type and duration_in_seconds is null;
if exists(select 1 from _errors) then
return array(select error from _errors);
end if;
if (select count(*) from _exercises) > _max_exercises then
return array[_err_too_many];
end if;
insert into exercise(
workout_id,
exercise_type_id,
target_type_id,
distance_in_meters,
duration_in_seconds
)
select
_workout_id,
exercise_type_id,
target_type_id,
distance_in_meters,
duration_in_seconds
from _exercises;
-- return emtpy array, no errors
return array[]::text[];
end;
$$;
-- tests
create or replace procedure test.add_exercises_workout_not_found() language plpgsql as $$
declare _result text[] = add_exercises('{}');
begin
if _result is null or cardinality(_result) <> 1 or _result[1] <> 'Workout not found' then
raise exception 'Expected: [Workout not found]';
end if;
rollback;
end;
$$;
call test.add_exercises_workout_not_found();
create or replace procedure test.add_exercises_workout_with_id_not_found() language plpgsql as $$
declare _result text[] = add_exercises('{"workoutId": 1}');
begin
if _result is null or cardinality(_result) <> 1 or _result[1] <> 'Workout not found with id: 1' then
raise exception 'Expected: [Workout not found with id: 1]';
end if;
rollback;
end;
$$;
call test.add_exercises_workout_with_id_not_found();
create or replace procedure test.add_exercises_workout_found_empty_exercises() language plpgsql as $$
declare _result text[];
begin
insert into workout(workout_id, name) overriding system value values (1, 'Workout 1');
_result = add_exercises('{"workoutId": 1, "exercises": []}');
if cardinality(_result) = 0 is false then
raise exception 'Expected result to be null, got: %', _result;
end if;
if exists(select 1 from exercise where workout_id = 1) is true then
raise exception 'Expected no exercises to be added';
end if;
rollback;
end;
$$;
call test.add_exercises_workout_found_empty_exercises();
create or replace procedure test.add_exercises_exercises_errors() language plpgsql as $$
declare _result text[];
begin
insert into workout(workout_id, name) overriding system value values (1, 'Workout 1');
_result = add_exercises('{"workoutId": 1, "exercises": [
{"exerciseType": 1, "targetType": 1, "distanceInMeters": null, "durationInSeconds": 10},
{"exerciseType": 1, "targetType": 2, "distanceInMeters": 100, "durationInSeconds": null}
]}');
if (cardinality(_result) = 2 and _result[1] = 'Missing distance' and _result[2] = 'Missing duration') is false then
raise exception 'Expected result %, got: %', array['Missing distance','Missing duration'], _result;
end if;
if exists(select 1 from exercise where workout_id = 1) is true then
raise exception 'Expected no exercises to be added';
end if;
rollback;
end;
$$;
call test.add_exercises_exercises_errors();
create or replace procedure test.add_exercises_exercises_errors() language plpgsql as $$
declare _result text[];
begin
insert into workout(workout_id, name) overriding system value values (1, 'Workout 1');
_result = add_exercises('{"workoutId": 1, "exercises": [
{"exerciseType": 1, "targetType": 1, "distanceInMeters": 100, "durationInSeconds": 10},
{"exerciseType": 1, "targetType": 2, "distanceInMeters": 200, "durationInSeconds": 20}
]}');
if cardinality(_result) = 0 is false then
raise exception 'Expected result to be null, got: %', _result;
end if;
if (select count(*) from exercise where workout_id = 1) <> 2 then
raise exception 'Expected number of new exercises to be 2, got: %', (select count(*) from excercies where workout_id = 1);
end if;
if (select count(*) from exercise where workout_id = 1 and exercise_type_id = 1 and target_type_id= 1 and distance_in_meters = 100) <> 1 then
raise exception 'Expected coount for {"exerciseType": 1, "targetType": 1, "distanceInMeters": 100, "durationInSeconds": 10} to be 1';
end if;
if (select count(*) from exercise where workout_id = 1 and exercise_type_id = 1 and target_type_id = 2 and duration_in_seconds = 20) <> 1 then
raise exception 'Expected coount for {"exerciseType": 1, "targetType": 2, "distanceInMeters": 200, "durationInSeconds": 20} to be 1';
end if;
rollback;
end;
$$;
call test.add_exercises_exercises_errors();Note: use @vbilopav/pgmigrations NPM package to run automated tests in parallel.
To receive notifications about new posts and updates, consider subscribing to my LinkdIn page:
vb-software linkedin
You will receive notifications about new posts on your LinkedIn feed.
vb-software linkedin
You will receive notifications about new posts on your LinkedIn feed.
Comments