What database should I choose?

I’m new to databases, and I need to choose one for my new project.
I’m making a small app for storing data about the store, i.e. stock, prices, accounting.
Here I read that MySQL is a very good choice for beginners, though it’s not that good at scaling, and it might cost a pretty penny in the future for me.
What’s your opinion, is MySQL okay for a small store, or I need to choose something more complex?
What would be your recommendations?

I would opt for PostgreSQL . Totally free and very good.

3 Likes

+1 for PostgreSQL.

2 Likes

What databases do you have already running?

Like if you run a MySQL, PostgreSQL or MS SQL server for some other thing, you may just add a database there for your own stuff.

If you are just using the database for single user input rather than a full network server system then I would probably just stick with the built in SQLite.
We use PosgreSQL for our software but that is for use in a multi user multi system environment. To use PostgreSQL you’ll need to install the server separately to your application. Things get much more complicated once you are in that arena.
Regards,

Lee

3 Likes

+1 for SQLite until your needs determine you need more.
Then, +1 for PostgreSQL.

3 Likes

Performance is less of an argument for me. Most databases are very good for a very loooong time, if you are starting small. MySQL or MariaDB has the advantage that you will find more (or more easy) help on the internet.

Both have very good documentation.

MySQL is probably a bit easier for a beginner. postgres for instance is stricter on case sensitivity and it is more complicated to alter tables (for instance if you want to sort your fields (not the content of the fields) after your initial design. On the other hand, postgres being more strict has a huge advantage too: you are learning as a beginner how to do things right. MySQL has a bigger tolerance but one day you might have to pay for that tolerance.

I would always opt for postgres. Hazzle-free from a licensing perspective and this for over 30 years (unlikely to change) and very good. Do yourself a favor and don’t run a search on performance benchmarks. It all depends about the questions asked in Google. Both are really on par for the average Joe for a very long time. If you will ever reach the state that only a particular database will speed up your stuff then you have probably 20 database engineers working for you.

I moved a few years ago completely over to Postgres. I’m using MySQL only, when I have to deal with Wordpress. Wordpress can run on Postgres (even very well), but most of the documentation, plugins etc. are expecting a MySQL database :-(. So why complicating your life? For Xojo it is either Postgres or SQLite (for very small tasks) for me.

1 Like

How many people will be running this app simultaneously? How many requests per hour will you make? If it’s just you, or even you and one other, and you make a few requests per day, then SQLite is perfectly adequate.

2 Likes

@Sergio_Murray What OS will your database server run on: Windows, MacOS, Linux ?

Installing a db server can be quite challenging, specifically on Linux. On top of that the choices you make during installation are important. If you don’t have much experience with database servers, I suggest having someone knowledgeable to help you installing the server.

The db managers suggested so fare are good and all have free versions. To that list you may also add MS SQL Server. Important things are:

  • Installation/management: not all db servers are easy to install and managed, you need to find one you will comfortable with
  • Security: who can do what, etc. If the db is exposed to Internet, security is VERY important
  • Backups: it’s important to have a good backup plan. On top of that one has to check that backups are running as scheduled, don’t forget the most important backup could be the one you don’t have :worried:.

SQLite is quite competent, and if you need multiaccess to you database there are SQLite servers like https://sqlabs.com/cubesql. https://www.valentina-db.com/en/valentina-server-overview may also serve SQLitte databases and offers a good tool to create and manage databases.

1 Like

Xojo uses PostgreSQL. So it gets slightly more love than the other Databases.

Excellent, and to add to this if I may: don’t forget to test your backup concept at least once in a while. This means: restore the data to a different instance of the DB. I have seen too often that clients had a bunch of backups, but none was complete, or working etc. With a sql dump chances are high it will work, but you never know if it is complete unless you thoroughly testet it yourself.

1 Like

I think the big comments from the OP is the words “small store.” There should really be no reason then that SQLite could not be used. Someone suggest MSSQL. Seriously? For a small application? Microsoft server products are incredibly robust, but getting them up and going is not for the faint of heart! Let alone the cost of setting up the server.

A simple app running SQLLite should do nicely for a small store with a few employees. Even if you have more than one user. Postgre would be the next step up after that as numerous people have stated. I doubt you’d need to go much further.

2 Likes

SQLlite is always a good recommendation in the design phase, as it is up and running without any “big” configuration. That’s my workflow most of the ime. Starting with SQLite and once I have designed at least the core of my app, I’m starting to move it to Postgres.

Regarding Microsoft SQL Server: I think Gilles only wanted to mention it for completeness, as it is a stable and good database too. Many Xojo users are working in a corporate environment, so they either have access to a MS SQL Server, or big experience in maintaining it. But you are right, if I had no experience at all I would rather learn how to set up a db on Linux than this MS beast :wink:

MSSQL. Seriously?

SQL Server 2019 Express is free, compatible with big brother, and makes moving up to the ‘real thing’ later on relatively painless.

1 Like

In case I was misinterpreted, my comments regarding MSSQL were not meant to put down anyone. Microsoft Server products are actually terrific, solid platforms. This is coming from a die hard Mac guy. They are just not for the faint of heart to set up and install.

Now, I was unaware of SQL Server 2019 Express. If it’s easy to set up for a small user, then great. But when someone says they are small, I take that literally that they have just a handful of people who might need to use one instance of the program. In that case, I see no need for anything other than SQLite.

But I can certainly understand that if growth is in the future that something like SQL Server 2019 Express could be an intriguing platform from which to start - particularly in a Windows environment (or is it Xplat too?).

1 Like

Express has a 10 GB limit, which of course isn’t a real limitation of small businesses. A lot of Software products are using the Express Version as it is free. And if you need more you business is probably in such a good shape that you can easily migrate to the full version and afford it.

But is is powerful to an extent that there is a learning curve. I don’t think it is the right tool for someone who has never dealt with it. This is true as well for a linux/msysl postgres combo, especially if you want to run it securely but these days there are cloud offerings where you can buy a sole database only.

And you have tons of instructions on how to get it set up on different hosters. I’m not saying that it is completely easy but I believe even an absolute beginner can set it up on Linux if willing to read a few hours through the documentation.

If your intended use is commercial (either for your own business or for commercial software), then I would stay away of MySQL or MariaDB. These products are not free for commercial use.

For single user usage: +1 for SQLite. I also use it together with PostgreSQL in my applications wherw I need a local data store in addition to the main DB.
For multi-user usage: I have used MSSQL (both express and full product - I still have only one application running on MSSQL, the rep^lacement is vbeing developed on PostgreSQL), and PostgreSQL. I find that tools for MSSQL are actually somewhat better than comparable tools for postgresql. MS SQL Studio is definitely easier to use than pgAdmin4 for example. Red Gate tools are powerful and useful in managing multiple databases, migrating databases etc. So far, I have not found anything equal on the postgresql side. Don’t get me wrong, I can do everything on PostgreSQL that I can with the tools on MSSQL. Only, it is a bit more manual. All that said, I now run mostly PostgreSQL because I find it a bit better fit with Xojo. Both RDBMS have ample documentation, so the learning curve, steep by moment, is still totally manageable. The programming language on both is obviously heavily reliant on SQL, with Postgre a bit closer to standards. I rely a lot on stored procedures on MSSQL and functions or stored procedures on PostgreSQL. Complex queries are faster on the multi-core RDBMS than in single core Xojo, especially if you use a separate DB server (for example in a web application scenario). If your application is used by only a handful of people or has light data processing only, then there is not much difference in the end, so coding data processing in Xojo works equally well. There are some who prefer to avoid stored procedures in order to be able to switch RDBMS at will if the need arises and so will develop all data processing routines in Xojo.

In short, SQLite is a good choice. MSSQL Express is a good choice and PostgreSQL is a good choice. Your use case will dictate which one is better. Because of the commercial restrictions to the MySQL licence, I would avoid it. But it is widely used, very well documented and works well also.

1 Like

Very good points made here. Something else I can add to this. Beginners often tempt to “only” select data from a database into Xojo and are then looping over all these data to achieve what they want. But it is often better ( not only for the reasons mentioned above ) to let the database do the heavy lift. Means working with views and more complex SQL statements like Joins etc., so basically using the SQ-Language (aka code the database) as much as possible.

Obviously there is a learning curve for this too, but I think it is a good investment to learn what a database can do for one, before reinventing the wheel with Xojo code.

1 Like

Depends. I tend to do the opposite, since I find anything more than simple SQL to be incomprehensible, and, like a regex, I have to pick my way through it each time.

1 Like

Fair point, we all have different backgrounds and what is easier for one, might be different for another.

Let me rephrase it this way: in general it is worth to put the heavy load to the database (but it might be a learning curve :slight_smile: ). It is utmost impossible to bring a modern database as a “normal” developers to its limits these days.

For instance I have one project with one table containing the users and their metadata and one table recording each login of a desktop app and the used app version during login. In a web app I wanted to know the latest login of each user and the version each one was using and getting this listed sorted in a descending way.

So I created an own view for this in postgres, and all I’m doing in Xojo is doing a normal Select on this particular view, just like I would do a select from a “real” table.

create view vlogins(id, displayname, datum, usedversion) as
	SELECT row_number() OVER (PARTITION BY true::boolean) AS id,
    benutzer.displayname,
    max(verlauf.datum) AS datum,
    "left"(string_agg(DISTINCT verlauf.usedversion, ','::text ORDER BY verlauf.usedversion DESC), 5) AS usedversion
   FROM verlauf
     JOIN benutzer ON verlauf.username = benutzer.aduser
  GROUP BY benutzer.displayname
  ORDER BY ("left"(string_agg(DISTINCT verlauf.usedversion, ','::text ORDER BY verlauf.usedversion DESC), 5)) DESC;

I would not have done anything different in pure Xojo but it would result in a few nested loops and a few arrays probably. Another advantage is the the built-in database optimizer is usually “optimizing” your SQL statements.

But there is a downside to this, no doubts. The “S” in SQL stands for “structured” and unfortunately not “standardized”.

I’m using things like “left” and “string_agg” in above SQL. Their syntax is often slightly different on all platforms, which makes a later migration more complex.

For instance I’m using above :: for casting, but the SQL-Standard is CAST(), which works as well on postgres, but I was obviously lazy :-). But casting is in general faster on a database than doing it in your code.