Script to Generate DB scripts for database update

Support
#1

I am new to cuba-platform and I am looking into updating the existing postgresql database using scripting as an alternative to use Run->Update database option from within Studio. We want to automate this process.

In the manual I have found these two options:

  1. using gradlew updateDb as per this article.

  2. using the dbupdate shell script.

I do not understand the difference between them since apparently they both run pre-generated scripts. I know these scripts can be generated in Studio using the Generate DB scripts option.

If what I wrote here is correct then my question is: what is the scripting alternative for Generate DB scripts ? Or, in other words, how can the scripts be generated from outside Studio?

1 Like
#2

hi,

so when you are talking about applying db update scripts, the most obvious solution is to use the application property cuba.automaticDatabaseUpdate = true. This way, when the application is deployed and has DB scripts included that are not yet applied to the database, the server will execute the scripts and mark the scripts as applied, so that next time the scripts will not be applied again.

In this scenario, you have already achieved 100% automation, because there is just nothing to do. You as a dev only create the SQL scripts through studio (or manually) and they will automatically get picked up at the next deployment.

The other alternatives you described are only for this case that a DB admin wants to manually look at the scripts or you can’t have downtime for DB updates etc.
In this case the shell script is probably more usable because of the fact that gradle is normally not available (as well as the build.gradle). on the production server.

I hope this helps.

Bye
Mario

#3

Thank you Mario for clarifying this for me and for your detailed answer, it is very helpful.

I would like to ask about the part where, as developer I am creating the SQL scripts through studio. Do I have this functionality outside of studio, a script to ‘Generate DB scripts’?

Thank you,
Gabriel

#4

Hi Gabriel,

Do I have this functionality outside of studio, a script to ‘Generate DB scripts’?

No, it’s in Studio only, one of its selling points :slight_smile:

#5

Hello Konstantin,

I have to revisit this post because we are in need for an automation for generating DB update scripts from outside Studio. We have already brought a license and wondering if you can prepare a license based utility that can do just that: Generate DB scripts. Which we can later be used with the already existing dbupdate shell script.

You are already providing similar approaches for gradle assemble where license and password are provided through arguments.

I hope you guys can help with a solution.

#6

Hi Gabriel,

This is currently impossible, as the script generation mechanism highly depends on the project model parsed by Studio.

Maybe in the future we will add this functionality to CLI but I cannot make any promises now.

#7

I realize this topic is old, but I would like to freshen it. Has there been any movement on this item in the cuba-cli or other area. I have done a quick perusal of the cuba-cli project and did not see any updates to address this item.

And, to be clear, I am a realtive newby to cuba-platform.

Here is my concern: What happens when our deployed cuba-platform application is running in production, with hundreds of entities tied to millions of records across a clustered database, deployed in the cloud, and we realize that our data model needs some updates. At this point we have 5-6 developers each updating a variety of entities, and perhaps there are even overlaps to the entity definitions.

I can see how this could easily lead to the database scripts getting out of sync amongst the different developers’ branches and PR’s, and wondered what the solution would be to reset the scripts. What’s more, I can also see that there would be an increasingly large number of these scripts, and at some point it may be nice to refresh the scripts, kind of start from scratch with the current model.

It seems to me that deleting the core/db directory locally and then having 1 person do a “Generate Database Scripts” from the studio to get the current model is the only way to do this.

How difficult would it be to provide this via the cuba-cli – so that we can have this be part of our automated ci/cd pipeline?

Does this seem like an unreasonable ask?

#8

Unfortunately, CUBA CLI cannot analyze the data model - only Studio is able to do it. That’s why only Studio can generate a diff between the data model and database schema.