About the FrontlineSMS Database Engine
FrontlineSMS for Desktop uses the H2 Database engine to store all the app's data. This engine was chosen over alternatives such as MySQL or PostgreSQL because of some of the core design features of H2:
- H2 does not need any server software to be installed on the host machine; the database driver and server are embedded in our FrontlineSMS software
- The entire database can be stored as a single file on disk, as opposed to other database engines which store the data in a less accessible format
These features make it easy for FrontlineSMS for Desktop to be distributed as a single installer file and work across various platforms, as well as simplifying processes like debugging or database backup & restore.
Locating the FrontlineSMS Database on disk
The H2 database files are located in a ".frontlinesms2" subfolder of the home directory of the host computer & account. On Windows machines, this would typically be "C:\Documents and Settings\<current user>\.frontlinesms2", while on Mac it would be "/Users/<current user>/.frontlinesms2". Note that on Unix systems (Mac or Linux), the leading period causes this to be a hidden directory. This may require you to toggle the visibility of hidden folders on your computer.
In this folder, there are config files and the application's logs, as well as the main database file, "prodDb.h2.db". There may be additional files with different suffixes, such as "prodDb.trace.db" or "prodDb.db.lock".
Connecting to the H2 Database
To connect to the db, you will need to download the H2 client Jar file. This requires Java to be installed on your system. The H2 database is locked when in use by FrontlineSMS, so you can only connect through the client when FrontlineSMS is not running.
To launch the client, invoke java from the terminal with the command
java -jar /path/to/jar/file/h2-1.4.181.jar
This will launch the h2 client app and open the UI in a tab on your web browser. To connect to the database, set the JDBC url to point at the frontlinesms2 directory and the database name: jdbc:h2:~/.frontlinesms2/prodDb. Note that you should not include the file extension in the source URL. The username is "sa" and password is blank.
Once you connect, you will be presented with a simple web console through which you can run queries on your database.
Automating tasks with the H2 database
A common request users have is to automatically read records from the database in an external app. This is possible using the Java h2 library, which can read records from the FrontlineSMS2 database and respond to the data found there. Note though that because only one app can connect to the database at a time, this method does not allow you to respond to app data in real time. It is also not possible to trigger outbound SMS by inserting them into the FrontlineSMS database.
Programatically connecting to the H2 database is suitable for one-off batch jobs like exporting all db data to a separate application. For real-time automation in an external app, the FrontlineCloud API is a more appropriate tool:
Backing up and restoring the FrontlineSMS database
Because all h2 database data is stored in the prodDb file, backup and restore can be done by copying this file into or out of the .frontlinesms2 folder. Note that for data consistency, you should only perform backups when the FrontlineSMS app is not running.
Note on database version compatibility
Each new release of FrontlineSMS 2.x for Desktop is backwards compatible with the previous releases. This means that you can successfully restore a backup from any given version, provided that the installed version of FrontlineSMS is the same as or more recent than the version of the installation in which the database was initially created. Note however that once connected, the database will be updated to the latest version, and this database file will no longer work with older installations.