Base - External Database Recommendation

For stability purposes I would like to run LO on my Mac using a standalone SQL database instead of the embedded base. My database will not be large and only used by me on my computer. For stability and availability of LO connectors, which database would be the best option? MySQL, PostgreSQL, MariaDB, HSQLDB etc? Firebird does not appear to be available for MacOS. May I also ask which LO connector type the answerer of this question suggests? Thanks!!

Just wanted to note that Firebird is available for MacOS.

It’s just 3.0.x branch isn’t yet released for Mac. But as you’re going to use non-embedded server, this shouldn’t matter.

“Stability” can mean a number of things, it can mean software quality, it can mean provider surety (sometimes things do go away, like when a vendor gets swallowed up), or it might mean SQL language interoperability.

I’ve struggled with data base language interoperability over the years. I lost a huge custom built hierarchical code base many years ago that was in a btrieve data base because when I upgraded to the next version of windows the database server didn’t run anymore, and it was some months before I found this out, and I lost my way to recover it.

To a lesser degree I’ve had an issue having to rewrite php code to move to the newer connector when the old one was being depreciated. So you’ll want a way to protect yourself against a variety of things that can impact your data.

SQL, is not SQL. There is a fair amount of language variability, even though there are standards for SQL. So watch out because what one SQL supports, another SQL might not, and Base is highly dependent on SQL specifics.

I’m currently using MariaDB, a binary drop in replacement for MySQL. When Oracle bought Sun, there was fear that MySQL might not be maintained because it was in competition to Oracle’s other data base products. This is still a fear. The lead author of MySQL, to make a long story short, gave us MariaDB so we can be sure it persists. I suggest you check it out. MySQL has been around for a long time and is used by a vast number of users and Internet servers. So far I’ve found MariaDB runs every bit as good as MySQL.

I hope to give PostgresSQL a spin someday, as I’ve heard many good things about it too. And Firebird is of note, because LO has adopted it as the future direction of Base to provide a built in alternative to HSLQDB 1.8, or split v2.3.

Finally, as Ratslinger, points to, you need to consider third party tools. My current favorite is HeidiSQL. It runs on both Linux (with WINE) and Windows, is fast and full featured. I use it to setup my databases and tables, and no longer even use the much less capable LO Tables editor. There is also phpmyadmin which I use from time to time. When things go bonk, it’s nice to have a few different ways to get at your data, to test it, and sometimes to back it up or move, rename, etc.

To me that gives a measure of stability.

Thanks for your reply @EastTrieve, and also for replying to so many other people’s questions on this site. Databases can be challenging so all help is appreciated!

@Justin100, You’re welcome. I’m just trying to give a little back. I’m so grateful for the community support. So many people have helped me when I get stuck. LO has been a steep learning curve for me.

You can use any of the mentioned DB’s. The main difference in use will be connectors. ODBC seems to be the most problematic. JDBC is common and available for most DB’s. I find Native connectors the easiest to use and provide the quickest interfacing with Base. You can find Native connectors for MySQL, PostgreSQL and MariaDB (same as MySQL).

I personally use MySQL only because I started with it for a totally different reason. You can use the MySQL Workbench with it for a graphical interface and modeling. However I have installed and have available all the mentioned DB’s currently on various systems including SQLite (not of personal liking). Have installed & used PostgreSql in previous job & had multi million record tables.

Edit: For more info on external DB’s see Base Handbook Chapter 2 found here.

For the MySQL/MariaDB native connector - here.

I believe PostgreSQL native connector is already installed in Base.

Great explanation! Thanks @ratslinger for answering my question for quickly!

You’re welcome.

As an after thought, here is a site I recently saw which notes some of the capabilities of many open source DB’s - click here.

It’s Linux oriented but the information applies to most systems.