43

I've seen some applications that are basically application software that run local to the system (so they don't have communicate much over the network). These applications seem to depend on database servers in order to store their data.

An example of an application is Amarok (a popular music player on Linux). I don't know if they still do this, but I remember there was a time where installing Amarok meant that you had to install a MySQL server and have it running in the background all the time.

What is the advantage of using a server for local storage compared to using a smaller embedded SQL solution like sqlite? I'm talking about application software in general, not necessarily amarok (that was just an example). Are there any situations where using a database server makes sense compared to an embedded database?

9a3eedi
  • 2,101
  • 3
  • 23
  • 29
  • 4
    Having a database may be very important. Having an in-process database or a separate-process database is an implementation choice / detail. – 9000 Apr 21 '15 at 16:44
  • 2
    I understand that. The question is more about why would you choose a separate process database (like mysql server) over an in-process database (like SQLite) for local applications. – 9a3eedi Apr 22 '15 at 07:25

7 Answers7

30

SQLite offers a pretty good rundown of when to use it or not vs alternatives:

https://www.sqlite.org/whentouse.html

This summary line captures the SQLite use-case extremely well in my experience:

SQLite does not compete with client/server databases. SQLite competes with fopen().

The article expands at length on this point. It also has a section titled "Situations Where A Client/Server RDBMS May Work Better". In a nutshell, they are:

  • Client/Server Applications: multiple users over a network.
  • High-volume Websites: either write intensive or read intensive enough to require sharding.
  • Very large datasets: larger than can be reasonably stored on one disk.
  • High Concurrency: in particular concurrent writes.
