This is the second of a three-part series whose goal is to introduce what databases are and give some opinions about the available database technologies, why you should use them, and when you should use them in a non-technical way. We’ve already answered the question of “Why use a database” and now we answer the question of “What database should I use?”
The following information, although coming from LabVIEW developers, is generally true for development in any language. Now that we have a clear idea of some of the advantages of using a database in our applications, we now must answer the high-level questions of: (1) What kind of database should I use; and (2) What kind of infrastructure do I need to support my chosen database?
A good chunk of the answers to most questions when it comes to choosing a SQL technology can be answered by the following ideas:
- Common SQL command syntax is compatible with all versions of SQL, and generally complex commands and items that are not ”configurable” once the database exists are specific to the SQL technology; meaning that IF you decide to switch your SQL technology halfway through development, it’s not a deal-breaker.
- If your database only needs to exist locally and won’t grow to an incredible size, use SQLite.
- If your database needs to be accessed remotely and will grow to a large size, don’t use SQLite.
- Most SQL technologies have an Open Database Connectivity (ODBC) driver so integration is easier for Windows-based operating systems.
Common SQL technologies are:
- SQLite (1)
- MySQL (2)/MariaDB(3)
- Microsoft SQL(4)
- PostgreSQL (Postgres) (5)
For all intents and purposes, they are mostly the same, with the caveat that SQLite is considered a mobile database, and thus, doesn’t provide any kind of application level security and exists as a file rather than a service.
The table below summarizes some of the differences between common SQL technologies.
| SQLite | MySQL/MariaDB | Microsoft SQL | PostgreSQL(Postgres) | |
| Open Source | Yes | Yes | Yes* | Yes |
| Linux | Yes | Yes | Somewhat* | Yes |
| Server | No | Yes | Yes | Yes |
| Local Only | Yes | Yes | No | No |
| File Security | Yes | No | No | No |
| Application Security | No | Yes | Yes | Yes |
* Not traditionally open source, but Microsoft is going to release it on Linux, so it’s somewhat opensource
As you can see, the major differences involve SQLite’s “mobile” nature. Although a boon to small applications that need to have locally persistent data, it’s not very well suited for distributed applications where multiple connections interact with data stored in a database simultaneously. SQL technologies which support servers, such as MySQL and Microsoft SQL, can integrate into an existing IT Infrastructure (where databases may already exist and be maintained) to ease the LabVIEW/data barrier and allow other applications to access that data.
I mentioned ODBC earlier, which is an application programming interface (API) to access different database technologies. The best LabVIEW example for how ODBC can make projects easier is that the LabVIEW Database Connectivity Toolkit (LVDCT) can interact with any database that has an ODBC driver, making your database code relatively portable with the added need for your application to have a dependency that includes on ODBC driver and a Windows OS.
The above should give you a brief introduction on how to choose a SQL technology and the pros, cons, and use cases of each.
In the next installment of this blog series, we will tackle the idea of scale: how much data is a lot of data and do I need a server?
- By Part of the SQLite documentation, which has been released by author D. Richard Hipp to the public domain. SVG conversion by Mike Toews. - SVG created from sqlite370.eps, distributed with version 3.7.2 documentation, Public Domain, https://commons.wikimedia.org/w/index.php?curid=11675072
- https://en.wikipedia.org/wiki/File:MySQL.svg
- By Mackphillips (Own work) [CC BY-SA 4.0 (https://creativecommons.org/licenses/by-sa/4.0)], via Wikimedia Commons
- https://blogs.msdn.microsoft.com/cdndevs/2015/03/11/python-and-data-sql-server-as-a-data-source-for-python-applications/
- By Daniel Lundin - https://wiki.postgresql.org/images/a/a4/PostgreSQL_logo.3colors.svg, PostgreSQL License, https://commons.wikimedia.org/w/index.php?curid=1395316
