Learn Sequelize ORM in Node.js with MySQL From Scratch

Learn Sequelize ORM in Node.js with MySQL From Scratch

This Nodejs and MySQL tutorial, we'll learn Sequelize ORM in Nodejs with MySQL From Scratch. Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It supports the dialects PostgreSQL, MySQL, SQLite, and MSSQL plus some cool features like transactions and eager-loading.

Getting Started

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite, and MSSQL plus some cool features like transactions and eager-loading.

Q: Why use Sequelize instead of standard SQL Queries?

A: Basically because it supports more than one database system and it provides you an OOP Database API with all queries in form of methods and function so you don't have to write or care about queries plus its advanced Association system for putting relations between Models.

I assume you have a basic Node.js project for working with Sequelize.

Now let's start by installing Sequelize on our project.

npm install sequelize --save 

Sequelize Also requires your database system driver (depending on what dialect you choose) on our case we will be working with MySQL.

npm install mysql2 --save 

This will install MySQL adapter which allows Sequelize to connect and to the MySQL Deamon server.

We also, need to install Sequelize-CLI for managing our project and keeping track of migrations.

npm install sequelize-cli -g 

We save the CLI globally because you will be using on more than one project and you need to access directly through the command line.

Connecting to Database

First, we need to open connection steam between Sequelize and the running MySQL Deamon.

Create a database folder under src/database and add a connection.js file under it.

/* connection/js */

const Sequelize = require("sequelize");

const sequelize = new Sequelize("socialnetwork", "root", "", {
  host: "127.0.0.1",
  dialect: "mysql",
  operatorsAliases: false
});

module.exports = sequelize;
global.sequelize = sequelize;

Change the config depending on your database server settings.

we export the connection instance as well as we export it globally which means we can access the Sequelize instance from any module on our environment without import (Global variables aren't good but in our case, it is the best scenario).

Now under the app.js (or main entry point module), you can require the connection module to connect to the database and expose the connection instance globally.

require("./src/database/connection");
Models & Schemas

Models are the building blocks of your tables each model represents a database table where you define a model by putting the schema and giving it a name.
The Schema is a set of attributes (table columns) and associations (table relations).

So let's say we are going to create a social media application something like twitter clone, ao first we start by creating the database, this is better done manually from the CLI of any other MySQL client.
mysql$ CREATE DATABASE socialnetwork
The above command will create a database for us.

For a basic Social network, we would have a tweet and a user (tweet's author), the user can tweet one or more tweets but a tweet belongs to a single user.

Let's create the tweet model.

const Sequelize = require("sequelize");

module.exports = sequelize.define("Tweet", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  },
  content: Sequelize.STRING(300)
});

The Sequelize define method registers a new model using the provided name and schema.
Attributes can have the type directly, all are defined in the main namespace of Sequelize (STRING, DATE...).
Or you can define attributes using options with type and allowNull, unique.

for type sizes, you simply use the type as a function and pass the length (number) as an argument.

the id attributes are the primary key that uniquely identifies each tweet.

We export the defined model so later we will be able to use it for inserting, fetching, updating or deleting model's data.

We also need to define and register the user model the same way we did the tweet but with different attributes.

const Sequelize = require("sequelize");

module.exports = sequelize.define("User", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  },
  username: {
    type: Sequelize.STRING(35),
    allowNull: false,
    unique: true
  },
  passwd: {
    type: Sequelize.STRING(20),
    allowNull: false
  }
});

The username has to be unique so we make it unique (only one persistence can be saved with a certain username string), we set unique to true.

You can create default attributes on Sequelize which gets added to any model (like the id).

Sequelize Migrations

Sequelize other than the ORM comes with a Database Builder which allow you to create and manage the structure of your database tables over time without manually interacting with the CLI or typing silly commands.

All Migrations features are manipulated through Sequelize-CLI that we have installed earlier.

First, for migrations to work we need to initialize our project with the CLI and add some database configuration.

sequelize init 

This, in fact, it is used to initialize a whole Sequelize project to work properly with models, migrations, and seeds and since we already created the connection stream and add some models we will remove the models folder.