Denis de Bernardy
  • 3,913
  • 21
  • 18
  • @9a3eedi: actually, among those four points there is none which describes a scenario with a full database server in combination with local storage (especially the first two are the exact opposite of that) - so would you mind to enlighten us why you picked this answer, though it does not fit to your original question? By the way, I gave this answer a downvote for exactly that reason. – Doc Brown Apr 23 '15 at 07:50
  • @DocBrown: Specific cases where you *should* use a client/server RDBMS are [see answer]; for everything else SQLite works fine. I'm not sure what's unclear in there, so feel free to edit the answer if you believe it's needed. – Denis de Bernardy Apr 23 '15 at 08:07
  • The original question was about specfic cases where a c/s database makes sense **if the application only does things locally** (cite from the title), or **using a server for local storage** (cite from the question text). The four scenarios above are either the exact opposite of that (the first two) or very unlikely or uncommon as "local" scenarios (the second two). So what you wrote is not wrong, but it does not fit to the question. – Doc Brown Apr 23 '15 at 08:54
  • @DocBrown -- and the short answer is: ***it doesn't make any sense*** unless you're dealing with outsized data sets or in need of concurrent writes. (Or, for obvious reasons, in need of something SQLite doesn't offer.) Again, feel free to edit the answer if that point is unclear to you. – Denis de Bernardy Apr 23 '15 at 09:04
  • Well, if you really think that list is complete (and so give a hidden statement that for a local scenario the use of a C/S DB server does not make any sense), then I disagree, and I do not think I can improve your answer by adding some clarifications. – Doc Brown Apr 23 '15 at 09:44
28

Even for a single system with a single user, a "real" database server makes sense:

  1. It uses a familiar language (SQL). SQLite does use SQL, but some embedded databases (e.g. object database, NoSQL) do not use SQL. Those tend to have a higher learning curve because they are less common.
  2. It provides referential integrity, constraints, triggers etc. that products such as SQLite may not provide or at least provide not in full.
  3. By targeting a true multi-user, ACID compliant network database, the application has the option of working in a single user/single workstation scenario, or as a multi-user, hosted application using the same codebase.
  4. The user has the ability to examine the data offline using standard tools (e.g. SQL Developer, MySQL Workbench, SQL Server Management Studio), load or backup data using those tools, etc. While it is possible to do so with many embedded databases of various types, people maybe more familiar with those tools from the C/S database world.

The primary drawback is needing to install and maintain the database server software, which is a bit complex for nontechnical users (and even many technical users). Operating systems such as Linux make this easier: I have PostgreSQL and MySQL running on my Linux system. I have installed applications that hooked into them with little to no interaction on my part.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
  • 9
    Actually, there is one database system (namely Sybase SQL Anywhere) with full SQL support, referential integrity, ACID, stored procedures, which does **not** require a server setup or an installation as a service when run in a local configuration (though it can be setup for a multi-user environment). I do not know any other database system with these properties, if someone knows one, I would be interested in. – Doc Brown Apr 21 '15 at 06:33
  • 3
    @DocBrown IIRC MS [SQLServer Compact](http://blogs.msdn.com/b/jerrynixon/archive/2012/02/26/sql-express-v-localdb-v-sql-compact-edition.aspx) gives you this as it comes as a dll, though LocalDB is probably the better choice - it doesn't require installation as a service though does require admin rights. – gbjbaanb Apr 21 '15 at 07:44
  • @gbjbaanb: SQLServer Compact has some of that properties (I already checked that some time ago), but it does not support triggers or stored procedures, and it cannot be reconfigured to work as a client/server database (for the latter one has to use MS SQL server, but that is a different database system, and you have to adapt your client code to make this work). Thanks for the hint to MS SQL Express LocalDB, I was not aware of that, maybe it is what I am looking for, have to check that. – Doc Brown Apr 21 '15 at 07:57
  • 5
    To add to the drawback validity discussion - apart from SQLite curiously already mentioned in the answer, a number of non-SQL database and database-like systems, e.g. OrientDB, Solr, and others, **have dedicated embedding support**. – mikołak Apr 21 '15 at 08:03
  • @mikołak I mentioned SQLite specifically because the question mentions it specifically. –  Apr 21 '15 at 10:17
  • @Snowman : ah, right, fair enough :). And to clarify - by "curiously" I merely meant that `SQLlite` is a prime example of a DBMS not requiring any maintenance or setup. – mikołak Apr 21 '15 at 11:03
  • 14
    SQLite does provide referential integrity (though it needs to be turned on) and basic constraints. It is also significantly faster than most server if used properly. It's main disadvantage compared to server is that it is single-writer. – Jan Hudec Apr 21 '15 at 12:36
  • 2
    @DocBrown: Firebird's embedded database provides full SQL support, including referential integrity, ACID guarantees, stored procs and triggers. It didn't used to support multiple concurrent connections to an embedded database, and I'm not sure if that limitation is still in place or not, but the entire SQL feature set is there. – Mason Wheeler Apr 21 '15 at 19:32
  • @MasonWheeler: thanks for that hint, I was aware of Firebird, but not Firebird embedded server, which looks promising. – Doc Brown Apr 21 '15 at 20:48
  • 1
    I don't find point 4 convincing. While not having to learn a new interface is kind of handy, I would think anyone capable of digging into a full blown database server has the technical know-how to learn how to use a GUI for SQLite, and how to copy a SQLite file around to different locations. Point 1 is actually a little stronger than stated, as SQLite only supports a subset of SQL commands. Notably, it's missing a lot of the `ALTER` commands, requiring people to resort to renaming/recreating/copying tables to make changes. I don't know if that limitation applies to other embedded databases. – jpmc26 Apr 21 '15 at 23:50
  • @DocBrown I'd also be interested to hear about alternatives to Sybase... Supporting historic versions and other quirks is no fun; though the ease of transferring database files from machine to machine is second to none. – Danny Beckett Apr 22 '15 at 00:52
  • -1: **1)** NoSQL isn't an umbrella term for embedded databases, but for databases that aren't SQL, never mind if local/remote; "higher" learning curve is ambiguous, 'course there's also a certain learning curve for SQL? **2)** Not true **3)** ACID may not be relevant for many multi user apps, and concurrency is well-supported in NoSQL databases (it has to, considering e.g. Twitter, or Trello which updates in real time on every machine, and the like). And Locality does not imply Single-User. **4)** There's no steep learning curve for most examiners. I find MySQL more tedious in this regard – phresnel Apr 22 '15 at 10:27
  • @phresnel you might want to reread my answer. I know exactly what NoSQL means, and I used it as one example of a database type. –  Apr 22 '15 at 22:41
  • @Snowman: You're right, I misunderstood your first point, pardon 'bout that. My other points seem still valid to me. In my own point 4), I did mean SQL, not MySQL, btw. – phresnel Apr 23 '15 at 08:52
  • @phresnel: improved point 2,3 and 4. Maybe now you like them more? – Doc Brown Apr 23 '15 at 10:02
  • @DocBrown: Still find them ambiguous. As said e.g., ACID is not a precondition for multi user software, etc. – phresnel Apr 27 '15 at 14:49
  • @phresnel ACID is not a precondition, but it is really nice to have once there are multiple threads (not necessarily users) accessing a database. That being said, I still stand by my word that while it might not be necessary in application as described by the question, it is still nice to have. –  Apr 27 '15 at 15:27
  • @Snowman: Of course and I don't doubt its nice-level :) It's just that other concurrency models exist and are just as nice, less nice, or even nicer. – phresnel Apr 28 '15 at 09:12
