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
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 incriminating 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
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