SQLAlchemy - SQLite for testing and Postgresql for development - How to port?

Question

I want to use sqlite memory database for all my testing and Postgresql for my development/production server.

But the SQL syntax is not same in both dbs. for ex: SQLite has autoincrement, and Postgresql has serial

Is it easy to port the SQL script from sqlite to postgresql... what are your solutions?

If you want me to use standard SQL, how should I go about generating primary key in both the databases?

Answer 1

My suggestion would be: don't. The capabilities of Postgresql are far beyond what SQLite can provide, particularly in the areas of date/numeric support, functions and stored procedures, ALTER support, constraints, sequences, other types like UUID, etc., and even using various SQLAlchemy tricks to try to smooth that over will only get you a slight bit further. In particular date and interval arithmetic are totally different beasts on the two platforms, and SQLite has no support for precision decimals (non floating-point) the way PG does. PG is very easy to install on every major OS and life is just easier if you go that route.