H2: generate insert scripts initialization script

Question

I have full h2 database with lots data in it. I want to launch integration tests agains that data.

Question1: Is it possible to generate *.sql insert files/scripts from full h2 database?

I've trie SCRIPT TO 'fileName' as described here. But it generates only CREATE/ALTER TABLE/CONSTRAINT queries, means creating schema without data.

If answer to the first question is - "Impossible", than:

Question2: Are *.sql insert files the only way to insert initial dataset into h2 db for integration tests?

Answer 1

Question1: Is it possible to generate *.sql insert files/scripts from full h2 database?

I have just tested with one of my H2 file databases and as result the export exports both structure and data.
I tested with the 1.4.193version of H2.

The both ways of exporting work :

• The SCRIPT command from H2 console
• org.h2.tools.Script tool from command line.

1) I have tested first the org.h2.tools.Script tool as I had already used it.

Here is the minimal command to export structure and data :

java -cp <whereFoundYourH2Jar> org.h2.tools.Script -url <url> 
     -user <user> -password <password>

Where :

• <whereFoundYourH2Jar> is the classpath where you have the h2.jar lib (I used that one which is my m2 repo).
• <url> is the url of your database
• <user> is the user of the database
• <password> the password of the database

You have more details in the official help of the org.h2.tools.Script tool :

Creates a SQL script file by extracting the schema and data of a database.
Usage: java org.h2.tools.Script <options>
Options are case sensitive. Supported options are:
[-help] or [-?]    Print the list of options
[-url "<url>"]     The database URL (jdbc:...)
[-user <user>]     The user name (default: sa)
[-password <pwd>]  The password
[-script <file>]   The target script file name (default: backup.sql)
[-options ...]     A list of options (only for embedded H2, see SCRIPT)
[-quiet]           Do not print progress information
See also http://h2database.com/javadoc/org/h2/tools/Script.html

2) I have tested with SCRIPT command from the H2 console. It also works.

Nevertheless, the result of the SCRIPT command may be misleading.
Look at the official documentation :

If no 'TO fileName' clause is specified, the script is returned as a result set. This command can be used to create a backup of the database. For long term storage, it is more portable than copying the database files.

If a 'TO fileName' clause is specified, then the whole script (including insert statements) is written to this file, and a result set without the insert statements is returned.

You have used the SCRIPT TO 'fileName' command. In this case, the whole script (including insert statements) is written to this file and as result in the H2 console, you have everything but the insert statements.
For example, enter the SCRIPT TO 'D:\yourBackup.sql' command (or a Unix friendly directory if you use it), then open the file, you will see that SQL insertions are present.

As specified in the documentation, if you want to get both structure and insert statements in the output result of the H2 console, don't specify the TO argument. Just type : SCRIPT.

Question2: Are *.sql insert files the only way to insert initial dataset into h2 db for integration tests?

As a long time discussed :) you can with DBunit dataset (a solution among others).