SQLAlchemy - SQLite for testing and Postgresql for development - How is the port?

Question

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

But the SQL syntax is not the same for both dbs. for example: SQLite has auto-increment, and Postgresql has serial

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

If you want me to use standard SQL, how do I need to generate a primary key in both databases?

Answer 1

Do not do that. Do not test in one environment and do not release or develop in another. Your request to use software with an error using this process.

Answer 2

My suggestion would be: no need. Postgresql's capabilities are far superior to SQLite's, especially in the areas of date / number support, functions and stored procedures, ALTER support, restrictions, sequences, other types such as UUIDs, etc., and even using various SQLAlchemy tricks to try for in order to smooth it, you get a little more. In particular, date and interval arithmetic are completely different animals on both platforms, and SQLite does not support exact decimal places (non-floating points), as PG does. PG is very easy to install on every major OS, and life is easier if you go this route.

Answer 3

Despite the fact that we started with sqllite for our test environment, we are seriously striving for postgres to be launched for each developer. We have scripts that create a test database that our unittests work with, and we have a development version that developers use.

We investigated running postgres 'in memory' on ramdisk, but this discussion: http://dbaspot.com/forums/postgresql/395602-memory-postgresql-database.html suggests that this is optional.

We have not yet encountered any problems, but it is still too early to develop a process, and we have not yet had to do something too unusual.

zzzeek points to some items that are likely to reveal us soon :(

It’s best to make a move ...