H2 Relational Database

H2 is a database distributed as library, making it a ideal for a self-contained development environment for a Clojure application with a relational database. Data is persisted to mv.db files as SQL queries are executed in Clojure.

H2 database main features include

Very fast, open source, JDBC API
Embedded and server modes; in-memory databases
Browser based Console application
Small footprint: around 2 MB jar file size

Whilst H2 could be used for very small production web applications, it is recommended only as a development time database.

Using h2 in the REPL

H2 works with next.jdbc, the defacto relational database library for Clojure.

Including H2 in Clojure projects

next.jdbc is highly recommended library for SQL queries in Clojure

deps.edn projects
Leiningnen projects

To use H2 database as only a development database, add an :extra-deps entry to include the H2 library in a :dev alias in the project deps.edn file.

{:deps
 {org.clojure/clojure    {:mvn/version "1.10.1"}
 seancorfield/next.jdbc {:mvn/version "1.1.569"}}}

{:alases
  {:dev
   {:extra-deps {com.h2database/h2 {:mvn/version "1.4.200"}}}}}

Alternative, if using practicalli/clojure-deps-edn configuration, use the :database-h2 alias when starting the REPL to include the H2 library on the class path.

Edit the project.clj configuration file and add the H2 library to the :dev-dependencies section to run H2 as the development only database.

(defproject project-name "1.0-SNAPSHOT"
  :description "Database application using next.jdbc with H2 as development database"
  :url "http://practicalli.github.io/clojure/"
  :dependencies [[org.clojure/clojure "1.10.1"]
                 [seancorfield/next.jdbc "1.1.582"]]

  :dev-depencencies [[com.h2database/h2 "1.4.200"]])

Auto-increment values in H2 database

The IDENTITY type is used for automatically generating an incrementing 64-bit long integer in H2 database.

CREATE TABLE public.account (
  id IDENTITY NOT NULL PRIMARY KEY ,
  name VARCHAR NOT NULL,
  number VARCHAR NOT NULL,
  sortcode VARCHAR NOT NULL,
  created TIMESTAMP WITH TIME ZONE NOT NULL);

No need to pass a value for our pkey column value as it is being automatically generated by H2.

INSERT INTO public.account ( id, name, number, sortcode, created)
VALUES ( ? , ? , ? , ? );

References

H2 Database website

Resources

next.jdbc documentation and next.jdbc db-types list
H2 Database website
SQL Constraints - W3Schools.com
Purpose of contraint naming - Stack Overflow
seancorfield/honeysql - SQL as data structures
stack overflow - auto increment id in h2 database

H2 Database