Back to articles list
What is a DDL script and why is it so crucial for systems that use relational databases?
DDL scripts are widely used in creating, documenting, and migrating systems that use relational databases. They can be designed for simple tasks, such as migrating a database from one server to another, or for more complex tasks.
DDL scripts are read sequentially and are written in SQL with commands specific for defining data structures (CREATE, ALTER, and DROP). This article presents the concept behind these scripts and how to use them successfully.
What Is DDL?
Before we explain why you’d need a DDL script, it is essential for you to understand what it is. DDL stands for Data Definition Language. This is a set of SQL commands used for creating, modifying, and removing database objects; its primary commands are CREATE, ALTER, DROP, and TRUNCATE. These instructions allow us to create, modify, and delete any object in an SQL database.
The CREATE command creates database object structures, such as a new database, table, or index. This command also serves a variety of other purposes. Creating tables is one of the most frequent uses of the CREATE command; the structure is as follows:
CREATE TABLE Product (
id_product serial NOT NULL,
nr_serial varchar(13) NOT NULL,
nm_product varchar(250) NOT NULL,
nm_brand varchar(250) NOT NULL,
tp_product varchar(250) NOT NULL,
de_product varchar(250) NOT NULL,
qt_weight decimal(9,2) NOT NULL,
vl_price_cost decimal(9,2) NOT NULL,
vl_price_sell decimal(9,2) NOT NULL,
last_updated date NOT NULL
);
This example uses the CREATE command to create a table called Product. As you might guess, it stores product information. You start by calling CREATE TABLE (indicating that you want to create a table). Then you open the parentheses and place the desired fields and their definitions inside, separating each new field with a comma. You end the command by closing the parentheses and adding a semicolon.
ALTER is used to change database object structures; you can use it to edit an existing object in your database. This would include adding or deleting a column from a table or updating a table to include a primary key. Here’s an example of an ALTER statement used on our Product table.
ALTER TABLE Product ADD PRIMARY KEY (id_product); ALTER TABLE Product ADD vl_tax decimal(9,2); ALTER TABLE Product DROP COLUMN vl_price_sell;
In these three commands, you can see the versatility of the ALTER command. In the first line, it is used to change the table and make the id_product field a primary key. Next, this same command is used to insert a field called vl_tax, which will be used to store the value of the tax on that product.
Finally, the ALTER command is used to remove the vl_price_sell column, which represents a product's sales value.
However, you need another command to delete objects. The DROP command removes objects and even entire databases; it is how the deletion of tables, users, databases, and other structures is done. Here are two examples:
DROP TABLE Product; DROP DATABASE Store;
The syntax is simple but very efficient: You use the DROP command followed by the type of element you want to destroy and the element’s name. In the snippet above, DROP removes the table Product (first line) and then the database Store (second line).
The TRUNCATE command immediately deletes all records from a table (but not the table itself or its structure). It is much faster than DELETE because it does not write the data being removed to the transaction log. To remove all records from a table, you’d write:
TRUNCATE TABLE Product;
It is important to note that since all data is not being written to transaction logs, it is impossible to use transactional control (ROLLBACK, for example) to recover the deletion performed by the TRUNCATE command. In other words, truncated data is permanently removed.
What Do SQL DDL Scripts Contain?
A DDL script can contain a lot of information that a database administrator, data analyst, or data engineer needs. Just a slight knowledge of SQL can help you decipher it. The comments are written in green and preceded by double hyphens (-). They are essential for mapping the script and recording what is being done during execution.
The menu on the right presents several functions that you can use to make your script even more specific to your needs. For example, you can define the database type. (Despite being a standard, DDL/SQL scripts have slight differences from one database to another, such as the way the data type is represented.)
Along with your script, you can run additional SQL scripts that allow you to perform actions before or after the CREATE and DROP commands. You can choose from dozens of script validation checks in Model Validation Settings (all selected by pattern) or choose the format in which your script will print. You can also change minor details like reference notations, table schemas, column details, primary keys, foreign keys, alternatives, and checks. Thus, the Vertabelo parameter configuration feature allows your script generation to be even more suited to your needs.
To generate your DDL script, click on the SQL item in the top bar; it will take you to the next screen, Generate SQL Script. Once there, indicate whether it is a CREATE or DROP DDL script (one serves to create the tables, the other to destroy them).
Next, select which elements you want to export (tables, references, sequences, etc.). Finally, choose if you will export only the selected elements or the entire database. Once this process is complete, click Generate.
Once your DDL script is finished, just download it or keep it saved in the Vertabelo drive, where it can be edited if necessary.
Using DDL Scripts
1.Reverse Engineering
Reverse engineering analyzes a system, object, or device (e.g. a database, a machine or a machine part, etc.) with the goal of understanding how it works. During this process, it examines structures, functions, and operations. In other words, it reworks the entire development to its initial design.
A great example of reverse engineering is disassembling a machine to analyze its parts and find out how it works. When applied to the database, reverse engineering is defined as redoing the whole development process using methodologies to return to the initial design from the actual physical structure. Database reverse engineering creates a document that maps the properties of the database and makes it easier to understand.
In incorrectly modeled and legacy systems (years-old systems that no longer offer access to the original team/documentation), understanding the database can be a challenge: mapping the numerous tables, the correlations between them, their cardinalities, and even the type of data that exists in each field can be an arduous and extensive task.
Vertabelo facilitates this process, as it can generate DDL scripts from a physical model. You can also build a new model by importing a DDL script, allowing entire databases to be quickly ported to an easy-to-understand environment and shared with other contributors who will help decipher the original purpose of that structure.
2. Migration
And if reverse engineering is not enough, you can also use DDL to generate migration scripts.
A system can have several versions, and its development can continue once a version is delivered. Therefore, it is common that a database is changed and its tables are modified after it is in use. Vertabelo allows a script to be generated from the new database version (as compared with the old one) and its updates to be applied. This kind of feature is precious for a company that needs to ensure the integrity of its production operation and wants to mitigate the damage that data loss might cause.
Vertabelo and Your DDL Scripts
It is easy to understand the advantage of using Vertabelo for developing models and documenting legacy systems through the extraction and import of DDL scripts. These resources are very versatile. Even better, they allow more people to share the imported model, speeding up group work and system documentation.
