Earlier quoted context omitted.
For me, using PostgreSQL is like using static typing: a bit more work, but it catches a lot of bugs. SQLite favours a very permissive approach, while PostgreSQL favours locking everything down with strict types and strong integrity checks, and it's very easy to verify lots of details of your data before it's accepted into the database. That takes work to set up properly and maintain, but it catches bugs early and red…
Strictly speaking, SQLite is dynamically typed in a sense, because the database does not enforce the values in the rows match what the columns claim about the type. But from a developer experience point of view, the main difference between dynamic and static typing is the following: Dynamic typing requires a lot of extra checks to ensure everything is what you expect it to be, and a lot of annotations to document wha…
RE static vs dynamic typing, I'd say that it's dynamic typing that has higher cognitive tax - you as a programmer are fully responsible for ensuring types agree up everywhere, whereas with static typing, all of that job is done by compiler. It's easier to change stuff in the code when you know the compiler will catch your dumb mistakes. And I say that as someone who loves writing in Common Lisp.
But the point about SQLite vs a typical RDBMS is spot-on, IMO. SQLite is a library. It operates on files. That's it. It's entirely local. You can use it without having to become a sysadmin, without having to set up a whole service on the OS on which you might not even have root privileges anyway. You don't have to make global changes to the system just for your program. Moreover, if your product is of the distributable kind (desktop apps, self-hosting web stuff) you don't need to make your users become sysadmins, manage a system-wide service, acquire root rights they might not have, etc.
SQLite is local. Its data is local. That's, IMO, its strongest benefit.