Back to articles list
A starting point for keeping your database management hassle-free is a good database modeling tool. A good ER diagram is not only about a pretty picture; it can actually carry a lot of secondary data. For example, it may contain all necessary column constraints or additional SQL scripts to be run at a specified time. A good database modeling tool lets you create a physical ER diagram, oversees and validates your model (including your custom data types), and also lets you generate SQL scripts to set up your database or adjust it to the changes in the model. There's more to look for in ER diagram tools – check them all out here.
As a good database modeling tool, Vertabelo can generate many SQL scripts for you, from the ones that create your database, the ones that help you with migrating and altering the existing instance, to those which can drop data from a table. It can really help you automate some processes for database management, especially when you use different database engines and need to switch between different DDL syntaxes. Let Vertabelo handle those differences and save you the mayhem.
CREATE Scripts
Let's now discuss the first type of scripts: the CREATE scripts. They might be called “database genesis" since they set the database up for you. They contain SQL CREATE statements bundled with some ALTERs that add constraints to the tables. They will also contain instructions for creating sequences, views, and other elements you've added to the ER diagram. In Vertabelo Modeler, you can fine tune what you want to generate; you can get just the tables or just the views.
You can also select a few elements from the diagram, then use the “Only selected elements” checkbox to have the script generated only for those elements. You can read more on how to generate the script and how to adjust it to your needs here. After generating the script, you can either save it for later to your Drive at Vertabelo or download it. Here is a piece of an example script:
Vertabelo lets you take a sneak peek at what will be generated for every element of your diagram. Just click on the element you want to verify, and click on the “SQL Preview” button in the panel on the right.
DROP Scripts
Within the same pop-up for the CREATE scripts, you can have the DROP scripts generated. They will help you clear all elements present in the ER diagram from your database. And as for the CREATE SQL scripts, you can decide what types or elements you want to drop.
Here are some parts of the example script:
Migration Scripts
Great! Let's discuss how we can reflect model updates in your database. Vertabelo can generate a migration SQL script between two different versions of the same model or even between two different models. Start by going to your Drive and selecting the model for which you want to create a migration script. Then, you can either right-click on it and pick “Model migration” from the menu, or click on the dropdown menu from the right-hand panel to select “Generate migration” from this version.
Then the new view will open, letting you pick the versions you want to migrate to and from. It's a good practice to name the versions before migrating; it'll help you orient yourself later with what and when has been done. On this screen, you can also check the “Show all models” checkboxes to generate the migration script between two different models. It's useful when you store your current database model in one model and the newly developed in another. You can easily go back and forth between any two versions, so you can roll back to the previous version easily.
Remember that the main focus of the migration scripts is the structure and not data. So, if the way to handle the data is not defined, Vertabelo will generate a list of warnings when your input is required. When you change the type of a column, sometimes the conversion is straightforward, and in other times you need to tell the database explicitly how you want to have the data converted. As the process is structure-oriented, Vertabelo will probably drop them and create new ones when renaming the elements. So, if you want to save the data, you should replace the proper parts of the migration script with the renaming instructions.
While generating other scripts, you can save the file within the Vertabelo Drive or just download it. You can find extended text about the migration process here.
SQL Scripts
In Vertabelo, you can create and edit SQL scripts in its SQL Script Editor, which also lets you run the script (in a transaction) against your database. Generated SQL scripts saved to the Drive are also available this way. You can also upload your own SQL scripts to the Drive or create them from scratch.
Vertabelo Can Generate SQL Scripts for You!
That'll be it for this overview. We've talked about three different types of SQL scripts that Vertabelo can generate for you:
- The CREATE scripts that will help you set up your database from the model.
- The DROP scripts that will assist you with clearing all or selected model elements from the live database.
The migration scripts that will prepare the SQL for updating your database structure.
