Moving from Sqlite to Postgres

Databases
1

Can anyone give me some tips, warnings, advises, etc… on this subject…

I have made a medium sized app based on sqlite but looks like I need now to support two or three users (connections) rather than one… Requirements have changed…

What things should I look for, consider, be aware of when I’ll make this move…?

Thanks

2

Postgres will strictly check types and limits. Whereas you could stuff War & Peace into a SQLite VarChar(2) column, PSQL will give you an error.

Postgres supports Dates and Timestamps, SQLite does not (directly).

There is no automatic “rowid” in PSQL, but you can define a column, including the Primary Key, as SERIAL or BIGSERIAL to get it to auto-increment from a sequence table. PSQL will take care of that setup for you.

You can easily add extensions to PSQL like citext for case-insensitive text.

PSQL does not have a database-wide encryption scheme.

I’m sure there is more but that’s what comes to mind immediately.

3

If you want to just move from single user to multiuser without much work, consider using CubeSQL as the database server although you need to pay if you need more than 2 users

4

cubeSQLServer is only $99 for 3 connections. Using that, you don’t have to do much re-coding at all. You’ll only have to rewrite Prepared Statements a little.

5

One other possibility if cubeSQL is attractive to you is to have it hosted at serverwarp.com, where you get unlimited cubeSQL connections included with all plans.

6

postgres is very strict on data types like Kem said.
so be prepared to have a lot of work in that field, except if you put all fields as TEXT …
don’t know if you did, but this is the time to deal with all the errors returned by the database after each select query.
moving from sqlite to postgres is a good way to enforce database integrity and robustness, but it takes time.

7

Well, most (if not all) of the fields are varchar, already… so hopefully that will meake things easier…

Thanks a lot everybody for the feedback

8

I have faced this type of problem long ago.
It’s not a walk.
All sql queries where aggregate data is used should be changed.
In the same tables it is a bit pricy to use autoincrement.
Cast in Postgree does not work very well for text fields that contain not just numbers
The triggers are completely different

Example MYSQL order | Postgree order with CAST
// Mysql

order by righe.datarif, cast(teste.protocollo AS decimal(10,3));

// Postgree

order by teste.datarif, f_ordnattxt(teste.protocollo),f_ordnatnum(teste.protocollo);

Temp Table
// Mysql

CREATE TEMPORARY TABLE

// Postgree

CREATE OR REPLACE TEMP VIEW

Obtain last autoincrement
// Mysql

SELECT LAST_INSERT_ID();

// Postgree

SELECT last_value from righescontrini_rowid;

Select Distinct for a numeric/text field
// Mysql

select distinct cespiticatministeriali.gruppo, cespiticatministeriali.gruppodescrizione from cespiticatministeriali order by cast(cespiticatministeriali.gruppo as decimal(10,3));
// Postgree

select distinct cespiticatministeriali.gruppo, cespiticatministeriali.gruppodescrizione, f_ordnattxt(cespiticatministeriali.gruppo) as ord1, f_ordnatnum(cespiticatministeriali.gruppo) as ord2 from cespiticatministeriali order by f_ordnattxt(cespiticatministeriali.gruppo),f_ordnatnum(cespiticatministeriali.gruppo);

Select distinct other form
// Mysql

SELECT DISTINCT articoli.rowid, articoli.stato, articoli.codice, articoli.descrizione, articoli.alias,

// Postgree

SELECT DISTINCT ON (articoli.codice) articoli.rowid, articoli.stato, articoli.codice, articoli.descrizione, articoli.alias,

This is a bit different, apart from the function ‘f_ordnatnum’ and ‘f_ordnattxt’ that I can not retrieve at this time.
I still think I can post it in the afternoon.

NB: Remember not convert data with tools, manually create your table and use ‘serial’ for autoincrement field.

9

Only one database type? …
If you are used to recycle code parts use the system to use them all …

USE 4 DATABASE

10

@Massimiliano Chiodi
The link to your DB KOALA Database CLASS gives a “404 Not Found” error.

11

If you think about rewriting code in order to support a multiuser database, you may want to check out making use of interfaces at that time. It will make it easier to add support for other dbms in the future:
http://vimeo.com/seminarpro/interface

And for a downloadable sample project:
download project

12

I was forced to make the switch last winter. I have never regretted the time it took to convert my project. I now have one customer using my program with 13 users. This thread might have some information that would be useful to you.

https://forum.xojo.com/37330-converting-to-postgresql-from-sqlite

13

[quote=332208:@Neil Burkholder]I was forced to make the switch last winter. I have never regretted the time it took to convert my project. I now have one customer using my program with 13 users. This thread might have some information that would be useful to you.

https://forum.xojo.com/37330-converting-to-postgresql-from-sqlite[/quote]

i could not do the above since i cannot use the postgresSQL database for single user without installing the server. my application can simply move from single to multi user and vis-versa with some additional file and of course for multi user the need to install the database server.

14

you also have to deal with record locking in postgres. this is an important part, with the strict data types.

15

Postres has a very nice feature to get the last insert ID:

INSERT INTO table (fields) VALUES (values) [ , (values) , ...] RETURNING id

In Xojo, you can call that using SQLSelect, then examine the returned RecordSet to get the inserted IDs. You can do the same when deleting too.

DELETE FROM table WHERE criteria RETURNING id

Instead of returning a single column, you could return * to get the entire record, useful for updating an ORM. It’s handy and reliable.

16

I have no idea if SQLite let’s you do something like this, but you can include code in your SQL too for more complex processing:

DO $$
DECLARE
  var1 text;
  var2 text;
BEGIN
  -- Code that you'd otherwise need a function for
END $$;

Like a function, either the entire code block succeeds or fails. If it fails, the database is left in the state that it was in when the code block started.

We use this all the time during our migrations. Aside from allowing us to do custom processing, we can include unit tests as part of the migration so a failure will cause the entire migration to rollback. This ensures that conditions on production are what we encountered on our development boxes.

17

[quote=332110:@Roman Varas]Can anyone give me some tips, warnings, advises, etc… on this subject…

I have made a medium sized app based on sqlite but looks like I need now to support two or three users (connections) rather than one… Requirements have changed…

What things should I look for, consider, be aware of when I’ll make this move…?

Thanks[/quote]

Are your users accessing the data from a Xojo web app that connects to a SQLite db in the same folder? If a handful of users are accessing the database that way, I don’t see why you need to move from SQLite to a real RDBMS. If you’re accessing the data from apps on various computers on a network, that’s different.

18

We moved from sqlite and valentina to postgres 2 years ago. And we are still very, very happy…

There are tons of cool tools for postgres and most of them are free:

If you need to load data from sqlite or other dbs fast look here: http://pgloader.io/
Convert from dump: look here https://gist.github.com/vigneshwaranr/3454093
If you are using a mac und you want a simple way to install and run postgres we can recommend postegreapp (http://postgresapp.com)
Syntax comparison look here http://hyperpolyglot.org/db
If you are not unsure which db is the best for you try both dbs with http://www.monkeybreadsoftware.de/xojo/plugin-sql.shtml and test the performance, syntax …

19

@Ralph Alvy

Yes, this is sort of the way it works now… I have a main user (desktop app) which can read/write data that connects to the Sqlite DB and also a read only webapp running on the same machine (which also connects to the same DB) to display the data online which can be accessed from other computers in the local network… I know this is probably fine, as there is actually only one computer writing to the DB but I am thinking about making the system future proof and eventually there may be more clients writing data to the DB…