Firebird (https://www.firebirdsql.org) can essentially work that way. It comes with two versions of the client library. One of them runs the database engine in-process (embedded mode), similar to SQLite. Later you can switch over to a separate server process, like with PostgreSQL. Though both those two set the bar pretty high, Firebird is a solid RDBMS that’s been around for a long time, with a well-balanced feature set (including strict data typing).