Add a table to a database

Magento 2 has a special mechanism which allows you to create database tables, modify existing ones and even add some data into them like setup data which has to be added when a module is installed. This mechanism allows those changes to be transferable between different installations.

The key concept is that instead of doing manual SQL operations that you have to do again and again when reinstalling the system, developers created an install or upgrade script that contains the data. The script will be executed every time a module is installed. Magento 2 has four types of such scripts, InstallSchema, InstallData, UpgradeSchema and UpgradeData. The install scripts are executed only once while the upgrade scripts are executed every time the modules version gets changed. To look at all four scripts we’ll complete the following greeting page tasks. Create a greeting_message table with the columns greeting ID and message. Add two records, Happy New Year and Happy Holidays. Modify the table by adding another field season to which we add the records Happy Thanksgiving and Fall. Update the types for the first and second records. The steps we need to take to accomplish these tasks are one, create a new module, two, create an InstallSchema script, three, create an InstallData script, four, add a new module and verify that a table with the data was created. Five, create an UpgradeSchema script, six, create an UpgradeData script, seven, run them and verify that the table has changed. Let’s go through each step. Step one, create a new module. We will create a new module called learning_greeting message.

Go into the app code folder and create the folders learning and learning greeting message.

Now create two files, registration.php and Etsymodule.XML.

Step two, create an InstallSchema script.

To create an InstallSchema script, go into the app code learning greeting message folder and create a setup folder.

Now create the file setup/InstallSchema.php.

Let’s take a minute to look at the code. The InstallSchema files are all very typical. The main code is located in the install method which has a set of parameter. This is a key parameter because it gives access to the connection object that allows database manipulations. The connection is an instance of Magento framework, DB adapter, PDO MySQL. Magento uses DDL or Data Definition Language to manipulate the database. Now let’s create the setup installed data file.

Now it’s time to run the install scripts and verify that a table with the initial data is there.

So we’ll run the setup upgrade script.

You should see a long list of modules that contain learning greeting message.

Now let’s connect to the database.

Check the table and data are there.

How does this work? When you create a new module and run the bin Magento setup upgrade script, Magento checks the code base to see if there are modules that were not installed. If it finds any, it checks whether there are any InstallScripts and if so runs them. After that, Magento updates the table setup module and puts information about the module and its version there.

The next time you run the bin Magento setup upgrade script, you will find a record in the database. We’ll compare the current version against the one in the database. If the versions match, it will do nothing. If the current version is higher, it will run the upgrade scripts discussed next.

Step five, create an UpgradeSchema script. To see how the upgrade scripts work, we’ll add some data to the database. First, change the version in the Etsy module to XML file to 0.0.2.

Then create the file setup UpgradeSchema.php. Note the version compare line. As described earlier, the upgrade script will be executed every time the version in the module XML has changed. So we only want the current version of upgrade script to execute and not previous upgrades. That’s why we put upgrades into an if clause.

Step six, create the UpgradeData script. Now create the file setup UpgradeData.php.

Step seven, run the upgrade scripts and verify that the table has changed.

We’ll run the setup upgrade script again, we can now connect to the database and verify that our changes are there.

We see the change in the schema and data version.

And we see the changes in the greeting message table.

This how to video we practice how to create a new table, add setup data and modify the table and corresponding data during the module lifecycle. It’s very important to understand that the data is added only once and should be installed when the module is created. Magento two uses multiple tools to manipulate the database from the code, model resource models and collections which are beyond the scope of this video. So if you need an interface which saves or fetches data from the database, you will use a module resource collection for that not an UpgradeData script. -