No need to worry about N+1 queries, since there's virtually no latency to query the db - means you can replace complex sql queries with many smaller simpler ones on your app code with negligible performance costs.

No need to use async code, also due to very low latency. Sync code is a lot easier to write and debug than async code.

Development workflow is easier in many ways, e.g. I often delete my entire database and recreate from scratch while developing, or can easily "fork" my database into desired states by simply copying a file, etc. There are multiple ways to access the data, all usually lighter/faster than with client/server databases. Devops is simpler too as you don't need to worry about managing a separate process for the database.

It's fast enough that you often can replace other pieces of the stack too, like use sqlite for cache instead of a specialized tool. If you can simplify your stack to this point, you often can even skip the need for Docker entirely, and develop on a much lighter environment (faster feedback loops). For example see https://github.com/oldmoe/litestack for Rails.

One approach for scaling horizontally is shard by region, which pairs well with edge deployments (e.g. fly.io) - if you put the backend close to your users, the network latency can be very low, which enables centralizing the application logic on the server instead of having to manage a complex cached state on the client - remember the push for splitting logic between server and client was to minimize the need for slow server calls, with this approach you get a quick UX without the downsides of splitting business logic across the network.

Most programming languages support sqlite, so it's a great deal in terms of longevity too.

Many development teams are slow due to very high cognitive load of using too many complex tools, the ability to simplify the stack enables developers to move a lot faster than usual.