One common issue you may have faced is the errors you can get when you want to exit the application. You can easily mitigate that issue by opening the application launcher as Administrator. But opening the launcher via administrator correctly can also be troublesome. This means that you’ll need to configure a setting so that it asks for administrator access each time you want to launch it.

In this quick tutorial, I’ll walk you through this process so you don’t have to struggle with it.

Video Guide

I have prepared a complete video where I show you how to download and install the client. I also teach you how to make some changes so that you don’t encounter any issues when you want to quit the application.

Step-By-Step Process

If you keep the default installation directory during the installation, it will install it inside the C drive of your Windows operating system.

So simply enter into the “C” drive and go inside the “xampp” directory.

Scroll down until you find the “xampp-control.exe” file.

Right-click after selecting the file and open the Properties.

Next, go to the Compatibility section. Make sure to add a check mark by clicking on the checkbox by “Run the program as an administrator”.

Click “Apply”, and “OK”.

That’s it!

Now, whenever you want to open the XAMPP launcher, it will always open with administrator access.

Quitting the application

When you want to quit the application, you need to click the “Quit” button instead of using the “X” exit button at the upper right side of the window.

And now you shouldn’t get any errors when exiting the control panel anymore!

Conclusion

Thanks for reading this short tutorial. I hope it helped you interact more easily with XAMPP.

You can follow me on GitHub, LinkedIn, and YouTube to get more content like this. Also, my website is always available for you!

We just published a course on the freeCodeCamp.org YouTube channel that will help you master MySQL. Josh from KeepItTechie teaches this course. He works as a Sql Server Database Administrator so he is the pefect person to teach this course.

MySQL is an open-source relational database management system that uses SQL (Structured Query Language) to interact with data. It is renowned for its speed, reliability, and ease of use. MySQL forms the database component of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) software stack, making it an essential tool for web development and application building.

This course is structured to provide a comprehensive and practical understanding of databases. Catering to both beginners and seasoned users looking to enhance their skills, the course covers a wide range of topics, from the basics to advanced functionalities.

Here are the sections covered in this course:

• Introduction
• MySQL Overview
• Installation
• Login to MySQL
• Exploring MySQL
• Show Databases
• Create Database
• Use Database
• Create Table
• Describe Table
• Drop Table
• Show Table
• Drop Database
• Install Test Database
• Create User Accounts
• Change Account Password
• List All Accounts
• Drop User Accounts
• Grant Privileges
• Revoke Privileges
• Show Granted Privileges
• Query
• Select Query
• Where Clause
• Order By
• Like
• Insert Query
• Update Query
• Delete Query
• Primary Key
• Foreign Key
• Inner Join
• Left Join
• Right Join
• View
• Index
• Sub Query
• Stored Procedure
• Trigger
• Processlist
• Backup Database
• Restore Database
• Outro

This is a hands-on approach and will ensures that you gain practical knowledge that can be applied in real-world scenarios. You can watch the course on the freeCodeCamp.org YouTube channel (2-hour watch).

There are a lot of clients out there for your MySQL-based needs, like XAMPP, DataGrip, and others. Among all of them, I prefer the MySQL Workbench. It is completely free, by the way.

In this tutorial, I will show you how you can install and configure your Windows machine for this MySQL and MySQL workbench from scratch.

If you enjoy learning from videos as well, then don't worry as I have also created a step-by-step video just for you:

How to Install MySQL Workbench

➡️ Download MySQL Workbench

Make sure to visit only the official website for downloading the MySQL Workbench. You do not want to get into shoddy websites and download the wrong file that infects your favorite machine, right?

Find the official website for MySQL Workbench: https://www.mysql.com/products/workbench/

Now click on the "DOWNLOADS" tab.

Scroll down until you find MySQL Community (GPL) Downloads ».

Click on MySQL Community (GPL) Downloads ». After that, on the new page, click "MySQL installer for Windows".

From the dropdown menu, select your operating system as "Microsoft Windows". Then download the file which is larger in size.

A .msi file will be downloaded. That is our installer file to install MySQL and MySQL workbench.

➡️ Install both MySQL and MySQL Workbench

Simply double click on the installer file. It will reload the necessary components and open the installer GUI selection window. Choose the setup type as custom and click "Next".

Select Custom

A new page will appear. Make sure to select the latest "MySQL Server", "MySQL Workbench" and "MySQL Shell". Selecting and clicking on the right side arrow will take the product name in the "Products to be installed section". Then click "Next".

Install necessary components

Click "Execute" to install the three necessary components. The process might take some time depending on your internet speed and computer configuration. After it gets finished, simply click "Next".

Execute

In the Product Configuration window, simply click "Next". It will install the three selected components for us.

Next

Keep everything as it is and simply click "Next". It will configure the MySQL Server.

Next

Keep everything as it is and simply click "Next". It will apply the TCP/IP connectivity for our MySQL server.

Next

Now give it a Root password. For testing purposes, I am using a very simple "1111" as my password, but I would recommend not doing the same. Also, make sure to remember the password as you will need it when you want to work in MySQL Workbench. Click "Next".

Next

Keep everything as it is, and simply click "Next". It will make sure to setup our root password for the MySQL workbench.

Root password

We want to run the service as a Standard System Account for our operating system. Therefore, keep everything as it is, and simply click "Next".

Next

Select the option to grant full access to the user running the Windows Service and then click "Next."

Next

Then click "Execute". This will grant the full access to the user running the Windows service and the administrator group only, but the other users and groups will not have its access.

So if you have multiple user accounts in your computer, then they will not be able to access the MySQL server/Workbench. If you want then you can change the settings here based on your need.

As I have only one user account in my Windows machine, I can safely keep the first option selected.

Execute

It might take some time. Then when you will receive a green check box in all configuration steps, simply click "Finish".

Finish

The configuration has beep applied successfully. Simply click "Next".

Next

Click "Finish" to complete the installation.

Finish

It will open the MySQL Workbench and MySQL Shell. Simply close all of them now.

➡️ Configuration

Now we need to configure the path variables for our operating system. Go to the drive where you have installed your Windows operating system. Like others, I have also installed my operating system on the "C" drive.

Therefore, I am going to the "C" drive and opening the "Program Files" directory.

Go to the "MySQL" folder.

Then go to the MySQL Server folder.

Go to the "bin" folder.

Copy the path/address.

Now open the Environment Variables settings. Simply click on the Windows button and type "env".

Click "Environment Variables".

Select the "Path" and click "Edit".

Click "New". A new blank box will appear. Paste the path/address that you copied earlier. Do not close the window now as we need to do the same thing for the MySQL Shell folder.

Now, we need to do the same thing for the MySQL Shell also. Open the MySQL Shell folder now.

Go to the "bin" folder.

Copy the path/directory.

Now apply the same process as you did earlier. Click "New" on the Edit environment variable window. Paste the path/directory in the new blank box.

Now click "OK".

Click "OK" again.

And click "OK" one more time.

➡️ Finishing Up

Our task is now finished. You can now open the MySQL Workbench.

Simply click on the Local instance. It will ask for the root password. Enter the password. If you do not want to go into the same hassle of entering a password every time, check the box on save password in the vault. Click "OK".

This is your default MySQL Workbench workspace.

Workbench workspace

If you want then you can also hide the SQL Additions tab by clicking on the colored box.

For getting the Schemas, click on the "Schemas" tab from the navigator.

Your MySQL Workbench is also ready for any kind of development process. You can also use MySQL from your terminal as well.

Conclusion

Thank you for reading the entire article.

If you have any questions, feel free to reach out to me using Twitter or LinkedIn.

Also, make sure to follow me on GitHub!

You can also subscribe to my YouTube channel for more helpful video content.

If you are interested then you can also check my website: https://fahimbinamin.com/

Have a great day! 😊

Cover: Photo by Boitumelo Phetla on Unsplash

But you may be working on a project where you have modified your database to an acceptable state and you're worried about altering it by mistake. This could happen when you're working in the DB, or when others who have access to your computer get in there.

Well, fear not! In MySQL, you can restrict the database to ensure its safety. I assume you're a SQL beginner, so I am not going to bore you with the difficult stuff. The easiest thing is to make the database READ ONLY. After that, no one can modify the database in any way if they aren't familiar with certain MySQL commands.

If you'd like to go through the process step-by-step in a video, here you go:

How to Make a Database READ ONLY

Let's assume that you're already in your SQL editor (from where you can execute your MySQL commands). Keep in mind that usually, when we create a database, it comes with read-write accessibility by default.

So for now, let's assume that the default status of a newly created database is READ-WRITE enabled.

To convert the database into a READ-ONLY state, use this command:

ALTER DATABASE database_name READ ONLY = 1;

Let me explain each part of the code for you now.

As you want to change something in the data, you are telling the database,
"hey, I want to alter something". So you used the ALTER command.

Then comes the part where you tell it which thing you want to alter, because there might be multiple tables or databases. So you need to tell it which thing you want to alter. So, you stated DATABASE to specify that you actually want to alter a database.

In your client (MySQL Workbench/XAMPP, and so on), there might be multiple databases. So wouldn't MySQL get confused about exactly which database you want to alter? You don't want to confuse it, right? This is why you need to specify the database name.

After that, you tell it exactly which modification you want it to make. You want to change the READ ONLY status to 1. This means that READ ONLY is on/enabled.

After this, nobody will be able to make any kind of alteration or changes (update/delete/addition) to that specific READ ONLY database anymore. Nobody will even be able to delete the database!

How to Make a Database READ-WRITE

What if you need to revert the changes so you can make updates to the database? You need to change the READ ONLY status to 0 to state that you want the READ ONLY status to be disabled (or no in short).

Simply use the following command to do that:

ALTER DATABASE database_name READ ONLY = 0;

After this, anyone will be able to make changes to the database or even delete the database if they want.

Conclusion

Isn't it a short but beautiful trick? Enjoy your coding journey!

Also, if you like programming-related content, then make sure to subscribe to my YouTube channel where I publish programming-related content regularly!

Also, you can follow me on GitHub and Twitter. You can also check my website: https://fahimbinamin.com/

If you want to endorse me for relevant skills, then do that using LinkedIn.

Cover image: Photo by AltumCode on Unsplash

MySQL not only offers robust data storage capabilities but also provides a powerful feature called "procedures" that allows developers to streamline complex database operations.

In this tutorial, we will delve into the concept of MySQL procedures and explore their benefits. Then I'll provide a step-by-step guide on how to use them effectively.

What are SQL Procedures?

SQL procedures are a set of SQL statements grouped together to form a logical unit of work. They are similar to functions or methods in programming languages, enabling you to encapsulate complex queries and operations into a single reusable entity.

Procedures enhance code modularity, readability, and maintainability, making it easier to manage and execute repetitive or intricate database tasks.

When to Use Stored Procedures

Let's consider an e-commerce website, where we have the functionality to generate sales reporting. We have a table called sales that we'll be working with for this example.

Generating sales reports in real time can be resource-intensive, especially when dealing with large datasets. By creating stored procedures that aggregate and summarize sales data, we can optimize the reporting process.

These procedures can calculate metrics like total sales, top-selling products, or revenue by category, making it easier to retrieve valuable insights quickly and efficiently.

Here's the schema of sales table:

To illustrate a simple example, let's consider sales table is populated with 1 million rows of mock data.

Mock data for sales table

select count(*) from sales;

The goal is to get sales reports for a particular period of time.

