Embedded database to distribute with an open source webapp

Question

I'm looking to build an open source website project that will require a small database backend. I'm leaning towards using SQLite since the website will be simple, low traffic, and the users of the website will only be reading. Only the site admin will be updating.

I wonder though, sharing a SQLite file on GitHub, is that the best way? It's kind of hard to easily view the data. Is there a better data store solution for open source projects? Is it best to just include the data in a second format, like CSV, alongside the database?

Answer 1

Several open source webapps (namely Alfresco and Liferay) distribute a small amount of data with the webapp, to make it easy to get started with. A beginner can just run the start script, and the webapp is up and running already with sample data.

Here is how they do:

1. Write a standard SQL script that creates the data.
2. Write some code that creates an empty HyperSQL and loads the SQL from step 1 into it.
3. Commit both the SQL script and the code into GitHub.
4. When a webmaster downloads the webapp and runs it, the first time it will create the database. The webmaster can then add more data.

Please note that the HyperSQL database has a memory cache, so each read does not mean a disk read access. Each write means a disk write access, though.

Answer 2

Your question is not clear, but it seems your goal is to distribute a relatively fresh copy of your database’s data alongside the source code of your app that accesses that database. But you do not explain your motivation or goal. I imagine you either want machines to read that data, or you want people to read that data.

Either way, consider distributing the data separately from the source code. Combining them is unusual and seems confusing to me.

Machine-readable

If your goal is convenience in establishing an instance of the database for the programmer using your source code to build and deploy for themselves, then you should include the database’s data files.

You could do this win SQLite. But beware that SQLite is indeed quite “lite”, meant more as a slightly better alternative to writing files, it is not meant to be a serious database. You have not stated the size and shape of your data, so I cannot advise if SQLite fits your needs or not.

A more serious database you might consider is the H2 Database Engine. It is written in Java, and so requires a JVM. H2 stores data to a single file that your could easily copy. H2 can run embedded within an app, or can be run separately as a database server.

Human-readable

If your goal is simply to let people peruse a recent copy of the data, then you need to export the data from the database to a human-readable text format.

If the goal is simply perusing, then write out web pages using basic HTML using the table tag. And perhaps a bit of CSS. Use the programming language of your app to generate these text files.

If you want the user to make use of the data such as opening in a spreadsheet, then use a common format such as Tab-delimited (TSV) or Comma-separated Values (CSV). You might want to use a library to assist in the writing of such files, like Apache Commons CSV in Java (which also writes TSV).

You will likely want to write out one file for each table in your database. Beware of memory limits in web browsers and spreadsheet apps; you may need to break a very large table into multiple files.