A Simple HyperSQL Database Server

I'm in the process of rebuilding one of my development boxes at the moment, and when I needed a SQL database engine this weekend to support a bit of quick prototyping, I decided I would take a shot at setting up a HyperSQL database server. This is something I've been meaning to look into for other reasons for a while: HyperSQL is small, fast, flexible, provides good SQL coverage (better, for example, than Apache Derby), and has the ability to tune its behavior to more closely mirror other SQL engines.

Here is a quick recipe for getting a basic server up and running. This should work equally well on both Mac OS X and Linux (I have used this basic recipe on both platforms).

My system already has a Java 7 JRE/JDK installed, which is really the only prerequisite. For the sake of simplicity, I install HyperSQL in /opt/hsqldb and I keep my database-related files in ~/hsqldb-catalogs/. Commands entered at the shell prompt are shown below starting with $, while commands entered in the HyperSQL CLI are shown below starting with >. Note that some of these commands may appear wrapped below; they should each be entered on a single line when used.

1. Download the latest version of HyperSQL (version 2.3.1, as of this writing) from hsqldb.org.
2. Unzip the downloaded file (assumed to be in ~/Downloads/):
   $ cd ~/Downloads
   $ unzip hsqldb-2.3.1.zip
   $ sudo mv ./hsqldb-2.3.1/hsqldb /opt
   $ rmdir hsqldb-2.3.1
3. Create the folder needed for your database files:
   $ mkdir ~/hsqldb-catalogs
4. Create the properties file that provides basic information for the server, including our test database. Save this file as ~/hsqldb-catalogs/server.properties:
   # server.properties
   # =====
   #
   # HSQLDB configuration file
   # Databases:
   server.database.0=file:test/test
   server.dbname.0=testdb
   
   #
   # =====
   # Other configuration:
   # Port
   server.port=9001
   
   # Show stuff on the console (change this to true for production):
   server.silent=false
   
   # Show JDBC trace messages on the console:
   server.trace=false
   server.no_system_exit=false
   
   # Allow remote connections to create a database:
   server.remote_open=false
   #
   # =====
5. Create the configuration file for the CLI HyperSQL tool, describing two different connections to our test database: one as the default system administrative user "SA" and one as a normal administrative user "RON" which we will create in a few minutes. Save this tool as ~/sqltool.rc:
   # =====
   # Connect to testdb on localhost as SA (no password initially):
   urlid localhost-sa
   url jdbc:hsqldb:hsql://localhost/testdb
   username SA
   password
   
   # Connect to testdb on localhost as RON:
   urlid localhost-ron
   url jdbc:hsqldb:hsql://localhost/testdb
   username RON
   password strong-random-password
   # =====
6. Start the server. This will create our test database (a "catalog" in HyperSQL parlance) using the following commands in a terminal window:
   $ cd ~/hsqldb-catalogs
   $ sudo java -cp /opt/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server --props ./server.properties
7. Open a second terminal window and start the HyperSQL CLI, connecting to our test database as user "SA":
   $ java -jar /opt/hsqldb/lib/sqltool.jar localhost-sa
8. As a general rule, I don't like signing in using the system administrator's account, so the first thing is to create an administrative user account for my use:
   > create user RON password 'strong-random-password';
   > grant DBA to RON;
   > \q
9. Make sure you have your "strong-random-password" recorded correctly in ~/sqltool.rc, and restart the HyperSQL CLI:
   $ java -jar /opt/hsqldb/lib/sqltool.jar localhost-ron

You're done. You have a fully-functional database server, providing a single database called "testdb" for your use.

If you want to play with sample data, consider the following (while connected as either "SA" or your administrative account):
> create schema sampledata authorization DBA;
> set schema sampledata;
> \i /opt/hsqldb/sample/sampledata.sql

When you're ready to shut down the server, you can do it while connected to the server as a user with DBA role:
> shutdown;

Alternatively, you can shut down the server from the command-line:
$ java -jar /opt/hsqldb/lib/sqltool.jar --sql 'shutdown;' localhost-ron

Some final thoughts:

1. Read the docs: the user guide and the utilities guide for HyperSQL are good, and provide a fairly thorough coverage of what HyperSQL is capable of and how to use it, including connecting via JDBC.
2. You almost certainly want to set a password for the default "SA" user, and remember to update your ~/sqltool.rc file when you do.
3. Protect ~/sqltool.rc -- obviously this file, with information about catalogs, users, and passwords needs to be protected.