5

I need to insert some default data in my database. I am using Spring Boot with the Flyway integration. For testing, I use H2. For production MySQL will be used.

I have made separate Flyway migration scripts so I can use database specific stuff for the default data (Creating the tables is done in a common script).

For MySQL, I have something like this:

INSERT INTO survey_definition (id, name, period) 
VALUES (0x2D1EBC5B7D2741979CF0E84451C5BBB1, 'disease-activity', 'P1M');

How can I do the same for H2?

I only found the RANDOM_UUID() function, which works, but I need use a known UUID because I am using it as foreign keys in further statements.

Wim Deblauwe
  • 25,113
  • 20
  • 133
  • 211
  • 1
    Using a different database for testing than production seems like a disaster in the making. How can you be sure your code will run correctly on MySQL unless you test on it first? – tadman Apr 08 '16 at 08:04

1 Answers1

2

Best if you use a syntax that works for all databases. I think most databases don't support the 0x syntax. For H2, this would work:

INSERT INTO survey_definition (id, name, period) 
VALUES ('2D1EBC5B7D2741979CF0E84451C5BBB1', 'disease-activity', 'P1M');

But to get a cross-database syntax, you may need to create a user defined function (for example uuid) and then use:

INSERT INTO survey_definition (id, name, period) 
VALUES (uuid('2D1EBC5B7D2741979CF0E84451C5BBB1'), 'disease-activity', 'P1M');
Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132