Best SQL Database?

Probably been asked before. I have just written my first SQLite application for the Mac OSX environment, and managed to learn how to code sigh and package it.
However, I realise that SQLite may not be optimal for future applications, and was wondering about the pros and cons of other SQL databases. I would appreciate any comments that anyone cares to make.

There is no real answer to the question. There are many database engines, from SQLite on the “lite” end, to Oracle on the large enterprise end with dozens in between. Some are great for single user applications such as SQLite, where others are required when you start geting into multi-tier client server applications.

So you statement “SQLite may not be optimal for future applications” really depends on what those “future applications” might be… perhaps it will, perhaps it won’t

simple answer :
sqlite is best for local databases
postgresql is best for network databases (but you can use it for local database too as it is more robust and strict than sqlite)
they are both completely free to use and deploy, which is one of their great feature.

and you will get here one answer per forum user, although I’m quite sure my opinion is the most shared here.

You should use something like ActiveRecord (free from BKeeney) which abstracts the database so changing databases isn’t a big deal.

http://www.bkeeney.com/rbinto/activerecord/

[quote=354829:@Jean-Yves Pochez]simple answer :
postgresql is best for network databases (but you can use it for local database too as it is more robust and strict than sqlite)
they are both completely free to use and deploy, which is one of their great feature.
.[/quote]

how do you set up postgresql for single user (local database)?

Has anyone any experience with CubeSQL?

i do… have been using it for ages… probably around 10 years. for a while it was called Real Server

If you have a Mac I’ve found a neat tool to temporarily set up a Postgres server locally for a single user. However, I’m not sure that you really want to distribute your app with instructions to download another. I would think it’s more for testing.

[quote=354895:@Tim Parnell]If you have a Mac I’ve found a neat tool to temporarily set up a Postgres server locally for a single user. However, I’m not sure that you really want to distribute your app with instructions to download another. I would think it’s more for testing.

https://postgresapp.com[/quote]

i know about this… but i want something for windows too…

Hi Jean, how easy to install for a single user postgreSQL?

install a postgresql server as you would do on any server.
connect to 127.0.0.1

As of today, on Linux and Windows, and on Mac if you dont mind Docker, MS SQL Server can scale “all the way up”. I use the free Express version for small workgroups (not commercial use). There are different other versions for different purposes.

Postgre is my other choice (only because I don’t know it quite as well as I do MS SQL). It works on Linux, Mac and Windows and scales “all the way up” also. It is free (really free, unlike mySQL and Maria where you can be bitten by the licence). Jean-Yves beat me to the finish with regards to local Install. MS SQL Server on Windows has other options as well for local install.

if i want to let client have a choice to use SQLite/CubeSQL or postgresSQL, what do i need to take care of while doing this???
what i mean is what is all the difference that i need to cater for???

[quote=354905:@Richard Duke]if i want to let client have a choice to use SQLite/CubeSQL or postgresSQL, what do i need to take care of while doing this???
what i mean is what is all the difference that i need to cater for???[/quote]
quite a bit actually.
each database engine will have its own unique base class [this has been discussed recently on this forum)
each will have subtle differences in SQL syntax (in some case features/functions that other DB engines won’t have)
each will have their own connection methods.

Basically each database engine will have as much unique/different as it has the same

oh my gosh… i might need to think about doing in future then.

The best database is always the one the client has.
Usually if they have MySQL, PostgreSQL, Oracle or whatever, I can use that and add a table or two as needed. This way a solution can directly interact with other solutions using databases.

If client has no database, you can ask what they can install and if the admin only does Microsoft stuff, you probably get Microsoft SQL Server. Remember, someone has to setup the database server and maintain it.

you are assuming there exists a “in place” client for whom an application is being custom designed…

That is actually rarely the case… as many developers are attempting to design “business solutions” to be presented to business entities that are “potential” clients… its called “vertical marketting”… you solve their problem before they know they have one.

So, it in the case it depends on the project design and scope. Does it need to support multiple users? Does it need to scale from desktop to enterprise? is it an enterprise level app to begin with.

and even if it were the case… for an enterprise level app, you should allow flexiblity…
Customer A may have Postgres,
but the next Customer may have mySQL

For a vertical solution, you want something which can be easily installed.
e.g. you can use Postgres and locally bundle it with the app configured to run on a special port.

Or just make a vertical solution and offer to connect it to MySQL, PostgreSQL or others you support.

A few developers do resell CubeSQL licenses with their products, which also works nice.

cubeSQL is quite capable for most projects unless you are having hundreds or thousands of concurrent writes per second it scales quite nicely and is affordable and multi-platform. Plus the added advantage of being based on SQLite which makes it quite easy to develop for.

PostgreSQL is fully featured, liberally licensed and overall a top contender. However frankly I actually prefer MySQL due to the large ecosystem, great administrative tools, easy deployment, and Oracle has actually done a fair job of maintaining and enhancing it. Uber recently switched to MySQL from PostgreSQL so it really comes down to taste and experience. You really can’t go wrong either way.

As for development and supporting multiple engines I really recommend giving SQLdeLite that I wrote a try: https://github.com/1701software/SQLdeLite

It abstracts most of the details of prepared statements and such so you can focus on the SQL and connectivity while still gaining all the performance and security benefits without all the hassle of preparing prepared statements.

[quote=354830:@Markus Winter]You should use something like ActiveRecord (free from BKeeney) which abstracts the database so changing databases isn’t a big deal.

http://www.bkeeney.com/rbinto/activerecord/[/quote]

I think ActiveRecord is fine for the simplest use cases but I think the desire to avoid SQL is the wrong approach. You can make huge optimizations and maximize database performance by simply querying for the columns you require when using native SQL. You also have full control over the relationships and can reduce the number of pulls to match data up by using native SQL.

SQL is not hard and really should be fully understood if you are developing database applications or you can end up in a bad situation. I wrote SQLdeLite to support ALL Xojo database plugins while allowing you to focus on the SQL and avoid the annoying parts like prepared statements.

Kem Tekinay’s SQLBuilder is another approach that provides many of the same benefits but takes a very different approach (https://github.com/ktekinay/XOJO-SQLBuilder). While you do not build SQL in a conventional sense you still very much have to understand how to narrow down the scope of your queries to get the data you want. This is a good thing.

However for the simplest of CRUD with little to no table relationships I think ActiveRecord can save you some time.

[quote=354917:@Christian Schmitz]The best database is always the one the client has.
Usually if they have MySQL, PostgreSQL, Oracle or whatever, I can use that and add a table or two as needed. This way a solution can directly interact with other solutions using databases.

If client has no database, you can ask what they can install and if the admin only does Microsoft stuff, you probably get Microsoft SQL Server. Remember, someone has to setup the database server and maintain it.[/quote]

This is good advice but I’ll only add do not blindly add support for a RDBMS just because you got it working in development. There are nuances and caveats to these systems that you will find in a heavy load production system. Be prepared to support the on-site support team on any enterprise development in case something is not working correctly. You do not want to be in a situation where you know little to nothing about say MySQL for instance while claiming you support it. Truly support it or do not. Multiple database servers can run on one machine and if your product provides enough value they can make it work.