CREATE PROCEDURE GenerateSalesReport (
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
    SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS Date,
           COUNT(order_id) AS TotalOrders,
           SUM(total_amount) AS TotalSales
    FROM orders
    WHERE order_date BETWEEN start_date AND end_date
    GROUP BY DATE_FORMAT(order_date, '%Y-%m-%d');
END

The example stored procedure GenerateSalesReport takes two input parameters: start_date and end_date. These define the date range for the sales report.

The procedure selects the order date, counts the number of orders, and calculates the total sales amount within the specified date range. The result is grouped by the date, using the DATE_FORMAT function to display it in the desired format.

Now, you might have a question:

"Can't we achieve the same outcome using a simple query instead of creating a stored procedure?"

Well. It's true that using a simple query is a viable option. But there are several compelling reasons to consider utilizing a stored procedure.

Here are few reasons that feels promising to use stored procedures at some places.

1. A stored procedure offers the advantage of code reusability. By encapsulating the query logic within a stored procedure, we can reuse it multiple times without duplicating the code.
2. Instead of rewriting the same query in different parts of the application, we can simply call the stored procedure whenever needed, streamlining the codebase and making it easier to manage and update.
3. Using a stored procedure can lead to improved performance in certain scenarios. When a stored procedure is executed, the database server can optimize the execution plan and cache it for subsequent invocations. This optimization can result in faster execution times, as the database engine leverages the cached plan.
4. Furthermore, stored procedures can minimize network round trips by combining multiple queries into a single call, reducing the overhead associated with individual query executions. This optimization can significantly enhance overall performance, especially when dealing with complex operations or large datasets.
5. Another significant advantage of stored procedures is enhanced security. By granting execution privileges only to the stored procedure and not directly to underlying tables, you can enforce access control and protect sensitive data.

In summary, while a simple query can achieve the desired outcome, utilizing a stored procedure offers distinct benefits such as code reusability, improved performance through query optimization, reduced network overhead, and enhanced security.

Building Blocks of Stored Procedures

Let's break down the stored procedure and examine each component individually. We will understand creating and running the stored procedure in MySQL.

There are several MySQL IDEs available, and I recommend using MySQL Workbench. But you are free to choose any IDE that suits your preferences and needs.

Procedure Name

Every stored procedure has a unique name that identifies it within the database. The name should be descriptive and relevant to the procedure's purpose.

Define a procedure

CREATE PROCEDURE `GenerateSalesReport`()
BEGIN
END

Parameters

Stored procedures can have input parameters that allow you to pass values into the procedure at runtime. We define start_date and end_date as our input parameters.

Sample parameters in stored procedure

CREATE PROCEDURE `GenerateSalesReport`(
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
END

Variables

Variables are used to store and manipulate data within the stored procedure. They can be declared and assigned values as needed.

There are two types of variables in SQL. We'll look at each of them now.

Session Variables

Session variables in MySQL are prefixed with the @ symbol (for example @variable_name). These variables are associated with the current session or connection and retain their values throughout the session until they are explicitly changed or the session ends.

Define Session variable in stored procedure

CREATE PROCEDURE `GenerateSalesReport`(
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
   SELECT @totalSales := 0;
   SELECT SUM(sales_amount) INTO @totalSales FROM sales;
   SELECT @totalSales As total_sales;
END

Regular Variables

Regular variables, also known as local variables, are declared using the DECLARE keyword within the scope of a stored procedure. Unlike session variables, regular variables do not have the @ prefix (for example variable_name). They are temporary and exist only within the block of code where they are declared.

Define Normal variable in stored procedure

CREATE PROCEDURE `GenerateSalesReport`(
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
   DECLARE totalSales INT;
   SELECT SUM(sales_amount) INTO totalSales FROM sales;
END

SQL Statements

The core functionality of a stored procedure is defined by SQL statements. These statements can include SELECT, INSERT, UPDATE, DELETE, and other SQL commands to interact with the database.

SQL statements in stored procedure

CREATE PROCEDURE `GenerateSalesReport`(
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
    SELECT DATE_FORMAT(saled_date, '%d-%m-%Y') AS Date,
           COUNT(sale_id) AS TotalOrders,
           SUM(total_amount) AS TotalSales
    FROM sales
    WHERE saled_date BETWEEN start_date AND end_date
    GROUP BY DATE_FORMAT(saled_date, '%d-%m-%Y');
END

Procedure Call

To execute the stored procedure and generate a detailed sales report for a specific date range, we can use the following syntax:

CALL <procedure_name>(<parameter1>, ...);

CALL GenerateSalesReport('2021-01-01', '2023-12-31');

The below screenshot shows the result of the stored procedure. The interesting part is that this query has processed around 1 million data in a second.

Result of sample procedure call to generate saled report

Importance of Using MySQL Stored Procedures

Improved Performance

Stored procedures offer a significant performance advantage over ad-hoc SQL queries. Once a stored procedure is created, it is compiled and stored in a pre-optimized form.

This compilation process eliminates the need for repetitive query parsing and optimization, resulting in faster execution times. By reducing the overhead associated with query processing, stored procedures enhance the overall performance of database operations.

Enhanced Security

Security is a critical aspect of database management. Stored procedures allow database administrators to define access rights and permissions for executing specific procedures. This fine-grained control ensures that only authorized users can interact with the database through the procedures, minimizing the risk of unauthorized data access or modifications.

By encapsulating sensitive operations within stored procedures, security vulnerabilities are reduced, strengthening the overall database security posture.

Code Reusability and Maintainability

Stored procedures promote code reusability, modularity, and maintainability. By encapsulating frequently used SQL statements and operations within a single procedure, you can avoid code duplication and ensure consistent execution across multiple instances.

This modularity makes it easier to maintain and update the database logic. Additionally, when modifications are required, changes can be made in a single location (the stored procedure) rather than in multiple places, simplifying the maintenance process.

Transaction Control

Stored procedures enable transaction control within the database. Transactions ensure data integrity by grouping multiple database operations into a single logical unit. By executing a series of operations within a transaction, you can ensure that either all the operations are successfully completed, or none of them are applied.

This atomicity ensures data consistency and protects against data corruption. Stored procedures allow you to define transaction boundaries, ensuring that complex operations are handled reliably and consistently.

Performance Optimization and Query Plan Caching

Another advantage of using stored procedures is the ability to optimize query execution plans.

Since stored procedures are compiled and stored, the database engine can generate optimized execution plans based on the stored procedure's statistics and data distribution. These optimized plans can significantly improve query performance.

Furthermore, the query execution plans for stored procedures are cached, which further reduces the overhead of plan generation for subsequent executions.

Conclusion

Stored procedures are a valuable tool in database management, and you'll want to use them in specific scenarios. When dealing with complex business logic, aiming for performance optimization, enhancing security and access control, promoting code reusability and maintainability, handling complex transactions, or integrating with legacy systems, stored procedures can provide significant benefits.

By leveraging their power effectively, you can streamline your database operations, improve application performance, and simplify code maintenance, leading to a more efficient and scalable database environment.

If you wish to learn more about SQL and Stored Procedures, subscribe to my email newsletter (https://5minslearn.gogosoon.com/) and follow me on social media.

We just published a full course on the freeCodeCamp.org YouTube channel that will teach you how to create a SQL database application with a Windows graphical interface.

Led by Shad Sluiter, a Professor Of Computer Science and Software Development at Grand Canyon University, this course will guide you through the process of creating a graphical Windows application with C# that can display and modify data from a mySQL database server.

Throughout the course, you'll gain practical experience with a range of topics, including creating a new database in mySQL, writing SQL queries, creating a GUI front-end app, and connecting to a database with a Data Access Object. You'll also learn how to query a database, insert new records, and perform compound select actions.

But that's not all – this course also covers more advanced topics such as creating a second table with a foreign key, performing table joins, designing with UML and ER diagrams, and even adding a video player to the app.

To follow along with the course, you'll need Microsoft Windows. With easy-to-follow video tutorials, this course is perfect for anyone looking to gain practical skills in database design and application development.

So, if you're ready to take your database skills to the next level, follow along with this course and start building your own Windows application with ease.

You can watch the full course on the freeCodeCamp.org YouTube channel (3-hour watch).

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!

What is a Database Transaction and Why is it Important?

A database transaction is a single area of the database where multiple data operations are carried out and written as a whole.

These operations can be create, read, update, or delete operations.

During the process of a transaction, the database is in an inconsistent state because there are ongoing operations that are making changes to the database. The DB returns to a more consistent state when the operations have been committed.

For a transaction to be successful, it means that every operation carried out has been committed.

Database transactions are very important in ensuring the consistency of your database when multiple operations are being performed at the same time. It also gives you a way to recover changes that may have occurred due to the failure or accidental misuse of an operation.

Overview of MySQL and its Transaction Support

MySQL databases offer support for database transactions by providing statements to initiate these transactions. It gives us the following in-built queries:

"START TRANSACTION / BEGIN": this query triggers the start of a transaction.

"COMMIT": this query allows the changes made to the database to become permanent. You can set your database to auto-commit changes by using the following query:

SET autocommit = 1;

"SET": this query allows you to set your commit by enabling the operations to commit automatically or disabling the auto-commit. That is, your operations won't commit automatically until you call the "commit" query.

/*Disabling the auto-commit  */
SET autocommit = 0;
/* OR */
SET autocommit = OFF;

/* Enabling the operations to automatically commit every operation*/
SET autocommit = 1;
/* OR */
SET autocommit = ON

"ROLLBACK": this query allows you to undo the changes you have made to the database, therefore returning the database to its previous (last commit) state.

ACID Properties of Transactions

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. Let's go through each term to understand how they relate to transactions.

Atomicity

Atomicity in a database transaction means that all the changes made during that transaction are treated as one "bundle" of changes. This means that when you are trying to modify your database, it's either all of the changes happen at the same time, or none of them happen at all.

It's like when you and your teammates are building an application. If one person writes a line of code, and then another person takes it off, it's like nothing happened. But if everyone keeps adding different lines of code and nobody takes any off, then the code base keeps getting bigger.

Consistency

Consistency in databases means that the data stored in the database is always in a valid and consistent state. For example, if the database contains any constraints such as primary keys, foreign keys, and so on, it should always conform to the rules surrounding the constraint.

For example, let's say a table has a rule that says a specific column must be an integer value. Consistency ensures that this rule is always followed and data inserted into the column can only be of the integer value data type.

Isolation

The ability of multiple transactions to execute without interfering with one another is known as Isolation. The isolation level of a transaction determines how the changes made by that transaction are visible to other transactions.

MYSQL supports the following isolation levels:

i. READ UNCOMMITTED: In the READ UNCOMMITTED level, which is also the lowest isolation level, a transaction can read data that is yet to be committed by other transactions. This means that other transactions can alter the data that another transaction is currently reading, but these changes may not be visible until the operation is complete.

ii. READ COMMITTED: This is the second-to-the-lowest isolation level. Here, a transaction is only able to read data that has already been committed by other transactions. This means that other transactions can alter the data that a transaction is currently reading but these changes will not be visible until the other transaction has been committed.

iii. REPEATABLE READ: This is a higher level of isolation. A transaction at this level is only able to read data that has already been committed by other transactions, and it also restricts other transactions from altering the data that is currently being read. This means that even though other transactions have committed changes, if a transaction executes a SELECT statement again, it will always see the same data.

iv. SERIALIZABLE: This is the highest level of isolation. At this level, a transaction is only able to read data that has already been committed by other transactions. It also prevents other transactions from altering the data that the transaction is reading and from adding new rows that would be visible to the current transaction.

MySQL uses the READ COMMITTED isolation level by default. However, it is possible to change the isolation level by using the "SET TRANSACTION ISOLATION LEVEL" statement.

Durability

Durability ensures that your data remains safe, even in the event of unforeseen circumstances. When a transaction is committed, its changes must remain in the database, even if it experiences a malfunction or a power outage.

But how does MySQL ensure durability? It makes use of write-ahead logging. This technique involves writing a log of the transaction to disk before making any changes to the database.

The log acts as a road map for the database and contains information about the changes that will be made in case of an unexpected system failure. In the event of this, the database can be recovered from the log, and the changes made in the transaction will be replayed to make sure that the database is still in a consistent state.

It's important to keep in mind that while write-ahead logging can have a performance impact, it's a small price to pay for the peace of mind that comes with knowing your data is safe.

Locking and Concurrency in MySQL Transactions

Locking is a technique that is used to prevent race conditions. A race condition is a process where multiple transactions are trying to access the same data at the same time.

MySQL uses different types of locks to control access to data in a transaction. These include:

1. Shared locks: This allows multiple transactions to read the same data at the same time but restricts any of them from writing or making changes to it.
2. Exclusive locks: This prevents different transactions from reading or writing the same data at the same time.
3. Intent locks: This is used to specify that a transaction is planning to read or write a certain section of data.
4. Row-level locks: This allows transactions to lock only the specific rows they need to access, rather than the entire table.

Concurrency is a method where multiple transactions can run simultaneously without interfering with each other's data.

MySQL uses a multi-version concurrency control (MVCC) mechanism. This allows multiple transactions to read and write to the same data at the same time without conflict.

I'm sure you are wondering how this can be achieved. Well, each transaction sort of captures the data it is about to modify at the start of the transaction and writes its changes to an entirely different version of the data. This allows other transactions to continue working with the original version of the data without a conflict of interest.

To achieve high concurrency, it's important to keep the transactions as short as possible and avoid long-running transactions that hold locks for extended periods.

How to Create and Use Transactions in MySQL

The first thing required is to start the transaction using the "START TRANSACTION" statement. Here is an example:

START TRANSACTION;
    INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');
    UPDATE accounts SET balance = SUM(balance) WHERE name = 'John Doe';

In this example, a new transaction is started with the START TRANSACTION statement. The next two statements, an insert and an update, are executed within the transaction.

The next step is to commit the changes to make sure they are permanent. We do this by including the COMMIT statement in the query.

START TRANSACTION;
    INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');
    UPDATE accounts SET balance = SUM(balance) WHERE name = 'John Doe';
COMMIT;

If by any chance there was an error during the transaction and you want to undo the changes, you can use the ROLLBACK statement. Then the transaction will be rolled back and the insert and update statements will not be executed. This means no change will take place in the database.

START TRANSACTION;
    INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');
    UPDATE accounts SET balance = SUM(balance) WHERE user_id=15;
ROLLBACK;

How to Use the InnoDB Storage Engine for Transactions

InnoDB is a storage engine for MySQL that has many functions that can improve your database performance. Some of these features include the ability to group and execute multiple SQL statements together, encrypt our data, create and drop indexes without affecting the database performance, handle CPU as well as large data, and many more.

To use InnoDB for transactions in MySQL, you will need to make sure that your tables are using the InnoDB storage engine. You can check this by running the following query:

SHOW TABLE STATUS FROM your_database_name;

This will show you the storage engine used by each table in your database. It is also possible for you to set the default storage engine to InnoDB by modifying the my.cnf configuration file, or by running the following command:

SET storage_engine=InnoDB;

After running this query, your database tables should be using the InnoDB storage engine. We can then begin to perform the functions we listed above.

START TRANSACTION;
    UPDATE accounts SET balance = 50 WHERE user_id = 1;
    UPDATE accounts SET balance = 2000 WHERE user_id = 2;
COMMIT;

This is a simple example of a transaction that updates two rows in the "accounts" table. If any of the statements fails, the entire transaction will be rolled back, and no changes will be made to the database.

Additionally, InnoDB also provides some additional features like row-level locking, foreign key constraints, and crash recovery which makes it more robust and reliable than other storage engines, particularly for transactional workloads.

How to Handle Errors and Exceptions in Transactions

Handling errors and exceptions are important, especially when working with transactions.

A method of handling errors and exceptions in transactions is to use a try-catch block. In MySQL, you can use the SIGNAL and RESIGNAL statements to raise and handle exceptions within a transaction.

Here's an example of how you might use a try-catch block to handle an exception within a transaction:

START TRANSACTION;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        START TRANSACTION
            ROLLBACK;
            RESIGNAL;
        END;
    UPDATE accounts SET balance = 5000 WHERE user_id = 1;
    UPDATE accounts SET balance = 1000 WHERE user_id = 2;
    IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
COMMIT;

The DECLARE EXIT HANDLER FOR SQLEXCEPTION block is used to catch any SQL exceptions that occur within the transaction.

If an exception is caught, the transaction is rolled back using the ROLLBACK statement. Then the RESIGNAL statement raises the exception again so that it can be handled by an outer try-catch block, if any.

The IF statement checks if the balance of user_id =1 is less than zero. If true, the SIGNAL statement raises an exception with a specific SQLSTATE '45000' and a message "Insufficient balance".

It's worth knowing that if an exception occurs within a transaction, any changes that may have occurred during the transaction will be rolled back, regardless of whether or not the exception is handled.

How to Use Savepoints in MySQL Transactions

It's good practice to use the SAVEPOINT statement within a transaction to set savepoints. This makes it possible for you to rollback to a specific point in the transaction, rather than rolling back the entire transaction.

Here is an example of how you might use the SAVEPOINT statement within the previous example:

START TRANSACTION;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        START TRANSACTION
            ROLLBACK TO SAVEPOINT my_savepoint;
            RESIGNAL;
        END;
    UPDATE accounts SET balance = 5000 WHERE user_id = 1;
    UPDATE accounts SET balance = 1000 WHERE user_id = 2;
    IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
COMMIT;

You use the SAVEPOINT statement to set a savepoint named "my_savepoint" before the two updates. If an exception is caught, the ROLLBACK statement rolls back the transaction to the savepoint, using the clause "TO SAVEPOINT my_savepoint", rather than rolling back the entire transaction.

This will undo only the changes made after the savepoint, and leave the changes made before the savepoint intact.

Conclusion

Transaction operations are quite important. They help developers ensure that their database remains in a consistent state and makes it easy to reverse changes if necessary.

MySQL provides features such as commit, rollback, and savepoint to make the process much easier. It also provides robust engines like InnoDB which support these features as well.

For further information on MySQL transactions, you can check out the official documentation.

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!

Also, it expands the database, which makes backups larger and slower. Because of this, it's not regarded as a best practice. Instead, using a distributed system to save files and adding a reference to such files to our database is a good choice.

In this article you will learn how to upload these files to Azure cloud distributed services and delete them from Azure using NestJS, a popular Node framework.

Table of Contents

• What is a Cloud Distributed System?

• What is Azure?

• Getting Started

• How to Connect to Azure

• How to Set up NestJS and MySQL DB

• How to Connect to Azure Blob through SDK

• How to Upload the Image through the API

• How to Create an Endpoint for Uploading Images

• How to Delete Files

• Summary

What is a Cloud Distributed System?

A distributed system is made up of a number of independent parts that are spread across several devices. They communicate with one another via messages to accomplish shared objectives.

As a result, to the end user the distributed system will look like a single interface or computer. Together, the system is expected to maximize information and resource usage while preventing errors because, even if one system fails, the service will still be available.

What is Azure?

Azure is a platform for public cloud computing that offers solutions for analytics, virtual computing, storage, networking, and much more.

These solutions include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). You can supplement or replace your on-premise servers with it.

Blobs, tables, and queues are the three main data services provided by Azure. These services are all widely distributed, highly scalable, and reliable. We will be utilizing one of this services in this article.

Getting Started

Before you start following along with this tutorial, make sure you have the following things ready:

• An Azure Subscription – You can sign up for a free Azure account if you don’t already have one.

• Basic Knowledge and installations of NestJS and MySQL server database. Learn more from the NestJS documentation.

How to Connect to Azure

One of Microsoft's Cloud Storage's cloud-based object storage solution is called a Blob. Large-scale unstructured data storage is best suited for blob storage. Unstructured data, such as text or binary data, is data that does not follow a specific data model or description.

How to create a blob storage

Step 1: The Azure dashboard is visible once you create an account and log in to the Azure site. Select Storage Accounts from the menu or use the search bar.‌

Creating blob storage step 1‌

Step 2: Choose create or Create storage account if you do not have an existing storage account from the menu in the following box.

Creating blob storage step 2

Step 3: In this window, we need to fill in subscription, resource group, storage account name, region, performance, and redundancy.

Creating blob storage step 3

• The Subscription allows Azure keep track of where to charge for the resource used. You can use your free subscription here.

• A resource group is a central grouping for your resource(s). It helps you structure and organize your Azure resources based on your wants.

• The Storage account name must be a unique name globally.

• Performance gives you different storage types such as HDD and SSD. Here, we are using Standard.

• Redundancy helps protect your storage from data center or region failures by duplicating your resource to other regions.

Step 4: Then click review to validate your options. After completing validation, you can click the Create button to create the Storage account. (Note here that we left every other option as default.)

Creating blob storage step 4

‌When successfully created, the following windows should show up:

Creating blob storage step 5

Step 5: Next, select Go to resource to be sent to the storage account dashboard. The left sidebar is then visible and has a number of options. Choose the containers option there, which is in the Data Storage section.

Creating blob storage step 5

‌Step 6: in the Containers dashboard, now click on + Container, then a form will appear on the right side. Fill out the form by giving the name and public access level (you can use any option according to your requirements) for the container. You can create any number of containers under one storage account

Creating blob storage step 6

Creating blob storage step 6

Click the create button once you've finished filling out the form.

Step 7: Copy the Credentials from the Azure portal.

You should have authorization before sending requests to Azure storage. Azure offers two keys for that purpose, each of which contains a connection string. As a result, you will need these credentials as a connection string to the NestJS application.

One of the connection strings is available for copying in the Access keys area of the Security + networking menu on the left. (We will add these to our NestJS .env file.)

Creating blob storage step 7

Cool! You've configured your Azure blob storage. The next step is to setup and link your NestJS application with the blob storage.

How to Set Up NestJS and MySQL DB

As mentioned earlier, we will be using NestJS as our server and the MySQL database to save a reference to the file saved on the Azure distributed system.

Firstly you need to have NestJS and MySQL server installed on your system. Then run the following NestJS command to start a new project. Let's call our project nestjs-file-upload-azure:

nest new nestjs-file-upload-azure

How to create a new project in Nestjs‌

Before we get started in creating out resources, let's install the necessary dependencies needed:

yarn add mysql2 @nestjs/typeorm @nestjs/config typeorm

To setup your MySQL database with NestJS, open the app.module.ts inside the src folder and add the following code:

import { Module } from '@nestjs/common'; 
import { ConfigModule, ConfigService } from '@nestjs/config'; 
import { TypeOrmModule } from '@nestjs/typeorm'; 
import { FileModule } from './modules/files/file.module'; 
import { UserModule } from './modules/users/user.module'; 

@Module({ 
    imports: [ 
        ConfigModule.forRoot({ 
            envFilePath: '.env', 
            isGlobal: true, 
        }), 
        TypeOrmModule.forRootAsync({ 
            imports: [ConfigModule], 
            inject: [ConfigService], 
            useFactory: (config: ConfigService) => ({ 
                type: 'mysql', 
                host: 'localhost', 
                port: 3306, 
                username: config.get('DB_USERNAME'), 
                password: config.get('DB_PASSWORD'), 
                database: 'azure_upload', 
                entities: [__dirname + '/**/*.entity{.ts,.js}'], 
                synchronize: true, 
            }), 
        }), 
        UserModule, 
        FileModule, 
    ], 
    controllers: [], 
    providers: [], 
}) 

export class AppModule {}

Inside the imports, configure a simple MySQL database with the TypeOrmModule.forRootAsync() called azure_upload locally. It injects ConfigService to allow us to use environment variables (in this case your database name and password).

For a production-based application, you should set synchronize to false and use migration so as to keep your database data safe.

Now the database is connected successfully, thanks to the TypeORM package we installed. You can check by running yarn start:dev on your terminal or npm run start:dev if you are using npm as your package manager.

How to Connect to the Azure Blob through SDK

Using the @azure/storage-blob storage we can connect to Azure. We are still going to need the Multer package for managing file handling operations, and we'll use UUID to generate a unique name for each blob.

Let’s install them first.

• yarn add @azure/storage-blob uuidv4 @types/multer or

• npm install @azure/storage-blob uuidv4 @types/multer

Now, let's add the connection string we saved earlier to our .env file

How to Upload the Image through the API

Since we’ve got the Azure connection set up, we can proceed with uploading our files. For starters, let’s create a file entity and service.

How to Create an Azure Service

The Azure service will help us in mapping out the logic to upload, download, and delete files from our Azure storage account we created.

To generate a service, we will use the NestJS CLI. Open the terminal and run nest g service files modules/files --no-spec --flat.

Add the following to the service files generated:

src/modules/files/files.service.ts

import { BlobServiceClient, BlockBlobClient } from '@azure/storage-blob'; 
import { Injectable } from '@nestjs/common'; 
import { ConfigService } from '@nestjs/config'; 
import { uuid } from 'uuidv4'; 

@Injectable() export class FilesAzureService { 
    constructor(private readonly configService: ConfigService) {} 
    private containerName: string; 

    private async getBlobServiceInstance() { 
        const connectionString = this.configService.get('CONNECTION_STRING'); 
        const blobClientService = await BlobServiceClient.fromConnectionString( connectionString, ); 
        return blobClientService; 
    } 

    private async getBlobClient(imageName: string): Promise<BlockBlobClient> {
        const blobService = await this.getBlobServiceInstance(); 
        const containerName = this.containerName; 
        const containerClient = blobService.getContainerClient(containerName); 
        const blockBlobClient = containerClient.getBlockBlobClient(imageName); 

        return blockBlobClient; 
    } 

    public async uploadFile(file: Express.Multer.File, containerName: string) { 
        this.containerName = containerName; 
        const extension = file.originalname.split('.').pop(); 
        const file_name = uuid() + '.' + extension; 
        const blockBlobClient = await this.getBlobClient(file_name);
        const fileUrl = blockBlobClient.url; 
        await blockBlobClient.uploadData(file.buffer); 

        return fileUrl; 
    } 
}

The private functions create an instance of our Azure blob storage with the connection strings using the azure-sdk methods BlobServiceClient.fromConnectionString(). It also expects the container name we gave our blob container earlier during azure storage creation using getContainerClient and getBlockBlobClient().

The uploadFile() is a public function that user service can call to make image upload to azure. This function uses the azure instance and the private functions to upload the file and returns the file url.

How to Create an Endpoint for Uploading Images

It's time to create our user resources that provide an endpoint for us to create (upload) images to Azure, view the image, and delete the image.

How to Create a User Resource

The NestJS CLI is a powerful tool that helps scaffold our resource by creating basic component as you know them. To easily create a resource, on the terminal type the following command and follow the prompt for REST APIs:

nest generate resource users modules/users --no-spec --flat

The --no-spec ignores test files and the --flat creates the resource directly in a modules/users folder.

The above command added the folders dto and entities and files user.controller.ts, user.module.ts and user.service.ts inside the src file. It also performed all necessary updates to sync with app.module.ts.

User resources created

How to Create a User Entity

By saving the image URL directly in the database, we can access the public file very quickly.

src/modules/users/users.entity.ts

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm'; 

@Entity() export class User { 
    @PrimaryGeneratedColumn() 
    id: number; 

    @Column() 
    image_url: string; 
 }

How to Create a User Service

The user service helps connect with the database and save the upload image_url, hence the saveUrl() function.

src/modules/users/users.service.ts

import { Injectable } from '@nestjs/common'; 
import { InjectRepository } from '@nestjs/typeorm'; 
import { Repository } from 'typeorm'; 
import { User } from './entities/user.entity'; 

@Injectable() export class UserService { 
    constructor( 
        @InjectRepository(User) private readonly userRepository: Repository<User>, 
    ) {} 

    async saveUrl(file_url: string) { 
        await this.userRepository.save({ image_url: file_url }); 
    } 
}

How to Create a User Controller

This defines the endpoint for a public user to make a file upload. To do that, we follow the NestJS documentation and use the FileInterceptor that utilizes multer under the hood.

src/modules/users/users.controllers.ts

import { Controller, Post, UseInterceptors, UploadedFile, } from '@nestjs/common'; 
import { UserService } from './user.service'; 
import { FilesAzureService } from '../files/file.azure.service'; 
import { FileInterceptor } from '@nestjs/platform-express';

@Controller('user') export class UserController { 
    constructor( 
        private readonly userService: UserService, 
        private readonly fileService: FilesAzureService 
    ) {} 

    @Post('upload') 
    @UseInterceptors(FileInterceptor('image')) 
    async create(@UploadedFile() file: Express.Multer.File) { 
        const containerName = 'fileupload'; 
        const upload = await this.fileService.uploadFile(file, containerName) 
        this.userService.saveUrl(upload); 
        return { upload, message: 'uploaded successfully' } 
    } 
}

We can test with Postman:

Test upload endpoint from Postman

And confirm on Azure:

Confirm image on the Azure portal

How to Delete Files

We also need a means to delete files after submitting them. We'll remove the files from both locations to maintain consistency between our database and Azure storage. Adding the method to the Files Service first:

src/modules/files/files.service.ts

async deleteFile(file_name: string, containerName: string) { 
    try { 
        this.containerName = containerName; 
        const blockBlobClient = await this.getBlobClient(file_name);
        await blockBlobClient.deleteIfExists(); 
    } catch (error) { 
        console.log(error); 
    } 
}

We must now apply it to our Users Service. A crucial addition is that we remove the previous file when a user uploads one while already having one and making an upload endpoint by user id.

src/modules/users/users.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { FilesAzureService } from '../files/file.azure.service';
import { User } from './entities/user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User) private readonly userRepository: Repository<User>,
    private readonly fileService: FilesAzureService,
  ) {}

  async saveUrl(id, file_url: string, containerName: string): Promise<void> {
    const user = await this.userRepository.findOne({
      where: { id },
    });
    const file_image = user?.image_url;
    let getfile = '';

    if (file_image) {
      getfile = file_image.split('/').pop();
    }
    await this.userRepository.save({
      ...user,
      image_url: file_url,
    });
    await this.fileService.deleteFile(getfile, containerName);
  }

  async getimage(id: number): Promise<any> {
    const user = await this.userRepository.findOne({
      where: { id },
    });

    return user;
  }

  async remove(id: number, containerName: string): Promise<User> {
    try {
      const user = await this.userRepository.findOne({
        where: { id },
      });
      const file_url = user?.image_url;
      if (file_url) {
        await this.userRepository.update(id, {
          ...user,
          image_url: '',
        });

        const file_ = file_url.split('/').pop();

        await this.fileService.deleteFile(file_, containerName);
      }

      return user;
    } catch (error) {
      console.log(error);
      return error;
    }
  }
}

Including an endpoint where users can send an image is the final component. To accomplish that, we use the FileInterceptor, which internally makes use of multer, in accordance with the NestJS documentation.

src/modules/users/users.controller.ts

Controller('user')
export class UserController {
  constructor(
    private readonly userService: UserService,
    private readonly fileService: FilesAzureService
  ) {}

  @Post('/:id/upload')
  @UseInterceptors(FileInterceptor('image'))
  async create(@UploadedFile() file: Express.Multer.File, @Param('id') id) {
    const containerName = 'fileupload';
    const upload = await this.fileService.uploadFile(file, containerName)
    this.userService.saveUrl(id, upload, containerName);
    return {
      upload,
      message: 'uploaded successfully'
    }
  }

  @Get('/:id')
  async getimage(@Param('id') id) {
    const image = await this.userService.getimage(id);
    return {
      image,
      message: 'get image successfully'
    }
  }

  @Delete('remove/:id')
  @UseInterceptors(FileInterceptor('image'))
  async remove(@UploadedFile() file: Express.Multer.File , @Param('id') id) {
    const containerName = 'fileupload';
    const user = await this.userService.remove(id, containerName);
    return {
      user,
      message: 'deleted successfully'
    }
  }
}

These sync with your Azure storage to delete unused file uploads.

You can find the full code repository Link on GitHub.

Cleaning Up

To avoid paying for the underlying Azure storage cost, you should clean up your resources if they're not being used.

To clean up your Azure resources, in the Azure portal go to or search for resources group, find the one you just created, and delete it. This will delete all resources in the resource group.

Summary

In this article, we've learnt the fundamentals of Azure Blob storage and how to use it in our NestJS API.

To accomplish that, we've given the required credentials to Azure SDK, and as a result, we've been able to upload and remove files to Azure.

To track our files, we've also kept our MySQL database synchronized with an Azure blob container. We used the FileInterceptor, which is powered by Multer, to upload files using the API.

As always, I hope you enjoyed the article and learned something new. If you want, you can also follow me on LinkedIn or Twitter.

Those aggregate functions are AVG(), COUNT(), SUM(), MIN(), and MAX().

While making queries with the aggregate functions, you can also use them in combination with the GROUP BY clause and HAVING statement in any relational database – MySQL PostgreSQL, and others.

In this article, you will learn how to use aggregate functions on their own and with the GROUP BY clause and HAVING statement.

What We'll Cover

• How to Use Aggregate Functions
• Syntax of Aggregate Functions
• How to Use the AVG() Aggregate Function
  • How to use the AVG() Function with GROUP BY and HAVING
• How to Use the COUNT() Aggregate Function
  • How to Use COUNT() with GROUP BY and HAVING
• How to Use the MAX() Aggregate Function
  • How to Use MAX() with GROUP BY and HAVING
• How to Use the MIN() Aggregate Function
  • How to Use MIN() with GROUP BY and HAVING
• How to Use the SUM() Aggregate Function
  • How to Use SUM() with GROUP BY and HAVING
• Conclusion

How to Use Aggregate Functions

To show you how the aggregate functions work, I’ll be working with an employees table in an employees_data database.

Running SELECT * FROM employees got me the following:

Syntax of Aggregate Functions

The syntax for working with aggregate functions looks like this:

aggregate_function(MODIFIER | expression)

• the aggregate function could be AVG, COUNT, MAX, MIN, or SUM
• the modifier could be all the values or the values in a particular column

This syntax would make more sense in practice, so let’s get to use it with the aggregate functions.

How to Use the AVG() Aggregate Function

The AVG() aggregate function gets the total number of data and calculates their average.

I was able to get the average wage paid to the employees this way:

SELECT AVG(wage) 
FROM employees

The query below gets the average wage of junior developers:

SELECT AVG(wage) 
FROM employees
WHERE role = "Junior dev"

How to use the AVG() Function with GROUP BY and HAVING

You can get the average number of entries (rows) in a particular column with the GROUP BY clause and HAVING statement. This means you have to combine those two with AVG().

For instance, I was able to get the average wage paid to employees in each row with this query:

SELECT role, AVG(wage) 
FROM employees
GROUP BY role

I was also able to get the average wage of senior developers by using the HAVING statement:

SELECT role, AVG(wage) 
FROM employees
GROUP BY role
HAVING role = "Senior dev"

How to Use the COUNT() Aggregate Function

COUNT() returns the number of rows in a table based on the condition (or filter) you apply.

For example, to get the total number of rows, I ran the query below:

SELECT COUNT(*) 
FROM employees

And I got 20:

To get the total number of employees from the USA, I ran the query below:

SELECT COUNT(*) 
FROM employees
WHERE country = "USA"

And to get the employees who are technical writers, I did this:

SELECT COUNT(*) 
FROM employees
WHERE role = "Tech Writer"

How to Use COUNT() with GROUP BY and HAVING

In a large database, you can use the GROUP BY clause and HAVING statement in combination with COUNT() to get the total number of entries (rows) in a particular column.

In the database I’m using in this article, I was able to get the total number of employees in each row with the GROUP BY clause:

SELECT role, COUNT(*) 
FROM employees
GROUP BY role

To get the number of only the employees that are senior developers, I attached HAVING role = "Senior dev" to the query:

SELECT role, COUNT(*) 
FROM employees
GROUP BY role
HAVING role = "Senior dev"

How to Use the MAX() Aggregate Function

The MAX() function returns the maximum value within non-NULL values. This means it would ignore fields that are empty and return the highest value within those that are not empty.

For example, to get the highest wage in the employees table, I used the MAX() function like this:

SELECT MAX(wage) 
FROM employees

To get the maximum wage for mid-level developers, I used the WHERE statement:

SELECT MAX(wage) 
FROM employees
WHERE role = "Mid level dev"

How to Use MAX() with GROUP BY and HAVING

To get the maximum wage in each role, the GROUP BY clause comes in handy:

SELECT role, MAX(wage) 
FROM employees
GROUP BY role

And to get the maximum wage in a particular role, combining the HAVING statement with the GROUP BY clause gets it done:

SELECT role, MAX(wage) 
FROM employees
GROUP BY role
HAVING role = "Tech writer"

How to Use the MIN() Aggregate Function

The MIN() function is the opposite of the MAX() function – it returns the minimum value within non-NULL values.

For example, I got the lowest wage on the employees table this way:

SELECT MIN(wage) 
FROM employees

How to Use MIN() with GROUP BY and HAVING

Again, to get the minimum wage in each role, the GROUP BY clause can get it done:

SELECT role, MIN(wage) 
FROM employees
GROUP BY role

And to get the minimum wage of a particular role, the HAVING statement and GROUP BY clause are what to use:

SELECT role, MIN(wage) 
FROM employees
GROUP BY role
HAVING role = "Junior dev"

How to Use the SUM() Aggregate Function

The SUM() aggregate function adds the number of entries in a column based on the filter applied.

The query below gets the total number of wages paid to employees:

SELECT SUM(wage) 
FROM employees

How to Use SUM() with GROUP BY and HAVING

To get the sum of the total wages paid for employees in each role, I selected the role, used SUM() on the wages, and grouped them by the role:

SELECT role, SUM(wage) 
FROM employees
GROUP BY role

To get the total wages paid to technical writers only, I used the HAVING statement:

SELECT role, SUM(wage) 
FROM employees
GROUP BY role
HAVING role = "Tech Writer"

Conclusion

This article took you through what aggregate functions are in SQL, their syntax, and how to use them.

In addition, you also learned how to use aggregate functions with the GROUP BY clause, HAVING, and WHERE statements.

If you want to learn how the HAVING statement works, you should read this article I wrote on it.

Thank you for reading.

In this tutorial, you will learn how to build a single page application. I'll take you through the process step by step, using cutting edge technologies like Laravel 9, Jetstream, Vuejs, Inertiajs, MySQL, Tailwind CSS, and Docker.

Let's get started.

What you need to follow this guide:

To follow along you will need:

• a computer
• to know how to install software
• a basic understanding of HTML, CSS, JavaScript, and PHP
• knowledge of at least one JavaScript framework and an understanding of the MVC design pattern.

This guide is organized into 10 chapters and is based off a live coding series that I record. The live coding series is completely unscripted, so there will be bugs and gotchas there that you won't find in this guide.

You can find the complete playlist at the end of this article.

Everything here should just work, but if it doesn't feel free to ask for help by joining my community on Slack. There you can share code snippets and chat with me directly.

Table of Contents

• What Tech Are We Using?
• How to Setup Your Machine
• How to build the app with Laravel 9, Laravel Sail, Jetstram, Inertia and Vue3
• How to Refactor the Admin Dashboard and Create New Admin Pages
• How to Submit Forms with Files
• How to Add the Form to the Component
• How to Store Data
• How to Update Operations
• How to Delete a Resourse
• Wrap up and what's next
• Conclusion

What Tech Are We Using?

First, let's go over the different tools we'll be using in this project.

Docker

Docker is a set of platform as a service products that use OS-level virtualization to deliver software in packages called containers.

To simplify this concept, Docker lets you package applications and dependencies in a container.

A containerized application allows you to have a flexible development environment so that you can run different applications without worrying about dependencies, their requirements, and conflicts between different versions. You can easily run applications that, for instance, require two different versions of PHP and MySQL.

Each team member can quickly reproduce the same environment of your application by simply running the same container's configuration.

If you want to learn more about Docker, its Documentation is a great place to start.

Here's a Handbook on Docker essentials, as well, so you can practice your skills.

Mysql

MySQL is an open-source relational database management system. You can use it to organize data into one or more tables with data that may be related to each other.

We need to store data somewhere and here is where MySQL comes into play.

Here are the Docs if you want to read up more. Here's a full free course on MySQL if you want to dive deeper.

Laravel

Laravel is a free, open-source PHP web framework that helps you develop web applications following the model–view–controller architectural pattern.

Laravel is an amazing PHP framework that you can use to create bespoke web applications.

Here's the Laravel Documentation for more info, and here's a full project-based course to help you learn Laravel.

Laravel Sail

Laravel Sail is a lightweight command-line interface for interacting with Laravel's default Docker development environment.

Sail provides a great starting point for building a Laravel application using PHP, MySQL, and Redis without requiring prior Docker experience.

Usually, creating a development environment to build such applications means you have to install software, languages, and frameworks on your local machine – and that is time-consuming. Thanks to Docker and Laravel Sail we will be up and running in no time!

Laravel Sail is supported on macOS, Linux, and Windows via WSL2.

Here's the Documentation if you want to read up on it.

Laravel Jetstream

When building web applications, you likely want to let users register and log in to use your app. That is why we will use Jetstream.

Laravel Jetstream is a beautifully designed application starter kit for Laravel and provides the perfect starting point for your next Laravel application.

It uses Laravel Fortify to implement all the back end authentication logic. Here are the Docs.

Vuejs

Vue.js is an open-source model–view–ViewModel front end JavaScript framework for building user interfaces and single-page applications.

Vue is a fantastic framework that you can use as a stand-alone to build single-page applications, but you can also use it with Laravel to build something amazing.

Here's the Vue Documentation if you want to read up. And here's a great Vue course to get you started.

Inertia JS

Inertia is the glue between Laravel and Vuejs that we will use to build modern single-page applications using classic server-side routing.

You can learn more about it in the Documentation here.

Tailwind

Tailwind CSS is a utility-first CSS framework packed with classes like flex, pt-4, text-center, and rotate-90 that you can use to build any design, directly in your markup

We'll use it in this project to build our design. Here's a quick guide to get you up and running if you aren't familiar with Tailwind.

How to Set Up Your Machine

To follow along with my live coding (and this tutorial), you will need to install Docker desktop on your machine. If you are using Windows, you will also need to enable WSL in your system settings.

Visit the Docker getting started page to install Docker Desktop.

If you are on Windows, enable WSL2 by following the steps here.

If you have any trouble, feel free to reach out or join my community on Slack to get help.

Laravel Installation with Sail

If you have successfully installed Docker Desktop on your machine, we can open the terminal and install Laravel 9.

Open a terminal window and browse to a folder where you want to keep your project. Then run the command below to download the latest Laravel files. The command will put all files inside a folder called my-example-app, which you can tweak as you like.

# Download laravel
curl -s "https://laravel.build/my-example-app" | bash
# Enter the laravel folder
cd my-example-app

Deploy Laravel on Docker using the sail up command

With Docker Desktop up and running, the next step is to start Laravel sail to build all the containers required to run our application locally.

Run the following command from the folder where all Laravel files have been downloaded:

vendor/bin/sail up

It will take a minute. Then visit http://localhost and you should see your Laravel application.

If you run sail up and you get the following error, it is likely that you need to update Docker Desktop:

ERROR: Service 'laravel.test' failed to build:

How to Build the App with Laravel 9, Laravel Sail, Jetstram, Inertia and Vue3

In this section, we will define a basic roadmap, install Laravel 9 with Laravel Sail, Run sail, and build the containers.

I will also take you on a tour of Laravel Sail and the sail commands.

Then we will install Jetstream and scaffold Vue and Inertia files and have a look at the files and available features.

Next, we will populate our database and add the front end provided by Jetstream to register an account and log into a fresh Laravel application.

Finally, we will have a look at the Jetstream dashboard, and the Inertia/Vue Components and then start playing around.

Along the way, we'll disable the registration, enable the Jetstream user profile picture feature, and then add our first Inertia page where we'll render some data taken from the database.

Here's the live coding video if you want to follow along that way:

And if you prefer following along in this written tutorial, here are all the steps.

Just a reminder – you should have Laravel installed with Sail and have Docker set up on your machine. You can follow the steps above to do so if you haven't already.

Laravel Sail Overview – Sail Commands

With Laravel Sail installed, our usual Laravel commands have sligtly changed.

For instance, instead of running the Laravel artisan command using PHP like php artisan, we now have to use Sail, like so: sail artisan.

The sail artisan command will return a list of all available Laravel commands.

Usually, when we work with Laravel, we also have to run the npm and composer commands.

Again, we need to prefix our commands with sail to make them run inside the container.

Below you'll find a list of some commands you will likely have to run:

# Interact with the database - run the migrations
sail artisan migrate # It was: php artisan migrate
# Use composer commands
sail composer require <packageName> # it was: composer require <packageName>
# Use npm commands
sail npm run dev # it was: npm run dev

You can read more in the Sail documentation.

Install Jetstream and Scaffold Vue and Inertia

Let's now install the Laravel Jetstream authentication package and use the Inertia scaffolding with Vue3.

cd my-example-app
sail composer require laravel/jetstream

Remember to prefix the composer command with sail.

The command above has added a new command to Laravel. Now we need to run it to install all the Jetstream components:

sail artisan jetstream:install inertia

Next we need to compile all static assets with npm:

sail npm install
sail npm run dev

Before we can actually see our application, we will need to run the database migrations so that the session table, required by Jetstream, is present.

sail artisan migrate

Done! Jetstream is now installed in our application. If you visit http://localhost in your browser you should see the Laravel application with two links at the top to register and log in.

Populate the Database and Create a User Account

Before creating a new user, let's have a quick look at the database configuration that Laravel Sail has created for us in the .env file.

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=my-example-app
DB_USERNAME=sail
DB_PASSWORD=password

As you can see, Laravel Sail configures everything we need to access the database container that is running on Docker. The DB_DATABASE is the name of the database and it is the same as the project folder. This is why in the previous step we were able to run the migrate command without issues.

Since we already migrated all database tables, we can now use the Laravel built-in user factory to create a new user then use its details to log in our user dashboard.

Let's open artisan tinker to interact with our application.

sail artisan tinker

The command above will open a command line interface that we can use to interact with our application. Let's create a new user.

User::factory()->create()

The command above will create a new user and save its data in our database. Then it will render the user data onto the screen. Make sure to copy the user email so we can use it later to log in. Then exit by typing exit;.

The default password for every user created with a factory is password.

Let's visit the login page and access our application dashboard.

Jetstream Dashboard

After login you are redirected to the Jetstream dashboard, which looks amazing by default. We can customize it as we like, but it is just a starting point.

Jetstream/Vue Components and Inertia Overview

The first thing you may notice after installing Jetstram is that there are a number of Vue components registered in our application. Not only that, also Inertia brings in Vue components.

To use Inertia, we need to get familiar with it when defining routes.

When we installed Jetstream, it created inside the resources/js directory a number of subfolders where all our Vue components live. There are not just simple components but also Pages components rendered by inertia as our Views.

The Jetstream inertia scaffolding created:

• resources/js/Jetstream Here we have 27 components used by Jetstream, but we can use them in our application too if we want.
• resources/js/Layouts In this folder there is the layout component used by inertia to render the dashboard page
• resources/js/Pages This is where we will place all our Pages (views) components. You will find the Dashboard page as well as the Laravel Welcome page components here.

The power of Inertia mostly comes from how it connects Vue and Laravel, letting us pass data (Database Models and more) as props to our Vue Pages components.

When you open the routes/web.php file you will notice that we no longer return a view but instead we use Inertia to render a Page component.

Let's examine the / homepage route that renders the Welcome component.

Route::get('/', function () {
    return Inertia::render('Welcome', [
        'canLogin' => Route::has('login'),
        'canRegister' => Route::has('register'),
        'laravelVersion' => Application::VERSION,
        'phpVersion' => PHP_VERSION,
    ]);
});

It looks like our usual Route definition, exept that in the closure we are returning an \Inertia\Response by calling the render method of the Inertia class Inertia::render().

This method accepts two parameters. The first is a component name. Here we passed the Welcome Page component, while the second parameter is an associative array that will turn into a list of props to pass to the component. Here is where the magic happens.

Looking inside the Welcome component, you will notice that in its script section, we simply define four props matching with the keys of our associative array. Then inertia will do the rest.

<script>
    import { defineComponent } from 'vue'
    import { Head, Link } from '@inertiajs/inertia-vue3';

    export default defineComponent({
        components: {
            Head,
            Link,
        },
        // 👇 Define the props 
        props: {
            canLogin: Boolean, 
            canRegister: Boolean,
            laravelVersion: String,
            phpVersion: String,
        }
    })
</script>

We can then just call the props inside the template. If you look at the template section you will notice that laravelVersion and phpVersion are referenced in the code as you normally would do with props in Vuejs.

<div class="ml-4 text-center text-sm text-gray-500 sm:text-right sm:ml-0">
  Laravel v{{ laravelVersion }} (PHP v{{ phpVersion }})
</div>

The dashboard component is a little different. In fact it uses the Layout defined under Layouts/AppLayout.vue and uses the Welcome component to render the Dashboard page content, which is the same as the laravel Welcome page.

<template>
    <app-layout title="Dashboard">
        <template #header>
            <h2 class="font-semibold text-xl text-gray-800 leading-tight">
                Dashboard
            </h2>
        </template>

        <div class="py-12">
            <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
                <div class="bg-white overflow-hidden shadow-xl sm:rounded-lg">
                    <welcome /> 
                </div>
            </div>
        </div>
    </app-layout>
</template>

Inside the layout component you will notice the two inertia components Head and Link.

We can use the Head component to add head elements to our page, like meta tags, page title, and so on. The Link component is a wrapper aroud a standard anchor tag that incercepts click events and prevents full page reload as you can read in the Inertia documentation.

Link Component Head Component

Disable the Registration Feature

If you are following along, the next step I'll take is to disable one on the features Jetstream provides – register an account.

To do that, we can navigate to config/fortify.php and comment out line 135 Features::registration() from the features array.

'features' => [
        //Features::registration(),
        Features::resetPasswords(),
        // Features::emailVerification(),
        Features::updateProfileInformation(),
        Features::updatePasswords(),
        Features::twoFactorAuthentication([
            'confirmPassword' => true,
        ]),
    ],

If we visit the welcome page we will notice that the register link is gone. Also, the route is no longer listed when we run sail artisan route:list.

Enable Jetstream User Profile Picture

Now let's try to enable the Jetstream feature called ProfilePhotos. As you can guess, this will allow the user to add a profile picture.

To do that we need to visit config/jetstream.php and uncomment line 59 Features::profilePhoto.

    'features' => [
        // Features::termsAndPrivacyPolicy(),
        Features::profilePhotos(), // 👈
        // Features::api(),
        // Features::teams(['invitations' => true]),
        Features::accountDeletion(),
    ],

If you log in you will see that in the user profile, a new section is available to upload a profile picture.

But before doing anything else we need to run sail artisan storage:link so that Laravel creates a symlink to the storage/app/public folder where we will save all user profile images.

Now try to visit the user profile and update the profile picture. If you get a 404 on the image this is because by default Laravel sail assumes we are using Laravel valet and sets the app URL like so APP_URL=http://my-example-app.test in the .env file. Let's change it and use localhost instead.

APP_URL=http://localhost

Now we should be good to go and be able to see and change our profile image!🥳

How to Add our First Inertia Page and Render Records from the DB

Since we are rendering Vue components instead of blade views, it is wise to start sail npm run watch to watch and recompile our Vue components as we create or edit them. Next let's add a new Photos page.

I will start by creating a new Route inside web.php:

Route::get('photos', function () {
    //dd(Photo::all());
    return Inertia::render('Guest/Photos');
});

In the code above I defined a new GET route and then rendered a component that I will place inside the resources/js/Pages/Guest and call Photos. Let's create it.

Create a Guest folder:

cd resources/js/Pages
mkdir Guest
cd Guest
touch Photos.vue

Then let's define a basic component:

<template>
  <h1>Photos Page</h1>
</template>

If we visit http://localhost/photos/ we will see our new page, cool! Let's copy over the page structure from the Welcome page so that we get the login and dashboard links as well.

The component will change to this:

<template>
    <Head title="Phots" />

    <div class="relative flex items-top justify-center min-h-screen bg-gray-100 dark:bg-gray-900 sm:items-center sm:pt-0">
        <div v-if="canLogin" class="hidden fixed top-0 right-0 px-6 py-4 sm:block">
            <Link v-if="$page.props.user" :href="route('admin.dashboard')" class="text-sm text-gray-700 underline">
                Dashboard
            </Link>

            <template v-else>
                <Link :href="route('login')" class="text-sm text-gray-700 underline">
                    Log in
                </Link>

                <Link v-if="canRegister" :href="route('register')" class="ml-4 text-sm text-gray-700 underline">
                    Register
                </Link>
            </template>
        </div>

        <div class="max-w-6xl mx-auto sm:px-6 lg:px-8">
            <h1>Photos</h1>

        </div>
    </div>
</template>

<script>
    import { defineComponent } from 'vue'
    import { Head, Link } from '@inertiajs/inertia-vue3';

    export default defineComponent({
        components: {
            Head,
            Link,
        },

        props: {
            canLogin: Boolean,
            canRegister: Boolean,

        }
    })
</script>

The next step is to render a bunch of data onto this new page. For that we will build a Model and add some records to the database.

saild artisan make:model Photo -mfcr

This command creates a Model called Photo, plus a database migration table class, a factory, and a resource controller.

Now let's define the database table inside the migration we just creted. Visit the database/migrations folder and you should see a file with a name similar to this: 2022_02_13_215119_create_photos_table (yours will be sligly different).

Inside the migration file we can define a basic table like the following:

 public function up()
    {
        Schema::create('photos', function (Blueprint $table) {
            $table->id();
            $table->string('path');
            $table->text('description');
            $table->timestamps();
        });
    }

For our table we defined just two new columns, path and description, plus the id, created_at and updated_at that will be created by the $table->id() and by the $table->timestamps() methods.

After the migration we will define a seeder and then run the migrations and seed the database.

At the top of the database/seeders/PhotoSeeder.php file we will import our Model and Faker:

use App\Models\Photo;
use Faker\Generator as Faker;

Next we will implement the run method using a for loop to create 10 records in the database.

    public function run(Faker $faker)
    {
        for ($i = 0; $i < 10; $i++) {
            $photo = new Photo();
            $photo->path = $faker->imageUrl();
            $photo->description = $faker->paragraphs(2, true);
            $photo->save();
        }
    }

We are ready to run the migrations and seed the database.

sail artisan migrate
sail artisan db:seed --class PhotoSeeder

We are now ready to show the data on the Guest/Photos page component. First update the route and pass a collection of Photos as props to the rendered component:

Route::get('photos', function () {
    //dd(Photo::all());
    return Inertia::render('Guest/Photos', [
        'photos' => Photo::all(), ## 👈 Pass a collection of photos, the key will become our prop in the component
        'canLogin' => Route::has('login'),
        'canRegister' => Route::has('register'),
    ]);
});

Second, pass the prop to the props in the script section of the Guest/Photos component:

props: {
    canLogin: Boolean,
    canRegister: Boolean,
    photos: Array // 👈 Here
}

Finally loop over the array and render all photos in the template section, just under the h1:

<section class="photos">
    <div v-for="photo in photos" :key="photo.id" class="card" >
        <img :src="photo.path" alt="">
    </div>
</section>

Done! if you visit the /photos page you should see ten photos. 🥳

How to Refactor the Admin Dashboard and Create New Admin Pages

In this chapter we will Re-route the Jetstream dashboard and make a route group for all admin pages.

Then we will see how to add a new link to the dashboard and add a new admin page.

Finally we will take a collection of data from the db and render them in a basic table. The default table isn't cool enough, so for those reading this article, I decided to add a Tailwind table component.

Re-route the Jetstream Dashboard

If we look at the config/fortify.php file we can see that around line 64 there is a key called home. It is calling the Home constant of the Route service provider.

This means that we can tweek the constant and redirect the authenticated user to a different route.

Lets go through it step-by-step:

• update the HOME Constant
• make a route group and redirect logged in users to admin/ instead of '/dashboard'

Our application will have only a single user, so once they're logged in it is clearly the site admin – so makes sense to redirect to an admin URI.

Change the HOME constant in app/Providers/RouteServiceProvider.php around line 20 to match the following:

public const HOME = '/admin';

How to Add an Admin Pages Route Group

Next let's update our route inside web.php. We will change the route registered by Jetstream from this:

Route::middleware(['auth:sanctum', 'verified'])->get('/', function () {
        return Inertia::render('Dashboard');
    })->name('dashboard');

To this:

Route::middleware(['auth:sanctum', 'verified'])->prefix('admin')->name('admin.')->group(function () {

    Route::get('/', function () {
        return Inertia::render('Dashboard');
    })->name('dashboard');

    // other admin routes here
});

The route above is a route group that uses the auth:sanctum middleware for all routes within the group, a prefix of admin, and adds a admin suffix to each route name.

The end result is that we will be able to refer to the dashboard route by name, which now will be admin.dashboard. When we log in, we will be redirected to the admin route. Our dashboard route will respond since it's URI is just / but the goup prefix will prefix every route in the group and make their URI start with admin.

If you now run sail artisan route:list you will notice that the dashboard route has changed as we expected.

Before moving to the next step we need to update both the /layouts/AppLayout.vue and /Pages/Welcome.vue components.

Do you remeber that the dashboard route name is now admin.dashboard and not just dashboard?

Let's inspect the two components and update every reference of route('dahsboard') to this:

route('admin.dahsboard')

and also every reference of route().current('dashboard') to this:

route().current('admin.dashboard')

After all the changes, make sure to recompile the Vue components and watch changes by running sail npm run watch. Then visit the home page to check if everything is working.

How to Add a New Link to the Dashboard

Now, to add a new admin page where we can list all photos stored in the database, we need to add a new route to the group we created earlier. Let's hit the web.php file and make our changes.

In the Route group we will add a new route:

Route::middleware(['auth:sanctum', 'verified'])->prefix('admin')->name('admin.')->group(function () {

    Route::get('/', function () {
        return Inertia::render('Dashboard');
    })->name('dashboard');

    // 👇 other admin routes here 👇

    Route::get('/photos', function () {
        return inertia('Admin/Photos');
    })->name('photos'); // This will respond to requests for admin/photos and have a name of admin.photos

});

In the new route above we used the inertia() helper function that does the same exact thing – returns an Inertia/Response and renders our Page component. We placed the component under an Admin folder inside Pages and we will call it Photos.vue.

Before we create the component, let's add a new link to the dashboard that points to our new route.

Inside AppLayout.vue, find the Navigation Links comment and copy/paste the jet-nav-link component that is actually displaing a link to the dashboard and make it point to our new route.

You will end up having something like this:

<!-- Navigation Links -->
<div class="hidden space-x-8 sm:-my-px sm:ml-10 sm:flex">
    <jet-nav-link :href="route('admin.dashboard')" :active="route().current('admin.dashboard')">
        Dashboard
    </jet-nav-link>
    <!-- 👇 here it is our new link -->
      <jet-nav-link :href="route('admin.photos')" :active="route().current('admin.photos')">
        Photos
    </jet-nav-link>
</div>

Our link above uses route('admin.photos') to point to the correct route in the admin group.

If you visit localhost/dashboard and open the inspector, you should see an error:

Error: Cannot find module `./Photos.vue`

It is fine – we haven't created the Photos page component yet. So let's do it now!

How to Add a New Admin Page Component

Make a file named Photos.vue inside the Pages/Admin folder. Below are the bash commands to create the folder and the file via terminal, but you can do the same using your IDE's graphical interface.

cd resources/js/Pages
mkdir Admin
touch Admin/Photos.vue

To make this new page look like the Dashboard page, we will copy over its content. You should end up having something like this:

<template>
  <app-layout title="Dashboard"> <!-- 👈 if you want you can update the page title -->
    <template #header>
      <h2 class="font-semibold text-xl text-gray-800 leading-tight">Photos</h2>
    </template>

    <div class="py-12">
      <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
        <div class="bg-white overflow-hidden shadow-xl sm:rounded-lg">
          <!-- 👇  All photos for the Admin page down here -->
          <h1 class="text-2xl">Photos</h1>

        </div>
      </div>
    </div>
  </app-layout>
</template>

<script>
import { defineComponent } from "vue";
import AppLayout from "@/Layouts/AppLayout.vue";

export default defineComponent({
  components: {
    AppLayout,
  },
});
</script>

I removed a few pieces from the Dashboard template so make sure to double check the code above. The welcome component was removed from the template as it is not required in this page, and also its reference in the script section. The rest is identical.

Feel free to update the page title referenced as prop on the <app-layout title="Dashboard">.

Now when you visit localhost/admin you can click on the Photos menu item and see our Photos page component content. It's not much for now, just an h1.

How to Render Records in the Admin Page as a Table

Now it's time to render the data onto a table. To make things work let's first add our markup and fake that we already have access to as an array of objects and loop over them inside our table. Than we will figure out how to make things work for real.

 <table class="table-auto w-full text-left">
  <thead>
    <tr>
      <th>ID</th>
      <th>Photo</th>
      <th>Desciption</th>
      <th>Actions</th>
    </tr>
  </thead>
  <tbody>
    <tr v-for="photo in photos">
      <td>{{ photo.id }}</td>
      <td><img width="60" :src="photo.path" alt="" /></td>
      <td>{{photo.description}}</td>
      <td>View - Edit - Delete</td>

    </tr>
  </tbody>
</table>

Ok, since we assumed that our component has access to a list of Photos, let's pass a new prop to the component from the Route.

Update the route in web.php and pass to the inertia() function a second argument that will be an associative array. It will have its keys passed as props to the Vue Page component.

In it we will call Photo::all() to have a collection to assign to a photos key, but you can use other eloquent methods if you want to paginate the results, for example.

Route::get('/photos', function () {
    return inertia('Admin/Photos', [
        'photos' => Photo::all()
    ]);
})->name('photos');

To connect the prop to our Page component we need to define the prop also inside the component.

<script>
import { defineComponent } from "vue";
import AppLayout from "@/Layouts/AppLayout.vue";

export default defineComponent({
  components: {
    AppLayout,
  },
  /* 👇 Pass the photos array as a props 👇 */
  props: {
    photos: Array,
  },
});
</script>

Extra: How to use a Tailwind table component

Tailwind is a CSS framework similar to Bootstrap. There are a number of free to use components that we can grab from the documentation, tweak, and use.

This table component is free and looks nice:https://tailwindui.com/components/application-ui/lists/tables.

We can tweek the Photos page template and use the Tailwind table component to get a nice looking table like so:

<template>
    <app-layout title="Dashboard">
        <template #header>
            <h2 class="font-semibold text-xl text-gray-800 leading-tight">Photos</h2>
        </template>

         <div class="py-12">
            <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
              <!-- All posts goes here -->
              <h1 class="text-2xl">Photos</h1>
              <a class="px-4 bg-sky-900 text-white rounded-md" href>Create</a>
              <div class="flex flex-col">
                  <div class="-my-2 overflow-x-auto sm:-mx-6 lg:-mx-8">
                      <div class="py-2 align-middle inline-block min-w-full sm:px-6 lg:px-8">
                          <div class="shadow overflow-hidden border-b border-gray-200 sm:rounded-lg">
                              <table class="min-w-full divide-y divide-gray-200">
                                  <thead class="bg-gray-50">
                                      <tr>
                                          <th
                                              scope="col"
                                              class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider"
                                          >ID</th>
                                          <th
                                              scope="col"
                                              class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider"
                                          >Photos</th>
                                          <th
                                              scope="col"
                                              class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider"
                                          >Description</th>
                                          <th scope="col" class="relative px-6 py-3">
                                              <span class="sr-only">Edit</span>
                                          </th>
                                      </tr>
                                  </thead>
                                  <tbody class="bg-white divide-y divide-gray-200">
                                      <tr v-for="photo in photos" :key="photo.id">
                                          <td class="px-6 py-4 whitespace-nowrap">
                                              <div
                                                  class="text-sm text-gray-900"
                                              >{{ photo.id }}</div>
                                          </td>

                                          <td class="px-6 py-4 whitespace-nowrap">
                                              <div class="flex items-center">
                                                  <div class="flex-shrink-0 h-10 w-10">
                                                      <img
                                                          class="h-10 w-10 rounded-full"
                                                          :src="photo.path"
                                                          alt
                                                      />
                                                  </div>
                                              </div>
                                          </td>

                                          <td class="px-6 py-4 whitespace-nowrap">
                                              <div class="text-sm text-gray-900">
                                                {{ photo.description.slice(0, 100) + '...' }}
                                              </div>
                                          </td>
                                        <!-- ACTIONS -->
                                          <td class="px-6 py-4 whitespace-nowrap text-right text-sm font-medium">
                                              <a href="#" class="text-indigo-600 hover:text-indigo-900">
                                              View - Edit - Delete
                                              </a>
                                          </td>
                                      </tr>
                                  </tbody>
                              </table>
                          </div>
                      </div>
                  </div>
                </div>
            </div>
        </div>
    </app-layout>
</template>

How to Submit Forms with Files

For the next section we will look into how to submit a form so that we can add a new photo to the database.

• Add a create button
• Add a create route
• Define the PhotosCreate component
• Add a form
• Validate data
• Show validation errors
• Save the file to the filesystem
• Save the model

How to Create a New Photo

Add a link that points to a create route:

<a class="px-4 bg-sky-900 text-white rounded-md" :href="route('admin.photos.create')">Create</a>

Create the route within the admin group:

Route::get('/photos/create', function () {
    return inertia('Admin/PhotosCreate');
})->name('photos.create');

Let's add also the route that will handle the form submission for later:

Route::post('/photos', function () {
    dd('I will handle the form submission')   
})->name('photos.store');

Create the Admin/PhotosCreate.vue component:

    <template>
    <app-layout title="Dashboard">
        <template #header>
            <h2 class="font-semibold text-xl text-gray-800 leading-tight">Photos</h2>
        </template>

         <div class="py-12">
            <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
                <h1 class="text-2xl">Add a new Photo</h1>
                <!-- 👇 Photo creation form goes here -->

            </div>
        </div>
    </app-layout>
</template>


<script>
import { defineComponent } from "vue";
import AppLayout from "@/Layouts/AppLayout.vue";

export default defineComponent({
  components: {
    AppLayout,
  },

});
</script>

How to Add the Form to the Component

The next step is to add the form to the page and figure out how to submit it.

If you hit the Inertia documentation you will find out that there is a useForm class that we can use to simplify the process.

First, import the module inside the script tag of the Admin/PhotosCreate.vue component:

import { useForm } from '@inertiajs/inertia-vue3';

Next we can use it in the setup function (Vue 3 composition API):

setup () {
    const form = useForm({
      path: null,
      description: null,
    })

    return { form }
  }

In the code above we defined the function called setup() then a constant called form to have the useForm() class assigned to it.

Inside its parentheses we defined two properties, path and description which are the column names of our photos model.

Finally we returned the form variable for the setup function. This is to make the variable available inside our template.

Next we can add the form markup:

<form @submit.prevent="form.post(route('admin.photos.store'))">

<div>
    <label for="description" class="block text-sm font-medium text-gray-700"> Description </label>
    <div class="mt-1">
        <textarea id="description" name="description" rows="3" class="shadow-sm focus:ring-indigo-500 focus:border-indigo-500 mt-1 block w-full sm:text-sm border border-gray-300 rounded-md" placeholder="lorem ipsum" v-model="form.description"/>
    </div>
    <p class="mt-2 text-sm text-gray-500">Brief description for your photo</p>
        <div class="text-red-500" v-if="form.errors.description">{{form.errors.description}}</div>
</div>
<div>
    <label class="block text-sm font-medium text-gray-700"> Photo </label>
    <div class="mt-1 flex justify-center px-6 pt-5 pb-6 border-2 border-gray-300 border-dashed rounded-md">
    <div class="space-y-1 text-center">
        <svg class="mx-auto h-12 w-12 text-gray-400" stroke="currentColor" fill="none" viewBox="0 0 48 48" aria-hidden="true">
        <path d="M28 8H12a4 4 0 00-4 4v20m32-12v8m0 0v8a4 4 0 01-4 4H12a4 4 0 01-4-4v-4m32-4l-3.172-3.172a4 4 0 00-5.656 0L28 28M8 32l9.172-9.172a4 4 0 015.656 0L28 28m0 0l4 4m4-24h8m-4-4v8m-12 4h.02" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" />
        </svg>
        <div class="flex text-sm text-gray-600">
        <label for="path" class="relative cursor-pointer bg-white rounded-md font-medium text-indigo-600 hover:text-indigo-500 focus-within:outline-none focus-within:ring-2 focus-within:ring-offset-2 focus-within:ring-indigo-500">
            <span>Upload a file</span>
            <input id="path" name="path" type="file" class="sr-only" @input="form.path = $event.target.files[0]" />
        </label>
        <p class="pl-1">or drag and drop</p>
        </div>
        <p class="text-xs text-gray-500">PNG, JPG, GIF up to 10MB</p>
    </div>
    </div>
</div>
<div class="text-red-500" v-if="form.errors.path">{{form.errors.path}}</div>

<button type="submit" :disabled="form.processing" class="inline-flex justify-center py-2 px-4 border border-transparent shadow-sm text-sm font-medium rounded-md text-white bg-indigo-600 hover:bg-indigo-700 focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-indigo-500">Save</button>
</form>

The code above uses the Vue v-on directive short end syntax @submit.prevent="form.post(route('admin.photos.store'))" on the form tag, and the dom event submit with the prevent modifier.

Then it uses the form variable that we created earlier and a post method. This is available because we are using the useForm class.

Next we point the form to the route named admin.photos.store that we created earlier.

Inside the form we have two groups of inputs. First, we have the textarea that uses the v-model to bind it to the property form.description that we declared before.

The second group uses the form.path in a Tailwind component (showing the markup for a drop file area).

Right now we are allowing users to upload only a single photo using the v-on directive on the input DOM event @input="form.path = $event.target.files[0]".

The last two things to notice are the error handling done via <div class="text-red-500" v-if="form.errors.path">{{form.errors.path}}</div> for the path and also for the description.

Finally we use form.processing to disable the submit button while the form is processing.

The next step is to define the logic to save the data inside the database.

How to Store Data

To store the data, we can edit the route we defined earlier like so:

Route::post('/photos', function (Request $request) {
    //dd('I will handle the form submission')  

    //dd(Request::all());
    $validated_data = $request->validate([
        'path' => ['required', 'image', 'max:2500'],
        'description' => ['required']
    ]);
    //dd($validated_data);
    $path = Storage::disk('public')->put('photos', $request->file('path'));
    $validated_data['path'] = $path;
    //dd($validated_data);
    Photo::create($validated_data);
    return to_route('admin.photos');
})->name('photos.store');

The code above uses dependency injection to allow us to use the parameter $request inside the callback function.

We first validate the request and save the resulting array inside the variable $validated_data. Then we use the Storage facades to save the file in the filesystem and obtain the file path that we store inside the $path variable.

Finally we add a path key to the associative array and pass to it the $path variable. Next we create the resource in the database using the Photo::create method and redirect the user to the admin.photos page using the new to_route() helper function.

Make sure to import the Request class and the Storage facades at the top of the web.php file like so:

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;

Now we can add a new photo in the database and show a list of photos for both the admin and standard visitors.

Next we need to complete the CRUD operations and allow the user to edit/update a photo and delete it.

How to Update Operations

Let's start by adding the routes responsible for showing the forms used to edit the resource and update its values onto the database.

Just under the other routes in the Admin group, let's add the following code:

Route::get('/photos/{photo}/edit', function(Photo $photo){
     return inertia('Admin/PhotosEdit', [
            'photo' => $photo
        ]);
})->name('photos.edit');

The route above uses dependency injection to inject inside the function the current post, selected by the URI /photos/{photo}/edit.

Next it returns the Inertia response via the inertia() function that accepts the Component name 'Admin/PhotosEdit' as its first parameter and an associative array as its second.

Doing ['photo' => $photo] will allow us to pass the $photo model as a prop to the component later.

Next let's add the new Page component under resources/js/Pages/Admin/PhotosEdit.vue

This will be its template:

<template>
    <app-layout title="Edit Photo">
        <template #header>
            <h2 class="font-semibold text-xl text-gray-800 leading-tight">Edit Photo</h2>
        </template>
        <div class="py-12">
            <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
                <form @submit.prevent="form.post(route('admin.photos.update', photo.id))">
                    <div>
                        <label
                            for="description"
                            class="block text-sm font-medium text-gray-700"
                        >Description</label>
                        <div class="mt-1">
                            <textarea
                                id="description"
                                name="description"
                                rows="3"
                                class="shadow-sm focus:ring-indigo-500 focus:border-indigo-500 mt-1 block w-full sm:text-sm border border-gray-300 rounded-md"
                                placeholder="lorem ipsum"
                                v-model="form.description"
                            />
                        </div>
                        <p class="mt-2 text-sm text-gray-500">Brief description for your photo</p>
                        <div
                            class="text-red-500"
                            v-if="form.errors.description"
                        >{{ form.errors.description }}</div>
                    </div>

                    <div class="grid grid-cols-2">
                        <div class="preview p-4">
                            <img :src="'/storage/' + photo.path" alt />
                        </div>
                        <div>
                            <label class="block text-sm font-medium text-gray-700">Photo</label>
                            <div
                                class="mt-1 flex justify-center px-6 pt-5 pb-6 border-2 border-gray-300 border-dashed rounded-md"
                            >
                                <div class="space-y-1 text-center">
                                    <svg
                                        class="mx-auto h-12 w-12 text-gray-400"
                                        stroke="currentColor"
                                        fill="none"
                                        viewBox="0 0 48 48"
                                        aria-hidden="true"
                                    >
                                        <path
                                            d="M28 8H12a4 4 0 00-4 4v20m32-12v8m0 0v8a4 4 0 01-4 4H12a4 4 0 01-4-4v-4m32-4l-3.172-3.172a4 4 0 00-5.656 0L28 28M8 32l9.172-9.172a4 4 0 015.656 0L28 28m0 0l4 4m4-24h8m-4-4v8m-12 4h.02"
                                            stroke-width="2"
                                            stroke-linecap="round"
                                            stroke-linejoin="round"
                                        />
                                    </svg>
                                    <div class="flex text-sm text-gray-600">
                                        <label
                                            for="path"
                                            class="relative cursor-pointer bg-white rounded-md font-medium text-indigo-600 hover:text-indigo-500 focus-within:outline-none focus-within:ring-2 focus-within:ring-offset-2 focus-within:ring-indigo-500"
                                        >
                                            <span>Upload a file</span>
                                            <input
                                                id="path"
                                                name="path"
                                                type="file"
                                                class="sr-only"
                                                @input="form.path = $event.target.files[0]"
                                            />
                                        </label>
                                        <p class="pl-1">or drag and drop</p>
                                    </div>
                                    <p class="text-xs text-gray-500">PNG, JPG, GIF up to 10MB</p>
                                </div>
                            </div>
                            <div class="text-red-500" v-if="form.errors.path">{{ form.errors.path }}</div>
                        </div>
                    </div>

                    <button
                        type="submit"
                        :disabled="form.processing"
                        class="inline-flex justify-center py-2 px-4 border border-transparent shadow-sm text-sm font-medium rounded-md text-white bg-indigo-600 hover:bg-indigo-700 focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-indigo-500"
                    >Update</button>
                </form>
            </div>
        </div>
    </app-layout>
</template>

The template is actually identical to the Create component, except for a few things. The form points to a route that expects a paramenter that we pass as the second argument to the funtion route. It looks like this: <form @submit.prevent="form.post(route('admin.photos.update', photo.id))">.

There is a section where we can see the original photo next to the upload form group:

 <div class="preview p-4">
    <img :src="'/storage/' + photo.path" alt />
</div>

The rest is identical, and here we have the script section:

import { defineComponent } from "vue";
import AppLayout from "@/Layouts/AppLayout.vue";
import { useForm } from '@inertiajs/inertia-vue3';

export default defineComponent({
    components: {
        AppLayout,
    },
    props: {
        photo: Object
    },
    setup(props) {
        const form = useForm({
            _method: "PUT",
            path: null,
            description: props.photo.description,
        })

        return { form }
    },

});

Notice that we are passing a props object with the photo key, which allows us to reference the model in the template.

Next, this _method: "PUT", line of code is required to be able to submit a PUT request instead of the POST request called on the form tag.

Now let's implement the logic to handle the form submission inside the Route below.

In web.php just under the previous route, let's add one that responds to the PUT request submitted by our form.

Route::put('/photos/{photo}', function (Request $request, Photo $photo)
    {
        //dd(Request::all());

        $validated_data = $request->validate([
            'description' => ['required']
        ]);

        if ($request->hasFile('path')) {
            $validated_data['path'] = $request->validate([
                'path' => ['required', 'image', 'max:1500'],

            ]);

            // Grab the old image and delete it
            // dd($validated_data, $photo->path);
            $oldImage = $photo->path;
            Storage::delete($oldImage);

            $path = Storage::disk('public')->put('photos', $request->file('path'));
            $validated_data['path'] = $path;
        }

        //dd($validated_data);

        $photo->update($validated_data);
        return to_route('admin.photos');
    })->name('photos.update');

The route logic is straigthforward. First we validate the description, next we check if a file was uploaded and if so we validate it.

Then we delete the previously uploaded image Storage::delete($oldImage); before storing the new image onto the datadabse and update the resource using $photo->update($validated_data);.

As before with the store route, we redirect to the admin.photos route using return to_route('admin.photos');.

How to Delete a Resource

The last step we need to take is to write the logic to delete the photo. Let's start by adding the route.

Right below the previous route we can write:

Route::delete('/photos/{photo}', function (Photo $photo)
{
    Storage::delete($photo->path);
    $photo->delete();
    return to_route('admin.photos');
})->name('photos.delete');

This route is also using a wildcard in its URI to identify the resource. Next, its second paramenter is the callback that uses the dependency injection as before. Inside the callback we first delete the image from the filesystem using Storage::delete($photo->path);.

Then we remove the resource from the database $photo->delete(); and redirect the user back return to_route('admin.photos'); like we did in the previous reoute.

Now we need to add a delete button to the table we created in one of the previous steps to show all photos.

Inside the template section of the component Admin/Photos.vue within the v-for, we can add this Jetstream button:

<jet-danger-button @click="delete_photo(photo)">
    Delete
</jet-danger-button>

Find the table cell that has the ACTIONS comment and replace the DELETE text with the button above.

So the final code will be:

<td class="px-6 py-4 whitespace-nowrap text-right text-sm font-medium">
    <a href="#" class="text-indigo-600 hover:text-indigo-900">
    View - Edit - 

    <jet-danger-button @click="delete_photo(photo)">
        Delete
    </jet-danger-button>
    </a>
</td>

As you can see there is a @click event listener on the button. It calls a method delete_photo(photo) that we need to define along with a bunch of other methods to have a nice modal opening to ask for confirmation from the user.

First import the Inertia helper function useForm:

// 0. Import the useForm class at the top of the script section along with all required components
import { useForm } from '@inertiajs/inertia-vue3';
import JetDangerButton from '@/Jetstream/DangerButton.vue'
import { ref } from "vue";

Remember to register the component JetDangerButton inside the components object before moving forward.

Next add the setup() function in the script section and implement the logic required to submit the form and show a modal. The comments in the code will guide you thorought all the steps.

// 1. add the setup function
setup() {
    // 2. declare a form variable and assign to it the Inertia useForm() helper function 
    const form = useForm({
        // 3. override the form method to make a DELETE request
        _method: "DELETE",
    });
    // 4. define a reactive object with show_modal and photo property
    // this will be used to figure out when to show the modal and the selected post values
    const data = ref({
        show_modal: false,
        photo: {
            id: null,
            path: null,
            description: null,
        }
    })

    // 5. define the delete_photo function and update the values of the show_modal and photo properties
    // of the reactive object defined above. This method is called by the delete button and will record the details 
    // of the selected post
    const delete_photo = (photo) => {
        //console.log(photo);
        //console.log(photo.id, photo.path, photo.description);
        data.value = {
            photo: {
                id: photo.id,
                path: photo.path,
                description: photo.description
            },
            show_modal: true
        };
    }
    // 6. define the method that will be called when our delete form is submitted
    // the form will be created next
    const deleting_photo = (id) => {
        form.post(route('admin.photos.delete', id))
        closeModal();
    }
    // 7. delare a method to close the modal by setting the show_modal to false
    const closeModal = () => {
        data.value.show_modal = false;
    }
    // 8. remember to return from the setup function the all variables and methods that you want to expose 
    // to the template.
    return { form, data, closeModal, delete_photo, deleting_photo }

}

Finally outside the v-for loop add the modal using the following code. You can place this where you want but not inside the loop.

 <JetDialogModal :show="data.show_modal">
    <template #title>
        Photo {{ data.photo.description.slice(0, 20) + '...' }}
    </template>
    <template #content>
        Are you sure you want to delete this photo?

    </template>
    <template #footer>
        <button @click="closeModal" class="px-4 py-2">Close</button>
        <form @submit.prevent="deleting_photo(data.photo.id)">
            <jet-danger-button type="submit">Yes, I am sure!</jet-danger-button>
        </form>
    </template>
</JetDialogModal>

This is our final JavaScript code:

import { defineComponent } from "vue";
import AppLayout from "@/Layouts/AppLayout.vue";
import TableComponent from "@/Components/TableComponent.vue";
import { Link } from '@inertiajs/inertia-vue3';
import { useForm } from '@inertiajs/inertia-vue3';
import JetDialogModal from '@/Jetstream/DialogModal.vue';
import JetDangerButton from '@/Jetstream/DangerButton.vue'
import { ref } from "vue";
export default defineComponent({
    components: {
        AppLayout,
        Link,
        TableComponent,
        JetDialogModal,
        JetDangerButton
    },
    props: {
        photos: Array,
    },

    setup() {

        const form = useForm({
            _method: "DELETE",
        });
        const data = ref({
            show_modal: false,
            photo: {
                id: null,
                path: null,
                description: null,
            }

        })


        const delete_photo = (photo) => {
            //console.log(photo);
            console.log(photo.id, photo.path, photo.description);
            data.value = {
                photo: {
                    id: photo.id,
                    path: photo.path,
                    description: photo.description
                },
                show_modal: true
            };
        }
        const deleting_photo = (id) => {
            form.post(route('admin.photos.delete', id))
            closeModal();
        }

        const closeModal = () => {
            data.value.show_modal = false;


        }

        return { form, data, closeModal, delete_photo, deleting_photo }

    }
});
</script>

And here we have the HTML:

<template>
    <app-layout title="Dashboard">
        <template #header>
            <h2 class="font-semibold text-xl text-gray-800 leading-tight">Photos</h2>
        </template>

         <div class="py-12">
            <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
              <!-- All posts goes here -->
              <h1 class="text-2xl">Photos</h1>
              <a class="px-4 bg-sky-900 text-white rounded-md" href>Create</a>
              <div class="flex flex-col">
                  <div class="-my-2 overflow-x-auto sm:-mx-6 lg:-mx-8">
                      <div class="py-2 align-middle inline-block min-w-full sm:px-6 lg:px-8">
                          <div class="shadow overflow-hidden border-b border-gray-200 sm:rounded-lg">
                              <table class="min-w-full divide-y divide-gray-200">
                                  <thead class="bg-gray-50">
                                      <tr>
                                          <th
                                              scope="col"
                                              class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider"
                                          >ID</th>
                                          <th
                                              scope="col"
                                              class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider"
                                          >Photos</th>
                                          <th
                                              scope="col"
                                              class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider"
                                          >Description</th>
                                          <th scope="col" class="relative px-6 py-3">
                                              <span class="sr-only">Edit</span>
                                          </th>
                                      </tr>
                                  </thead>
                                  <tbody class="bg-white divide-y divide-gray-200">
                                      <tr v-for="photo in photos" :key="photo.id">
                                          <td class="px-6 py-4 whitespace-nowrap">
                                              <div
                                                  class="text-sm text-gray-900"
                                              >{{ photo.id }}</div>
                                          </td>

                                          <td class="px-6 py-4 whitespace-nowrap">
                                              <div class="flex items-center">
                                                  <div class="flex-shrink-0 h-10 w-10">
                                                      <img
                                                          class="h-10 w-10 rounded-full"
                                                          :src="photo.path"
                                                          alt
                                                      />
                                                  </div>
                                              </div>
                                          </td>

                                          <td class="px-6 py-4 whitespace-nowrap">
                                              <div class="text-sm text-gray-900">
                                                {{ photo.description.slice(0, 100) + '...' }}
                                              </div>
                                          </td>
                                        <!-- ACTIONS -->
                                         <td class="px-6 py-4 whitespace-nowrap text-right text-sm font-medium">
                                            <a href="#" class="text-indigo-600 hover:text-indigo-900">
                                            View - Edit - 

                                            <jet-danger-button @click="delete_photo(photo)">
                                                Delete
                                            </jet-danger-button>
                                            </a>
                                        </td>
                                      </tr>
                                  </tbody>
                              </table>
                          </div>
                      </div>
                  </div>
                </div>
            </div>
        </div>
         <JetDialogModal :show="data.show_modal">
            <template #title>
                Photo {{ data.photo.description.slice(0, 20) + '...' }}
            </template>
            <template #content>
                Are you sure you want to delete this photo?

            </template>
            <template #footer>
                <button @click="closeModal" class="px-4 py-2">Close</button>
                <form @submit.prevent="deleting_photo(data.photo.id)">
                    <jet-danger-button type="submit">Yes, I am sure!</jet-danger-button>
                </form>
            </template>
        </JetDialogModal>
    </app-layout>
</template>

That's it. If you did everything correctly you should be able to see all photos, create new photos as well as edit and delete them.

I will leave you some home work. Can you figure out how to implement the view and edit links before the delete button in the section below?

<!-- ACTIONS -->
<td class="px-6 py-4 whitespace-nowrap text-right text-sm font-medium">
    <a href="#" class="text-indigo-600 hover:text-indigo-900">
    View - Edit - 

    <jet-danger-button @click="delete_photo(photo)">
        Delete
    </jet-danger-button>
    </a>
</td>

Wrapup and What's next

During this guide we took our first steps and learned how to build a single page application using Laravel as our backend framework and Vue3 for the front end. We glued them together with Inertia js and built a simple photo application that lets a user manage photos.

We are just at the beginning of a fantastic journey. Learning new technologies isn't easy, but thanks to their exaustive documentations we can keep up and build awesome applications.

Your next step to master Laravel, Vue3, Inertia and all the tech we have been using so far is to hit their documentation and keep learning. Use the app we have build if you want, and improve it or start over from scratch.

Just keep that in mind, coding is fun so relax and enjoy it.

Conclusion

This is just an overview of how I'd build a single page application using these technologies.

If you are familiar with server-side routing and Vuejs then you will enjoy bulding a single page application with Laravel, Inertia, and Vuejs. The learning curve isn't that steep plus you have great documentation to help you out.

I hope you've enjoyed this guide. If so, let me know and consider subscribing to my YouTube channel and following me on Twitter. And if you get stuck, get in touch for help.

You can find the source code for this guide here.

Follow me on Twitter Join me on slack

When you're working on a project that consists of a lot of microservices, it'll likely also include multiple databases.

For example, you might have a MySQL database and a PostgreSQL database, both running on separate servers.

Usually, to join the data from the two databases, you would have to introduce a new microservice that would join the data together. But this would increase the complexity of the system.

In this tutorial, we will be using Materialize to join MySQL and Postgres in a live materialized view. We'll then be able to query that directly and get results back from both databases in real-time using standard SQL.

Materialize is a source-available streaming database written in Rust that maintains the results of a SQL query (a materialized view) in memory as the data changes.

The tutorial includes a demo project which you can start using docker-compose.

The demo project that we are going to use will monitor the orders on our mock website. It'll generate events that could, later on, be used to send notifications when a cart has been abandoned for a long time.

The architecture of the demo project is as follows:

Prerequisites

All of the services that we will be using in the demo will run inside Docker containers, that way you will not have to install any additional services on your laptop or server rather than Docker and Docker Compose.

In case that you don't have Docker and Docker Compose already installed, you can follow the official instructions on how to do that here:

• Install Docker
• Install Docker Compose

Overview

As shown in the diagram above, we will have the following components:

• A mock service to continually generate orders.
• The orders will be stored in a MySQL database.
• As the database writes occur, Debezium streams the changes out of MySQL to a Redpanda topic.
• We'll also have a Postgres database where we can get our users.
• We'll then ingest this Redpanda topic into Materialize directly along with the users from the Postgres database.
• In Materialize we'll join our orders and users together, do some filtering, and create a materialized view that shows the abandoned cart information.
• We will then create a sink to send the abandoned cart data out to a new Redpanda topic.
• At the end we will use Metabase to visualize the data.
• You could, later on, use the information from that new topic to send out notifications to your users and remind them that they have an abandoned cart.

As a side note here, you would be perfectly fine using Kafka instead of Redpanda. I just like the simplicity that Redpanda brings to the table, as you can run a single Redpanda instance instead of all of the Kafka components.

How to Run the Demo

First, start by cloning the repository:

git clone https://github.com/bobbyiliev/materialize-tutorials.git

After that you can access the directory:

cd materialize-tutorials/mz-join-mysql-and-postgresql

Let's start by first running the Redpanda container:

docker-compose up -d redpanda

Build the images:

docker-compose build

Finally, start all of the services:

docker-compose up -d

In order to Launch the Materialize CLI, you can run the following command:

docker-compose run mzcli

This is just a shortcut to a Docker container with postgres-client pre-installed. If you already have psql you could run psql -U materialize -h localhost -p 6875 materialize instead.

How to Create a Materialize Kafka Source

Now that you're in the Materialize CLI, let's define the orders tables in the mysql.shop database as Redpanda sources:

CREATE SOURCE orders
FROM KAFKA BROKER 'redpanda:9092' TOPIC 'mysql.shop.orders'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081'
ENVELOPE DEBEZIUM;

If you were to check the available columns from the orders source by running the following statement:

SHOW COLUMNS FROM orders;

You would be able to see that, as Materialize is pulling the message schema data from the Redpanda registry, it knows the column types to use for each attribute:

    name      | nullable |   type
--------------+----------+-----------
 id           | f        | bigint
 user_id      | t        | bigint
 order_status | t        | integer
 price        | t        | numeric
 created_at   | f        | text
 updated_at   | t        | timestamp

How to Create Materialized Views

Next, we will create our first Materialized View, to get all of the data from the orders Redpanda source:

CREATE MATERIALIZED VIEW orders_view AS
SELECT * FROM orders;

CREATE MATERIALIZED VIEW abandoned_orders AS
    SELECT
        user_id,
        order_status,
        SUM(price) as revenue,
        COUNT(id) AS total
    FROM orders_view
    WHERE order_status=0
    GROUP BY 1,2;

You can now use SELECT * FROM abandoned_orders; to see the results:

SELECT * FROM abandoned_orders;

For more information on creating materialized views, check out the Materialized Views section of the Materialize documentation.

How to Create a Postgres Source

There are two ways to create a Postgres source in Materialize:

• Using Debezium just like we did with the MySQL source.
• Using the Postgres Materialize Source, which allows you to connect Materialize direct to Postgres so you don't have to use Debezium.

For this demo, we will use the Postgres Materialize Source just as a demonstration on how to use it, but feel free to use Debezium instead.

To create a Postgres Materialize Source run the following statement:

CREATE MATERIALIZED SOURCE "mz_source" FROM POSTGRES
CONNECTION 'user=postgres port=5432 host=postgres dbname=postgres password=postgres'
PUBLICATION 'mz_source';

A quick rundown of the above statement:

• MATERIALIZED: Materializes the PostgreSQL source’s data. All of the data is retained in memory and makes sources directly selectable.
• mz_source: The name for the PostgreSQL source.
• CONNECTION: The PostgreSQL connection parameters.
• PUBLICATION: The PostgreSQL publication, containing the tables to be streamed to Materialize.

Once we've created the PostgreSQL source, in order to be able to query the PostgreSQL tables, we would need to create views that represent the upstream publication’s original tables.

In our case, we only have one table called users so the statement that we would need to run is:

CREATE VIEWS FROM SOURCE mz_source (users);

To see the available views execute the following statement:

SHOW FULL VIEWS;

Once that is done, you can query the new views directly:

SELECT * FROM users;

Next, let's go ahead and create a few more views.

How to Create a Kafka Sink

Sinks let you send data from Materialize to an external source.

For this Demo, we will be using Redpanda.

Redpanda is Kafka API-compatible and Materialize can process data from it just as it would process data from a Kafka source.

Let's create a materialized view, that will hold all of the high volume unpaid orders:

 CREATE MATERIALIZED VIEW high_value_orders AS
      SELECT
        users.id,
        users.email,
        abandoned_orders.revenue,
        abandoned_orders.total
      FROM users
      JOIN abandoned_orders ON abandoned_orders.user_id = users.id
      GROUP BY 1,2,3,4
      HAVING revenue > 2000;

As you can see, here we are actually joining the users view which is ingesting the data directly from our Postgres source, and the abandond_orders view which is ingesting the data from the Redpanda topic, together.

Let's create a Sink where we will send the data of the above materialized view:

CREATE SINK high_value_orders_sink
    FROM high_value_orders
    INTO KAFKA BROKER 'redpanda:9092' TOPIC 'high-value-orders-sink'
    FORMAT AVRO USING
    CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

Now if you were to connect to the Redpanda container and use the rpk topic consume command, you will be able to read the records from the topic.

However, as of the time being, we won’t be able to preview the results with rpk because it’s AVRO formatted. Redpanda would most likely implement this in the future, but for the moment, we can actually stream the topic back into Materialize to confirm the format.

First, get the name of the topic that has been automatically generated:

SELECT topic FROM mz_kafka_sinks;

Output:

                              topic
-----------------------------------------------------------------
 high-volume-orders-sink-u12-1637586945-13670686352905873426

For more information on how the topic names are generated check out the documentation here.

Then create a new Materialized Source from this Redpanda topic:

CREATE MATERIALIZED SOURCE high_volume_orders_test
FROM KAFKA BROKER 'redpanda:9092' TOPIC ' high-volume-orders-sink-u12-1637586945-13670686352905873426'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

Make sure to change the topic name accordingly!

Finally, query this new materialized view:

SELECT * FROM high_volume_orders_test LIMIT 2;

Now that you have the data in the topic, you can have other services connect to it and consume it and then trigger emails or alerts for example.

How to Connect Metabase

In order to access the Metabase instance visit http://localhost:3030 if you are running the demo locally or http://your_server_ip:3030 if you are running the demo on a server. Then follow the steps to complete the Metabase setup.

Make sure to select Materialize as the source of the data.

Once ready you will be able to visualize your data just as you would with a standard PostgreSQL database.

How to Stop the Demo

To stop all of the services, run the following command:

docker-compose down

Conclusion

As you can see, this is a very simple example of how to use Materialize. You can use Materialize to ingest data from a variety of sources and then stream it to a variety of destinations.

Helpful resources:

• CREATE SOURCE: PostgreSQL
• CREATE SOURCE
• CREATE VIEWS
• SELECT

I have already written about SQL joins here and here, but let's take a moment to review how a join works first, and particularly the syntax specific to MySQL.

SQL Join Statement

Join is a statement that lets you put together two tables, matching rows that are related to each other, and keeping only the rows that can be matched, not keeping unpaired rows.

SELECT * FROM table1 
  INNER JOIN table2
  ON table1.id = table2.id;

The SELECT ... FROM statement indicates which is the first table, then the second table name is written just after the INNER JOIN keywords.

How the two tables should be joined is written in the ON statement. In this case the two tables are joined using the relationship table1.id = table2.id.

It is possible to use multiple join statements together to join more than one table at the same time.

SELECT *
  FROM table1
  INNER JOIN table2
  ON table1.id = table2.id
  INNER JOIN table3
  ON table2.id = table3.id;

To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.

Let's talk a moment about the relationships you can have between tables and why you might want to join three tables together.

Relationships Between Tables in SQL

When you have tables that are related to each other, their relationships could be one of various types.

one-to-many

In a one-to-many kind of relationship, one row of the first table can be related to multiple rows of the second table.

In a relational database this can be implemented with the second table having a first_table_id column that says to which row of the first table that row is related.

many-to-one

In a many-to-one kind of relationship, one row of the first table can be related to one single row of the second table, and one row of the second table can be related to multiple rows of the first table.

In a relational database this can be implemented with the first table having a second_table_id column that says to which row of the second table that row is related.

Many-to-one

many-to-many

In this case multiple rows are related to multiple rows.

Many-to-many

This kind of relationship can't be represent as is with SQL tables – you need to add a coupling table between the two tables so that only many-to-one and one-to-many relationships are present between tables.

Each row of the table in the middle represents one relationship between the rows of the left table and and the rows of the right table.

In practice in MySQL, that middle table will have a column for first_table_id and a column for second_table_id, with each combination being unique.

Joining SQL Tables in Practice

Let's imagine we have an organization's database, where we have a table with teams (their name, and other identifing info), and a table with projects (name, progress, and so on).

As a team can work on multiple projects, and a project can be worked on by multiple teams, there is also a third table that keeps track of team-project matches.

We can use a JOIN statement to put everything together when we need to view the info from the tables in a human readable way, like this:

SELECT
  teams.team_name AS team_name,
  projects.project_name AS project_name
FROM TABLE teams
INNER JOIN matches
  ON teams.id = matches.team_id
INNER JOIN matches
  ON matches.project_id = projects.id
ORDER BY teams.id;

We choose which columns to show from each table with a SELECT statement.

We specify how the rows of the tables are to be combined with an ON statement.

And we order the rows in the way we prefer with an ORDER BY statement.

The ON statements teams.id = matches.team_id and matches.projects_id = projects.id mean that the rows are combined using the rows of the matches table. Each row of the output table has the project name and the team name combined using the pairs of project id and team id in the matches table.

The output table will look like below.

There is no column directly from the matches table. The matches table is not shown in the output but it is used as instructions for how to combine the rows of the teams and projects tables.

Conclusion

The JOIN statement lets you join together one or more tables. It has to be used in conjunction with the ON statement to determine the relationship between the rows of a table and the rows of a different table.

In this article you have learned how to use the JOIN statement to join together three different tables.