This what will be created under the root of our project.

  • config: database connection configuration for (development, testing, and production). So make sure to put your settings in the development object.
  • migrations: All migrations will be stored in here with their timestamp of creation.
  • seeders: A seed is a data that gets added to the database by default.
  • models: All models should go here where there is also a connection stream to Sequelize (in our case we will delete this).

Make sure to delete the newly added models folder by the CLI.

Let's add our tweets and users tables migrations, we will use the CLI to generate a new migration which will be placed by default under the migrations/ folder with some pre-defined code.

sequelize migration:generate --name create_tweets_table

This will generate a new migration named (create_tweets_table) and place it under migrations/ for the naming convention of migrations you always want it to be like a verb or a sentence tells what the migration does exactly.

Open the tweets migration file (Sequelize will add a timestamp represents when the migration has been generated to the filename), and add your column types which to be created on up method and dropTable command to drop and remove the table from the database if down is executed.

/* 20190214224339-create_tweets_table.js */
"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    //You must return a promise 
    return queryInterface.createTable("tweets", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      },
      content: Sequelize.STRING(300),
      userId: Sequelize.INTEGER(11),
      //Those are added by default on insertion (make sure to create the their columns)
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE
    });
  },

  down: (queryInterface, Sequelize) => {
    //Return a promise that drops a table in case of (migration:undo)
    return queryInterface.dropTable("tweets");
  }
};

If we run migrate command the up method will be called and the table will be created, but Sequelize is smart enough to only run the up command once (it keeps track of migrated tables).

the userId attribute we will use it later to associate a user with one or more tweets.

And in case you run migration:undo or migration:undo:all it will rollback either the last submitted migration or simply or migrations in this case whatever you put inside the down method will be executed.

Add the user's migration too.

sequelize migration:generate --name create_users_table

And add the schema of the table on up and drop table on down.

/* 20190214225010-create_users_table.js */

"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable("users", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      },
      username: {
        type: Sequelize.STRING(35),
        allowNull: false,
        unique: true
      },
      passwd: {
        type: Sequelize.STRING(20),
        allowNull: false
      },
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE
    });
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("users");
  }
};

Now our migrations are created now we need to migrate them to the database.

sequelize db:migrate

It will output the successfully migrated migration and errors in case of any.

You can use the CLI to take a look at the users and tweets tables structure using the describe command on your MySQL CLI.

use socialnetwork;
describe tweets;
describe users;
Inserting to Database

Let's create a bootstrap.js module that will do the fake data insertion on the database for our social network.

/* src/bootstrap.js */
//Export a default function (we use Async/Await)
module.exports = async () => {
  //Require models
  const Tweet = require("./models/Tweet");
  const User = require("./models/User");
  //Create Relations 
  User.hasMany(Tweet, { as: "Tweets", foreignKey: "userId" });
  Tweet.belongsTo(User, { as: "User", foreignKey: "userId" });
};

For our Models Association (Relation) we have two constraints:

  • User: can have one or more Tweets (one-to-many).
  • Tweet: can have one user only as an author (one-to-one)

Each Sequelize model has a built-in methods used to describe associations among other models, the first parameter it takes is the target and an options object as the second parameter.

  • hasMany: for a one-to-many association, will associate User with many tweets with configuration (foreignKey will be added to the tweets table).
  • as option: used to set setters in the User Model so you can call userInstance.setTweets(tweets).
  • belongsTo: for a one-to-one association to associate each tweet with one and only one user.

Check the Association Docs for more detailed info.

Let's insert some User to the database.

//Generic Error Handler 
const errHandler = err => {
  //Catch and log any error.
  console.error("Error: ", err);
};
//create returns a promise which gets resolved to the user instance 
//We also use await, you can use standard then callback.
const user = await User.create({
  username: "alexdmc",
  passwd: "1234567890"
}).catch(errHandler); ///< Catch any errors that gets thrown
//You must provide the userId to get each tweet linked to a single user.
const tweet = await Tweet.create({
  content: "This is actually the tweet content Tweeted from Iphone",
  userId: user.id
}).catch(errHandler);

