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?
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.
Don't do it. Don't test in one environment and release and develop in another. Your asking for buggy software using this process.
Although we started with sqllite for our testing environment we are seriously looking to having postgres running for each developer. We have scripts that build the test database that our unittests run against, and we have a 'development' version that the devs use.
We investigated running postgres 'in memory' on ramdisk, but this discussion: http://dbaspot.com/forums/postgresql/395602-memory-postgresql-database.html suggests that it isn't necessary.
We haven't run into any problems yet, but it is still early in the development process and we haven't had to do anything too fancy yet.
zzzeek points out some items that will probably trip us up soon :(
Best make the move now....