Support #2156

Switch between sqlite and postgre

Added by Bud T over 9 years ago. Updated about 9 years ago.

Target version:
Start date:
Due date:
% Done:


Estimated time:


Is it customary to use sqlite for development with postgre database in production? How is code set up to manage switching from one to the other based upon deployment environment?


Updated by Koen Deforche over 9 years ago

  • Status changed from New to Feedback
  • Assignee set to Koen Deforche


I don't think it's a setup we see, you probably want to detect possible PostgreSQL issues also during development (performance, syntax, ...) since Wt doesn't interpret the SQL syntax you enter and SQLite3 and PostgreSQL will not necessarily agree on more advanced constructs.

However, you can do it by simply seeding the session or thread with the correct connection depending on e.g. a configuration property or environment variable. There is no specific support provided in Wt(::Dbo) for this.




Updated by Bud T over 9 years ago

On a related issue, I'm wondering how schema changes are managed when pushing out changes in a project database that has a lot of activity. What is the protocol for backing up data, rolling out changes with Wt:dbo, and then importing data back in without problems? It would be nice to have a utility for this incorporated into Wt:dbo. It obviously couldn't accommodate every conceivable scenario, but it would be a great productivity tool if it just allowed data import and export a part of deployments. Perhaps managing some of the most common use cases and providing for reporting of any database errors that arise during usage.


Updated by Koen Deforche over 9 years ago


In practice, there is a database migration that is needed, which usually involves a SQL script that performs an upgrade (without needing to export/reimport all data). Such a migration task would be nice to have, but isn't straight-forward at all (for example you need to specify a default value for a non-null column that you are adding) and usually such a SQL script first relaxes constraints, completes the data, and then (re-)adds constraints.

The first step would need to be the ability for Wt::Dbo to read the current schema and decide what the differences are, or, put database-version information in some auxiliary table. I'm not sure any approach has particular benefits but again it's something that will be food for discussion as soon as we would implement one or the other method.




Updated by Bud T about 9 years ago

I haven't used Rails but do know that it allows rollback to previous version, including synchronization with the database. I'd imagine that there is also some facility for managing database during updates. Might be a good place to go for ideas, not sure. It is just that with the database object model, if some change is made to the model, it would be a nice efficiency if some of those changes were automated or handled by the library. For instance, a change to datatype of a field might cause an update statement to be run on the database. That sort of thing.

Also available in: Atom PDF