I've successfully implemented this pattern in a medium sized project with about 500 different CRUD screens and their business rules.

Works well and is very easy to grasp for newcomers to the project.

One pitfall to avoid is mixing request processing with business logic. Certain types of request processing/filtering belong to middlewares and/or the request handlers (Controller Actions). It is very easy to the inexperienced developer to put request processing inside service code.

For example: file upload handling for the user avatar, should it belong to the user service or controller? Or perhaps a dedicated async file upload handler?

I tend to encapsulate messy file upload handling and calling it from the controller so services get a clean file_id. There are different approaches though.

I really like this pattern and have been using it both at work and my hobby projects.

For the database abstraction, you can use sqlc.dev

Next version will have beta support for sqlite but I've been building it from HEAD and have found no issues so far.

I mean it's sort of just the obvious layering between input validation and business logic.

Like a 2-layer model.

Once your domain objects get sufficiently complex more than just User, you end up with models. 3-layer i.e MVC

Lack of db switchability and mockability are too much for me. Maybe I'm too old fashioned. But the author does a good job of highlighting these drawbacks.

How often do you find yourself migrating DBs in the course of a project's lifetime?

Point taken, but IMO it is beneficial to split tests correctly into unit tests vs integration tests, so the ability to be able to mock the call or switch to a test db is highly desirable. Of all of the projects I've worked on over the last five years, there was some external impetus to change db's at least once.

In my past experience, any decently long lived bit of software (i.e. 5-10 years) goes through migrations to at least 2 different database engines (Usually as part of various other migrations like on-prem->cloud, or due to some licensing/$ reason) over its lifespan. And when you do it does tend to be quite a bit of work.

Same goes for java app containers.

I guess the question for me is:

Does implementing a different pattern that abstracts away the database engine substantially lower the amount of work it takes to migrate the db in the codebase, and is it worth 5-10 years of dealing with another layer of abstraction that is unused most of the time.

My hypothesis is that even with a more configurable pattern for the DB there is still a lot of code that needs to be changed.

sql.DB is already switchable/mockable underneath. Please stop mocking layers above it just to map to other SQL stores.

It’s basically the controller, service pattern without repository, isn’t it? What’s the difference beside the naming?

Looks like the Transaction-Script pattern with an API Layer on top.

What's next, how to assign values to variables?

This is ancient stuff, beginner stuff. Also I don't agree that the service should be internal. Useless

Agreed on the last point. There seems to be a lot of weird advice out there regarding internal/, and many seem to be treating it as some secure hidden folder or something. There are a few good times to consider internal, but this isn't one of them.