Which DB system to choose?

Hi there,

I am new at Xojo and after spending countless hours googling for this I’ve decided to try to ask here…

I’m starting to develop a software based on a app I have developed in the past using Filemaker. It is an project management/accounting software that is based on a relational database and needs to be used by several users at the same time. For this reason I think I can’t use SQLite.

I’ve seen there is quiet a few solutions out there but I am wondering if there is any solution that would not require the need of installing a data server outside the app? Like an embedded database server? My main concern is for my clients to be able to install the software easily without needing to worry about installing mysql or other software. And of course have as little maintenance as possible needed.

Anyone can point me in the right direction?

Kind regards,
Raphael

CubeSQL is probably the easiest and simplest multi-user database server I can think of. Valentina is another but I don’t think it’s as simple.

FWIW, I have a client that uses CubeSQL and loves it. And you don’t have to do much different to work with it. Prepared Statements are different, but for the most part you won’t have to change much code.

If it’s a web app, you might be able to just use SQLite since the web app functions as a server and works locally with the SQLite DB.

If it’s a desktop app, you’ll need a db server. You could use SQLite via CubeSQL. Otherwise PostgreSQL and MySQL are popular choices. On the OS X, there is a pretty easy way to get it running: Postgres.app. Valentina (in the OmegaBundle now) might also be an option.

Indeed, for a web app, Sqlite is suitable for use in a small business by several people simultaneously. It can even be shared by multiple processes (multiple threads of course, but also several Xojo apps, as these applications are on the same web server).

By cons, it seems there is a problem in the xojo-sqlite bridge.

https://forum.xojo.com/7793-how-to-keep-the-ui-responsive-during-long-taking-sqlite-queries/p1#p243685

https://forum.xojo.com/7793-how-to-keep-the-ui-responsive-during-long-taking-sqlite-queries/p2#p246064

I hope Xojo will fix this bug. In the meantime, be careful: SQLite is still a good solution, but it depends on the number of users who often write in the database. Sqlite can not be exploited to its full power in Xojo.

Well once you start talking about multiple users, then you really need to think server. CubeSQL is definitely worth considering as it’s install is very straight forward and you can easily write a commandline XOJO app to pick up where the install leaves off, to create the DB, Tables, security etc… It is very easy to operate etc… The only downside is that it costs, but even there they usually over it for $99 once a year or so… like right now! I would suggest downloading a copy, have a play around with it, check out docs (which are very good) and see how it goes.

Apart from that the only other one I would consider is Posttgres.

FWIW, we’ve done an entire accounting application using SQLite in a Xojo web app. Well over a hundred tables and multiple users. Be wary of long queries but other than that we’ve not experienced many issues.

I would say: SQLite for small single user applications and PostgreSQL for bigger multiuser purposes. Both are free available, free to use, multiplatform, very good and have a very large number of users around the globe.

Unless it’s a web app. It is rare in this case there is need to directly access the database via a port. And servers built on sqlite are 5 to 10 times slower than native sqlite.

For example, The sqlite.org website uses sqlite database. Every day, there over 40 000 requests are firing on the sqlite database.

But for me, I think the best solution is to use the SQL MBS plugin. It’s very effective, and you can easily change database if necessary (sqlite, postgres, etc.).

Well for multiuser-enviroments I still love mysql and latest versions have robust db replicating functions. It’s free as long as you do not bundle/ redistribute it with your software. And there’s a lot of import- and exporting tools (I love the excel one).

Of course your customer should consider to buy a license but this is not your obligation then.

I am afraid to say: 40.000 queries per day is something not really big when speaking in terms of business applications…

sqlite &/or postgresql is my choice - their licensing is VERY simple - free for ANY use
That is, IMHO, a bug plus

Avoid the hassles that mysql and maria will give you

mysql has a dual licensing model that can surprise the hell out of your customers when they get sent a demand letter insisting they buy a license - which is then quite expensive

maria … well … See “Distributing a proprietary application with the MariaDB / MySQL server” on https://mariadb.com/kb/en/mariadb/licensing-faq/

It’s OK for a demo or a small site like you describe, but seriously 40k requests in a day is nothing. Most web applications I’ve worked on needed to be able to handle 25K requests per hour during period ends etc…

Wow, thank you all so much for this incredible feedback!

Well, it looks like what make sense (considering the cost) would be to go with PostgreSQL. Though I’m worried about the install process for this. Also I guess the data would be on the server, not in a file that I can move location easily if needed?

I’m still wondering if it would be possible to use SQLite. Are the tables readable while records are being written or it is under complete lock? Also is it possible to know if an other user is already accessing a specific record when loading it or would I have to create my own check?

Anyway, thank you so much for all your replis, means a lot!

For what it’s worth, it’s very easy to dump a Postgres db to a file to be read in another location.

PostgreSQL is not a straight forward install for non-geeky people. I use my mother (non-geek/non-techie) as a test bed if something is truly user friendly.

I am very pro-PostgreSQL so dont get me wrong. you might need to help your customers with the installs/configs of it.

If I needed easy to setup, easy to manage, affordable, dependable, supported, well documented… I’d go with CubeSQL.

cubeSQL is having a sale right now on v5.5 (just released). They also have discounted (going forward) the licenses, so they are even more affordable. I just picked up an unlimited connection license for $99 on the sale. And there is only a few days left on the sale. Once the sale is over the unlimited connection license goes to $599 (if my memory is correct).

I have been using cubeSQL for a while now and cant say enough good things about it.

Except for when you can use Postgres.app on OS X, which does not have any installation process at all.

I like SQLite for small-medium web projects and other tools and utility apps where the connection needs are going to be everything on the same computer.

Anything with multiple computer access I go with Postgres. If you spend a little bit of time getting familiar with how to use and configure it, it will pay off. It’s really not that hard as there is a ton of info about it available online.

Sure it is :stuck_out_tongue:
Here’s install;ers for just about everything anyone would care about
http://www.enterprisedb.com/products-services-training/pgdownload

[quote=246798:@Norman Palardy]Sure it is :stuck_out_tongue:
Here’s install;ers for just about everything anyone would care about
http://www.enterprisedb.com/products-services-training/pgdownload[/quote]

if it was so straight forward why do my friends at EnterpriseDB get called in soo many times to “fix” the install?