I haven't done this, but I'd want it. I'd like to write my code to use a Postgres database, except swap in something similar to a SQLite database instead when I'm either testing or not yet at scale. SQLite does everything in just a library linked with your code, unlike Postgres which is a separate running server. I can't simply use SQLite for this because its API is different from Postgres's.

You should read on the ancient tech called ODBC.

I almost said ODBC too but wasn't sure if I fully understood it.

Doesn't almost every language have some kind of abstraction layer that allows this?

JDBC/ODBC/DB-API/Sequelize ORM (ignoring the ORM part).

This used to be one of the major use cases for SQLite - running automated tests and being able to throw away the database file (or manage it at different points in the tests).

ODBC doesn't help you if you use any sort of pgsql-specific extension, however.

Yeah, but the abstraction layers get in the way if/when I need to optimize queries or use special features, plus it's easier to just write SQL. I'd rather target a specific DBMS and rewrite my queries if I ever change it.

> ignoring the ORM part

As it should be :)

No, all (?) these things let you pass raw SQL straight to the database.

In the case of JDBC and ODBC (and I think DB-API) thats all they do.

But raw SQL made for Postgres doesn't necessarily work for SQLite and vice versa. That's what I mean by the SQL standard being thrown out the window. You might be able to write SQL that works in both if you're careful and avoid a lot of features.