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?

@Berger correct me pls if I'm wrong, but that reference is about configure Spring with embedded h2. They use `db/sql/insert-data.sql` script to init data. My question is about how to auto-create `db/sql/insert-data.sql` script from existent database — VB_, Dec 12 '16 at 14:00
If you use DBUnit you could inject dataset that you could create from your own queries. I discuss about it in this post : http://stackoverflow.com/questions/40884098/hibernate-is-it-possible-to-save-cascade-only-on-test/40884456#40884456 — davidxxx, Dec 12 '16 at 14:02
@davidxxx I can inject initial data without DBUnit with plain sql. The problem is that dataset is pretty big, and I'll spend at least few days to describe init dataset. — VB_, Dec 12 '16 at 14:04
And generating the init dataset with DBunit would be not a alternative ? — davidxxx, Dec 12 '16 at 14:28
@davidxxx what the benefit of using DBUnit? That means writing the same script, but instead of well known sql syntax I'll get xml.I gonna use init.sql script plus Spring's \@Transactional annotation on tests. Moreover DBUnit has some drawbacks, you can see http://stackoverflow.com/questions/3950002/is-there-a-dbunit-like-framework-that-doesnt-suck-for-java-scala — VB_, Dec 12 '16 at 14:32
I don't want to sell the solution but I think that you don't know very well DBunit and the extensibility that it provides. You don't need to write dataset it if you generate the script from a SQL query with DBunit. If you enrich DBunit, you can perform many things. For example,you can add in JUnit listener for both populating data (fixture) but also doing assertion. After if you use it raw, indeed, you have limitations. In the post, the accepted response of Pascal indicates rather that it is a good tool. — davidxxx, Dec 12 '16 at 14:42
@davidxxx ok, DBUnit definitely has some benefits. But I can't generate the dataset from SQL script, because I haven't got SQL script :(( This question is about how to generate SQL script (or DBUnit if you want) without writing it manually — VB_, Dec 12 '16 at 14:44

davidxxx · Accepted Answer · 2016-12-12T23:56:36.067

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).

H2: generate insert scripts initialization script

1 Answers1