How to bi-directionally update a SQLite and PostgreSQL database?

Databases
1

Hello all,

I need to bi-directionally update a SQLite and PostgreSQL database from one application to another. In addition, the database tables may, or may not be the same. Even the table names may be different - although, if that was mandatory, then they could be created strictly for this purpose.

I am able to transfer data, quite easily from apps that use only SQLite, but could really use some ideas, how to do this from SQLite to PostgreSQL - and back.

Any ideas?
Thanks all,
Tim

2

do columns match ?
if not you basically have to write code that maps one to the other with what ever table & column names
But don’t want to hard code it as if things change then you have to redo that code
but you need a “map” - this table and column goes here, the one here etc

IF i were you i’d try to make them match at least table & column names
That reduces the complexity a fair bit

3

Hello Norman,

In some cases columns may match. I suppose I could change things and force the match. Hard coding is really all bad! It will either bite me now, or bigger and harder later… best not do that.

Thanks for your input Norman!

Any other Ideas anyone?
Tim

4

if you control both ends then making the tables & columns match will be easiest and easiest to update since you can use much the same script for updating both.
The more they diverge the worse problems get.
Replicating data is mostly mechanical but depending on how you have it keyed etc may also involve business rules.

And things like foreign key can trip you up - you have to have master records inserted before detail one etc.

5

Hi Norman,
I do have both ends, and it is still mid game - no release yet…
So from your POV, best to make any column values that will be updated, should be the same name.
The key of course has to come over too, but in my case, there are no foreign keys - simpler.

To rough it, on receipt, match the column names; look up the record using the supplied key, then update the record.

Does that sound roughly correct? Too much mud in the water, and not enough time to clean it out before needing to use it.

Thanks again Norman!
Tim

6

[quote=77724:@Tim Seyfarth]Hi Norman,
I do have both ends, and it is still mid game - no release yet…
So from your POV, best to make any column values that will be updated, should be the same name.
The key of course has to come over too, but in my case, there are no foreign keys - simpler.

To rough it, on receipt, match the column names; look up the record using the supplied key, then update the record.

Does that sound roughly correct? Too much mud in the water, and not enough time to clean it out before needing to use it.

Thanks again Norman!
Tim[/quote]

I’d make the schemas the same - why cause yourself pain ?

But yes make table & column names match and then moving data back & forth is less complex

7

The problem that you are describing is generally referred to as an interface (not in OOP terms, but in business systems implementation terms). I will start comment at a very high level.

Implementing an interface requires that:

  • you have very clear requirements on the business processes that your interface will serve
  • you have a very detailed data mapping documentation, complete with data conversion rules, including if required intermediate cross-reference tables

Then, you need to program data access layers on either side to make the bi-directional communication happen.

Now, this is just the Readers’ Digest version of the things you have to do. The real message is that IMHO, there is no magic solution, just a process to follow. And everyting that was said above remains true at the lower level.

8

if you need to guarantee the two DB system are kept in sync, you’d have to write a transaction manager, see two-phase commit . PostgreSQL allows you to prepare transactions since version 8.1 but with SQLite, your mileage may vary…

9

Thanks everyone! I appreciate the input.
Tim