HSQL Database Engine: Architecture, Features, and Best Practices

Getting Started with the HSQL Database Engine: A Beginner’s Guide

What HSQLDB is

HSQLDB (HyperSQL Database) is a lightweight, open-source relational database written in Java. It supports in-memory and disk-based tables, full SQL-92/SQL:2008 features, transactions, stored procedures, and JDBC connectivity, making it suitable for embedded applications, testing, and small-to-medium production systems.

Quick use cases

• Embedded desktop or desktop-app storage
• Unit and integration testing (fast in-memory mode)
• Lightweight server for small services
• Education and prototyping

Editions and modes

• In-memory mode: Fast, ephemeral, data lost on shutdown unless persisted.
• Server mode (TCP/HTTP): Multi-client access like a typical RDBMS.
• Disk-based (file) mode: Persistent storage using .script/.data files or cached tables.

Installation

1. Download the HSQLDB distribution JAR from the official site or Maven Central.
2. Add the JAR to your project’s classpath or as a Maven/Gradle dependency:
   • Maven:

     Code
     Copy CodeCopied
     
     org.hsqldb
       hsqldb
       2.7.2
     
     

3. Optionally unpack the distribution to use the provided server and client scripts.

Starting a simple server (quick example)

Run the server from command line:

Code
Copy CodeCopied
java -cp /path/to/hsqldb.jar org.hsqldb.server.Server –database.0 file:mydb –dbname.0 xdb 

Connect with JDBC URL:

Code
Copy CodeCopied
jdbc:hsqldb:hsql://localhost/xdb 

Or use in-memory mode in code:

java
Copy CodeCopied
String url = “jdbc:hsqldb:mem:mydb”;
Connection c = DriverManager.getConnection(url, “SA”, ””);

Basic SQL examples

• Create table:

sql
Copy CodeCopied
CREATE TABLE person (
  id INTEGER IDENTITY PRIMARY KEY,
  name VARCHAR(100),
  created TIMESTAMP DEFAULT CURRENTTIMESTAMP
);

sql
 ffON2NH02oMAcqyoh2UU MQCbz04ET5EljRmK3YpQ CPXAhl7VTkj2dHDyAYAf” data-copycode=“true” role=“button” aria-label=“Copy Code”>Copy CodeCopied
INSERT INTO person (name) VALUES (‘Alice’);

sql
Copy CodeCopied
SELECT id, name, created FROM person;

java
Copy CodeCopied
conn.setAutoCommit(false);
... // statements
conn.commit();

sql
Copy CodeCopied
SHUTDOWN;