Generate HSQL init scripts for tests with different main datastore

Support
#1

Good day,

I am trying to set up tests to use a local in-memory HSQL database, but I’m having trouble generating the init scripts for my entities since the main datastore is Postgres, not HSQL (there are no additional datastores in this project).

My question

How can I automatically generate the init scripts for HSQL when the real database type is Postgres?

Background

Using Cuba Studio’s Generate Database Scripts

In order to generate the init scripts the official way, I need to change the project’s database type to HSQL, then click Generate Database Scripts in the menu, then change the project’s database settings back. This cannot be done automatically, and seems like a lot of work to do something seemingly simple.

I dug around for the code that this action uses, but it’s part of the Studio plugin and is inaccessible from the project. I had hoped to trigger the HSQL generation from inside the test container, but that doesn’t seem possible.

Using Eclipse Link

I succeeded in using Eclipse Link to generate init scripts when starting a test based on the metadata, but the resulting SQL is slightly different from what Cuba Studio generates:

Cuba:

alter table table1 add constraint FK_TABLE1_FIELD foreign key (FIELD) references table2(ID) on delete SET NULL

Eclipse Link:

ALTER TABLE table1 ADD CONSTRAINT FK_table1_field FOREIGN KEY (field) REFERENCES table2 (ID)

I find the mostly-opposite uppercase/lowercase amusing for some reason, but the problematic part is that Eclipse Link isn’t generating on delete set null. I realize that this is because of @OnDeleteInverse(DeletePolicy.UNLINK), which is a Cuba thing and Eclipse Link has no support for it, nor does Eclipse Link have any alternative that I can find.

This results in failure to delete some entities after a test runs, since a foreign key is pointing to the item that needs to be deleted. I’ve tried using @PreRemove and a BeforeDeleteEntityListener, but it seems those aren’t able to modify the foreign key to set it as null before table2 is deleted.

Any ideas on what can be done?

#2

Hi,

Yes, that’s what you are supposed to do if you plan to support several DBMS in your project.
Also note that in order to generate database scripts, you also need to have reference database, because scripts are generated by comparing project’s data model with database schema. So you need to have PostgreSQL server with some reference database anyway.

My other sincere advice is:
If you are targeting PostgreSQL as production database, you definitely should use PostgreSQL as your project’s database in the development environment!
Databases are not freely interchangeable, they differ in functionality they provide and in SQL implementation details. With your current approach you risk to meet some Postgres-related problems only on target server, too late in the development pipeline.

Sorry, but saying “I’m using HSQL in development, and PostgreSQL in production” sounds for me like “I write my program using C#, but I plan to switch to Java when going to production”.

1 Like
#3

Yes, CUBA Studio generates update scripts differently. It does better job at generating database update scripts at least at some cases, e.g. when you are adding not-null column to existing table, or when you change entity inheritance settings. That’s why CUBA Studio has its own implementation of generating DDL scripts and does not rely on EclipseLink auto DDL generation.

#4

We do have a development environment that uses its own Postgres DB, but the goal was to run something more lightweight locally for tests. Nevertheless, your advice applies all the same.

With that in mind, I found TestContainers (https://www.testcontainers.org/), which seemed quite easy to set up to automatically run a local Postgres instance in Docker, and lets us use the init scripts as generated by Studio (using DbUpdater.updateDatabase()). I am feeling quite comfortable with the result.

Thanks for your advice; it nudged me in the right direction.

1 Like