21

I think it has to do with inertia.

Amarok is based on XMMS which is from 1997. To have to have good database capabilities you had to use a server, because it was so much more powerful then the file based solutions, which by no means had good database capabilities.

The upcoming and gaining popularity of good local embedded databases like SQLlite is something fairly recent.

Pieter B
  • 12,867
  • 1
  • 40
  • 65
  • From what I remember, AmaroK 1 (which might've been based on XMMS) did not depend on a database server. It was Amarok 2, which got released with KDE4 that introduced the dependency, and at the time I found it very strange that they would require me to instal MySQL and keep it running in the background – 9a3eedi Apr 22 '15 at 07:15
10

The most important discriminating feature is concurrency.

If you have only one application that runs in one instance for the user, embedded solution (whether sqlite or some object storage) is usually OK.

However if you have multiple instances that need to manipulate the database concurrently, you need to have a server to synchronize it. SQLite only allows one write at a time, in the whole database, and so do most other embedded solutions. And if you even have multiple applications, you are likely to need more detailed constraint specification that the embedded solutions generally don't allow either.

Jan Hudec
  • 18,250
  • 1
  • 39
  • 62
5

Many of the other answers talk about concurrency as an advantage, but also since the db is running as a server, the database can run tasks without the need of the application running. This could be maintenance, backups, synchronization with another server or any of scheduled task.

If you feel your app could turn into a client/server app, you may want to start with using an RDBMS from the start instead of porting it over later.

I have no idea if the example given takes advantage of this or not.

JeffO
  • 36,816
  • 2
  • 57
  • 124
2

Unless you are running an embedded system with low memory and cpu, I don't think that running a server on the background is doing you any harm.

Running a database server locally is fine. The database is meant to access and manipulate data. The network access is a plus, which may or may not be needed. There are some engineering and scientific tools that do this.

Say you are using data, on a local application. Why shouldn't you use a database? as opposed to what?

cauchi
  • 1,192
  • 2
  • 13
  • 22
  • If I were to deploy an application to normal users (say a music player like AmaroK), I would think that having them install MySQL server and having it run in the background is a bit too much of a system requirement and will be something users would not like. But I guess it depends on the application. This is as opposed to using something like SQLite. – 9a3eedi Apr 22 '15 at 07:20
2

It depends on your data abstraction and overall application space, access management requirements, the investment you are planning on data maintenance, urgency of the required prototype, where you are in the learning curve, etc.

If you would like to ensure a tightly integrated database to an application which does not require access from other applications, creating islands of embedded database. The Mozilla Firefox Web Storage implementation with SQLite could be given as an example.

If you need even more efficiency with limited data, a design selection of In-Memory Databases is preferrable.

On the other hand, if you have many applications running multiple queries on the same data, and it requires better structuring of data storage to optimize the performance, a centralized DBMS is required. I will absolutely prefer it for scientific research, when it requires massive amount of data and where the query response time will impact overall user experience drastically.

For the Amarok case, I guess it was a selection of open-source DBMS at that time, before they pick the path of embedded databases.

If there is a specific system definition at your hand, it will be easier to weight cons and pros.

V/r, Umut

Umut Kahramankaptan
  • 129
  • 7