A model.create method is used to create a user instance using the provided data and then save it and finally returns a promise holding the new user instance.

We use await which gets resolved if the creation and saving succeed otherwise the catch callback (errHandler) will get called.

To associate a tweet with its author (user) we have to put the userId attribute under the tweet to point to the author's unique id.

Fetching from Database (Eager Loading)

For fetching specific data related to a specific model we have to use the Model class providing it with a filter (condition) to fetch a specific record(s) from the database.

Let's try to fetch users data plus all its tweets (using eager-loading).

eager-loading is the process of fetching a models data that is associated with another model in one fetch call (also known as Populating).

//Find All Users with Thier Tweets
const users = await User.findAll({
  where: { username: "alexdmc" },
  include: [{ model: Tweet, as: "Tweets" }] ///< include used to eager-load associated model 
}).catch(errHandler);
//log users & tweets
console.log("AlexDMC Tweets: ", JSON.stringify(users));

We findAll the users with their associated tweets the where attributes take the filter object and include is an array on which you specify the associated model you want to eager-load alongside the current model (User).

the include takes an array of object we need to specify associated model's model class and the as attribute (which you used to define the association between the two models).

The output should have all the registered users with an array of their tweets.

Read More on Eager-Loading Docs.

What's Next

This tutorial is just a quick introduction to Sequelize and how to use its main features, if you want to master and work with Sequelize you must read the http://docs.sequelizejs.comDocs for more detailed pieces of information and instructions.

Video

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

What’s new in HTML6

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Learn & Use PostgreSQL - Getting Started with Sequelize and PostgreSQL

Learn & Use PostgreSQL - Getting Started with Sequelize and PostgreSQL

In this tutorial, we will learn about NodeJS ORM called Sequelize interfacing with PostgreSQL Database. We are going to learn and use PostgreSQL. Sequelize is the NodeJS module which provides common functionalities and utilities to manipulate SQL based databases. Technically, it is an ORM ( Object relational mapping ) module for NodeJS. It supports the following databases: MySQL, PostgreSQL, MariaDB, SQLite, MSSQL

In this tutorial, we will learn about NodeJS ORM called Sequelize interfacing with PostgreSQL Database.

Introducing Sequelize

Sequelize is the NodeJS module which provides common functionalities and utilities to manipulate SQL based databases. Technically, it is an ORM ( Object relational mapping ) module for NodeJS. It supports the following databases:

  • MySQL
  • PostgreSQL
  • MariaDB.
  • SQLite.
  • MSSQL

We have already covered tutorials on MySQL, SQLite, and MariaDB. In this tutorial, we are going to learn and use PostgreSQL.

About PostgreSQL

PostgreSQL is considered to be the most advanced relational database system. It offers amazing features such as multi-version concurrency control, reliability, availability and strong data types backed by the 15 years of research and development.

I have not personally used it in a production system hence cannot provide any live experience. If any of you used it, please provide your feedback in comments.

We will use this to test out the Sequelize ORM. To begin with, visit the official site of PostgreSQL and download it. It supports all Major operating system.

Once you have installed it, open up the PostgreSQL admin screen. It will look something like this.


Awesome! PostgreSQL is up and running!

Getting Started with Sequelize and PostgreSQL

Let’s get started with Sequelize. To begin with, create a new folder and switch to it using Terminal.

Then run the following command to generate a new fresh NodeJS project.

npm init --y

This generate the following:

{
  "name": "sequalize-postgres",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Then, install node dependencies for Sequelize and PostgreSQL using the following command.

npm i --save sequelize pg pg-hstore

We will do the following task with Sequelize and PostgreSQL:

  • Connecting to the Database
  • Creating the model
  • Performing CRUD Operation
  • Monitoring Queries

Connecting to the Database

Create new file say app.js and add the following code.

const Sequelize = require('sequelize');
const sequelize = new Sequelize('codeforgeek', 'postgres', 'shahid', {
  host: 'localhost',
  dialect: 'postgres',
  pool: {
    max: 9,
    min: 0,
    idle: 10000
  }
});

sequelize.authenticate().then(() => {
  console.log("Success!");
}).catch((err) => {
  console.log(err);
});

Run the code using the following command.

node app.js

Since we haven’t created the database in PostgreSQL, it will return the following error ( You will receive more fields ).

{
  name: 'SequelizeConnectionError',
  message: 'database "codeforgeek" does not exist'
}

To create the database, open your PostgreSQL Admin.

Give your database a proper name and choose the user.

Now, run the same code and you should see the message as shown below.

Let’s create tables inside the PostgreSQL using Sequelize.

Creating model using Sequelize

Place this code inside the authenticate() success block.

sequelize.authenticate().then(() => {
  console.log("Success!");
  var Posts = sequelize.define('posts', {
    title: {
      type: Sequelize.STRING
    },
    content: {
      type: Sequelize.STRING
    }
  }, {
    freezeTableName: true
  });

  Posts.sync({force: true}).then(function () {
    return Posts.create({
      title: 'Getting Started with PostgreSQL and Sequelize',
      content: 'Hello there'
    });
  });
}).catch((err) => {
  console.log(err);
});

Run the code and you should be seeing queries running in the terminal. Similar to what is shown below.

Performing CRUD Operation

We have already created a data in the table using the code shown above. Let’s do some reading.

To read something from SQL based databases, we use SELECT query. Something like this.

SELECT * FROM `posts` WHERE `id`=2019;

Sequelize provides functions to perform database reads. The function is findAll() which accepts various parameters to cover clauses such as WHERE, JOINS etc and returns promise function.

To read the data from the Posts table, we will use the following code.

 Posts.findAll({}).then((data) => {
    console.log(data);
 }).catch((err) => {
    console.log(err);
 });

If we want to add WHERE clause in it, then it can be tweaked to the following code.

 Posts.findAll({
  where: {
    id: '100'
  }
 }).then((data) => {
    console.log(data);
 }).catch((err) => {
    console.log(err);
 });

You can find all read operation combinations here.

You can update any fields using update() method. Here is the sample code.

 Posts.update({
    content: 'This is a tutorial to learn Sequelize and PostgreSQL'
  }, {
    where: {
      id: 1
    }
  }).then(() => {
    console.log('Updated');
  }).catch((e) => {
    console.log("Error"+e);
  });

You can delete the rows using destroy() function.

  Posts.destroy({where: {
    id: 1
  }}).then(() => {
    console.log("Deleted");
  }).catch((e) => {
    console.log("Error"+e);
  });

This covers all four CRUD operation.

Monitoring Queries

Sequelize runs SQL queries in the background to deal with the database. In the terminal, you can view those queries as well. For example, refer to the screenshot below.

You can monitor those queries live, on each method call, Sequelize executes these queries.

Conclusion

I have tried and tested various node modules to interface with a variety of databases. It’s tough to maintain the track and keeping in touch with the development of these modules because there is too many of them. I am glad something like Sequelize came up to solve these issue. One module, multiple databases is what I needed!

Let me know if you are going to work with Sequelize or share your experience if you have already used it.

Write Web Form Data to MySQL Databases in Minutes

Write Web Form Data to MySQL Databases in Minutes

In this MySQL Databases tutorial, you'll learn how to wirte web form data to MySQL Databases. We'll set up a web form and use it to collect data. Then, we'll write that data to a heroku database.

Hey Everyone! In this one we'll set up a web form and use it to collect data. Then, we'll write that data to a heroku database.

How to Manage and Use MySQL Database Triggers on Ubuntu 18.04

How to Manage and Use MySQL Database Triggers on Ubuntu 18.04

In this MySQL Database Triggers tutorial, you'll learn how to Manage and Use MySQL Database Triggers on Ubuntu 18.04. You’ll create, use, and delete different types of triggers from a MySQL database. In MySQL a trigger is a user-defined SQL command that is invoked automatically during an INSERT, DELETE, or UPDATE operation.

Introduction

In MySQL a trigger is a user-defined SQL command that is invoked automatically during an INSERT, DELETE, or UPDATE operation. The trigger code is associated with a table and is destroyed once a table is dropped. You can specify a trigger action time and set whether it will be activated before or after the defined database event.

Triggers have several advantages. For instance, you can use them to generate the value of a derived column during an INSERT statement. Another use case is enforcing referential integrity where you can use a trigger to save a record to multiple related tables. Other benefits include logging user actions to audit tables as well as live-copying data across different database schemas for redundancy purposes to prevent a single point of failure.

You can also use triggers to keep validation rules at the database level. This helps in sharing the data source across multiple applications without breaking the business logic. This greatly reduces round-trips to the database server, which therefore improves the response time of your applications. Since the database server executes triggers, they can take advantage of improved server resources such as RAM and CPU.

In this tutorial, you’ll create, use, and delete different types of triggers on your MySQL database.

Step 1 — Creating a Sample Database

In this step, you’ll create a sample customer database with multiple tables for demonstrating how MySQL triggers work.

First, log in to your MySQL server as root:

mysql -u root -p

Enter your MySQL root password when prompted and hit ENTER to continue. When you see the mysql> prompt, run the following command to create a test_db database:

Create database test_db;

OutputQuery OK, 1 row affected (0.00 sec)

Next, switch to the test_db with:

Use test_db;

OutputDatabase changed

You’ll start by creating a customers table. This table will hold the customers’ records including the customer_id, customer_name, and level. There will be two customer levels: BASIC and VIP.

Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.01 sec)

Now, add a few records to the customers table. To do this, run the following commands one by one:

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

You’ll see the following output after running each of the INSERT commands:

OutputQuery OK, 1 row affected (0.01 sec)

To make sure that the sample records were inserted successfully, run the SELECT command:

Select * from customers;

Output+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

You’ll also create another table for holding related information about the customers account. The table will have a customer_id and status_notes fields.

Run the following command:

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

Next, you’ll create a sales table. This table will hold sales data related to the different customers through the customer_id column:

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.01 sec)

You’ll add sample data to the sales data in the coming steps while testing the triggers. Next, create an audit_log table to log updates made to the sales table when you implement the AFTER UPDATE trigger in Step 5:

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

OutputQuery OK, 0 rows affected (0.02 sec)

With the test_db database and the four tables in place, you’ll now move on to work with the different MySQL triggers in your database.

Step 2 — Creating a Before Insert Trigger

In this step, you’ll examine the syntax of a MySQL trigger before applying this logic to create a BEFORE INSERT trigger that validates the sales_amount field when data is inserted into the sales table.

The general syntax for creating a MySQL trigger is shown in the following example:

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

The structure of the trigger includes:

DELIMITER //: The default MySQL delimiter is ;—it’s necessary to change it to something else in order for MySQL to treat the following lines as one command until it hits your custom delimiter. In this example, the delimiter is changed to // and then the ; delimiter is redefined at the end.

[TRIGGER_NAME]: A trigger must have a name and this is where you include the value.

[TRIGGER TIME]: A trigger can be invoked during different timings. MySQL allows you to define if the trigger will initiate before or after a database operation.

[TRIGGER EVENT]: Triggers are only invoked by INSERT, UPDATE, and DELETE operations. You can use any value here depending on what you want to achieve.

[TABLE]: Any trigger that you create on your MySQL database must be associated with a table.

FOR EACH ROW: This statement tells MySQL to execute the trigger code for every row that the trigger affects.

[TRIGGER BODY]: The code that is executed when the trigger is invoked is called a trigger body. This can be a single SQL statement or multiple commands. Note that if you are executing multiple SQL statements on the trigger body, you must wrap them between a BEGIN...END block.

Note: When creating the trigger body, you can use the OLD and NEW keywords to access the old and new column values entered during an INSERT, UPDATE, and DELETE operation. In a DELETE trigger, only the OLD keyword can be used (which you’ll use in Step 4).

Now you’ll create your first BEFORE INSERT trigger. This trigger will be associated with the sales table and it will be invoked before a record is inserted to validate the sales_amount. The function of the trigger is to check if the sales_amount being inserted to the sales table is greater than 10000 and raise an error if this evaluates to true.

Make sure you’re logged in to the MySQL server. Then, enter the following MySQL commands one by one:

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
END IF//
DELIMITER ;

You’re using the IF...THEN...END IF statement to evaluate if the amount being supplied during the INSERT statement is within your range. The trigger is able to extract the new sales_amount value being supplied by using the NEW keyword.

To raise a generic error message, you use the following lines to inform the user about the error:

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

Next, insert a record with a sales_amount of 11000 to the sales table to check if the trigger will stop the operation:

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

OutputERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

This error shows that the trigger code is working as expected.

Now try a new record with a value of 7500 to check if the command will be successful:

Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7500');

Since the value is within the recommended range, you’ll see the following output:

OutputQuery OK, 1 row affected (0.01 sec)

To confirm that the data was inserted run the following command:

Select * from sales;

The output confirms that the data is in the table:

Output+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

In this step you’ve tested triggers to validate data before insertion into a database.

Next, you’ll work with the AFTER INSERT trigger to save related information into different tables.

Step 3 — Creating an After Insert Trigger

AFTER INSERT triggers are executed when records are successfully inserted into a table. This functionality can be used to run other business-related logics automatically. For instance, in a bank application, an AFTER INSERT trigger can close a loan account when a customer finishes paying off the loan. The trigger can monitor all payments inserted to a transaction table and close the loan automatically once the loan balance is zero.

In this step, you’ll work with your customer_status table by using an AFTER INSERT trigger to enter related customer records.

To create the AFTER INSERT trigger, enter the following commands:

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
DELIMITER ;

OutputQuery OK, 0 rows affected (0.00 sec)

Here you instruct MySQL to save another record to the customer_status table once a new customer record is inserted to the customers table.

Now, insert a new record in the customers table to confirm your trigger code will be invoked:

Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

OutputQuery OK, 1 row affected (0.01 sec)

Since the record was inserted successfully, check that a new status record was inserted into the customer_status table:

Select * from customer_status;

Output+-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

The output confirms that the trigger ran successfully.

The AFTER INSERT trigger is useful in monitoring the lifecycle of a customer. In a production environment, customers’ accounts may undergo different stages such as account opening, suspension, and closing.

In the following steps you’ll work with UPDATE triggers.

Step 4 — Creating a Before Update Trigger

A BEFORE UPDATE trigger is similar to the BEFORE INSERT trigger—the difference is when they are invoked. You can use the BEFORE UPDATE trigger to check a business logic before a record is updated. To test this, you’ll use the customers table in which you’ve inserted some data already.

You have two levels for your customers in the database. In this example, once a customer account is upgraded to the VIP level, the account can not be downgraded to the BASIC level. To enforce such a rule, you will create a BEFORE UPDATE trigger that will execute before the UPDATE statement as shown following. If a database user tries to downgrade a customer to the BASIC level from the VIP level, a user-defined exception will be triggered.

Enter the following SQL commands one by one to create the BEFORE UPDATE trigger:

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
END IF //
DELIMITER ;

You use the OLD keyword to capture the level that the user is supplying when running the UPDATE command. Again, you use the IF...THEN...END IF statement to signal a generic error statement to the user.

Next, run the following SQL command that tries to downgrade a customer account associated with the customer_id of 3:

Update customers set level='BASIC' where customer_id='3';

You’ll see the following output providing the SET MESSAGE_TEXT:

OutputERROR 1644 (45000): A VIP customer can not be downgraded.

If you run the same command to a BASIC level customer, and try to upgrade the account to the VIP level, the command will execute successfully:

Update customers set level='VIP' where customer_id='1';

OutputRows matched: 1  Changed: 1  Warnings: 0

You’ve used the BEFORE UPDATE trigger to enforce a business rule. Now you’ll move on to use an AFTER UPDATE trigger for audit logging.

Step 5 — Creating an After Update Trigger

An AFTER UPDATE trigger is invoked once a database record is updated successfully. This behavior makes the trigger suitable for audit logging. In a multi-user environment, the administrator may want to view a history of users updating records in a particular table for audit purposes.

You’ll create a trigger that logs the update activity of the sales table. Our audit_log table will contain information about the MySQL users updating the sales table, the date of the update, and the new and old sales_amount values.

