Setting up a 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. Here’s a quick recipe for getting a basic server up and running, and it should work on both Mac OS X and Linux.

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 (more complete, for instance 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 with the java command available in the shell’s PATH, 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 where the server will store its 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:
    # File: 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
    # Don't show JDBC trace messages on the console:
    server.trace=false
    server.no_system_exit=false
    # Do not 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:
    # File: ~/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 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 “sqltool”, 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 account, so the first thing I typically do is 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 thorough coverage of what HyperSQL is capable of (which appears to be quite a bit) 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.

4 thoughts on “Setting up a HyperSQL Database Server”

  1. Hi there,

    Thanks for posting instructions on how to setup HSQLDB as a server for Mac OSX. Yours appear to be the ONLY documentation for this online!

    I am VERY new to the terminal, so I’m a bit confused. I understand that the commands that start with a dollar sign ($) are to be entered in the shell, and I understand that commands that start with a greater than sign (>) are entered in the CLI, but what about the commands that start with a pound sign (#)? I’ve searched high and low on the internet, but I can’t find any reference to the pound sign as a prompt.

    If there’s a beginner’s book or web site that you’d recommend, I’d love to know that as well.

    Thanks so much!

    -Andy Blubaugh

  2. @Andy: Glad you’ve found it useful. Part of my thinking behind putting together this post was that I also could not find much on the subject, and have long been a fan of HyperSQL. The chunks in steps 4 and 5 are the content of the two files you will need to create. The server.properties file (step 4) identifies the catalogs (or databases) the server will make available, and the sqltool.rc file (step 5) provides connection information for the HyperSQL CLI. Within those two files, lines starting with the “#” character are comments. In both cases, those lines are ignored in terms of actually causing anything to happen but are there for explanation. The “# =====” lines are just my own way of noting the end of the file; there’s nothing magic about them.

    As far as books or other references for HyperSQL, I’m afraid I don’t have much to recommend. The reference manual for HyperSQL is reasonably well-written and reasonably complete. I find the PDF version more usable than the on-line HTML version simply because it is searchable. Most of what I’ve learned in terms of getting this set up has been based on noodling through the manual and just trying to get stuff to work. I worked through this same sort of process with Apache Derby in the past; the process of getting it set up is similar but a little more straightforward. I found fairly quickly, however, that I wanted the broader SQL syntax support of HyperSQL.

  3. @sudhir I have Java installed in whatever it’s default location is on the system (it varies for OS X and Linux); the location doesn’t matter as long as it is on the shell’s path. I touch on the other folders I use for the HSQLDB jar files and catalogs (the databases) in the third paragraph. Does that help or have I completely misunderstood your question?

Comments are closed.