Simple Table Export

script simple columns nopasswords nosettings table <table>

Also useful for generating INSERT statements for <table>.

TLS

Set up a kestore and register it with the H2 server JVM using the details at this page.

Then add the -tcpSSL command-line option in addition to the -tcp option.

Change JDBC URL to:

jdbc:h2:ssl://localhost/database

CSV Import/Export

H2 has good support for reading/writing quoted CSV files via the csvread and csvwrite functions.

Export

call csvwrite('/path/to/export.csv', 'select * from table_to_export');

Import

Note: Importing directly into H2 rather than generating insert statements from the CSV is easier/safer since quote characters in the text are automatically handled (which I always forget about until something explodes).

Using a temporary table allows us to check/compare data if our ultimate destination is a table that already contains data.

-- create temporary table from CSV data
create temporary table import_table as select * from csvread('/path/to/import.csv');

-- import data into temporary table
insert into import_table select * from csvread('/path/to/import.csv');

Linked Tables

Useful for merging data without having to mess around with ETL tools.

CREATE LINKED TABLE some_table_linked('org.h2.Driver', 'jdbc:h2:/path/to/database/file', 'sa', '', 'target_table') READONLY

Drop the tables after merging since there should be no reason to keep the links after that point, and it avoids disasters if the target databases move and prevent the database from being opened.

Mixed Mode (Auto Server)

Can be used to create embedded databases that will automatically start the TCP server when needed (handy if you want to poke around in the DB via the console while the application is still running). Only works for persistent databases (can't be used with mem DBs).

Create a persistent database as per usual. Then use the following in the application's JDBC URI to connect to the embedded database.

jdbc:h2:/path/to/db;AUTO_SERVER=TRUE;IFEXISTS=TRUE

All applications/clients need to use the AUTO_SERVER=TRUE option in their connection URI.

You can spin up a new H2 console pointing at the DB using the following:

java -cp /path/to/h2.jar org.h2.tools.Console -web -browser -user sa -password password -url "jdbc:h2:/path/to/db;AUTO_SERVER=TRUE;IFEXISTS=TRUE"

Embedded databases run with IFEXISTS=FALSE by default, so the database will be created by the first connection if there is no database at the given path. It is safer to always use IFEXISTS=TRUE to prevent the auto-creation behaviour — most problems I have with H2 embedded DBs are because I have the wrong connection somewhere causing a new database to get created and then not realising that I have two independent databases running.

Transient Servers

Running transient databases (mem mode) is handy for testing, or running student code without crapping up your baseDir folder. You can use mem databases in client-server mode — this is useful to allow you poke around in the database via the console to see what the application is doing. You do have to do things in the right order to make this work:

Create the database from the H2 console. There is no need to use the 'Create a new database' dialog — you can just type in the URI and hit connect. The URI should look like:
```
 jdbc:h2:mem:dbname
```
This creates the database. Whatever username/password you use when you connect is what is used for the admin account.
Create tables in the DB via the console (unless the application does it for you).
Applications that connect to the database use the following URI:
```
jdbc:h2:tcp://localhost/mem:dbname
```
The application should use the same username/password as used when connecting in the console.

The database only exists while the console is connected — if you disconnect then the database is killed.

Query Analysis

The following template shows how to analyse query plans and performance in H2 (thanks Nigel).

set cache_size 0;

set query_statistics true;

@loop 1000 explain analyze
select blah blah blah ...;

select sql_statement,
    round(max_execution_time, 1) as max_time,
    round(average_execution_time, 1) as average_time
from information_schema.query_statistics;

set query_statistics false;

PostgreSQL Mode

Makes H2 behave close enough to Postgres that you should be able to switch between the two (assuming you stick to common functions and operators).

Add the following to the JDBC URL when creating the database (can be used in the right-click Create a new database dialog, or when first connecting to a new database when IFEXISTS=false):

;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH