There are many rules that govern how entities interact with each other, to make things work. For example, a student can’t take a course that the school doesn’t offer. A soccer player can’t have a jersey number less than 1 or greater than 99. And a car must always have a plate number.

Relational databases are also able to represent and enforce these rules using constraints. And in this article, I’ll explain how constraints work with practical examples.

Whether you’re a beginner or just looking to refresh your knowledge, this article will help you learn the essentials. If you need some more background, you can read this article on the basics of relational databases before continuing.

What We’ll Cover:

1. What is a Relational Database Constraint?

2. Types of Relational Database Constraints

   • Inherent Model-based Constraints (Implicit Constraints)

   • Schema-based Constraints (Explicit Constraints)

   • Application-based constraints (Semantic constraints)

3. Testing Constraints

   • How to Delete a Record

4. Summary

What is a Relational Database Constraint?

Relational database constraints are a set of database rules that are used to define or determine what set of values are acceptable or valid in a database. They’re usually based on the many rules of the real world.

They are put in place to:

• Ensure data accuracy: only values that would be acceptable in real life should be acceptable in the database. Learn more about data accuracy here.

• Ensure data integrity: values in the database remain correct, accurate, complete, and valid as long as the database exists. Learn more about data integrity here.

• Ensure data consistency: values always maintain same agreed form throughout their lifetime.

These rules limit what can be entered into a database or what can be deleted from it. They also limit data update to ensure validity after original creation.

These integrity constraints help enforce business rules on data in the tables to ensure the accuracy and reliability of the data. - AWS

Types of Relational Database Constraints

There are many ways to group or categorise database constraints, depending on how they’re applied or what they’re preventing. This article focuses on three popular types:

• Inherent model-based constraints (implicit constraints)

• Schema-based constraints (explicit constraints)

• Application-based constraints (semantic constraints)

Inherent Model-based Constraints (Implicit Constraints)

These rules are the base rules that come with the database and are enforced by the DMBS. Some of these rules are:

• Each row must be unique. This is with or without a UNIQUE or PRIMARY KEY constraint.

• Columns can only store one value at a time. The value of a field like age will always be one value like 23, not 23 and 35.

• Each column name in a table must be unique.

• Columns exist for all rows. Every row will have the same number of columns. For some of the rows, the data might be empty, but the column will always be there.

Schema-based Constraints (Explicit Constraints)

These constraints are expressed by the developer or database designer on database creation. They’re expressed directly in the database schemas, using the DDL.

These can be further broken down into:

• Domain constraints

• Key constraints

  • Entity integrity constraint (Primary key)

  • Unique constraint (Unique key)

  • Referential integrity constraint (Foreign key)

1. Domain Constraints

These are used to define a range or set of possible values for an attribute of a database table. They help ensure that column values are valid and consistent by defining acceptable data types, formats, and ranges for an attribute. This prevents incorrect or illogical data entry and maintains data integrity.

You can define them simply by specifying a data type that the values must follow. For example, the age of a person can only be a number, or could be a number between 18-60 if the database is for a company, or a number between 5-65 if it’s for an amusement park.

The database will enforce this rule by rejecting age values outside of the given range or type. The DDL for the age would look like this:

CREATE TABLE people (
    age INT, -- Any integer value is allowed
    age INT CHECK (age BETWEEN 18 AND 60), -- Only allows ages between 18 and 60
    age INT CHECK (age BETWEEN 5 AND 65) -- Only allows ages between 5 and 65
);

The INT means only integer values are accepted, and the CHECK is used with the BETWEEN and AND keywords to specify the sub-domain or range of values.

Other data types in SQL include: CHAR, BIT, DATE, VARCHAR and so on. You can use all of them to define the acceptable domain for database values.

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    age INT CHECK (age BETWEEN 18 AND 60)
);

As well as defining a range of acceptable values, you can also define the optionality of an attribute using the NOT NULL keyword. You’d use this in cases where the data must exist and must also be within the given range.

CREATE TABLE employees (
    employee_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age BETWEEN 18 AND 60)
);

In this example, every employee record needs to have an employee_id and a name but not an age. This works for real life situations where, although the range of values is known, the actual value is either unknown or doesn’t exist. An example would be the minor course of study of a student at a university – many students only have majors, and as such, the minor course of study will be empty (NULL) for those students.

2. Entity integrity constraint (Primary key)

This ensures that no primary key is NULL. The primary key is the one attribute or set of attributes that must be unique to each row in the database. It’s the primary value that uniquely identifies the rest of the data. This means that every row in the database will remain uniquely identifiable with a primary key.

A NULL primary key means that rows will not be unique, or identifiable, and the database can contain duplicates. Without the primary key, we can’t have data consistency.

For example, in a school, every student will have a unique student id number with which they can always be distinguished from other students. The government uses methods like passport numbers or tax ids to uniquely identify citizens.

In our example, it’s impossible to be a student without a student id number. You can implement this constraint by using the PRIMARY KEY keyword.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age BETWEEN 18 AND 60)
);

3. Unique constraint (Unique key)

This is similar to the Entity integrity constraint in that it only accepts unique values – but it’s different in that it accepts NULL values.

An example of this would be in a students table, every student must have a student id number that uniquely identifies them. This number cannot be NULL, and it must the unique. Students can also have an email address that the school can reach them on. This email must be unique for each student. But, not every student has to have an email. So the condition is: “If the value exists, it must be unique”.

You can implement this constraint using the UNIQUE keyword, like this:

CREATE TABLE students (
    student_id INT PRIMARY KEY, -- Must exist and must be unique
    email VARCHAR(255) UNIQUE -- Can be NULL, but must be unique if provided
);

4. Referential integrity constraint (Foreign key)

This constraint guards the relationship between two related tables. It is used to maintain consistency in the relationship. It requires that data from one table, A, being referenced in another table, B, must exist in the original table, A. For example, a student can’t register for a course the school doesn’t have.

To enforce this, the FOREIGN KEY keyword is used with the REFERENCES to define the table being referenced, and what attribute is being referred to.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

In this example, every value provided in the course_id of the students must be in the courses table.

Application-based constraints (Semantic constraints)

These can also be called business rules. They can’t be directly expressed in the database schema, so they’re often implemented the application layer instead.

These are logical constraints, like saying “a course cannot have more than 30 students enrolled” or “a customer cannot place an order if it would exceed their credit limit”.

These rules are best implemented in the application, because it would be too complex (or sometimes impossible) to implement them on the database itself.

Testing Constraints

To demonstrate the constraints we’ve discussed here, let’s look at this sample school database setup:

CREATE TABLE courses (course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL, max_students INT CHECK (max_students > 0));

CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, age INT CHECK (age BETWEEN 5 AND 25));

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

This shows the creation of a sample school database with three tables: courses, students, and enrollments.

The courses table includes a primary key for course IDs, course names, and a constraint ensuring that the maximum number of students is greater than zero. The students table contains a primary key for student IDs, student names, unique email addresses, and an age constraint between 5 and 25. The enrollments table links students to courses with primary keys for enrollment IDs and foreign keys referencing the students and courses tables, along with a non-null enrollment date.

At this point, the tables are created, and setup with the constraints guiding them.

Now we’ll test a few queries:

1. Insert courses, Mathematics and History, into the courses table:

INSERT INTO courses (course_id, course_name, max_students) VALUES (1, 'Mathematics', 30);
INSERT INTO
    courses (course_id, course_name, max_students)
VALUES
    (2, 'History', 25);

The query works perfectly, as the records get inserted.

2. Insert students, Alice and Bob, into the students table:

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (101, 'Alice', 'alice@example.com', 20);

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (102, 'Bob', NULL, 18);

The query works perfectly, as the records get inserted.

3. Enroll Alice into Mathematics:

INSERT INTO
    enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
    (1001, 101, 1, '2026-01-14');

The query works perfectly, as the record gets inserted.

4. Insert a new student, Charlie, into the students table:

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (103, 'Charlie', 'charlie@example.com', 30);

This fails because Charlie has an age value of 30, which is outside of the specified range of age INT CHECK (age BETWEEN 5 AND 25). The record of Charlie never gets added.

Here’s a list of some other queries that will fail:

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (104, 'David', 'alice@example.com', 19); -- Fails for duplicate email

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (NULL, 'Evra', 'evra@example.com', 20); -- Fails for NULL primary key

INSERT INTO
    enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
    (1002, 999, 1, '2026-01-14'); -- Fails for invalid student reference

In each case, the DBMS will provide a reason for the rejection or failure.

5. Delete Bob from the students table:

DELETE FROM students
WHERE
    student_id = 102;

The query works perfectly, as the record gets deleted.

6. Delete Alice from the students table:

DELETE FROM students
WHERE
    student_id = 101; -- Fails for referential integrity constraint

This fails because Alice, with student_id of 101, has an enrollment record in the enrollments table. Deleting the record would mean there will be an enrollment record for a non-existent student which should not be possible.

How to Delete a Record

In some cases, you do want to delete a record, even though it has records tied to it. There are two main ways to go about this:

CASCADE

You can use this to define situations where, when a parent record is deleted, the child records cannot exist. All dependent (child) records in other tables are automatically deleted. You can use this to ensure that all enrollment records are deleted when the course is no longer available, or when a student is no longer in the school.

CREATE TABLE enrollments (enrollment_id INT PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, FOREIGN KEY (course_id) REFERENCES courses (course_id) ON DELETE CASCADE);

DELETE FROM courses
WHERE
    course_id = 1;

SET NULL or SET DEFAULT

You can use these methods to define situations where child records can still exist without the parent. All dependent (child) records in other tables are automatically set to null or automatically set to a defined default.

A useful example is if a school had a mentor assigned to students, when the mentor leaves the school, you don’t want to delete the students – you want to set the mentor to NULL or a default staff.

CREATE TABLE teachers (teacher_id INT PRIMARY KEY, teacher_name VARCHAR(100) NOT NULL);

CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, mentor_id INT, FOREIGN KEY (mentor_id) REFERENCES teachers (teacher_id) ON DELETE SET NULL);

7. Update Alice’s details. Change her email to a new one, and increase her age:

UPDATE students
SET
    email = 'alice.new@example.com',
    age = 22
WHERE
    student_id = 101;

The query works perfectly, as the record gets updated.

8. Update Alice’s age to 30:

UPDATE students
SET
    age = 30
WHERE
    student_id = 101;

This fails just like the 4th test for the same reason: the age is out of the stated range.

Here’s another query that will fail:

UPDATE enrollments
SET
    course_id = 999
WHERE
    enrollment_id = 1001;

This will fail because the new course_id does not exist in the courses table.

Summary

Databases are a pivotal part of everyday modern technology, and understanding their fundamental concepts can open doors to building and managing more accurate databases.

This article introduced you to what relational database constraints are, some of the different types, and how they’re enforced and violated. You should now have the essential knowledge to navigate the world of database constraints confidently.

If you’re curious to learn more, connect with me on LinkedIn, Twitter, or GitHub. Let’s continue this journey together toward mastering database systems!

But what exactly is a database, and how does it work? This article will give you a foundational understanding of core DB concepts like:

• What databases are

• Different database models

• Database Management Systems (DBMS)

• How Relational Database models work

• Basics of Structured Query Language (SQL)

Whether you’re a beginner or just looking to refresh your knowledge, this article will help you learn the essentials.

What Is A Database?

A database is a collection of information – information that’s preferably related, and preferably organised. This means that a database can be in any form or shape. It could be a pile of paper records in an office, or a large Excel sheet, or on a computer (most likely in this day and age). But in the most basic terms, a database just helps you store data – so ultimately you can decide what it is.

In the digital world, a database consists of physical files on your computer, or in a cloud computer. These files are installed (or downloaded) when you set up the database software on your computer.

A database allows you to record, organise, manage, retrieve, and update that data efficiently. A database is usually structured, organised, and containing related information, otherwise it will just be a pile of random data.

The structure of a database consists of two main parts, the data and the metadata.

• data is the actual information stored in the database. So for example, a database of football players would contain information about players like their names, ages, clubs, and so on.

• metadata is the structural description of the data in a database. It describes the names of fields used to store data, the length of those fields (where applicable), and their datatypes. metadata gives structure and organisation to raw data.

How to Update a Database

You can make changes to the different parts of a database using various commands. There are two general types of commands:

Data Definition Language (DDL)

First, we have Data Definition Language, or DDL. It’s made up of commands that define or alter the shape or structure of the data in the database. These commands affect the metadata part of a database.

You might make alterations like creating new tables in a relational database, changing the shape of documents in a document-based database by adding new fields, or removing an entire graph in a graph database. DDL might define a field as a specific data type, for example, the "date" type, ensuring only valid dates can be entered.

Data Manipulation Language (DML)

We also have Data Manipulation Language, or DML. It’s made up of commands that interact with the data stored in the database. These commands do not affect the structure of the data, but rather the data itself. These command only affect the data part of a database.

Some of the things you can do with DML include reading data from a database, adding new data to the database, editing data, and deleting data.

Applications like TablePlus let you see the data and the metadata in a database. For example, the data and metadata parts of a football application might look like the images below, respectively:

A datatype defines what type of information can be stored in a field. Datatypes help computers understand how to store, process, and use data efficiently.

So a field in a table with a datatype of date will only be able to store date records, and will throw an error if you try to store something else, like a name. The same goes for a field with a number datatype – it will only accept numbers, and can be set to accept values within a range or to a certain number of decimal points.

Common datatypes include varchar for data that might contain different characters (text + numbers), date for date values, int for whole numbers, and so on. You can find other common database datatypes here.

What Is a Database Model?

A database model is a concept used to describe the information stored on a database. Think of it as a building’s blueprint designed by an architect. It details all the tables, columns, and datatypes of the database. But it, in itself, is not a physical entity like the database. A database model determines how data is logically represented and accessed.

Database models define if data is stored in tables using rows and columns, or in JSON-like objects. They also define how data relates, how you can query it, and how you manage it. Database models are often chosen (and often developed) to suit specific data/application needs.

Popular database models:

Relational Model: The Relational Model is the most popular database model. This model uses tables with rows and columns to store data. This model uses the SQL language to manage the data.

Examples of some relational databases include MySQL, PostgreSQL, and SQLite. This model is popularly used for general-purpose applications that require structured, and often related, data and complex queries. The rest of the article will focus on this model.

Document Model: Data is stored as documents, often in JSON or XML format, in this model. Databases like MongoDB and CouchDB use this model. Document DBs promote flexibility with their JSON-like structures, and they’re commonly used in applications dealing with semi-structured data or hierarchical data, where flexibility is key.

Key-Value Model: In this model, data is stored as simple key-value pairs, like in a map in programming. This model is used by Redis and DynamoDB. Due to the simplicity of this model, it is used in high-performance scenarios for simple lookups or caching.

Graph Model: this model uses nodes (entities) and edges (relationships) to manage data. Neo4j and Amazon Neptune are examples of databases using this model. The shape of the nodes and edges in the graph model make it a common choice in applications involving relationships or connections between data points.

There are many more database models. You can find and study them here if you’d like more info.

How Do Relational Databases Work?

A relational database has the ability to establish links – or relationships – between information by joining tables, which makes it easy to understand and gain insights about the relationship between various data points. - Google

The relational database model was developed as an improvement to an older database model, the Hierarchical Database model. Relational databases build on it and improve some of its restrictions and relationships. The tables in a relational DB model are often called relations.

Each row in a database table represents a single record in the table. The row tells the full story of the data. It contains data for all the columns in that table for one specific entity.

For example, in a table storing information about football players, each row represents one player and will include player details like name, age, country, and so on. Rows are also sometimes referred to as records or tuples in database terminology.

Each column lists an attribute of the record in question, such as name, age, or country. The column only tells a small part of the story. Each column has a name and a datatype, and it applies to all rows in the table. These columns could also have constraints in addition to their datatypes. These constraints could be as simple as the NOT NULL constraint that says that the column can not be empty on any row, or as complex as you define it.

For example, in a table of football players, columns might include “name”, “age”, and “country”. All rows in the table will have values under these columns for their respective attributes. In some contexts, columns are also referred to as fields.

The “relational” part of the name Relational Databases is often attributed to the fact that this model focuses on how data relates with other data, and how tables relate to each other. For example, tables can be linked (related) together. Tables can also be independent.

Despite this flexibility with relationships, the data in a table can be accessed directly without having knowledge of related or unrelated tables. You can easily access records as long as you know what you’re looking for. Primary and Foreign keys are used in the relational model to manage these relationships.

What Is a DataBase Management System (DBMS)?

A DataBase Management System (DBMS) is a collection of programs for managing and communicating with an underlying database engine. In simpler terms, a DBMS is the database engine coupled with whatever additional tools that come with it.

A DBMS helps you create, manage, and use databases. It provides an abstraction over the database engine and lets you more easily store, update, and retrieve data in a secure way.

The tools that come in a DBMS can include, but are not always limited to:

• frontend tools (like a query interface, or an administration panel) that help you run queries and visualise the resulting data in the database

• backup and recovery tools that work in the background with little to no user interaction

• security tools for user access management (roles and permissions)

• and data import or export tools.

And as you would imagine, DBMS are usually model-specific, so there are DBMS focused on the Relational Database Model called RDBMS, where the “R” is for Relational. Examples of popular RDBMS include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server. RDBMS use SQL (Structured Query Language) to interact with the data.

SQL Basics

Structured query language (SQL) is a programming language for storing and processing information in a relational database. You can use SQL statements to store, update, remove, search, and retrieve information from a database. You can also use SQL to maintain and optimise database performance. - Amazon

It serves as the primary interface for interacting with databases, allowing users to perform various operations such as creating, modifying, querying, and deleting data and database structures. It’s the base upon which RDBMS like MySQL, PostgreSQL, and SQLite are built, with their own optimisations and extensions.

In this section, we’ll take a look at some basic SQL commands, with practical examples.

DDL Commands

1. CREATE

This is the SQL command used to create and define new database objects. It's a part of the Data Definition Language (DDL), and its primary function is to establish the structure or schema of the database.

You can use this command to do the following (amongst many other uses):

• Create new databases

• Create new tables

• Create a new index in a table

• Create views

• Create a user with specific access rights

CREATE is most commonly used however to create a table in a database, or to create the database itself (although you usually do this using the GUI options the RDBMS provides).

This command has the following structure:

CREATE OBJECT_TYPE object_name (optional_further_arguments)

The ENTITY_TYPE is a placeholder and could be DATABASE, TABLE, VIEW, and so on from the list of database objects. The entity_name defines the name for the object being created. And finally, the optional_further_arguments is used to show that some of the objects only need a name to be created, while others like tables need more context about the columns of the table.

So based on our example of a football application, creating the football_db database above would involve first creating the database, like this:

CREATE DATABASE football_db;

This command creates a new database with the provided name, football_db. Next up, using the CREATE command followed by the object type TABLE, you can create a players table, like this:

CREATE TABLE `players` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `age` int NOT NULL,
    `country` varchar(100) NOT NULL,
    `level` enum('Academy', 'Amateur', 'SemiPro', 'Professional') NOT NULL,
    `position` enum('Goalkeeper', 'Defender', 'Midfielder', 'Striker') NOT NULL,
    `foot` varchar(6) NOT NULL,
    `club` varchar(100) NOT NULL,
    `scores` json NOT NULL,
    `jerseyNumber` int NOT NULL
);

The command creates a table called players, and defines the columns (id, name, age, country, level, position, foot, club, scores, jerseyNumber) with their datatypes (int, varchar, enum, json). It also defines their constraints (PRIMARY KEY, AUTO_INCREMENT, NOT NULL).

2. ALTER

This command modifies the structure of an existing table. This command is versatile and allows for a wide range of table modifications. These include adding, removing, modifying, and renaming columns, and managing constraints and indexes.

To add a new height column to the newly created players table, you can use the ALTER command like this:

ALTER TABLE players
ADD height INT NOT NULL;

The command runs successfully and the new column, defined as an integer column, gets added.

3. DROP

This command deletes an existing table or database. When you use the DROP command, it completely removes the object from the database, and this action is irreversible. You can use us to remove databases, tables, and indexes.

If you ever stop using the players table, you can easily delete it using the DROP command like this:

DROP TABLE players;

4. TRUNCATE

This command removes all data from a table while preserving its structure. This same result can be achieved using the DELETE DML command.

DML Commands

These DML commands are foundational for CRUD operations, which stand for Create, Read, Update, and Delete – the basic actions you perform with data in a database.

1. INSERT

Adds a new record to the database. This is the Create part of CRUD.

The command has the structure like this:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The INSERT INTO is the first part of the query, it is mandatory and followed by the name of the table to insert into. The name of the table to insert into is represented by the table_name placeholder. The name can then be followed by a list of columns to populate, or the VALUES keyword. In case when the columns to populate are listed, the list of values has to have the same length as the length of the columns provided, as each entry in both lists will be mapped. In case when the columns to populate are not listed, the items in the values list are mapped to the database columns, and every column will have to be provided. The command also allows for the insertion of multiple records at the same time, following same rules as singe insertions, just with comma-separated multiple lists of values to insert.

To add a few players to the players table to achieve a similar result as in the first screenshot, you can use a couple of insert commands like this:

INSERT INTO
    `players` (`id`, `name`, `age`, `country`, `level`, `position`, `foot`, `club`, `scores`, `jerseyNumber`, `height`)
VALUES
    (1, 'Christiano Ronaldo', 36, 'Portugal', 'Professional', 'Striker', 'Right', 'Manchester United', '\"4, 3, 5, 2, 4\"', 7, 187),
    (2, 'Alisson Becker', 31, 'Brazil', 'Professional', 'Goalkeeper', 'Right', 'Liverpool', '\"5, 6, 7, 8, 9\"', 1, 193),
    (3, 'John Stones', 30, 'England', 'Professional', 'Defender', 'Right', 'Manchester City', '\"4, 5, 6, 7, 8\"', 5, 188),
    (4, 'Kevin DeBruyne', 33, 'Belgium', 'Professional', 'Midfielder', 'Right', 'Manchester City', '\"9, 8, 7, 6, 5\"', 17, 181),
    (5, 'Erling Haaland', 24, 'Norway', 'Professional', 'Striker', 'Right', 'Manchester City', '\"10, 9, 8, 7, 6\"', 9, 194),
    (6, 'Chris Waddle', 20, 'England', 'SemiPro', 'Midfielder', 'Left', 'Tow Law Town', '\"3, 4, 5, 6, 7\"', 11, 183),
    (7, 'Ian Wright', 25, 'England', 'SemiPro', 'Striker', 'Right', 'Greenwich Borough', '\"4, 5, 6, 7, 8\"', 8, 175),
    (8, 'Charlie Austin', 34, 'England', 'SemiPro', 'Striker', 'Right', 'Poole Town', '\"5, 6, 7, 8, 9\"', 9, 188),
    (9, 'Troy Deeney', 33, 'England', 'SemiPro', 'Striker', 'Right', 'Chelmsley Town', '\"6, 7, 8, 9, 10\"', 9, 183),
    (10, 'Rickie Lambert', 39, 'England', 'SemiPro', 'Striker', 'Right', 'Macclesfield Town', '\"7, 8, 9, 10, 11\"', 9, 187);

2. SELECT

The SELECT command has the basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The command has the following parts:

• SELECT is the mandatory keyword that begins every query,

• column1, column2, … is a placeholder for the list of columns to be retrieved. This is especially useful when dealing with large tables as you do not always want to display all columns every time. To display all columns, replace the list with the * character.

• FROM is another mandatory keyword that is followed by the name of the table to fetch the data from,

• table_name is the name of the table the data should come from.

• WHERE condition is one of the optional commands that can be attached to the SELECT command. It is used to filter the records by specific conditions.

This is the Read part of CRUD. The simplest form of the SELECT command is used to view all records in a table (all columns and rows):

SELECT * FROM players;

3. UPDATE

The UPDATE command modifies existing records in the database. This is the Update part of CRUD.

To update the details of Christiano Ronaldo to be more accurate, you can use the UPDATE command like this:

UPDATE `players`
SET
    `name` = 'Cristiano Ronaldo',
    `age` = 38,
    `club` = 'Al Nassr'
WHERE
    `id` = 1;

This command changes his name slightly, his club from Manchester United to his current club of Al Nassr, and updates his age to 38.

4. DELETE

The DELETE command removes records from the database. This is the Delete part of CRUD.

It is syntactically similar to the SELECT command, having a basic syntax like this:

DELETE FROM table_name
WHERE condition;

In this structure,

• The DELETE FROM keyword is the mandatory start of any delete queries,

• It is followed by the name of the table to be deleted, represented by the table_name.

• The WHERE condtion is optional where all the rows of the table are to be deleted. But otherwise, it is used to specify the rows to be deleted by matching a condition.

To remove players not playing at the professional level from the table, you can use a command like this:

DELETE FROM `players`
WHERE `level` != 'Professional';

These are the basic commands you’ll use to interact with databases. You can learn more about them in this SQL command cheatsheet.

Summary

Databases are a cornerstone of modern technology, and understanding their fundamental concepts can open doors to building and managing efficient, data-driven systems.

This article introduced you to the basics of what a database is and how relational database models work. You should now have the essential knowledge to navigate the world of databases confidently.

To deepen your understanding, consider exploring the following:

• Hands-On Practice: Use tools like TablePlus to interact with relational databases.

• Learning SQL: Start with beginner-friendly SQL tutorials like this course on freeCodeCamp’s YouTube channel or this SQL command cheatsheet.

• Experiment with Non-Relational Databases: Try MongoDB or Neo4j to explore how other database models work.

If you’re curious to learn more, connect with me on LinkedIn, Twitter, or GitHub. Let’s continue this journey together toward mastering database systems!

They're useful in so many applications, and many other data structures build on top of JavaScript arrays and objects. While the language provides a lot of helpful array and object methods, you can take it even a step further using Collect.js.

This article takes you through:

• What is Collect.js?
• How to install Collect.js
• How to Use Collect.js
• Some Collect.js Methods

What Is Collect.js?

The official docs of Collect.js describes it as a "Convenient and dependency free wrapper for working with arrays and objects."

A simpler way to explain this is that Collect.js is a JavaScript library for working with arrays and objects. It provides a layer on top of the built-in functions to make working with them easier.

Collect.js works how Laravel collections (where the inspiration came from) work. It makes it very easy for Laravel developers, when working with JavaScript, to develop as fast as they would if they were using PHP. But this doesn't mean a native JavaScript developer wouldn't find it really helpful, too.

Collect.js is growing gradually, as it currently has over 6k stars on GitHub and about 200k weekly downloads on NPM at the time of this writing.

How to Install Collect.js

To start using Collect.js in your projects, you have to install it first. Like other JavaScript libraries, you can easily install Collect.js using npm or yarn. You can also install it using a CDN. For this tutorial, we will install it using npm like this:

npm i collect.js

After installation, you can import it into the modules where you need it, like this:

import collect from 'collect.js';

And once it has been imported, you can begin making magic with Collect.js.

You can read about other installation methods on the official documentation website.

How to Use Collect.js

After installation and importation, to use Collect.js in your projects you'll need to convert your required data to a Collect.js collection.

A Collect.js collection is a JavaScript object that has functions not natively available to regular JavaScript arrays and objects.

To create a collection, simply use the collect method imported earlier on any array or object. It's that simple – here's an example:

const students = ['John', 'James', 'Ian', 'David'];

const studentsCollection = collect(students);

Now, using an IDE like WebStorm, you can see methods available to the simple collection you just created:

List of available methods

This article does not cover all of these functions, but you can see that you now have more than JavaScript originally offers through Collect.js.

Some Collect.js Methods

To help understand how much Collect.js simplifies common array and object methods, we'll now look at how to use some very useful Collect.js methods.

The average method (or avg)

This method, as you may assume, calculates an average of a collection of numbers. Here's how you use it:

const numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
const average = collect(numbers).avg();

console.log(average); // 5.5

In this example, we've create an array of numbers between 1 and 10, inclusive. Then we get the average of these numbers using Collect.js.

To do this in vanilla JavaScript, you would have to first sum the numbers using array.reduce or a loop, then get the length of the original array, and divide the sum by this length. See the below example implementation:

const sum = arr => arr.reduce( ( p, c ) => p + c, 0 );
const size = numbers.length;
const average = sum(numbers) / size;

console.log(average); // 5.5

You can see that it's way easier and more elegant using Collect.js.

You can also use the average method directly on more complex structures like an array of objects. Here's an example where the average score of a class of students, stored in an array of students details with a scores key, is calculated using Collect.js:

const studentsDetails = [
    { name: 'John', score: 40, subject: 'Maths' },
    { name: 'James', score: 70, subject: 'Science' },
    { name: 'Ian', score: 50, subject: 'Maths' },
    { name: 'David', score: 60, subject: 'Science' },
];

const studentsCollection = collect(studentsDetails);
const averageScore = studentsCollection.avg('score');
console.log(averageScore); // 55

Here the average is gotten in a very straightforward approach and takes less effort than doing it in vanilla JavaScript. And it uses the short hand avg instead of the longer average.

The chunk method

This method breaks an array into smaller bits based on a given size. This is a common operation when dealing with JavaScript arrays in real life. A common use case would be in pagination of records.

Using the same studentsDetails array created in the last example, I could break down the collection into groups of two using the chunk method, like this:

const studentsCollection = collect(studentsDetails);
const studentGroups = studentsCollection.chunk(2);

This breaks down the original studentsCollection array into two sub collections that look like this:

Collection {
  items: [
    { name: 'John', score: 40, subject: 'Maths' },
    { name: 'James', score: 70, subject: 'Science' }
  ]
}
Collection {
  items: [
    { name: 'Ian', score: 50, subject: 'Maths' },
    { name: 'David', score: 60, subject: 'Science' }
  ]
}

To achieve a similar result in vanilla JavaScript takes more effort as you would require a loop:

const chunkedArray = [];
for (let i = 0; i < studentsDetails.length; i += 2) {
    const chunk = studentsDetails.slice(i, i + 2);
    chunkedArray.push(chunk);
}

console.log(chunkedArray);

The contains method

You can use this method to check whether some key or value exists in a collection. This function allows you to check regardless of the shape or structure of the data.

For example, to check the existing studentDetails for the subject Physics, you can do this:

const studentsDetailsCollection = collect(studentsDetails);

console.log(studentsDetailsCollection.contains('Physics')); // false

To check if we have a student named Science instead of the subject, we could specify what field to check by passing in the field name as a first parameter before the search value:

const studentsDetailsCollection = collect(studentsDetails);

console.log(studentsDetailsCollection.contains('name', 'Science')); // false

You can even check to see if the collection contains values that match a certain condition. Like to see if any student scored more than 50:

const studentsDetailsCollection = collect(studentsDetails);

console.log(studentsDetailsCollection.contains((value, key) => value.score > 50)); // true

To perform any of these checks in vanilla JavaScript would involve using a loop to check each object in the array. You could also do it using the some method.

The diff method

This method is used to get the difference between two collections. The collections could be plain arrays or array of objects.

const numbers = collect([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);
const primeNumbers = collect([2, 3, 5, 7]);

console.log(numbers.diff(primeNumbers).all()); // [ 1, 4, 6, 8, 9, 10 ]

Here we get the numbers that are not prime between 1 and 10 inclusive by removing the array of primeNumbers from the array of those numbers using the diff method.

The get method

This method is used to get values from a collection. If the collection was created from an array, it can accept the array index to return the value at that position.

If the collection was created from an object, it can accept a key and return the value for that key. It returns null when no value is found. You can pass in a default value to prevent it from returning null.

Using the numbers collection created in the last example, you can get the first and twelfth elements, and return 10 if it doesn't exist, like this:

const firstNumber = numbers.get(0);
const twelfthNumber = numbers.get(11, 10);

console.log(firstNumber); // 1
console.log(twelfthNumber); // 10

The firstNumber returns 1 as expected, but the twelfthNumber returns 10, instead of returning null or throwing an error. This is very useful when dealing with user inputs and having optional parameters.

The all method

This method has already been used in a few examples, so you can probably guess it's usage already. You use it to get the object or array under the collection.

Using this method on the numbers collection just returns the original array of numbers:

console.log(numbers.all()); // [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ]

The groupBy() method

If you have any SQL experience, this method name will sound familiar. It works similarly to the SQL function, too: it groups data in a collection by a given key.

Using this method, we can group the students by the subject offered, like this:

const studentsGroupedBySubject = collect(studentsDetails).groupBy('subject');
console.log(studentsGroupedBySubject.all());

This will create two subcollections for the two subjects, maths and physics.

{
  Maths: Collection { items: [ [Object], [Object] ] },
  Science: Collection { items: [ [Object], [Object] ] }
}

The isEmpty and isNotEmpty methods

The isEmpty method checks is a collection is empty, and the isNotEmpty checks otherwise. These methods help prevent the object spoofing vulnerability.

console.log(numbers.isEmpty()) // false
console.log(numbers.isNotEmpty()) // true

The first and last methods

These method names are as descriptive as they can be. The first method gets the first element of a collection. It can also be used to get the first that matches a condition.

For example, to get the first student that scores more than 40 in the subject maths, you can use the first method like this:

console.log(studentsDetailsCollection.first((student) => student.score > 40 && student.subject === 'Maths'));

The last method is also very similar to the first method. It gets the last element of a collection when called with no condition. When a condition is specified, it gets the last element that matches that condition.

For example, to get the last student that fails maths in the studentsDetailsCollection, use this code:

console.log(studentsDetailsCollection.last((student) => student.score < 40 && student.subject === 'Maths'));

This last example returns undefined as no student fails maths.

To acheive these same results using vanilla JavaScript would involve a loop that checks each element and keeps track of the first and last that match the condition, like this:

const getFirstStudentThatPassesMaths = (studentDetails) => {
    let firstStudentThatPassesMaths = undefined;

    studentDetails.forEach((student) => {
        if (student.subject === 'Maths' && student.score > 40) {
            firstStudentThatPassesMaths = student;
            return false;
        }
    });

    return firstStudentThatPassesMaths;
}

const getLastStudentThatFailsMaths = (studentDetails) => {
    let lastStudentThatFailsMaths = undefined;

    studentDetails.forEach((student) => {
        if (student.subject === 'Maths' && student.score < 40) {
            lastStudentThatFailsMaths = student;
        }
    });

    return lastStudentThatFailsMaths;
}

console.log(getFirstStudentThatPassesMaths(studentsDetails));
console.log(getLastStudentThatFailsMaths(studentsDetails));

See how much simpler the Collect.js implementations are.

The macro method

This method is very useful, as it allows you even extend Collect.js further by adding your own methods. It has the following structure:

collect().macro('functionName', function () {
    // function body
});

Here, functionName is the name of the new function you're adding, and the function body is where your new logic goes.

For example, to assign grades to students using our studentsDetailsCollection, we can create a new method called grade. We can make it a bit more complex and have the function modify the collection by adding the calculated grade to each student object.

collect().macro('grade', function () {
    return this.map(item => {
        if (item.score >= 70) item.grade = 'A';
        else if (item.score >= 60) item.grade = 'B';
        else if (item.score >= 50) item.grade = 'C';
        else if (item.score >= 45) item.grade = 'D';
        else if (item.score >= 40) item.grade = 'E';
        else item.grade = 'F';

        return item;
    });
});

You can then call the method on the collection like any other built-in method like this:

studentsDetailsCollection.grade();

After modification, your collection will look like this:

[
  { name: 'John', score: 40, subject: 'Maths', grade: 'E' },
  { name: 'James', score: 70, subject: 'Science', grade: 'A' },
  { name: 'Ian', score: 50, subject: 'Maths', grade: 'C' },
  { name: 'David', score: 60, subject: 'Science', grade: 'B' }
]

Summary

I hope you now understand how to simplify your JavaScript development using simple Collect.js functions. This article only covers some of the most useful methods, but there are more available. To learn more, refer to the official documentation.

If you have any questions or relevant advice, please get in touch with me.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

You've probably encountered cloud requirements in job listings, heard people talk about it in conversations, or seen ads by companies offering cloud services – and now you're interested in learning more about it.

In this article, I'll demystify the cloud for you, breaking down complex concepts into easy-to-understand bits.

Here's what we'll cover in this article:

• The World Before Cloud Computing
• What Is Cloud Computing?
• Cloud Service Providers
• Different Cloud Services
• Challenges that Cloud Computing Helps Solve
• How Can Cloud Computing Help You?
• Cloud Deployment Models

So let's dive in and learn cloud computing basics so you can start your journey into this growing field.

The World Before Cloud Computing

To understand cloud computing, it is important to understand the problem cloud computing tries to solve. This will help you understand its history and appreciate the convenience it brings into the world of technology.

Before the cloud, deploying web services was a very expensive process. To deploy a web application, you had to purchase servers with the correct amount of storage and memory, keep them on-site, setup these servers, and use them to host your application.

This comes with many additional costs, other than the original server costs, like electricity and the data required to keep your server up and online every time. There are also security concerns, as you'll need to prevent your server from being damaged or stolen by bad actors.

Beyond all this, many developers are not exactly server specialists. So you would either have to train your developers to become system admins or hire system admins to setup and configure the servers to work with your applications. Then to deploy a new application, you'd have to repeat the whole process with new costs.

Scaling applications wasn't easy before the cloud. If you tried to manage costs and not buy too many servers, or hire many system engineers, you might end up with insufficient compute power and you'd need to scale. Scaling means buying more servers (or replacing existing ones with better ones), configuring them to match existing configurations, and deploying your applications on them.

These are some of the issues we faced before cloud computing. Now, you might wonder, "how does cloud computing solve these?". Let's find out.

What Is Cloud Computing?

Cloud computing is the on-demand availability of computer system resources, especially data storage (cloud storage) and computing power, without direct active management by the user.

Cloud computing relies on sharing of resources to achieve coherence and typically uses a pay-as-you-go model, which can help in reducing capital expenses but may also lead to unexpected operating expenses for users. – Wikipedia

Think of Cloud Computing like renting a computer from a cybercafe. In a cybercafe, they have the computers ready for use – you just come in, pay for your desired time, and use the computer for that period.

In the case of cloud computing, you don't have to worry about buying the computer, or protecting it, or covering the running costs. The cloud provider covers all these worries. You rarely ever have to worry about software issues, or the absence of specific software on these computers. And the best part? You only pay for the computer you use, and only for the time you use it.

So in more relatable terms, say you have a web application to deploy. You go to a cloud service provider, select specific server requirements your application requires, select software dependencies, and deploy your application, with zero worries.

Cloud computing as explained to a five year old is "letting someone else manage your computing needs". You have some website that you need to put online, or maybe a mobile app, or some other piece of technology, and instead of having to buy servers to deploy it, someone else buys and sets up the servers. You simply upload your files. Its almost the same as renting an apartment, but now you are renting compute space on another computer.

Cloud Service Providers

Companies that provide these cloud services are called cloud services providers. These companies already have many servers, and system engineers. They're concerned with all the stuff you don't have to worry about like server costs and running costs. They provide a web interface where you can access their servers and use them when you need.

Currently, the most popular cloud service providers are Google (Google Cloud Platform), Amazon AWS (Amazon Web Services), and Microsoft (Azure). These companies offer similar services, but with various different price models, features, and so on.

Here's a summary of what each of them offers:

Google

They offer IaaS (Compute Engine), Containers As A Service, CaaS, (Kubernetes Engine), and PaaS (App Engine) services. They also offer data storage services, with tools like Google Cloud Storage, Cloud SQL, and Cloud Bigtable.

AWS

This is the first cloud services provider. They offer IaaS (Elastic Compute, EC2), CaaS (Elastic Kubernetes Service, EKS) and PaaS (Elastic Beanstalk) services. They also offer data storage services, with tools like Amazon S3, and DynamoDb.

Microsoft

They offer IaaS (Virtual Machine), CaaS (Kubernetes Service, AKS), and PaaS (App Service) services. They also offer data storage services, with tools like Cosmos DB.

To maximise resources, cloud service providers have their clients sharing servers. The clients don't have to know or worry about this allocation.

Now let's understand what some of the above acronyms/terms mean so you have a better idea of what these cloud services are.

Different Cloud Services

Cloud service providers offer plenty of different computing services. Here are the most common services:

SaaS (Software As A Service)

Geek Super

This service involves you just using some software without any knowledge of its source code or host environment or development details. You just use it and trust that it's being managed and updated properly.

PaaS (Platform As A Service)

Here, you focus only on your application development as everything has been handled (the hardware, computing environments, and required software).

IaaS (Infrastructure As A Service)

This is the most flexible of all cloud services. Here you have the most control over things. You can customise and change things as you see fit.

But you don't own the servers. The cloud service provider only provides you with the infrastructure you need, and you are responsible for creating your own computing environments and installing required software for your application to work.

The idea here is similar to buying hardware. The only difference is that now, you're renting it and it's virtual (to you).

Challenges that Cloud Computing Helps Solve

Cloud computing removes the need to purchase and setup servers on-site every time a new application needed to be developed. This is a great advantage as it saves companies a lot of money – both on servers, as well as on the engineers who would setup and configure the servers to work for your applications.

Scaling isn't usually easy as it often involves a lot of setup and configuration. For example, to provide more storage for an application, you could get an external data store and connect to the current server. This definitely has its limits, though.

A point will likely come when you need a better server, or an additional server. Either way, every software that you installed to get it running on the former server would have to be installed again on the new server. Application files would have to be moved, and so on.

Also, these applications are often underutilising these hardware resources. This, from the business perspective, is a loss. An initial fix to this was virtualisation. This basically meant creating virtual (contained) environments where the application has all of its required software and can function well on the servers. These led to better utilisation of resources. But it definitely wasn't the perfect solution.

How Can Cloud Computing Help You?

Cloud computing offers plenty benefits, some of which are highlighted here:

• It is cheaper to pay a subscription than to build a whole datacenter. You have plans that let you use only what you use.
• Scaling and management are responsibilities of the cloud service provider.
• Easy setup. Developers get to just focus on the code as all the actual server and hardware setup can be done using a user interface provided by the cloud service provider. This leads to faster development time, and applications are shipped faster.
• Accessibility. Cloud service providers tend to have multiple datacenters around the world, making sure your users always get access to your services as fast as possible.
• Data security. Because your data is not stored in your physical space anymore, it's more difficult for bad actors to come in and damage or steal your servers.

Cloud Deployment Models

A cloud deployment model determines where your data (and applications) is stored and how your customers interact with it.

Public cloud

Here, everything is handled by the cloud service provider. It's the most popular model. When using a public cloud, you're not concerned about the server maintenance, and you're sure of high reliability and the possibility of unlimited scalability. This usually means you share servers with other people.

It is the cheapest model, and companies usually use a pay-as-you-go model (so you never have to commit too much upfront financially).

A potential issue with this is, having everything handled by the cloud service provider means you have little or no control over the services.

Private cloud

This is similar to the traditional ways of hosting applications. Here, you do have your own data centers. This means that only you get to use the servers and have unlimited control over them.

Now, the difference is, you provide a self service interface to users of your servers, usually developers in your company. You still have to manage the physical servers, and you're responsible for their maintenance and scaling.

This method can be more secure than the public cloud, as you have control and can setup as much security as possible. It also comes in very handy if you have data that must not get out due to compliance requirements.

But this method is way more cost intensive, although with a more predictable or fixed pricing model. These servers and hardware are expensive. Having them means having someone to manage them, which means more expense. Scaling also means buying new devices every time.

Hybrid cloud

Hybrid Cloud | W3Codemasters

This approach merges both the public and cloud methods. It basically allows you more flexibility. You get to use public services, and also setup your own when you need to. Here you're able to comply with regulations, through your servers, and also offer high accessibility, through a cloud service provider. This method is perfect for organizations trying to migrate from one model to another, keeping the business operation during the transition.

An example application of this would be hosting an application on a public cloud and connecting it to a database on a secured private cloud.

This method is also more expensive than the public cloud as you do have to cater for some of the servers. It could also get complicated as data gets distributed between multiple servers and applications.

How to Choosed a Deployment Model

The choice between public, private, or hybrid cloud deployment models depends on an organization's unique requirements.

• Public clouds offer cost efficiency and global reach
• Private clouds provide enhanced security and control
• Hybrid clouds offer flexibility and a strategic balance between the two.

The decision ultimately hinges on factors such as security needs, compliance requirements, scalability, and the level of control desired over the IT infrastructure.

Conclusion

Now you should have have enough information to kickstart your career in cloud computing.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

Message queues help solve this problem by providing an additional branch to the usual request-response journey. This additional branch helps make sure users can get immediate responses, and the time-consuming processes can be done on the side. Everybody goes home happy.

This article will focus on explaining what message queues are and how to get started with them by building a very simple application. You should be familiar with the basics of Node.js, and you should have Redis installed either locally or on a cloud instance. Learn how to install Redis here.

What is a Queue?

A queue is a data structure that allows you store entities in an order. Queues use a first-in-first-out (FIFO) principle.

The concept of queues in computer science is the same as the concept of queues in everyday life where people line up to get things. You join a queue from the back, wait till it is your turn, then leave the queue from the front after you have been attended to.

In computer science, when a process like an API request is running, and you need to remove a certain task (like sending an email) from the current flow, you push it to a queue and continue the process.

The diagram below illustrates the lifecycle of a queue:

Queue Lifecycle | https://optimalbits.github.io/bull/

What is a Job?

A job is any piece of data that is used on a queue, usually a JSON-like object.

As demonstrated in the cover image of this article, you can think of a job as each person on a queue at an airport. Each person holds a briefcase containing specific data, and other instructions (passports and maybe medical papers where required) that will help when it is their turn to be attended to.

New people joining this queue will enter from the back (as the last person), and people will be attended to from the front. That is how jobs are also processed, each job contains data that will be used for its processing. New jobs are added from the back while jobs are taken out from the front.

What is a Job Producer?

A job producer is any piece of code that adds a job to a queue. In real life, this would be the security guard at the airport that gives direction to people, telling them which queue to join for different purposes.

A job producer can exist independently of a job consumer. This means that in a microservice setup, a particular service might just be concerned with adding jobs to a queue, but not how they're processed after.

What is a Worker (Job Consumer)?

A worker, or job consumer, is a process, or function, that can execute a job. Think of a worker as a bank cashier attending to people on a queue at the bank. When the first person comes in, they join the queue as the only one on the queue. The cashier then calls for them and the queue is emptied.

The cashier requests for specific details to be used to process the transaction from the person. While the cashier attends to that customer, four more customers could have lined up. They will remain on the queue till the cashier is done with the first customer before calling for the next one. This is the same process with queue workers — they pick the first job in the queue, and process it.

What are Failed Jobs?

Often times, some jobs might fail during processing.

Here are some reasons why a job could fail:

• Invalid or missing input data: When data required for a job to be processed is missing, the job will fail. For example, a job to send an email will fail without the recipient's email address.
• Timeout: A job could be failed by the queue mechanism if it is taking longer than usual. This could be due to an issue on a dependency of the job or something else, but usually you don't want a single job running forever.
• Network or infrastructure problems: These problems are almost out of your control, but they do happen. A database connection error for example would fail a job.
• Dependency issues: Sometimes a job relies on some external resources to function well. Whenever these other resources are unavailable or unsuccessful, the job will fail.

When jobs fail, you can configure your queue mechanism to retry them. You can either retry the job immediately, or after a calculated amount of time. You can set a maximum number of attempts, which is recommended. If not, you end up running a job that will always fail infinitely.

Why Use Queues?

Queues are useful for creating robust communication channels between microservices. Multiple services can use the same queue. Different services could be tasked with different problems. When a service completes its task, it can push a job to another service that has workers waiting for that job. That service will pick it up and do whatever is needed with the data.

Queues are also useful for offloading heavy tasks from a process. As you'll see in this article, a time consuming task like sending of an email can be put on a queue to avoid slowing down response time.

Queues help avoid single points of failure. A process that has the ability to fail and can be retried is best processed using a queue where it can be retried after a while.

How to Build a Simple Application that Uses Queues

In this article, we'll build a simple project using Node.js and Redis. We'll use the Bull library as it simplifies a lot of the complexities involved in building a queue system. The project will have a single endpoint to send emails.

Create a New Node.js Project

mkdir nodejs-queue-project
cd nodejs-queue-project
npm init -y

The commands above will create a new folder named nodejs-queue-project and a package.json file in it. The package.json file should look like this:

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

Install the Required Dependencies

npm i express @types/express @types/node body-parser ts-node ts-lint typescript nodemon nodemailer @types/nodemailer

The commands above will install the different packages and dependencies required for the project.

After installation, you can update the scripts section of your package.json to have a dev command. Your whole package.json file should look like this now:

{
  "name": "nodejs-queue-project",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon src/app.ts"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "@types/express": "^4.17.17",
    "@types/node": "^20.3.3",
    "@types/nodemailer": "^6.4.8",
    "body-parser": "^1.20.2",
    "express": "^4.18.2",
    "nodemailer": "^6.9.3",
    "nodemon": "^2.0.22",
    "ts-lint": "^4.5.1",
    "ts-node": "^10.9.1",
    "typescript": "^5.1.6"
  }
}

The file above shows all your installed dependencies. The npm run dev command will run when you use the dev script.

How to Build the Endpoint

The first thing to do is to create a new folder named src. This folder will contain all your code files. The first file it will contain is the root file of the application — the app.ts file as defined in the package.json file.

We'll use the app.ts file to import required packages and create a simple server with a single endpoint to send a email as seen below:

import express from "express";
import bodyParser from "body-parser";
import nodemailer from "nodemailer";

const app = express();

app.use(bodyParser.json());

app.post("/send-email", async (req, res) => {
  const { from, to, subject, text } = req.body;

  // Use a test account as this is a tutorial
  const testAccount = await nodemailer.createTestAccount();

  const transporter = nodemailer.createTransport({
    host: "smtp.ethereal.email",
    port: 587,
    secure: false,
    auth: {
      user: testAccount.user,
      pass: testAccount.pass,
    },
    tls: {
      rejectUnauthorized: false,
    },
  });

  console.log("Sending mail to %s", to);

  let info = await transporter.sendMail({
    from,
    to,
    subject,
    text,
    html: `<strong>${text}</strong>`,
  });

  console.log("Message sent: %s", info.messageId);
  console.log("Preview URL: %s", nodemailer.getTestMessageUrl(info));

  res.json({
    message: "Email Sent",
  });
});

app.listen(4300, () => {
  console.log("Server started at http://localhost:4300");
});

Now, you can start your server by running npm run dev in your terminal. You should see a message saying Server started at [http://localhost:4300](http://localhost:4300) in your terminal.

npm run dev message

You can now test the endpoint using a tool like Postman:

Endpoint testing on Postman

The request took almost 4 seconds as shown in the screenshot. This is very slow for an endpoint. If you take a look at your terminal, you should also see a URL where you can preview the email that was sent.

Opening the link lets you see how the email looks.

Email content

How to Create the Queue

To make the process even faster, the email can be queued to be sent later and a response sent to the user immediately.

To do this, install the bull library and its @types library as we'll use it to create a queue. That is:

npm i bull @types/bull

Creating a new queue using bull is as easy as instantiating a new Bull object with a name for the queue:

// This goes at the top of your file
import Bull from 'bull';

const emailQueue = new Bull("email");

When the queue is created with just the queue name, it tries to use the default Redis connection URL: localhost:6379. If you prefer using a different URL, simply pass in a second object to the Bull class as an options object:

const emailQueue = new Bull("email", {
  redis: "localhost:6379",
});

At this point, you can create a simple function to serve as a job producer and add a job to the queue every time a request comes in.

type EmailType = {
  from: string;
  to: string;
  subject: string;
  text: string;
};

const sendNewEmail = async (email: EmailType) => {
  emailQueue.add({ ...email });
};

This newly created function, sendNewEmail, accepts an object containing details of the new email to be sent of type EmailType. There's sender email address (from), recipient email address (to), subject of the email, and the content of the email (text). Then it pushes a new job to the queue.

You can use this function instead of sending the email during the request now. Modify the endpoint to do this:

app.post("/send-email", async (req, res) => {
  const { from, to, subject, text } = req.body;

  await sendNewEmail({ from, to, subject, text });

  console.log("Added to queue");

  res.json({
    message: "Email Sent",
  });
});

At this point, the code is simpler and the process is faster. The request only takes about 40m — about 100x faster than before.

Endpoint testing with Postman

At this point, the email is added to a queue. It will remain on the queue until processed. The job can be processed by the same application or another service (if in a microservice setup).

How to Process the Jobs

The cycle is incomplete and useless if the mails never leave the queue. We'll create a job consumer to process the jobs and clear the queue.

We can do this by creating the logic for a function that accepts a Job object and sends the email:

const processEmailQueue = async (job: Job) => {
  // Use a test account as this is a tutorial
  const testAccount = await nodemailer.createTestAccount();

  const transporter = nodemailer.createTransport({
    host: "smtp.ethereal.email",
    port: 587,
    secure: false,
    auth: {
      user: testAccount.user,
      pass: testAccount.pass,
    },
    tls: {
      rejectUnauthorized: false,
    },
  });

  const { from, to, subject, text } = job.data;

  console.log("Sending mail to %s", to);

  let info = await transporter.sendMail({
    from,
    to,
    subject,
    text,
    html: `<strong>${text}</strong>`,
  });

  console.log("Message sent: %s", info.messageId);
  console.log("Preview URL: %s", nodemailer.getTestMessageUrl(info));

  return nodemailer.getTestMessageUrl(info);
};

The function above accepts a Job object. The object has useful properties that shows the status of and data in a job. Here, we use the data property.

At this point, all we have is a function. It doesn't pick up jobs automatically because it doesn't know which queue to work with.

Before connecting it to the queue, you can go on to add a few jobs to the queue by sending some requests. You can check the email jobs currently queued by running this command in your redis-cli:

LRANGE bull:email:wait 0 -1

This checks the email waitlist, and returns the ids of the waiting jobs.

Redis CLI

I have created a few jobs just to show how workers actually work.

Now, connect the worker to the queue by adding this line of code:

emailQueue.process(processEmailQueue);

This is what your app.ts file should now look after that:

import express from "express";
import bodyParser from "body-parser";
import nodemailer from "nodemailer";
import Bull, { Job } from "bull";

const app = express();

app.use(bodyParser.json());

const emailQueue = new Bull("email", {
  redis: "localhost:6379",
});

type EmailType = {
  from: string;
  to: string;
  subject: string;
  text: string;
};

const sendNewEmail = async (email: EmailType) => {
  emailQueue.add({ ...email });
};

const processEmailQueue = async (job: Job) => {
  // Use a test account as this is a tutorial
  const testAccount = await nodemailer.createTestAccount();

  const transporter = nodemailer.createTransport({
    host: "smtp.ethereal.email",
    port: 587,
    secure: false,
    auth: {
      user: testAccount.user,
      pass: testAccount.pass,
    },
    tls: {
      rejectUnauthorized: false,
    },
  });

  const { from, to, subject, text } = job.data;

  console.log("Sending mail to %s", to);

  let info = await transporter.sendMail({
    from,
    to,
    subject,
    text,
    html: `<strong>${text}</strong>`,
  });

  console.log("Message sent: %s", info.messageId);
  console.log("Preview URL: %s", nodemailer.getTestMessageUrl(info));
};

emailQueue.process(processEmailQueue);

app.post("/send-email", async (req, res) => {
  const { from, to, subject, text } = req.body;

  await sendNewEmail({ from, to, subject, text });

  console.log("Added to queue");

  res.json({
    message: "Email Sent",
  });
});

app.listen(4300, () => {
  console.log("Server started at http://localhost:4300");
});

Once you save, you'll notice that the server restarts and immediately starts sending out mails. This is because the worker sees the queue and begins processing immediately.

Server sending out queued emails

Now, both the producer and the worker are active. Every new API request will be pushed to the queue, and the worker will immediately process it unless there's some pending jobs already.

Summary

I hope this article helped you understand what a message queue is, how to add jobs and create processes to run them, and how you can use them to build better web applications. You can find the code files used in this article on GitHub.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

To solve this problem, you can create multiple functions to solve for the different possible number of inputs. Or we can write a large function to solve this problem.

A better way to do this is to create different variations of a simple function to solve for the different scenarios you have.

In this tutorial you will learn how PHP lets you easily solve this problem using method overloading and how you can use these variations.

What is Polymorphism?

Polymorphism is a Greek word that literally means many forms. In programming terms, it is defined as the ability of objects of different classes to respond differently based on the same message.

Polymorphism is the concept that lets classes share the same interface and have different definitions for the same method. Polymorphism is one of the key concepts in Object Oriented Programming (OOP).

What is Method Overloading?

Method overloading is a concept that allows you to have a method that can perform differently based on its number of parameters. It allows you have multiple definitions for a same method in the same class.

This method will have the same name for all its uses, but might produce different output in different situations. Method overloading is a key concept under the umbrella of polymorphism.

Traditional Overloading

For example, say you have an add method that you want to use to sum a couple of numbers in these ways:

function add(int $a, int $b): int
{
    return $a + $b;
}

function add(int $a, int $b, int $c): int
{
    $sum = $a + $b + $c;
    return $sum > 10 ? 10 : $sum;
}

In the first definition, the method takes in two parameters and simply returns their sum. In the second definition, it takes three parameters and it returns the sum of these only when it's equal to 10 or less.

Now, unlike other programming languages, PHP doesn't really let you redefine a method multiple times like this:

class SampleClass
{
    function add(int $a, int $b): int
    {
        return $a + $b;
    }

    function add(int $a, int $b, int $c): int
    {
        return $a + $b + $c > 10 ?? 10;
    }
}

You would get an error like this: PHP Fatal error: Cannot redeclare SampleClass::add(). But PHP supports method overloading using a magic keyword, __call.

The __call keyword

This is a magic method that PHP calls when it tries to execute a method of a class and it doesn't find it. This magic keyword takes in two arguments: a function name and other arguments to be passed into the function. Its definition looks like this:

function __call(string $function_name, array $arguments) {}

Using this magic method, you can create as many methods and as many variations of each of these methods as you like.

For example, to achieve our intended goal with the add function, update the __call definition and your SampleClass to be like this:

class SampleClass
{
    function __call($function_name, $arguments)
    {
        $count = count($arguments);

        // Check function name
        if ($function_name == 'add') {
            if ($count == 2) {
                return array_sum($arguments);
            } else if ($count == 3) {
                return array_sum($arguments) > 10 ? 10 : array_sum($arguments);
            }
        }
    }
}

The code is pretty self explanatory. Here's a step by step breakdown:

• Use the count method to know how many arguments are passed to your method.
• Check the function name being passed in. This __call will house all the different methods you intend to create variations of, so the name should be unique and be used to group variations of the methods.
• Handle the logic as you like based on the different number of arguments. Here, we return the sum as is when we have two arguments. We return the sum if it's less than 10 when we have three arguments.
• When you call the add method, PHP checks for a method with the same name in the class, if it doesn't find it, it calls the __call method instead, and that is how the code is run.

To call the add method now, create a new instance of the SampleClass class and try it out.

$sampleObject = new SampleClass;
echo $sampleObject->add(12, 12) . PHP_EOL; // Outputs 24 
echo $sampleObject->add(12, 2, 6) . PHP_EOL; // Outputs 10

The two variations work perfectly 🥳.

Applications Of Method Overloading

• In sort functions: The method can be programmed to behave differently with no arguments, or with one argument, or with two depending on what you're trying to solve.
• Payment processing: The same method can be used to handle different payment processors. And these processors often need a different number of inputs to work well. So the method can figure which to use based on the number of arguments passed in and react accordingly.
• Database wrappers: A database class could have a "query" method that can handle different types of queries with the same method name. For example, the "query" method could take in a SELECT query, an INSERT query, or an UPDATE query as parameters and execute the appropriate query based on the parameter passed in.

Summary

I hope you now understand what method overloading is in PHP, and how you can use it to write better PHP applications.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

In this tutorial, you'll learn the basics of how Redis works, when to use it, how to install it on your device, and how to use it as a caching system in a PHP web application.

What Is Redis?

Redis is a data store – like a database, but one that stores data primarily in-memory. This makes it much faster than traditional databases where data is stored in disks. Because of this speed, Redis is often used as a caching tool.

Redis can store data in any data type, as it uses a key-value pair system to store data. This is also unlike traditional databases that use documents or rows.

You can think of a Redis database as a big JSON object, where everything in the database is a key-value pair. This means it might not be the best place to store structured data.

You can also use Redis as a database, as it has the ability to write data to disk for persistence. You can configure Redis to persist data either periodically or after every command you issue. When Redis isn't configured to persist data, it is very volatile, and a system crash would result in a loss of data.

Redis is popular in production level applications and it's used by large companies like Twitter, Github, SnapChat and StackOverFlow.

When to Use Redis

• For One Time Passwords (OTPs): These are usually generated to be used once, and have short lifespans. With Redis' ability to set an expiry date for data, you can safely store the OTP without worrying about deleting them after a certain period.
• For frequently accessed resources: For data that doesn't change too frequently but is accessed a lot, you can use Redis to save time that would have been spent querying the database or making a call to some external service.
• For heavy duty queries: For database queries that take time, and also won't change too often, use Redis to reduce this time by storing the results for as long as you like.

How to Install Redis

You can install Redis on any operating system. Here are the instructions for macOS, Windows Subsystem for Linux, and Linux.

macOS

To install Redis on macOS, run:

brew install redis

Then, run this command to start Redis:

redis-server

Windows Subsystem for Linux and Linux

Redis doesn't exactly support the Windows operating system yet, so you can install WSL (Windows Subsystem for Linux) on windows to have a Linux environment.

To install Redis on Linux, run:

curl -fsSL https://packages.redis.io/gpg | sudo gpg --dearmor -o /usr/share/keyrings/redis-archive-keyring.gpg

echo "deb [signed-by=/usr/share/keyrings/redis-archive-keyring.gpg] https://packages.redis.io/deb $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/redis.list

sudo apt-get update
sudo apt-get install redis

Then, run this command to start Redis:

sudo service redis-server start

Now that Redis is installed, you can test it by running redis-cli ping. This will output "PONG". Like this:

Testing Redis Installation

Redis Basics

To use Redis as a REPL or as a standalone application, run redis-cli. It will open the REPL environment.

How to Set Data

Use the SET keyword to set a key value pair in Redis. To set a username key to the value Zubs , run this:

SET username Zubs

Setting a key-value pair

How to Get Data

To get the recently saved username key, use the GET keyword like this:

GET username

Getting a value by key

How to Delete Data

You can also delete a previously stored key using the DEL keyword like this:

DEL username

Deleting a value by key

How to Check if a Value Exists

You can check for the existence of a key by using the EXISTS keyword. It returns 0 when the key doesn't exist, and 1 if it does. You can test by checking if the recently deleted username key exists. Like this:

How to Set a Time to Live for Keys

Redis lets you specify how long some key should exist for when creating it. This is one really great feature of Redis. To do this, use the SETEX keyword like this:

SETEX key seconds value

You can check the time to live for a specific key using the TTL keyword. This returns -1 if the key has no set expiration, meaning it will be stored indefinitely. It returns -2 if the key doesn't exist. And it returns the time in seconds if the key exists.

You can set an expiration time in seconds for a key previously created without an expiration time using the EXPIRE keyword. For example, create a key to store a variable age with a value of 26.

SET age 26

Then, set an expiration time of 10 seconds for it.

EXPIRE age 20

Check the time left to live repeatedly a couple of times to see how it actually reduces and eventually doesn't exist again.

How to Build a Simple Application with Redis

To help you understand how Redis works, we'll now build a basic web application that uses Redis to cache data to load responses faster. You'll be building a simple application that fetches images data from JSONPlaceholder and returns them.

Create a New PHP Project Using Composer

Create a new folder for the project, change directory into the newly created folder, and run the following compound to create a new composer project:

composer init -q

This will create a new composer.json file that should look like this:

{
    "require": {}
}

Next, create a public folder to house your public facing code files. Then create a new index.php file in the folder.

Put in some boilerplate content in the PHP file for now and start a server.

<?php

echo "Hello World!";

php -S localhost:8080

Install a Simple Router and Handle Requests

To complete the project, install a simple PHP router, Altorouter, and a web client, Guzzlehttp.

composer require altorouter/altorouter guzzlehttp/guzzle

Update the index.php to contain this code:

<?php

// Import composer autoload file
require_once __DIR__ . '/../vendor/autoload.php';

// Import GuzzleHttp Client
use GuzzleHttp\Client;

// Instantiate router and web client
$router = new AltoRouter();
$client = new Client();

// Register Sample route
$router->map('GET', '/', function () {
    // Set response Content-Type
    header('Content-Type: application/json; charset=utf-8');

    // Return basic response
    echo json_encode(['data' => 'Hello World']);
});

/**
 * Route to get all photos
 */
$router->map('GET', '/photos', function () use ($client) {
    // Make request to JSONPlaceholder
    $response = $client->request('GET', 'https://jsonplaceholder.typicode.com/photos');

    header('Content-Type: application/json; charset=utf-8');
    echo json_encode([
        'data' => json_decode($response->getBody()->getContents())
    ]);
});

/**
 * Route to get single photo by id
 */
$router->map('GET', '/photos/[i:id]', function (int $id) use ($client) {
    $response = $client->request('GET', 'https://jsonplaceholder.typicode.com/photos/' . $id);

    header('Content-Type: application/json; charset=utf-8');
    echo json_encode([
        'data' => json_decode($response->getBody()->getContents())
    ]);
});

$match = $router->match();

if( is_array($match) && is_callable( $match['target'] ) ) {
    call_user_func_array( $match['target'], $match['params'] );
} else {
    // no route was matched
    header( $_SERVER["SERVER_PROTOCOL"] . ' 404 Not Found');
}

The code is pretty self explanatory. But, here's a breakdown for clarity. From lines 1-11, the required classes GuzzleHttp and AltoRouter are imported and instantiated.

From lines 14-20, the first route is registered, with a simple closure that returns "Hello World!". Lines 25-45 register two more routes, one to fetch all photos, /photos and another to fetch a single photo, /photos/id.

The final lines are required based on documentation of the router package to actually execute the closures set in the routes declaration.

You can test these routes using Postman.

Hello World route

Get All Photos route

Get a Single Photo route

The /photos route takes an average of 1400ms per request. The /photos/id takes an average of 900ms per request.

Install and Instantiate Redis

These times can be reduced by caching the results of the original request to JSONPlaceholder, then returning a response from the cache instead of making a request every time.

To use Redis with PHP, install the PhpRedis extension. This extension provides an API for communicating with Redis. You can easily install it using the command:

pecl install redis

After installation, you can then use this class in your PHP project. Import the class and instantiate it at the top of your index.php file:

$redis = new Redis();
$redis->connect('127.0.0.1');

Having done this, you can now use Redis in your project.

How to Cache Data with Redis

Store the raw JSON response returned from JSONPlaceholder to Redis with an expiry time of 1 hour (3600 seconds).

$response = $client->request('GET', 'https://jsonplaceholder.typicode.com/photos');

$redis->setex(
    'photos',
    3600,
    $response->getBody()->getContents()
);

Here, you create a new key called photos, give it an expiration time of 1 hour, then assign it the raw response gotten from JSONPlaceholder.

But at this point the API still takes a long time to respond. This is because you're only storing this response, you're not using Redis to return the response.

To fix this, when a new request comes in, check if you have some data previosuly stored in-memory. If yes, you return the data in-memory, else, you make a call to JSONPlaceholder.

Update the /photos block to this:

/**
 * Route to get all photos
 */
$router->map('GET', '/photos', function () use ($client, $redis) {
    // Check if Redis has the key
    if (!$redis->exists('photos')) {
        $response = $client->request('GET', 'https://jsonplaceholder.typicode.com/photos');

        // Store the data for next use
        $redis->setex(
            'photos',
            REDIS_STANDARD_EXPIRY,
            $response->getBody()->getContents()
        );
    }

    header('Content-Type: application/json; charset=utf-8');
    echo json_encode([
        'data' => json_decode($redis->get('photos'))
    ]);
});

Testing in Postman to see improvements, you see the average response time after the first call (the original call before it is cached) has dropped to an average of 20ms for the /photos route. This is an improvement of over 50x. Redis saves a lot of processing time and power.

Update the /photos/id route to use Redis too:

$router->map('GET', '/photos/[i:id]', function (int $id) use ($client, $redis) {
    if (!$redis->exists('photos:' . $id)) {
        $response = $client->request('GET', 'https://jsonplaceholder.typicode.com/photos/' . $id);

        $redis->setex(
            'photos:' . $id,
            REDIS_STANDARD_EXPIRY,
            $response->getBody()->getContents()
        );
    }

    header('Content-Type: application/json; charset=utf-8');
    echo json_encode([
        'data' => json_decode($redis->get('photos:' . $id))
    ]);
});

The /photos/id route is now also much faster as it takes less than 5ms to get a response, an improvement of over 45x.

Summary

I hope you now understand the what Redis is, its basics, and how you can use it to enhance the speed of your PHP web applications. You can find the code files used in this article on GitHub.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

Here's a helpful description of them from the MySQL docs:

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.

Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update. - MySQL.com

Triggers are useful for automating repetitive tasks. You can just set up a trigger to do some calculation after every specific database action. You can also set up a trigger to perform data validation tasks on a table.

A trigger gets fired when an INSERT, UPDATE or DELETE operation happens on a database table. A trigger is fired per row, so if multiple rows of data are being inserted or deleted, each one still fires the action setup by the trigger. A trigger can be set to fire before or after an action.

In this tutorial, you'll learn how to create triggers, how to drop them, and when they're useful.

How to Create A Trigger

To create a new trigger, use the CREATE TRIGGER command. This command has the following structure:

CREATE TRIGGER trigger_name
trigger_time 
trigger_event
ON table_name 
FOR EACH ROW
trigger_body

So here's a breakdown of every line:

• The keyword CREATE TRIGGER is mandatory and is followed by the name of the trigger. You'll use this name to refer to the trigger in future, and to delete the trigger if the need ever arises. This name should be unique per database.
• The trigger_time is a variable value that can only be either BEFORE or AFTER. This determines whether the trigger will fire before or after the event has happened.
• The trigger_event is another variable that has a limited number of possible options. This variable cannot be any value other than INSERT, UPDATE, or DELETE. It specifies what event to listen for.
• table_name is the name of the table the trigger should watch. This has to be the name of an existing table in your database, but it can be an empty table.
• The FOR EACH ROW is the other mandatory part of the trigger definition.
• trigger_body is the SQL query that you want to be run when this trigger is fired.

To create an example trigger, I will create a simple users table for practice.

CREATE TABLE
    users (
        fullname VARCHAR(120),
        email VARCHAR(120),
        username VARCHAR(30),
        password VARCHAR(60)
    );

Now, we can create a simple trigger and attach it to this empty table. A trigger that encrypts string passwords before they are inserted using the MD5 function would make sense.

CREATE TRIGGER password_hasher BEFORE INSERT ON users FOR EACH ROW
SET
    NEW.password = MD5 (NEW.password);

This example is pretty straightforward and self-explanatory. But there's a NEW keyword there. This keyword gives you access to the new data being created and lets you use or modify the values as you like.

You can only modify these values if your set event_time is BEFORE. If the event_time is set to AFTER, the data has already been stored before getting to the trigger so it cannot be modified again.

You can use the NEW keyword in INSERT and UPDATE events but not the DELETE event.

There's also the OLD keyword that you can use in DELETE and UPDATE event triggers that gives you access to the former values of the affected record. You can't use this keyword on an INSERT event because there is no previous record before new data is created.

To test this trigger, insert a row into the users table:

INSERT INTO
    users
VALUES
    (
        'idris babu',
        'zubs@test.com',
        'zubby1',
        'password'
    );

Check your table for the values. You should have something like this:

The password value has been correctly encrypted. 🥳

How to Drop a Trigger

After creating a trigger, you might want to stop its execution for some reason. In this case, you can drop the trigger.

To drop the trigger, use the DROP TRIGGER command. The command only requires the name of the trigger. You can use the command like this:

DROP TRIGGER password_hasher;

Running this query will remove the trigger that we created above and every record inserted from now on will not have the password encrypted.

To test this, insert the same record as before, and check the result.

The newly created record doesn't have an encrypted password.

Something to keep in mind: if you drop the table completely, all associated triggers are also dropped automatically.

When to Use Triggers

1. Logging: You can have a trigger to automatically write to another table on insertion, update, or deletion of record from a table.
2. Data validation: You can write a trigger to ensure data is a certain type and correct values can be set when needed.
3. Data syncronisation: You can use a trigger to keep related tables updated. For example, in an ecommerce table, every time a sales record gets created, a trigger can update the vendor's balance. Or if the vendor's record is deleted, a trigger can remove all their products.

Summary

I hope you now understand SQL triggers and when to use them so you can write better queries.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

These queries over time become less performant, difficult to read and understand, and even more difficult to manage. And no one wants to do the hard job of refactoring them, so they just live on.

Or you have probably had to fetch similar data based on a set of data or parameters. To achieve this, you write many similar, sometimes exactly identical subqueries and bring them together using the UNION keyword.

Well, you can make your life easier and solve these problems efficiently using a Common Table Expression.

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. – MySQL.com

Using a Common Table Expression, you can write more readable and more performant queries very easily. It's actually easier than it is to write multiple subqueries that could make your queries unreadable and less performant.

You'll primarily use a common table expression for two reasons:

• To write queries without using subqueries (or using fewer subqueries)
• To write recursive functions

In this tutorial, I'll show you how to write your own common table expressions.

How to Create a Common Table Expression

You can create a Common Table Expression (CTE) using the WITH keyword. You can specify multiple common table expressions at the same time by comma-separating the queries making up each common table expression.

The general shape of a Common Table Expression is like so:

WITH cte_name AS (query)

-- Multiple CTEs
WITH
    cte_name1 AS (
        -- Query here
    ),
    cte_name2 AS (
        -- Query here
    )

The WITH keyword is followed by the CTE name. After the name, you introduce the query to be run in the CTE using the AS keyword. You need to enclose the query must in parentheses. The CTE cannot be followed by a semicolon like other SQL queries. Instead it is followed by another query that uses it.

After creating a CTE, you can easily use the result of the queries run in the CTE by referencing the CTE in other queries, other CTEs, or even in itself.

CTE Example

If you have a table of world_cup players, for example, you can create a CTE like this:

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    *
FROM
    barca_players;

Here, we've created a CTE named barca_players. This CTE will return the name, position, age, and nationality of every Barcelona player that was at the world cup. It contains the subquery:

SELECT
    id,
    player_name,
    nationality,
    position,
    TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
FROM
    wc_players
WHERE
    club = 'Barcelona';

This subquery is what produces the CTE result. Next, it is followed by a query that uses this result. You can see the result of selecting every record in the CTE below.

You can also select only specific fields from the CTE, for example:

WITH
    barca_players AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    position
FROM
    barca_players;

This query is almost the same as the first one, except that it selects only the player names and positions from the list.

How to Use Common Table Expressions With Parameters

You can also pass arguments to the CTE. These are aliases you can use for referencing columns of the query results. The number of parameters passed into the CTE must be the same as the number of columns being selected in its subquery. This is because the columns get matched to the aliases one by one, one after the other.

For example, in the barca_players CTE created above, you can decide to refer to the nationality column as country, and position as role:

WITH
    barca_players (id, player_name, country, role, age) AS (
        SELECT
            id,
            player_name,
            nationality,
            position,
            TIMESTAMPDIFF (YEAR, player_dob, CURRENT_DATE) age
        FROM
            wc_players
        WHERE
            club = 'Barcelona'
    )
SELECT
    player_name,
    role
FROM
    barca_players;

Notice that in the CTE subquery, you still use the correct column names. But in the outer SELECT query, you use the new aliases specified as parameters to the CTE.

Recursive Common Table Expressions

When you reference a Common Table Expression within itself, it becomes a recursive Common Table Expression.

A Recursive Common Table Expression, as the name implies, is a common table expression that can run a subquery multiple times, as long as a condition is met. It iterates continuously until it reaches a break point, when the condition stops being true.

To define a recursive CTE, the RECURSIVE keyword must be in its name. Without this keyword, MySQL throws an error.

For example, you can write a common table expression that prints numbers 1 to 10 and their squares like this:

WITH RECURSIVE
    numbers_list (n, square) AS (
        SELECT
            1,
            1
        UNION ALL
        SELECT
            n + 1,
            (n + 1) * (n + 1)
        FROM
            numbers_list
        WHERE
            n < 10
    )
SELECT
    *
FROM
    numbers_list;

Let's examine what is happening here:

In the first two lines, the recursive common table expression is defined with two parameters, one representing the column for the number, and the other representing the column for the square:

WITH RECURSIVE
    numbers_list (n, square) AS (

Next, the subquery. The subquery is in two parts, joined by the UNION ALL keyword to form one. You can also join these subqueries by the UNION keyword if you don't need duplicate records.

The first part of the subquery is a key part of recursive common table expressions. It is the base query, the first result set, the initial iteration. This query is the starting point of all iterations.

In this example, it is static, as no records are being fetched.

SELECT
    1,
    1

After this first query, the result table has one row, and looks like this:

The second part of the subquery is where the iteration really happens.

In this query, the CTE is referenced within itself, and its columns can be used. When a column name is mentioned, the most recent value of that column is taken.

So at the start of the iteration, n is 1 and square is also 1. That means, n + 1 is 2, and (n + 1) * (n + 1) is 2 *2 which is 4. 2 and 4 get added to the result table and then become the most recent values in the table. n becomes 2, and square becomes 4.

This continues until the condition in the WHERE keyword is stops being true.

The WHERE keyword in the query specifies the breakpoint of the CTE. Until the condition specified is met, the query keeps getting run. In this case, after every iteration, the query checks if n is less than 10.

If a condition that will always evaluate to true is set, then this creates an endless loop and you get an error like Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

SELECT
    n + 1,
    (n + 1) * (n + 1)
FROM
    numbers_list
WHERE
    n < 10

Now you might think, "If the condition checks for n < 10 , how come 10 is still in the final table?".

Well, the reason is because in SQL, the WHERE keyword part of a query is evaluated first before other parts. So, when n = 9 is the last row, the query runs once more, and before insertion or anything, it checks if 9 is less than 10. Since 9 is less than 10, it adds n + 1 which is 10 to the list. Then on the next iteration, 10 is the most recent record and it is not less than itself, so the loop ends.

Keep in mind that a Recursive Common Table Expression consists of a recursive SELECT query, and a non-recursive SELECT query.

Simple Recursive Common Table Expression Rules

• You can't use the GROUP BY keyword. This is because you can only group a collection, but in a recursive common table expression, records are handled and evaluated individually. Other keywords like ORDER BY, DISTINCT, and aggregate functions like SUM cannot be used either.
• You can't use window functions.

These rules apply to the recursive part of a recursive common table expression.

Use Cases for Recursive CTEs

Fibonacci Sequence

The Fibonacci sequence is a sequence in which each number is the sum of the two preceding ones. The sequence commonly starts from 0 and 1, although some authors start the sequence from 1 and 1 or sometimes from 1 and 2. (source)

You can easily generate a Fibonacci sequence of any length using a recursive common table expression. For example, here's a query that will get the first 20 numbers of a Fibonacci sequence starting from 0 and 1.

WITH RECURSIVE
    fibonacci (n, fib_n, next_fib_n) AS (
        /*
        * n - Number of iterations
        * fib_n - Currennt Fibonnaci number. Starts at 0
        * next_fib_n - Next Fibonnaci number. Starts at 1
        */
        SELECT
            1,
            0,
            1
        UNION ALL
        SELECT
            n + 1,
            next_fib_n,
            fib_n + next_fib_n
        FROM
            fibonacci
        WHERE
            n < 20
    )
SELECT
    *
FROM
    fibonacci;

Hierarchical Data Traversal

In many application databases, you will find that hierarchical data is stored in the same table.

For example, a categories table will usually contain main categories and sub-categories referencing their parent category. An employees table will contain regular employees with their manager_id, as well as their managers or supervisors, because they are also employees.

If you had a categories table like this, with 4 records, 1 main category, and a chain of sub-categories:

CREATE TABLE
    categories (
        id int,
        cat_name varchar(100),
        parent_category_id int DEFAULT NULL
    );

INSERT INTO
    categories
VALUES
    (1, 'Mens', NULL),
    (2, 'Tops', 1),
    (3, 'Jerseys', 2),
    (4, 'England', 3);

You can fetch each category, with its parent category attached easily like this:

WITH RECURSIVE
    category_tree AS (
        SELECT
            id,
            cat_name,
            parent_category_id,
            cat_name AS full_name
        FROM
            categories
        WHERE
            parent_category_id IS NULL
        UNION ALL
        SELECT
            c.id,
            c.cat_name,
            c.parent_category_id,
            CONCAT (ct.full_name, ' > ', c.cat_name)
        FROM
            categories c
            JOIN category_tree ct ON c.parent_category_id = ct.id
    )
SELECT
    full_name
FROM
    category_tree;

In this example, the base query selects the root category, where parent_category_id IS NULL. Then it goes on to look for a category where the parent_category_id is the id of the current category by using a JOIN. It repeats this until it gets to the final category. The result of this query is the following:

Summary

I hope you now understand how to use MySQL Common Table Expressions, their variations (regular and recursive), and when to use them so you can write better queries. You can find more about common table expressions in the docs here.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

In this article, you'll learn how MySQL functions help make date management very easy.

These functions help perform various tasks. Some perform simple tasks like adding days to dates, finding how many days are between two dates, or even more complicated tasks like how to tell how far into a year a date is by number of days.

Before proceeding, keep in mind that this article was written on 2023-01-24. So your results on running the queries here might be slightly different based on when you read it.

How to Use the CURRENT_DATE Function in SQL

This function returns today's date in the format 'YYYY-MM-DD'. It is one of the simplest MySQL functions to use. It takes no arguments at all.

SELECT CURRENT_DATE;
-- Returns 2023-01-24

This function has synonymous functions that work just the way it does: CUR_DATE and CURRENT_DATE() will return the exact same result as CURRENT_DATE.

How to Use the ADDDATE Function in SQL

This functions performs additions, or subtractions, on date values. It takes an interval that can be in days, or months, or even years. This interval can be positive or negative. The function takes this format:

ADDDATE(date/expr, INTERVAL expr unit);

Here, the date/expr refers to the base date value to be added to or subtracted from. And the INTERVAL is a constant keyword that has to come before the expr that is used to set the value of the increment in numbers. Finally, you have the unit, which can be day, week, month, quarter or even year. The unit can also be a smaller value like second or even microsecond. Check the MySQL docs for more possible values.

This functions works exactly the same as the DATE_ADD and you can use them interchangeably.

Using ADDDATE, you can find the date of 45 days from today like this:

SELECT ADDDATE(CURRENT_DATE, INTERVAL 45 DAY);
-- Returns 2023-03-10

To get the date of the day 7 months and 3 weeks ago, use the ADDDATE like this:

SELECT ADDDATE(
    ADDDATE(CURRENT_DATE, INTERVAL -7 MONTH), 
    INTERVAL -3 WEEK
);
-- Returns 2022-06-03

Here, we called the ADDDATE function twice. First, to get the date of 7 months ago. Then, we called it again to get the date of 3 weeks before that time.

A common use case of ADDDATE in real life applications is to get data values to be used in a WHERE clause as a range.

For example, if you had an employees table with a hiredate field that stores their resumption date. To see all employees that resumed in the past year (where hiredate > the date of a year ago), use ADDDATE like this:

SELECT * 
FROM employees 
WHERE hiredate > ADDDATE(CURRENT_DATE, INTERVAL -1 YEAR);

Another common case would be when you have to filter by a time range. In a songs table with a released field, to fetch all songs released in the last three weeks except for the ones released this week, use ADDDATE like this:

SELECT * 
FROM songs 
WHERE released 
BETWEEN ADDDATE(CURRENT_DATE, INTERVAL -3 WEEK) 
AND ADDDATE(CURRENT_DATE, INTERVAL -1 WEEK);

How to Use the DATEDIFF Function in SQL

This function returns the number of days between two dates. It takes in the two dates to be subtracted. Let's use DATEDIFF to find the number of days between today and 2023-03-10.

SELECT DATEDIFF('2023-03-10', CURRENT_DATE);
-- Returns 45

Rearranging the dates and calling the function again results in a difference in the response:

SELECT DATEDIFF(CURRENT_DATE, '2023-03-10');
-- Returns -45

You can use this function with the ABS function to get the absolute value and not have issues with the negative sign or value.

SELECT ABS(DATEDIFF(CURRENT_DATE, '2023-03-10'));
-- Returns 45

This is very useful when you have to return data with respect to time. For example, in many blogs, you see a part that says something like 'Posted 7 days ago'. You can use theDATEDIFF` to get this value easily.

How to Use the DATE_FORMAT Function in SQL

This function lets you present your data anyhow you want it. This is a very useful function. It takes in the date to be formatted, and also a string representing the desired format. The function takes this format:

DATE_FORMAT(date, format)

The format string can be of any length and each character in it defines a specific format and must be prefixed by the percentage symbol, %. For example, given the date 2023-03-10, you can present this as Fri 10th March, 2023 like so:

SELECT DATE_FORMAT('2023-03-10', '%a %D %M, %Y');

Here, we passed in the format string '%a %D %M, %Y'. But, what does this truly mean? Here's a few things to note:

• The provided format string, '%a %D %M, %Y', is exactly the same shape as the result, Fri 10th March, 2023. This means you can shape the result anyhow you like – even the space characters matter. Every character in the format string is returned as part of the result, except it is prefixed using the percentage sign, then it is read as a format character. For example, rewriting the format string to '45 days from today is %a, %D day of %M, %Y' will result in 45 days from today is Fri, 10th day of March, 2023.
• The a used results in the abbreviated weekday name, Fri.
• The D returned the day of the month with English suffix, 10th.
• The M returned the name of the month, March.
• The Y returned the year, 2023.

There are many more characters that you can use in the format string, and you can find them here.

How to Use the MAX and MIN Functions in SQL

While these functions aren't limited or specific to date data type, they are very useful when working with dates. You can use the MAX to find the latest record in a table. You can use the MIN to find the oldest record in a table.

In a table of employees, with a birthday field storing their date of birth, you can find the oldest employee using the MAX function like this:

SELECT *
FROM employees
WHERE birthday = (SELECT MAX(birthday) from employees);

Or alternatively, like this:

SELECT *
FROM employees
ORDER BY birthday DESC
LIMIT 1;

You could get the youngest employee using the MIN function:

SELECT *
FROM employees
WHERE birthday = (SELECT MIN(birthday) from employees);

SELECT *
FROM employees
ORDER BY birthday
LIMIT 1;

Summary

I hope you now understand the MySQL date functions we discussed here, their variations and arguments, and when to use them so you can write better queries. You can find more of these functions here.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

Data analysis is commonly done with languages like Python and R. Very few people know that you can also perform Data Analysis with JavaScript, and it is pretty easy, too.

This article focuses on the most basic data analysis functions that you can do in JavaScript. Let's dive in.

How to Find the Average with JavaScript

When you want to find the average of a group of numbers, you add them all together and divide by the number of items you have.

For example, if you have a group of numbers 2, 5, 7, 9, and 12, you add them all together and divide that result by 5 (there are five numbers in your group). So the average is 2+5+7+9+12 = 35, and 35/5 = 7.

The average, in other words, is the result obtained by dividing the sum of all the values in the set by the length or count of the set.

Take this sample set of random numbers between 1 and 1000 as an example:

const data = [
    943, 504, 733, 122, 868, 994, 553, 376, 450, 212,
    295, 859, 29, 820, 148, 589, 621, 870, 941, 909,
    725, 160, 198, 568, 409, 625, 207, 338, 162, 439,
    894, 937, 929, 648, 91, 235, 550, 851, 626, 926,
    190, 770, 33, 274, 79, 355, 768, 504, 415, 232,
    33, 327, 100, 1000, 775, 803, 587, 676, 17, 952,
    931, 838, 447, 358, 282, 606, 877, 185, 514, 263,
    887, 725, 270, 716, 762, 633, 900, 948, 786, 28,
    950, 858, 587, 804, 127, 803, 111, 609, 606, 461,
    947, 868, 43, 432, 113, 607, 852, 698, 984, 575
];

To calculate the average of this set in JavaScript, you can use the array.reduce method (to get the sum of the array) along with the array.length method (to get the count of values in the set) to find the average like this:

const average = data.reduce((a, b) => a + b) / data.length; // Returns 552.35

You could also find the average using a third party library like math.js, like this:

import { mean } from 'mathjs';

const average = mean(...data); // Returns 552.35

You can also find the average using either a for or a forEach loop.

let sum = 0;

for (let datum of data) sum += datum;

const average = sum / data.length; // Returns 552.35

let sum = 0;

data.forEach((datum) => sum += datum);

const average = sum / data.length; // Returns 552.35

How to Find the Maximum and Minimum with JavaScript

When you're working with functions, the maximum and minimum are the largest and smallest values of that function. You can calculate these for a specific range, or for the entire set of values.

Using the same array from above, we can get the maximum value using the max method of the built-in Math module.

const max = Math.max(...data); // Returns 1000

You can also get the minimum value using the min method.

const min = Math.min(...data); // Returns 17

Alternatively, you can also use a third partly library like math.js to find the maximum and minimum values, like this:

import { min, max } from 'mathjs';

const maxValue = max(...data); // Returns 1000
const minValue = min(...data); // Returns 17

You can also decide to find the maximum and minimum values by yourself. You can do this using the array.sort method and picking the first and last items in the list as your minimum and maximum values, respectively. Or you can also do this by using a loop and keeping track of the maximum and minimum values.

const sortedData = data.sort((a, b) => a - b);

const min = sortedData[0]; // Returns 17
const max = sortedData[sortedData.length - 1]; // Returns 1000

let min, max;

for (let datum of data) {
    if (!min || !max) {
        min = datum;
        max = datum;
    } else if (datum < min) min = datum;
    else if (datum > max) max = datum;
}

How to Find the Sum with JavaScript

The sum, or total, is the result of the addition of a sequence of numbers. In the section explaining average above, we saw a way to get the sum of a sequence using array.reduce.

const sum = data.reduce((a, b) => a + b); // Returns 55235

Another very easy way would be to use the sum method of math.js.

import { sum } from 'mathjs';

const sumValue = sum(...data); // Returns 55235

You can also find the sum using a loop if you like.

How to Find the Mode with JavaScript

The mode of a dataset represents the value that appears the greatest number of times in the set. If you're analyzing that dataset, it's the value that you're most likely to find.

You can find the most occurring element of an array by iterating over the array, using an object to map each value to its count, and looping over this object at the end to find the highest value.

To make this easier to illustrate, modify the data array that we've used so far, like this:

data[99] = 33;

Now, you can find the mode like so:

let frequency = {};

for (let datum of data) {
    if (frequency[datum]) frequency[datum] += 1;
    else frequency[datum] = 1;
}

let highestFrequency = 0;
let modeValue = 0; 

for (let datum in frequency) {
    if (frequency[datum] > highestFrequency) {
        highestFrequency = frequency[datum];
        modeValue = datum;
    }
}

You can make this a function if you ever need to do it more than once.

You can find the mode more easily by using the mode method of the math.js library:

const modeValue = mode(...data); // Returns 33

How to Find the Median with JavaScript

If you want to find the median of a dataset, you just need to find the value in the exact middle of the set. This means that the data has to be ordered, or sorted in ascending or descending order – otherwise the middle value has no significance.

You can find the median by sorting the array first, then selecting the item in the middle position if the array has an odd number of elements. If the array has an even number of elements, you select the two items in the middle and find their average.

const sortedArray = data.sort((a, b) => a - b);

const middlePosition = Math.floor(data.length / 2);

const median = data.length % 2 == 0 ? (sortedArray[middlePosition] + sortedArray[middlePosition - 1]) / 2 : sortedArray[middlePosition]; // Returns 597.5

Alternatively, you can find the median by using the median method on math.js.

import { median } from 'mathjs';

const medianValue = median(...data); // Returns 597.6

Summary

I hope you now understand how to perform these basic Data Analysis functions using JavaScript. The math.js library is one of the many JavaScript libraries that contain multiple helpful functions to make Data Analysis very easy with JavaScript.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

In an office management web application database, you would have tables for employees, their departments, their managers, the projects they work on, and so on depending on the structure of your application.

In the employees table, you would find data like the employee ID, name, salary, department ID (used to link the employee to the department), and other fields that match your needs. The other tables would also contain data for their specific entities.

What Are Joins?

If you ever need to bring multiple tables in your database together to access the data, you use a JOIN.

Joins let you fetch data that is scattered across tables. For example, using the database tables that we'll create in a moment, we'll be able to get all the details of an employee, along with their manager name, and department they're working in by using a join.

A join lets you use a single query to achieve this. You use a join because you can only get this data by bringing data from the employees table, departments table, and projects table together. In simple terms, you would be JOIN-ing these tables together.

To perform a join, you use the JOIN keyword. And we'll see how it works in this tutorial.

Prerequisites:

To continue with this tutorial, you should know the basics of insertion and retrieval operations with SQL.

Also, you can setup a demo database that we'll use for this article. The database should have tables like this:

CREATE TABLE employees (
    id int,
    emp_name varchar(100),
    salary int,
    dept_id int,
    manager_id int
);

INSERT INTO employees 
VALUES (1, 'Idris', 1000, 1, 1), (2, 'Aweda', 2000, 2, 2), (3, 'Zubair', 3000, 3, 2), (4, 'Young', 4000, 3, 3), (5, 'Babu', 5000, 1, 3), (6, 'John', 1000, 8, 1);

CREATE TABLE departments (
    id int,
    dept_name varchar(100)
);

INSERT INTO departments
VALUES (1, 'Engineering'), (2, 'Product'), (3, 'Marketing'), (4, 'Support');

CREATE TABLE managers (
    id int,
    manager_name varchar(100),
    dept_id int
);

INSERT INTO managers
VALUES (1, 'Doe', 1), (2, 'Jane', 2), (3, 'May', 4);

CREATE TABLE projects (
    id int,
    project_name varchar(100),
    emp_id int
);

INSERT INTO projects
VALUES (1, 'Fintech App', 1), (1, 'Fintech App', 5), (1, 'Fintech App', 6), (2, 'Cooking Website', 1), (2, 'Cooking Website', 2);

How to Use an Inner Join in SQL

There are many types of joins in SQL, and each one has a different purpose.

The inner join is the most basic type of join. It is so basic that sometimes, you can omit the JOIN keyword and still perform an inner join.

For example, say you want to fetch the name of all employees in the organanization, along with the name of their departments. In a situation like this, you need data from both the employees table and the departments table. A simple join like this would do:

SELECT e.emp_name, d.dept_name 
FROM employees e, departments d 
WHERE e.dept_id = d.id;

So how does this actually work? To start with, take a look at the FROM part of the query:

FROM employees e, departments d

Here, data is being fetched from more than one table, and each table is aliased. The alias is very useful for scenarios where both tables have similarly named fields, like the id field both tables have in this case. You would be able to access the different fields easily using the short alias created.

Next, in the SELECT part of the query, we also specify the columns we want (and we use the alias to tell which table each value comes from):

SELECT e.emp_name, d.dept_name

And finally, to ensure only correct values are matched to each other, the WHERE part of the query specifies the conditions that have to be met for the data to be joined.

WHERE e.dept_id = d.id;

So for the first employee, the dept_id is 1, so we fetch the department with id = 1, and it's name is returned. This happens for as many rows as there are in the employees table.

The result of the query looks like this:

Result of JOIN query

Here, notice that the number of employees returned is smaller than the number of employees that actually exist. This is because when you use an INNER JOIN, you only get records that exist in both tables.

That is, the employee with id = 6 that was not returned has a dept_it = 8. Now, this department isn't in the departments table, so it wasn't returned.

Another way to achieve this same result would be to actually spell out the JOIN like this:

SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
ON (e.dept_id = d.id);

Or use the INNER JOIN like this:

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON (e.dept_id = d.id);

These queries return exactly the same result as the first one. But they are more readable as they're explicit.

In these queries, you're selecting from the employees table, then joining the departments table to the result. The ON in the query is used to specify the conditions on which to JOIN. It's the same as the WHERE condition in the first query.

INNER JOIN Use Case

In real applications, you use an INNER JOIN when only records that exist in both tables matter.

For example, in an inventory management application, you could have a table for sales, and another for products. Now, the sales table will contain product_id (a reference to the sold product), along with other details like sold_at (when the product was sold) and maybe customer details.

The products table, on the other hand, will have the name, price, and maybe the quantity of every product.

Now say it's end of the week and you need to do a sales report. You would need to fetch all sales records, along with the product name and price to display on a dashboard or export as a CSV of some sort.

To do this, you would use an INNER JOIN of the products table on the sales table, because you do not care about products that were not sold – you only want to see every sale that was made, and the name and price of the product that was sold. Every other product will be exempted from this report.

How to Use a Left Join in SQL

In another scenario, you might want to fetch all the employee names and their department names, but this time without leaving any employee or department name out. Here, you'd you use a LEFT JOIN.

In a LEFT JOIN, every record from the table on the left, the base table, will be returned. Then values from the right table, the table being joined, will be added where they exist.

The LEFT JOIN is also known as LEFT OUTER JOIN and you can use them interchangeably.

So to fetch all employee and department names, you can modify the previous query to use LEFT JOIN, like this:

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d
ON (e.dept_id = d.id);

The result of this query looks like this now:

Result of LEFT JOIN

Now, employee with id = 6 and dept_id = 8 is returned, with the department name being set as NULL because there is no department with id = 8.

LEFT JOIN Use Case

In real applications, you use a LEFT JOIN when there's a primary, always existing entity that can be related to another entity that doesn't always exist.

An easy use case would be in a multi-vendor ecommerce application where after a user signs up, they can set up a store and add products to the store.

A user, on signing up, doesn't automatically have a store until they create it. So if you try to view all users, with their store details, you would use a LEFT JOIN of the stores table on the users table. This is because every record in the users table is important, store or no store.

When the user has a store set up, the store details are returned, and if otherwise, NULL is returned. But, you wouldn't be losing any existing data.

How to Use a Right Join in SQL

The RIGHT JOIN works like the opposite of the LEFT JOIN. In a RIGHT JOIN, every record from the table on the right, the table being joined, will be returned. Then values from the left table, the base table, will be added where they exist.

The RIGHT JOIN is also known as the RIGHT OUTER JOIN and you can use them interchangeably.

An example would be to modify the previous query to use a RIGHT JOIN instead of a LEFT JOIN, like this:

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d
ON (e.dept_id = d.id);

Now, your result looks like this:

Result of RIGHT JOIN

Now, every department in the departments table was returned. And employees in those departments were returned too. For the last row, there is no employee with dept_id = 4, which is why the NULL value gets returned.

RIGHT JOIN Use Case

The RIGHT JOIN works exactly as the LEFT JOIN works in real applications. The difference between them comes from the level of importance of the tables to be joined.

The LEFT JOIN is more commonly used because you very likely will write your query from left to right, listing tables in that order of importance too. Otherwise, the RIGHT JOIN works exactly as the LEFT JOIN.

How to Combine JOINS in SQL

So far, we've only joined one table to another. But, you can actually join as many tables as you like by using any or all of these joins together as you like.

For example, say you want to fetch the names of all employees, with their department names, manager names, and projects names. You would have to join the employees table to the departments table, the managers table, and the projects table. You can achieve this using this query:

SELECT e.emp_name, d.dept_name, m.manager_name, p.project_name
FROM employees e
LEFT JOIN departments d
ON (e.dept_id = d.id)
LEFT JOIN managers m
ON (e.manager_id = m.id)
LEFT JOIN projects p
ON (e.id = p.emp_id);

In this query, start from the employees table as a base table. Then you LEFT JOIN the departments table. You also LEFT JOIN the managers table, and finally, the projects table.

The result of this query will look like this:

Result of general JOIN query

The reason for using a LEFT JOIN here is because you have to fetch ALL employees. You could use an INNER JOIN in place of the LEFT JOIN in the managers table because all employees have a manager_id that actually exists in the managers table. But to be safe, you can just use the LEFT JOIN.

How to Use a Cross Join in SQL

This is also known as a CARTESIAN JOIN. It returns every record from both tables in a multiplication-like manner. It returns every possible combination of rows from both tables. It doesn't need a JOIN condition like the other JOINs.

For example, if you do a CROSS JOIN between tables employees and departments, your result will look like this:

SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;

CROSS JOIN of employees and departments tables.

Here you have 24 rows, which is a product of the number of rows in the employees table, 6, and the number of rows in the departments table, 4. The records were returned so that for every record in the employees table, it is mapped to a record in the departments table.

CROSS JOIN Use Case

A common use case of CROSS JOIN would be in an ecommerce application where it is possible to have size or color variations of all products. If you ever need to fetch a list of all products in different sizes, like this:

Result of a basic CROSS JOIN

This result was gotten from CROSS JOINing a sizes table that contains an id for each size, a string size that can be either 'Small', 'Medium', or 'Large' and another field called ratio to affect how this size affects the product price. So, for every product, it is mapped to a size, and the price is calculated.

SELECT 
  p.product_name, 
  ROUND(p.price * s.ratio, 2) as price, 
  s.size 
FROM products p 
CROSS JOIN sizes s;

How to Use a Self Join in SQL

As the name implies, is when you try to join a table to itself. There is no self JOIN keyword.

Take this new categories table, for example. This table contains both main categories and sub-categories. If you ever have to fetch the categories and their sub-categories, you can use a SELF JOIN.

CREATE TABLE categories (
    id int,
    cat_name varchar(100),
    parent_category_id int DEFAULT NULL
);

INSERT INTO categories 
VALUES (1, 'Ladies', NULL), (2, 'Mens', NULL), (3, 'Lingeries', 1), (4, 'Shoes', 2);

SELECT cat.cat_name, parent_cat.cat_name AS parent 
FROM categories cat
JOIN categories parent_cat 
ON cat.parent_category_id = parent_cat.id;

Here, see how the table was referenced twice. Be careful with the alias as it's important in differentiating both instances. The result of this query looks like this:

Result of simple Self JOIN on categories table

SELF JOIN Use Case

In many applications, you find hierarchical data stored in a single table. Like the category and sub-category as shown in the previous example. Or as in employee and manager, because they're both employees of the company.

In case of the latter, the table will have fields such as id, name, manager_id (this is basically the id of another employee). Let's say you want to write a query where you have to fetch a list of managers and the number of their employees. Given that these managers are also employees, you only have one table to fetch from, the employees table. To do this fetch, do a SELF JOIN of the employees table on the employees table like this:

SELECT e2.name AS supervisor, COUNT(e1.name) AS number_of_employees
FROM employee e1
JOIN employee e2
ON e1.manager_id = e2.id
GROUP BY e2.name;

This would correctly return the managers and the number of employees working under them.

Summary

I hope you now understand SQL JOINs, the different types, and when to use them so you can write better queries.

All the JOINs here work with MySQL. There are other JOINs like FULL OUTER JOIN and NATURAL JOIN that we didn't discuss, but you can look into them yourself if you like.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

This is especially true if there's more than one developer, and multiple environments where changes have to be manually implemented.

Database migrations help developers manage these changes easily, across multiple environments and developers.

This article explains:

• What database migrations are.
• How to get started with database migrations in PHP using Phinx.
• How to manage tables in your database.

This article is meant for readers with basic PHP knowledge. It will help you learn to easily (and better) manage your databases.

What Are Database Migrations?

In basic terms, migrations contain changes that you wish to make to your database. These changes could be creating or dropping a table, adding or removing some field(s) from a table, changing column types, and many more.

These files make it easy to make these same changes across multiple systems as anyone with the files can just run them, and have their database updated.

So in a real life scenario, some developer on the team could make a change to the users table to allow the gender field to accept more than the default male and female options, maybe a third other option.

After making this change, the developer creates a migration. This migration includes changes that they have made to the database – in this case a change to a column on a table – and other developers can easily get this change to their own local databases by running the migrations.

Migrations are like version control for your database, allowing your team to define and share the application's database schema definition. If you have ever had to tell a teammate to manually add a column to their local database schema after pulling in your changes from source control, you've faced the problem that database migrations solve. - Laravel

Many popular web frameworks already have support for migrations built in. But in this article, we explore using migrations in vanilla PHP.

Learn more about database migrations here.

What Is Phinx?

Phinx is a PHP library that makes it ridiculously easy to manage the database migrations for your PHP app. - Phinx

Phinx makes it possible to manage migrations easily regardless of whether you're using a PHP framework or not. It is also very easy to install (as we will see later on).

It ships with a couple of commands to make operations easier. It is fully customisable (you can do whatever you want with it 🙃). It also works in multiple environments, meaning you can have some production migrations, testing migrations, and dev migrations.

Phinx Installation

You can add Phinx to any PHP project using composer.

$ mkdir php-migrations
$ cd php-migrations
$ composer init

The first command creates a folder in your current directory, php-migrations, and the second command moves into it. The last command starts an interactive shell.

Follow the prompt, filling in the details as required (the default values are fine). You can set the project description, author name (or contributors' names), minimum stability for dependencies, project type, license, and define your dependencies.

When you get to the dependencies part, install the robmorgan/phinx phinx package as a dependency.

Accept the other defaults and proceed to generating the composer.json file. The generated file should look like this currently:

{
    "name": "zubair/php-migrations",
    "description": "A simple tutorial on how to use and manage migrations in PHP applications.",
    "type": "project",
    "require": {
        "robmorgan/phinx": "^0.12.10"
    },
    "license": "ISC",
    "autoload": {
        "psr-4": {
            "Zubs\\": "src/"
        }
    },
    "authors": [
        {
            "name": "Zubs",
            "email": "zubairidrisaweda@gmail.com"
        }
    ]
}

Init Phinx

After installing Phinx, you need to initialise it. You can do this very easily using its binary installed in the vendor folder.

$ ./vendor/bin/phinx init

This creates phinx's configuration file as a PHP file. It could be created as a JSON file too. I prefer JSON for configurations, so I will use the JSON format.

$ ./vendor/bin/phinx init --format=json

Here's what the default configuration file looks like:

{
    "paths": {
        "migrations": "%%PHINX_CONFIG_DIR%%/db/migrations",
        "seeds": "%%PHINX_CONFIG_DIR%%/db/seeds"
    },
    "environments": {
        "default_migration_table": "phinxlog",
        "default_environment": "development",
        "production": {
            "adapter": "mysql",
            "host": "localhost",
            "name": "production_db",
            "user": "root",
            "pass": "",
            "port": 3306,
            "charset": "utf8"
        },
        "development": {
            "adapter": "mysql",
            "host": "localhost",
            "name": "development_db",
            "user": "root",
            "pass": "",
            "port": 3306,
            "charset": "utf8"
        },
        "testing": {
            "adapter": "mysql",
            "host": "localhost",
            "name": "testing_db",
            "user": "root",
            "pass": "",
            "port": 3306,
            "charset": "utf8"
        }
    },
    "version_order": "creation"
}

In this configuration file, notice how Phinx expects that you have a db/migrations path (for your migrations) by default. You can change this if you want, but I think it's fine and I'll be keeping it.

$ mkdir db && db/migrations

You can read more about these configurations in the official documentation.

Phinx also ships with commands for different actions to make it easier to use in our projects.

How to Create A Migration

Phinx uses classes for its migrations. To create a new migration (say, one to create a posts table), use the create command with the name of the migration.

$ ./vendor/bin/phinx create PostsTableMigration

Creating A Migration

This creates a 20220328122134_posts_table_migration.php file in the db/migrations directory created earlier. This file is named using the YYYYMMDDHHMMSS_my_new_migration.php format. In this format, the first 14 characters, YYYYMMDDHHMMSS, are representations of the current timestamp.

The 20220328122134_posts_table_migration.php looks like this currently:

<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class PostsTableMigration extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * More information on writing migrations is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     *
     * Remember to call "create()" or "update()" and NOT "save()" when working
     * with the Table class.
     */
    public function change(): void
    {

    }
}

This file (and all other migrations created using Phinx) extends the Phinx\Migration\AbstractMigration class. This class has all the methods you need to interact with your database.

This migration file also includes a change method. This method was introduced recently to Phinx in version 0.2.0 to implements Phinx's idea of reversible migrations.

These are migration files with just one method, change, that contains logic for performing some action, leaving Phinx to figure out how to reverse it. Rather than the traditional use of two methods, up and down, to create and reverse actions.

Phinx still allows you use up and down methods. But it gives the change method preference over them when they are used together. It ignores them.

How to Manage Tables

Tables are the basis on which structured databases are built and are the most important part of what Phinx offers.

You can easily manage database tables using PHP code with Phinx. Phinx offers a powerful table() method. This method retrieves an instance of the Table object.

How to Create a Table

Creating a table is really easy using Phinx. You create a new instance of the Table object using the table() method with the table name.

$table = $this->table('posts');

Next, you can add columns with their settings.

$table->addColumn('title', 'string', ['limit' => 20])
    ->addColumn('body', 'text')
    ->addColumn('cover_image', 'string')
    ->addTimestamps()
    ->addIndex(['title'], ['unique' => true]);

Here, I've created columns title, body, cover_image, created_at, and updated_at. I also set the type of the title to be a string with 20 or fewer characters.

I set the body to be a text field, so it can hold long posts. The cover_image is also a string field that uses the default size of a string (255).

The created_at and updated_at fields are timestamps automatically generated in the addTimestamps() method.

Finally, I set the title field to be unique (as it would be in a real blog).

You can get all the available column types by checking the Valid Column Types. You can also get all the available column options by checking the Valid Column Options.

Finally, you can say that the database should be created by using the create method.

$table->create();

In the end, your migration file's change method should look like this:

public function change(): void
{
    $table = $this->table('posts');

    $table->addColumn('title', 'string', ['limit' => 20])
        ->addColumn('body', 'text')
        ->addColumn('cover_image', 'string')
        ->addTimestamps()
        ->addIndex(['title'], ['unique' => true]);

     $table->create();
}

We can now run this migration to create our table.

How to Run Migrations

After creating migrations, the next step is to enforce these desired changes in the database. Running migrations actually enforces these changes.

$ ./vendor/bin/phinx migrate

Running a migration

This image shows the result of the migration. You can see the time taken to run the migration.

Now, if you check your database GUI tool, you'll notice that the posts table was created with an additional field, the id field. This field is also the primary field by default. And it also auto-increments.

posts table.

You may change the primary key to some other key by either specifying some other field as the primary field, or by mapping the id field to the desired primary field. The latter includes the auto incrementing ability of the normal id field.

$table = $this->table('posts', [
    'id' => false,
    'primary_key' => ['posts_key']
]);

$table = $this->table('posts', [
    'id' => 'posts_key',
]);

In the first method, the primary key to be used has to be a column on the table (it is not auto-created).

You may also set which environment you want to run the migrations.

$ ./vendor/bin/phinx migrate -e testing

How to ReverseMigrations

Migrations can be reversed by being run down. This is the reverse of migrating up. The table previously created will be dropped, columns added will be removed, and the database will be returned to its initial pre-migration state.

$ ./vendor/bin/phinx rollback

Reversing a migration

How to Check Migration Status

As your application size increases, it is expected that your database migrations will increase. Due to this, at some point, you may wish to check the status of your migrations, to know which have been run, and which have not.

$ ./vendor/bin/phinx status

Checking migration status

How to Drop a Table

You can easily use the drop method, followed by the save method to persist the change, on the Table object.

$this->table('posts')->drop()->save();

How to Rename a Table

$table = $this->table('posts');

$table->rename('articles')
    ->update();

To drop a table, get the table. Then use the rename method with the new name, followed by the update method to persist this change.

How to Change a Table's Primary Key

You can also change a table's primary key very easily.

$table = $this->table('posts');

$table->changePrimaryKey('new_primary_key');

$table->update();

Conclusion

Now you know how to set up migrations in your PHP applications.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

So what is unit testing and how can you do it? You'll learn that and more in this article.

What is Unit Testing?

Unit testing is a software development process in which the smallest testable parts of an application, called units, are individually and independently scrutinised for process operation. - SearchSoftwareQuality

In basic terms, unit testing means that you break your application down to its simplest pieces and test these small pieces to ensure that each part is error free (and secure).

This testing is automated and written by software engineers as part of their development process. This is a very important step during development as it helps developers build better applications with fewer bugs.

What is PHPUnit?

You can perform unit testing in PHP with PHPUnit, a programmer-oriented testing framework for PHP. PHPUnit is an instance of the xUnit architecture for unit testing frameworks. It is very easy to install and get started with.

PHPUnit Installation

You can install PHPUnit globally on your server. You can also install it locally, on a per-project, development-time basis as a dependency to your project using composer. This article will explain how to use it on a per project basis.

To get started, create and initiate a new project with composer using these commands:

$ mkdir test-project
$ cd test-project
$ composer init

The first command creates a folder in your current directory, test-project and the second command moves into it. The last command starts an interactive shell.

Composer init prompt

Follow the prompt, filling in the details as required (the default values are fine). You can set the project description, author name (or contributors' names), minimum stability for dependencies, project type, license, and define your dependencies.

You can skip the dependencies part, as we are not installing any dependencies. PHPUnit is supposed to be a dev-dependency because testing as a whole should only happen during development.

Now, when the prompt asks Would you like to define your dev dependencies (require-dev) interactively [yes]?, press enter to accept. Then type in phpunit/phpunit to install PHPUnit as a dev-dependency.

Accept the other defaults and proceed to generating the composer.json file. The generated file should look like this currently:

{
    "name": "zubair/test-project",
    "require-dev": {
        "phpunit/phpunit": "^9.5"
    },
    "autoload": {
        "psr-4": {
            "Zubair\\TestProject\\": "src/"
        }
    },
    "authors": [
        {
            "name": "Idris Aweda Zubair",
            "email": "zubairidrisaweda@gmail.com"
        }
    ],
    "require": {}
}

To learn how to install PHPUnit globally on your server, read here.

How to Write Tests in PHPUnit

Writing tests in PHPUnit is quite simple. Here are a few conventions to get you started:

• To test a class in PHP, you'll create a test class named after that class. For example, if I had some sort of User class, the test class would be named UserTest.
• The test class, UserTest, will usually inherit the PHPUnit\Framework\TestCase class.
• Individual tests on the class are public methods named with test as a prefix. For example, to test a sayHello method on the User class, the method will be named testSayHello.
• Inside the test method, say testSayHello, you use PHPUnit's method like assertSame to see that some method returns some expected value.

A popular convention is to have all tests in a tests directory, and all source code in the src directory.

PHPUnit Testing Example

To help understand this article, here's a sample User class with simple methods that will be tested:

<?php

namespace Zubair\TestProject;

use InvalidArgumentException;

class User
{
    public int $age;
    public array $favorite_movies = [];
    public string $name;

    /**
     * @param int $age
     * @param string $name
     */
    public function __construct(int $age, string $name)
    {
        $this->age = $age;
        $this->name = $name;
    }

    public function tellName(): string
    {
        return "My name is " . $this->name . ".";
    }

    public function tellAge(): string
    {
        return "I am " . $this->age . " years old.";
    }

    public function addFavoriteMovie(string $movie): bool
    {
        $this->favorite_movies[] = $movie;

        return true;
    }

    public function removeFavoriteMovie(string $movie): bool
    {
        if (!in_array($movie, $this->favorite_movies)) throw new InvalidArgumentException("Unknown movie: " . $movie);

        unset($this->favorite_movies[array_search($movie, $this->favorite_movies)]);

        return true;
    }
}

This user class could be the User class in your movie streaming application. The user has a name, age, and a list of favourite movies that can be updated. For the rest of the article we will test that all these features work as they're expected to.

Create a UserTest class in the tests folder. Paste this in to start:

<?php

namespace Zubair\TestProject;

use PHPUnit\Framework\TestCase;

final class UserTest extends TestCase
{
    // Tests will go here
}

Test Constructor

Normally, you wouldn't be testing the __construct method. However, since we're setting values in it, it only makes sense to be sure that the values are being set correctly.

This seems like a very small thing to test, but that's the whole point of unit tests – to ensure that the smallest parts of your application function as expected.

Create a testClassConstructor method to test the constructor:

public function testClassConstructor()
{
    $user = new User(18, 'John');

    $this->assertSame('John', $user->name);
    $this->assertSame(18, $user->age);
    $this->assertEmpty($user->favorite_movies);
}

Let's take a quick break now, to see how to run the tests.

How to Run Tests in PHPUnit

You can run all the tests in a directory using the PHPUnit binary installed in your vendor folder.

$ ./vendor/bin/phpunit --verbose tests

You can also run a single test by providing the path to the test file.

$ ./vendor/bin/phpunit --verbose tests/UserTest.php

You use the --verbose flag to get more information on the test status.

Now, we can run the test and see the output:

Test Output

The output shows that we ran 1 test, and made 3 assertions in it. We also see how long it took to run the test, as well as how much memory was used in running the test.

These assertions are what PHPUnit uses to compare values returned from the methods to their expected value.

This example uses assertSame to check if the name and age properties on the user object match the entered values. It also uses assertEmpty to check that the favorite_movies array is empty.

To see a list of all these assertions, you can check out PHPUnit's docs here.

Edit the code to check if the user age is the same as 21.

public function testClassConstructor()
{
    $user = new User(18, 'John');

    $this->assertSame('John', $user->name);
    $this->assertSame(21, $user->age);
    $this->assertEmpty($user->favorite_movies);
}

Running the test again this time gives this output:

Failed Assertion Output

The output now shows that we ran 1 test, with 2 successful assertions, and also a failed one. We can see some explanation of the failure, showing the expected value, the gotten value, and the line where the error is from.

Test testName and tellAge

Next, we can test the testName method. This method tells the name of a user as a sentence. So, we can write the test to check:

• If the returned value is a string.
• If the returned string has the user's name in it (with or without case sensitivity).

public function testTellName()
{
    $user = new User(18, 'John');

    $this->assertIsString($user->tellName());
    $this->assertStringContainsStringIgnoringCase('John', $user->tellName());
}

The test uses the assertions assertIsString and assertStringContainsStringIgnoringCase to check that the return value is a string and that it contains the string John, respectively.

The testAge method is very similar to testName and uses the same logic. Its test will be similar to the previous one:

public function testTellAge()
{
    $user = new User(18, 'John');

    $this->assertIsString($user->tellAge());
    $this->assertStringContainsStringIgnoringCase('18', $user->tellAge());
}

Test addFavoriteMovie

We can test this method, too. This method adds a movie to the list of movies. To test it, we can check if the newly added movie is in the list, and that the number of items in the list actually increased.

The latter is for confirming that items are not being displaced. Also, since the function returns some value at the end, we can check that this value is correct too.

public function testAddFavoriteMovie()
{
    $user = new User(18, 'John');

    $this->assertTrue($user->addFavoriteMovie('Avengers'));
    $this->assertContains('Avengers', $user->favorite_movies);
    $this->assertCount(1, $user->favorite_movies);
}

Here, we use a few new assertions – assertTrue, assertContains, and assertCount – to check that the returned value is true, that it contains the newly added string, and that the array now has one item in it.

Test removeFavoriteMovie

Finally, we can test that the method to remove a movie works.

public function testRemoveFavoriteMovie()
{
    $user = new User(18, 'John');

    $this->assertTrue($user->addFavoriteMovie('Avengers'));
    $this->assertTrue($user->addFavoriteMovie('Justice League'));

    $this->assertTrue($user->removeFavoriteMovie('Avengers'));
    $this->assertNotContains('Avengers', $user->favorite_movies);
    $this->assertCount(1, $user->favorite_movies);
}

Here, we're adding some movies to the list. Then, we remove one of them, and confirm that the function returned true. Next, we confirm the removal by checking that the value is no longer in the list. Finally, we confirm that we have only one movie in the list, instead of two.

Conclusion

Now you know how to set up PHPUnit in your projects and how to test and ensure that you're building world class software. You can find all the code for this article here.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!

But the more functionality the app has, the clumsier your route paths will get.

What if there was a way to separate all these parts into smaller components with better and cleaner routes? Something that users could easily access and use independently, under the same website?

Fortunately, there is such a way: Subdomains.

What is a Subdomain?

Credit: Electroica Blog

Here's a basic definition of a subdomain:

A subdomain is, as the name would suggest, an additional section of your main domain name. You create subdomains to help organize and navigate to different sections of your main website. Within your main domain, you can have as many subdomains as necessary to get to all of the different pages of your website.

So let's say you have a website called mysite.com. You have a blog section, a store section, and a general website section for about and contact pages. The website could have subdomains like blog.mysite.com and store.mysite.com, where the main website would use the main domain.

Why Should You Use Subdomains?

Subdomains are pretty useful, and here are some of their main advantages:

• Users can easily remember you website domains, which means they'll likely use your site more.
• You'd be able to split your large application into smaller groups, so it will be easier to manage, debug, and update or upgrade.
• Subdomains also allow for personalisation – for example, a blog app could give each user their own subdomain (like username.domain.com).
• Subdomains also let developers test version of their application before pushing to production. You could have a beta.site.com to preview changes before deploying them to the main site.

Let's see how all this works by building an actual project and testing it out.

How to Create New Laravel Project

I have Docker setup on my laptop, so I'll be using the Sail setup that Laravel ships with.

curl -s "https://laravel.build/example-app" | bash

You can use any other method you feel comfortable with. See the docs for help.

Start the Laravel Server

./vendor/bin/sail up -d

How to Configure the Route Files

In your web.php file, you can define individual routes with their domain (or subdomain) like this:

Route::get('/', function () {
    return 'First sub domain';
})->domain('blog.' . env('APP_URL'));

Now you can access the page at blog.domain.com.

But more often than not, you'll have more than one path in an application, like a domain and subdomains. So, it's a good idea to use a route group to cover all the routes in the same domain or subdomain.

Route::domain('blog.' . env('APP_URL'))->group(function () {
    Route::get('posts', function () {
        return 'Second subdomain landing page';
    });
    Route::get('post/{id}', function ($id) {
        return 'Post ' . $id . ' in second subdomain';
    });
});

Now, all the routes for the domain can be handled in one place.

How to Make Subdomains Dynamic

As I mentioned earlier, you can use subdomains to allow personalisation in web applications, so they need to be dynamic. For example, Medium gives authors domains like username.domain.com.

You can do this easily in Laravel as subdomains may be assigned route parameters just like route URIs. This allows you to capture a portion of the subdomain for usage in your route closure or controller.

Route::domain('{username}.' . env('APP_URL'))->group(function () {
    Route::get('post/{id}', function ($username, $id) {
        return 'User ' . $username . ' is trying to read post ' . $id;
    });
});

In this example, you could have a domain like zubair.domain.com with route parameters, too.

Route Service Providers

For very large applications, the web.php could get a bit messy if the routes keep increasing. It is best to split the routes into different files, preferably by subdomain.

In your RouteServiceProvider.php file, you'll see this code in the boot method:

public function boot()
    {
        $this->configureRateLimiting();

        $this->routes(function () {
            Route::prefix('api')
                ->middleware('api')
                ->namespace($this->namespace)
                ->group(base_path('routes/api.php'));

            Route::middleware('web')
                ->namespace($this->namespace)
                ->group(base_path('routes/web.php'));
        });
    }

This is Laravel's default route configuration to separate API routes from web routes. We'll use this same file to separate subdomains.

Add the following to the method:

Route::domain('blog.' . env('APP_URL'))
                ->middleware('web')
                ->namespace($this->namespace)
                ->group(base_path('routes/blog.php'));

This is telling Laravel that whenever someone hits the blog.domain.com endpoint, look for the route in the blog.php (that we are yet to create).

We can go on to create the blog.php file in the routes folder, and add the following content:

<?php

use Illuminate\Support\Facades\Route;

Route::get('/', function () {
    return 'Route using separate file';
});

At this point, you're done with all the code! All that's left is some server configuration.

Server Configuration

If you're using a service such as Laravel Valet, it is way easier to setup.

In the root directory of your project, run:

valet link domain
valet link blog.domain

And if you're not using Laravel Valet, you can add this to your /etc/hosts/ file:

127.0.0.1       domain.test
127.0.0.1       blog.domain.test

This is basically just mapping the domain to the IP.

Summary

Now you know how to set up and manage subdomains in your Laravel apps. You can find all the code for this article here.

If you have any questions or relevant advice, please get in touch with me to share them.

To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!