To create the trigger, run the following SQL commands:

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

You insert a new record to the audit_log table. You use the NEW keyword to retrieve the value of the sales_id and the new sales_amount. Also, you use the OLD keyword to retrieve the previous sales_amount since you want to log both amounts for audit purposes.

The command SELECT USER() retrieves the current user performing the operation and the NOW() statement retrieves the value of the current date and time from the MySQL server.

Now if a user tries to update the value of any record in the sales table, the log_sales_updates trigger will insert a new record to the audit_log table.

Let’s create a new sales record with a random sales_id of 5 and try to update it. First, insert the sales record with:

Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

OutputQuery OK, 1 row affected (0.00 sec)

Next, update the record:

Update sales set sales_amount='9000' where sales_id='5';

You’ll see the following output:

OutputRows matched: 1  Changed: 1  Warnings: 0

Now run the following command to verify if the AFTER UPDATE trigger was able to register a new record into the audit_log table:

Select * from audit_log;

The trigger logged the update. Your output shows the previous sales_amount and new amount registered with the user that updated the records:

Output+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | [email protected] | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

You also have the date and time the update was performed, which are valuable for audit purposes.

Next you’ll use the DELETE trigger to enforce referencing integrity at the database level.

Step 6 — Creating a Before Delete Trigger

BEFORE DELETE triggers invoke before a DELETE statement executes on a table. These kinds of triggers are normally used to enforce referential integrity on different related tables. For example, each record on the sales table relates to a customer_id from the customers table. If a database user deleted a record from the customers table that has a related record in the sales table, you would have no way of knowing the customer associated with that record.

To avoid this, you can create a BEFORE DELETE trigger to enforce your logic. Run the following SQL commands one by one:

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The customer has a related sales record.';
END IF//
DELIMITER ;

Now, try to delete a customer that has a related sales record:

Delete from customers where customer_id='2';

As a result you’ll receive the following output:

OutputERROR 1644 (45000): The customer has a related sales record.

The BEFORE DELETE trigger can prevent accidental deletion of related information in a database.

However, in some situations, you may want to delete all the records associated with a particular record from the different related tables. In this situation you would use the AFTER DELETE trigger, which you’ll test in the next step.

Step 7 — Creating an After Delete Trigger

AFTER DELETE triggers are activated once a record has been deleted successfully. An example of how you can use an AFTER DELETE trigger is a situation in which the discount level a particular customer receives is determined by the number of sales made during a defined period. If some of the customer’s records are deleted from the sales table, the customer discount level would need to be downgraded.

Another use of the AFTER DELETE trigger is deleting related information from another table once a record from a base table is deleted. For instance, you’ll set a trigger that deletes the customer record if the sales records with the related customer_id are deleted from the sales table. Run the following command to create your trigger:

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;

Next, run the following to delete all sales records associated with a customer_id of 2:

Delete from sales where customer_id='2';

OutputQuery OK, 1 row affected (0.00 sec)

Now check if there are records for the customer from the sales table:

Select * from customers where customer_id='2';

You will receive an Empty Set output since the customer record associated with the customer_id of 2 was deleted by the trigger:

OutputEmpty set (0.00 sec)

You’ve now used each of the different forms of triggers to perform specific functions. Next you will see how you can remove a trigger from the database if you no longer need it.

Step 8 — Deleting Triggers

Similarly to any other database object, you can delete triggers using the DROP command. The following is the syntax for deleting a trigger:

Drop trigger [TRIGGER NAME];

For instance, to delete the last AFTER DELETE trigger that you created, run the following command:

Drop trigger delete_related_info;

OutputQuery OK, 0 rows affected (0.00 sec)

The need to delete triggers arises when you want to recreate its structure. In such a case, you can drop the trigger and redefine a new one with the different trigger commands.

Conclusion

In this tutorial you’ve created, used, and deleted the different kinds of triggers from a MySQL database. Using an example customer-related database you’ve implemented triggers for different use cases such as data validation, business-logic application, audit logging, and enforcing referential integrity.

Originally published by FRANCIS NDUNGU at https://www.digitalocean.com