Introduction
H2 is a lightweight database server written in Java. It can be embedded in Java applications, or run as a standalone server.
In this tutorial, we'll review why H2 can be a good option for your projects. We'll also learn how to integrate H2 with Node.js by building a simple Express API.
The Features of H2
H2 was built with performance in mind.
"H2 is a combination of: fast, stable, easy to use, and features".
Although H2 is prominent mainly because it can be embedded in Java applications, it has some interesting features that also apply to its server version. Let's see some of them next.
Size and Performance
The .jar file used for the server version is around 2MB. We can download it from the H2 site, bundled with extra scripts and documentation. If we search in Maven Central though, we can download the .jar file on its own.
H2 performance shines in its embedded version. Even so, the official benchmark shows that its client-server version is also impressive.
In-Memory Databases and Encryption
In-memory databases are not persistent. All data is stored in memory, so speed is greatly increased.
The H2 site explains that in-memory databases are particularly useful when prototyping, or when using read-only databases.
Encryption is another useful feature to protect data at rest. Databases can be encrypted with the AES-128 algorithm.
Other Useful Features
H2 also provides a cluster mode, the ability to run multiple servers and connect them together. Writes are done on all servers at the same time, while reads are done from the first server in the cluster.
H2 surprises for its simplicity. It provides several useful features and it's easy to set up.
Let's start an H2 server in preparation for the following sections:
$ java -cp ./h2-1.4.200.jar org.h2.tools.Server -tcp -tcpAllowOthers -tcpPort 5234 -baseDir ./ -ifNotExists
The arguments that start with tcp enable communication to the server. The ifNotExists argument allows the database to be created when accessing it for the first time.
Description of the API and General Diagram
Let's suppose we are writing an API to register all exoplanets found to date. Exoplanets are planets found outside our Solar System, orbiting other stars.
If you're not yet familiar with the creation of REST APIs, read our Building a REST API with Node and Express!
This is our simple API definition, a CRUD for one resource:
This definition along with the rest of the code we'll see next is available in this GitHub repo.
This is how our application will look like at the end of this tutorial:
Left of the diagram we see the API Client. That client can be the "Try it out" function of the Swagger Editor, or any other client, like Postman or cURL.
On the other end we find the H2 database server, running on TCP port 5234 as explained above.
Finally, our application in the middle is composed of two files. The first one will have the Express app that will answer all REST API requests. All endpoints we described in the definition above will be added to this file.
The second file will have the persistence, functions to access the database to execute the CRUD operations, using the JDBC package.
Database Schema
To store the Exoplanet resource to an H2 database we should write the basic CRUD functions first. Let's start with the creation of the database.
We use the JDBC package to access databases through JDBC:
const JDBC = require('jdbc');
const jinst = require('jdbc/lib/jinst');
if (!jinst.isJvmCreated()) {
jinst.addOption("-Xrs");
jinst.setupClasspath(['../h2-1.4.200.jar']);
}
let h2 = new JDBC({
url: 'jdbc:h2:tcp://localhost:5234/exoplanets;database_to_lower=true',
drivername: 'org.h2.Driver',
properties: {
user : 'SA',
password: ''
}
});
let h2Init = false;
function getH2(callback) {
if (!h2Init)
h2.initialize((err) => {
h2Init = true;
callback(err);
});
return callback(null);
};
function queryDB(sql, callback) {
h2.reserve((err, connobj) => {
connobj.conn.createStatement((err, statement) => {
if (callback) {
statement.executeQuery(sql, (err, result) => h2.release(connobj, (err) => callback(result)));
} else {
statement.executeUpdate(sql, (err) => h2.release(connobj, (err) => { if (err) console.log(err) }));
}
});
});
};
module.exports = {
initialize: function(callback) {
getH2((err) => {
queryDB("CREATE TABLE IF NOT EXISTS exoplanets ("
+ " id INT PRIMARY KEY AUTO_INCREMENT,"
+ " name VARCHAR NOT NULL,"
+ " year_discovered SIGNED,"
+ " light_years FLOAT,"
+ " mass FLOAT,"
+ " link VARCHAR)"
);
});
},
The initialize() function is simple enough because of the helper functions written beforehand. It creates the exoplanets table if it doesn't exist already. This function should be executed before our API starts receiving requests. We'll see later where to do that with Express.
The h2 object gets configured with the connection string and credentials to access the database server. It is simpler for this example, but there is room for improvement regarding security. We could save our credentials elsewhere, like environment variables for example.
Also, we needed to add the path to the H2 jar file on the method jinst.setupClasspath(). This is because the JDBC package needs a driver to connect to H2, org.h2.Driver.
The JDBC connection string ends in /exoplanets;database_to_lower=true. This means that when connecting for the first time a database called exoplanets will be created. Also, the table and column names will be saved in lowercase. This will simplify the API so no conversion of property names will be needed.
The queryDB() function uses the JDBC library methods to access the database. First, it needs to reserve() a connection to the database. The next steps are to createStatement() and then executeQuery() if a result is expected, or executeUpdate() otherwise. The connection is always released.
All functions above may return an error. To simplify this example all errors are left unchecked, but on a real project we should check them.
The getH2() function returns an object that represents the database. It will create that object only once, using the same mechanism Singleton classes use to return only one instance always.
Check out our hands-on, practical guide to learning Git, with best-practices, industry-accepted standards, and included cheat sheet. Stop Googling Git commands and actually learn it!
Let's now validate user data and allow them to perform CRUD operations.
CRUD Database Functions
Let's make the required functions to allow this app to perform CRUD operations on exoplanets. We'll add them to module.exports so that we can reference them from other files easily and create a persistence.js helper module that we can use:
module.exports = {
getAll: function(callback) {
getH2((err) => queryDB("SELECT * FROM exoplanets", (result) => {
result.toObjArray((err, results) => callback(results));
}));
},
get: function(id, callback) {
getH2((err) => queryDB(`SELECT * FROM exoplanets WHERE id = ${id}`, (result) => {
result.toObjArray((err, results) => {
return (results.length > 0) ? callback(results[0]) : callback(null);
});
}));
},
create: function(exoplanet) {
getH2((err) => {
columns = Object.keys(exoplanet).join();
Object.keys(exoplanet).forEach((key) => exoplanet[key] = `'${exoplanet[key]}'`);
values = Object.values(exoplanet).join();
queryDB(`INSERT INTO exoplanets (${columns}) VALUES(${values})`);
});
},
update: function(id, exoplanet) {
getH2((err) => {
keyValues = []
Object.keys(exoplanet).forEach((key) => keyValues.push(`${key} = '${exoplanet[key]}'`));
queryDB(`UPDATE exoplanets SET ${keyValues.join()} WHERE id = ${id}`);
});
},
delete: function(id) {
getH2((err) => queryDB(`DELETE FROM exoplanets WHERE id = ${id}`));
},
};
Both get() and getAll() functions query the database to return one or more exoplanets. The API will return them directly to the API client.
All functions are mainly SQL queries, but create() and update() deserve more explanation.
The INSERT SQL statement can receive column and values separated, in the form INSERT INTO table (column1Name) VALUES ('column1Value'). We can use the join() method to generate one string of columns separated by commas, and do something similar to join all values we want in the create() function.
The UPDATE SQL statement is a bit more complex. Its form is UPDATE table SET column1Name = 'column1Value'. So we need to create a new array in the update() function to store the values in this format and join() them later.
Let's save all database functions on its own file, persistence.js, so we can add some context when we call the functions in the API file, like this:
const persistence = require('./persistence');
persistence.getAll();
Joi Schema
As a rule of thumb, we should always validate what a user sends before using it, for example when the user attempts to create a resource.
Some packages make this task easy. We'll use Joi to accomplish validation.
First, we need to define a schema of our resource, a definition of properties and their types. It reminds us of the SQL CREATE statement we defined before:
const Joi = require('joi');
const exoplanetSchema = Joi.object({
id: Joi.number(),
name: Joi.string().required(),
year_discovered: Joi.number(),
light_years: Joi.number(),
mass: Joi.number(),
link: Joi.string().uri()
});
const options = { stripUnknown: true };
// Inupt to validate
let input = { /*...*/ };
const { value } = exoplanetSchema.validate(input, options);
Each type will enforce some validation. For example, the link property needs to look like a URI, and the name is required().
Later we can validate a resource by using the exoplanetSchema.validate(input, options) method. This method will return an object with an error property with validation errors if there were any, and a value property with the processed object. We will use this validation when creating and updating an object.
To add robustness to our API, it would be nice to ignore and discard any extra property not included in our schema. This is achieved in the definition above by setting the stripUnknown option to true.
REST API with Express
We'll use the Express package to create our REST API. And as we've just seen, we'll also use Joi to validate resources.
Let's set up a regular Express server:
const express = require('express');
const cors = require('cors');
const app = express();
app.use(cors());
app.use(express.json());
The app variable is our API, empty for now. Express allows extending its functionality through the use of middleware, functions that can modify the requests and responses of our API. In this case, we are using two middleware.
First, cors() will allow other browser applications to call our API. This includes the Swagger Editor we may use to test our API later. If you'd like to read more about Handling CORS with Node.js and Express, we've got you covered.
Second, we add the express.json() middleware to enable parsing of JSON objects in the body of requests.
Let's now add a few endpoints to the API. We'll start with post() and put(), as they use the Joi validation explained in the last section:
app.post('/exoplanets', (req, res) => {
delete req.body.id;
const { error, value } = exoplanetSchema.validate(req.body);
if (error)
res.status(405).send(error.details[0].message);
persistence.create(value);
res.status(201);
});
app.put('/exoplanets/:id', (req, res) => {
delete req.body.id;
const { error, value } = exoplanetSchema.validate(req.body);
if (error) {
res.status(405).send(error.details[0].message);
}
persistence.get(req.params.id, (result) => {
if (result) {
persistence.update(req.params.id, value);
res.status(201);
} else {
res.status(404);
}
});
});
Express supports one function per HTTP verb, so in this case, we have post() and put() as two functions.
In both functions, the resource is validated first, and any error is returned to the API client. To keep this code simple, only the first validation error is returned in that case.
put() also checks if the resource exists by attempting to get it from the database. It will update the resource only if it exists.
With the post() and put() functions that require validation out of the way, let's handle the get() methods when users would like to take a look at the exoplanets, as well as the delete() function used to remove an exoplanet from the database:
app.get('/exoplanets', (req, res) => persistence.getAll((result) => res.send(result)));
app.get('/exoplanets/:id', (req, res) => {
persistence.get(req.params.id, (result) => {
if (result)
res.send(result);
else
res.status(404);
});
});
app.delete('/exoplanets/:id', (req, res) => {
persistence.get(req.params.id, (result) => {
if (result) {
persistence.delete(req.params.id);
res;
} else {
res.status(404);
}
});
});
Having defined all endpoints, let's set up the port on which the application will listen for requests on:
app.listen(5000, () => {
persistence.initialize();
console.log("Exoplanets API listening at http://localhost:5000");
});
The callback above will be called only once when starting the server, so it's the perfect place to initialize() the database.
Conclusion
H2 is a useful database server, performant and easy to use. Although it's a Java package, it also runs as a standalone server, so we can use it in Node.js with the JDBC package.
In this tutorial, we first defined a simple CRUD to illustrate how to access the database, and which functions are available. After that, we defined a REST API with Express. This helped us to have a more complete idea on how to receive resources and save them to H2.
Although several concepts were omitted for the sake of brevity, like authentication and paging, this tutorial is a good reference to start using H2 in our Express projects.
