An Introduction to Queries in MySQL

An Introduction to Queries in MySQL

In this guide, we will discuss the basic syntax of SQL queries as well as some of the more commonly-employed functions and operators. We will also practice making SQL queries using some sample data in a MySQL database.


Introduction

Databases are a key component of many websites and applications, and are at the core of how data is stored and exchanged across the internet. One of the most important aspects of database management is the practice of retrieving data from a database, whether it’s on an ad hoc basis or part of a process that’s been coded into an application. There are several ways to retrieve information from a database, but one of the most commonly-used methods is performed through submitting queries through the command line.

In relational database management systems, a query is any command used to retrieve data from a table. In Structured Query Language (SQL), queries are almost always made using the SELECT statement.

In this guide, we will discuss the basic syntax of SQL queries as well as some of the more commonly-employed functions and operators. We will also practice making SQL queries using some sample data in a MySQL database.

MySQL is an open-source relational database management system. One of the most widely-deployed SQL-databases, MySQL prioritizes speed, reliability, and usability. It generally follows the ANSI SQL standard, although there are a few cases where MySQL performs operations differently than the recognized standard.


Prerequisites

In general, the commands and concepts presented in this guide can be used on any Linux-based operating system running any SQL database software. However, it was written specifically with an Ubuntu 18.04 server running MySQL in mind. To set this up, you will need the following:

With this setup in place, we can begin the tutorial.


Creating a Sample Database

Before we can begin making queries in SQL, we will first create a database and a couple tables, then populate these tables with some sample data. This will allow you to gain some hands-on experience when you begin making queries later on.

For the sample database we’ll use throughout this guide, imagine the following scenario:

You and several of your friends all celebrate your birthdays with one another. On each occasion, the members of the group head to the local bowling alley, participate in a friendly tournament, and then everyone heads to your place where you prepare the birthday-person’s favorite meal.

Now that this tradition has been going on for a while, you’ve decided to begin tracking the records from these tournaments. Also, to make planning dinners easier, you decide to create a record of your friends’ birthdays and their favorite entrees, sides, and desserts. Rather than keep this information in a physical ledger, you decide to exercise your database skills by recording it in a MySQL database.

To begin, open up a MySQL prompt as your root MySQL user:

sudo mysql

Note: If you followed the prerequisite the tutorial on Installing MySQL on Ubuntu 18.04, you may have configured your root user to authenticate using a password. In this case, you will connect to the MySQL prompt with the following command:

mysql -u root -p

Next, create the database by running:

CREATE DATABASE birthdays;

Then select this database by typing:

USE birthdays;

Next, create two tables within this database. We’ll use the first table to track your friends’ records at the bowling alley. The following command will create a table called tourneys with columns for the name of each of your friends, the number of tournaments they’ve won (wins), their all-time best score, and what size bowling shoe they wear (size):

CREATE TABLE tourneys (
name varchar(30),
wins real,
best real,
size real
);

Once you run the CREATE TABLE command and populate it with column headings, you’ll receive the following output:

OutputQuery OK, 0 rows affected (0.00 sec)

Populate the tourneys table with some sample data:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Dolly', '7', '245', '8.5'),
('Etta', '4', '283', '9'),
('Irma', '9', '266', '7'),
('Barbara', '2', '197', '7.5'),
('Gladys', '13', '273', '8');

You’ll receive an output like this:

OutputQuery OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

Following this, create another table within the same database which we’ll use to store information about your friends’ favorite birthday meals. The following command creates a table named dinners with columns for the name of each of your friends, their birthdate, their favorite entree, their preferred side dish, and their favorite dessert:

CREATE TABLE dinners (
name varchar(30),
birthdate date,
entree varchar(30),
side varchar(30),
dessert varchar(30)
);

Similarly for this table, you’ll receive feedback confirming that the command ran successfully:

OutputQuery OK, 0 rows affected (0.01 sec)

Populate this table with some sample data as well:

INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'),
('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'),
('Irma', '1941-02-18', 'tofu', 'fries', 'cake'),
('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'),
('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

OutputQuery OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

Once that command completes successfully, you’re done setting up your database. Next, we’ll go over the basic command structure of SELECT queries.


Understanding SELECT Statements

As mentioned in the introduction, SQL queries almost always begin with the SELECT statement. SELECT is used in queries to specify which columns from a table should be returned in the result set. Queries also almost always include FROM, which is used to specify which table the statement will query.

Generally, SQL queries follow this syntax:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

By way of example, the following statement will return the entire name column from the dinners table:

SELECT name FROM dinners;

Output+---------+
| name |
+---------+
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
+---------+
5 rows in set (0.00 sec)

You can select multiple columns from the same table by separating their names with a comma, like this:

SELECT name, birthdate FROM dinners;

Output+---------+------------+
| name | birthdate |
+---------+------------+
| Dolly | 1946-01-19 |
| Etta | 1938-01-25 |
| Irma | 1941-02-18 |
| Barbara | 1948-12-25 |
| Gladys | 1944-05-28 |
+---------+------------+
5 rows in set (0.00 sec)

Instead of naming a specific column or set of columns, you can follow the SELECT operator with an asterisk (*) which serves as a placeholder representing all the columns in a table. The following command returns every column from the tourneys table:

SELECT * FROM tourneys;

Output+---------+------+------+------+
| name | wins | best | size |
+---------+------+------+------+
| Dolly | 7 | 245 | 8.5 |
| Etta | 4 | 283 | 9 |
| Irma | 9 | 266 | 7 |
| Barbara | 2 | 197 | 7.5 |
| Gladys | 13 | 273 | 8 |
+---------+------+------+------+
5 rows in set (0.00 sec)

WHERE is used in queries to filter records that meet a specified condition, and any rows that do not meet that condition are eliminated from the result. A WHERE clause typically follows this syntax:

. . . WHERE column_name comparison_operator value

The comparison operator in a WHERE clause defines how the specified column should be compared against the value. Here are some common SQL comparison operators:

Operator What it does = tests for equality != tests for inequality < tests for less-than > tests for greater-than <= tests for less-than or equal-to >= tests for greater-than or equal-to BETWEEN tests whether a value lies within a given range IN tests whether a row’s value is contained in a set of specified values EXISTS tests whether rows exist, given the specified conditions LIKE tests whether a value matches a specified string IS NULL tests for NULL values IS NOT NULL tests for all values other than NULL For example, if you wanted to find Irma’s shoe size, you could use the following query:

SELECT size FROM tourneys WHERE name = 'Irma';

Output+------+
| size |
+------+
| 7 |
+------+
1 row in set (0.00 sec)

SQL allows the use of wildcard characters, and these are especially handy when used in WHERE clauses. Percentage signs (%) represent zero or more unknown characters, and underscores (_) represent a single unknown character. These are useful if you’re trying to find a specific entry in a table, but aren’t sure of what that entry is exactly. To illustrate, let’s say that you’ve forgotten the favorite entree of a few of your friends, but you’re certain this particular entree starts with a “t.” You could find its name by running the following query:

SELECT entree FROM dinners WHERE entree LIKE 't%';

Output+--------+
| entree |
+--------+
| tofu |
| tofu |
+--------+
2 rows in set (0.00 sec)

Based on the output above, we see that the entree we have forgotten is tofu.

There may be times when you’re working with databases that have columns or tables with relatively long or difficult-to-read names. In these cases, you can make these names more readable by creating an alias with the AS keyword. Aliases created with AS are temporary, and only exist for the duration of the query for which they’re created:

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;

Output+---------+------------+-----------+
| n | b | d |
+---------+------------+-----------+
| Dolly | 1946-01-19 | cake |
| Etta | 1938-01-25 | ice cream |
| Irma | 1941-02-18 | cake |
| Barbara | 1948-12-25 | ice cream |
| Gladys | 1944-05-28 | ice cream |
+---------+------------+-----------+
5 rows in set (0.00 sec)

Here, we have told SQL to display the name column as n, the birthdate column as b, and the dessert column as d.

The examples we’ve gone through up to this point include some of the more frequently-used keywords and clauses in SQL queries. These are useful for basic queries, but they aren’t helpful if you’re trying to perform a calculation or derive a scalar value (a single value, as opposed to a set of multiple different values) based on your data. This is where aggregate functions come into play.


Aggregate Functions

Oftentimes, when working with data, you don’t necessarily want to see the data itself. Rather, you want information about the data. The SQL syntax includes a number of functions that allow you to interpret or run calculations on your data just by issuing a SELECT query. These are known as aggregate functions.

The COUNT function counts and returns the number of rows that match a certain criteria. For example, if you’d like to know how many of your friends prefer tofu for their birthday entree, you could issue this query:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu';

Output+---------------+
| COUNT(entree) |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)

The AVG function returns the average (mean) value of a column. Using our example table, you could find the average best score amongst your friends with this query:

SELECT AVG(best) FROM tourneys;

Output+-----------+
| AVG(best) |
+-----------+
| 252.8 |
+-----------+
1 row in set (0.00 sec)

SUM is used to find the total sum of a given column. For instance, if you’d like to see how many games you and your friends have bowled over the years, you could run this query:

SELECT SUM(wins) FROM tourneys;

Output+-----------+
| SUM(wins) |
+-----------+
| 35 |
+-----------+
1 row in set (0.00 sec)

Note that the AVG and SUM functions will only work correctly when used with numeric data. If you try to use them on non-numerical data, it will result in either an error or just 0, depending on which RDBMS you’re using:

SELECT SUM(entree) FROM dinners;

Output+-------------+
| SUM(entree) |
+-------------+
| 0 |
+-------------+
1 row in set, 5 warnings (0.00 sec)

MIN is used to find the smallest value within a specified column. You could use this query to see what the worst overall bowling record is so far (in terms of number of wins):

SELECT MIN(wins) FROM tourneys;

Output+-----------+
| MIN(wins) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)

Similarly, MAX is used to find the largest numeric value in a given column. The following query will show the best overall bowling record:

SELECT MAX(wins) FROM tourneys;

Output+-----------+
| MAX(wins) |
+-----------+
| 13 |
+-----------+
1 row in set (0.00 sec)

Unlike SUM and AVG, the MIN and MAX functions can be used for both numeric and alphabetic data types. When run on a column containing string values, the MIN function will show the first value alphabetically:

SELECT MIN(name) FROM dinners;

Output+-----------+
| MIN(name) |
+-----------+
| Barbara |
+-----------+
1 row in set (0.00 sec)

Likewise, when run on a column containing string values, the MAX function will show the last value alphabetically:

SELECT MAX(name) FROM dinners;

Output+-----------+
| MAX(name) |
+-----------+
| Irma |
+-----------+
1 row in set (0.00 sec)

Aggregate functions have many uses beyond what was described in this section. They’re particularly useful when used with the GROUP BY clause, which is covered in the next section along with several other query clauses that affect how result sets are sorted.


Manipulating Query Outputs

In addition to the FROM and WHERE clauses, there are several other clauses which are used to manipulate the results of a SELECT query. In this section, we will explain and provide examples for some of the more commonly-used query clauses.

One of the most frequently-used query clauses, aside from FROM and WHERE, is the GROUP BY clause. It’s typically used when you’re performing an aggregate function on one column, but in relation to matching values in another.

For example, let’s say you wanted to know how many of your friends prefer each of the three entrees you make. You could find this info with the following query:

SELECT COUNT(name), entree FROM dinners GROUP BY entree;

Output+-------------+---------+
| COUNT(name) | entree |
+-------------+---------+
| 1 | chicken |
| 2 | steak |
| 2 | tofu |
+-------------+---------+
3 rows in set (0.00 sec)

The ORDER BY clause is used to sort query results. By default, numeric values are sorted in ascending order, and text values are sorted in alphabetical order. To illustrate, the following query lists the name and birthdate columns, but sorts the results by birthdate:

SELECT name, birthdate FROM dinners ORDER BY birthdate;

Output+---------+------------+
| name | birthdate |
+---------+------------+
| Etta | 1938-01-25 |
| Irma | 1941-02-18 |
| Gladys | 1944-05-28 |
| Dolly | 1946-01-19 |
| Barbara | 1948-12-25 |
+---------+------------+
5 rows in set (0.00 sec)

Notice that the default behavior of ORDER BY is to sort the result set in ascending order. To reverse this and have the result set sorted in descending order, close the query with DESC:

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;

Output+---------+------------+
| name | birthdate |
+---------+------------+
| Barbara | 1948-12-25 |
| Dolly | 1946-01-19 |
| Gladys | 1944-05-28 |
| Irma | 1941-02-18 |
| Etta | 1938-01-25 |
+---------+------------+
5 rows in set (0.00 sec)

As mentioned previously, the WHERE clause is used to filter results based on specific conditions. However, if you use the WHERE clause with an aggregate function, it will return an error, as is the case with the following attempt to find which sides are the favorite of at least three of your friends:

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;

OutputERROR 1111 (HY000): Invalid use of group function

The HAVING clause was added to SQL to provide functionality similar to that of the WHERE clause while also being compatible with aggregate functions. It’s helpful to think of the difference between these two clauses as being that WHERE applies to individual records, while HAVING applies to group records. To this end, any time you issue a HAVING clause, the GROUP BY clause must also be present.

The following example is another attempt to find which side dishes are the favorite of at least three of your friends, although this one will return a result without error:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;

Output+-------------+-------+
| COUNT(name) | side |
+-------------+-------+
| 3 | fries |
+-------------+-------+
1 row in set (0.00 sec)

Aggregate functions are useful for summarizing the results of a particular column in a given table. However, there are many cases where it’s necessary to query the contents of more than one table. We’ll go over a few ways you can do this in the next section.


Querying Multiple Tables

More often than not, a database contains multiple tables, each holding different sets of data. SQL provides a few different ways to run a single query on multiple tables.

The JOIN clause can be used to combine rows from two or more tables in a query result. It does this by finding a related column between the tables and sorts the results appropriately in the output.

SELECT statements that include a JOIN clause generally follow this syntax:

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.related_column=table2.related_column;

Note that because JOIN clauses compare the contents of more than one table, the previous example specifies which table to select each column from by preceding the name of the column with the name of the table and a period. You can specify which table a column should be selected from like this for any query, although it’s not necessary when selecting from a single table, as we’ve done in the previous sections. Let’s walk through an example using our sample data.

Imagine that you wanted to buy each of your friends a pair of bowling shoes as a birthday gift. Because the information about your friends’ birthdates and shoe sizes are held in separate tables, you could query both tables separately then compare the results from each. With a JOIN clause, though, you can find all the information you want with a single query:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
JOIN dinners ON tourneys.name=dinners.name;

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

The JOIN clause used in this example, without any other arguments, is an inner JOIN clause. This means that it selects all the records that have matching values in both tables and prints them to the results set, while any records that aren’t matched are excluded. To illustrate this idea, let’s add a new row to each table that doesn’t have a corresponding entry in the other:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Bettye', '0', '193', '9');

INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

Then, re-run the previous SELECT statement with the JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
JOIN dinners ON tourneys.name=dinners.name;

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

Notice that, because the tourneys table has no entry for Lesley and the dinners table has no entry for Bettye, those records are absent from this output.

It is possible, though, to return all the records from one of the tables using an outer JOIN clause. In MySQL, JOIN clauses are written as either LEFT JOIN or RIGHT JOIN.

A LEFT JOIN clause returns all the records from the “left” table and only the matching records from the right table. In the context of outer joins, the left table is the one referenced by the FROM clause, and the right table is any other table referenced after the JOIN statement.

Run the previous query again, but this time use a LEFT JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
LEFT JOIN dinners ON tourneys.name=dinners.name;

This command will return every record from the left table (in this case, tourneys) even if it doesn’t have a corresponding record in the right table. Any time there isn’t a matching record from the right table, it’s returned as NULL or just a blank value, depending on your RDBMS:

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
| Bettye | 9 | NULL |
+---------+------+------------+
6 rows in set (0.00 sec)

Now run the query again, this time with a RIGHT JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
RIGHT JOIN dinners ON tourneys.name=dinners.name;

This will return all the records from the right table (dinners). Because Lesley’s birthdate is recorded in the right table, but there is no corresponding row for her in the left table, the name and size columns will return as NULL values in that row:

Output+---------+------+------------+
| name | size | birthdate |
+---------+------+------------+
| Dolly | 8.5 | 1946-01-19 |
| Etta | 9 | 1938-01-25 |
| Irma | 7 | 1941-02-18 |
| Barbara | 7.5 | 1948-12-25 |
| Gladys | 8 | 1944-05-28 |
| NULL | NULL | 1946-05-02 |
+---------+------+------------+
6 rows in set (0.00 sec)

Note that left and right joins can be written as LEFT OUTER JOIN or RIGHT OUTER JOIN, although the OUTER part of the clause is implied. Likewise, specifying INNER JOIN will produce the same result as just writing JOIN.

As an alternative to using JOIN to query records from multiple tables, you can use the UNION clause.

The UNION operator works slightly differently than a JOIN clause: instead of printing results from multiple tables as unique columns using a single SELECT statement, UNION combines the results of two SELECT statements into a single column.

To illustrate, run the following query:

SELECT name FROM tourneys UNION SELECT name FROM dinners;

This query will remove any duplicate entries, which is the default behavior of the UNION operator:

Output+---------+
| name |
+---------+
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
| Bettye |
| Lesley |
+---------+
7 rows in set (0.00 sec)

To return all entries (including duplicates) use the UNION ALL operator:

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;

Output+---------+
| name |
+---------+
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
| Bettye |
| Dolly |
| Etta |
| Irma |
| Barbara |
| Gladys |
| Lesley |
+---------+
12 rows in set (0.00 sec)

The names and number of the columns in the results table reflect the name and number of columns queried by the first SELECT statement. Note that when using UNION to query multiple columns from more than one table, each SELECT statement must query the same number of columns, the respective columns must have similar data types, and the columns in each SELECT statement must be in the same order. The following example shows what might result if you use a UNION clause on two SELECT statements that query a different number of columns:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;

OutputERROR 1222 (21000): The used SELECT statements have a different number of columns

Another way to query multiple tables is through the use of subqueries. Subqueries (also known as inner or nested queries) are queries enclosed within another query. These are useful in cases where you’re trying to filter the results of a query against the result of a separate aggregate function.

To illustrate this idea, say you want to know which of your friends have won more matches than Barbara. Rather than querying how many matches Barbara has won then running another query to see who has won more games than that, you can calculate both with a single query:

SELECT name, wins FROM tourneys
WHERE wins > (
SELECT wins FROM tourneys WHERE name = 'Barbara'
);

Output+--------+------+
| name | wins |
+--------+------+
| Dolly | 7 |
| Etta | 4 |
| Irma | 9 |
| Gladys | 13 |
+--------+------+
4 rows in set (0.00 sec)

The subquery in this statement was run only once; it only needed to find the value from the wins column in the same row as Barbara in the name column, and the data returned by the subquery and outer query are independent of one another. There are cases, though, where the outer query must first read every row in a table and compare those values against the data returned by the subquery in order to return the desired data. In this case, the subquery is referred to as a correlated subquery.

The following statement is an example of a correlated subquery. This query seeks to find which of your friends have won more games than is the average for those with the same shoe size:

SELECT name, size FROM tourneys AS t
WHERE wins > (
SELECT AVG(wins) FROM tourneys WHERE size = t.size
);

In order for the query to complete, it must first collect the name and size columns from the outer query. Then, it compares each row from that result set against the results of the inner query, which determines the average number of wins for individuals with identical shoe sizes. Because you only have two friends that have the same shoe size, there can only be one row in the result set:

Output+------+------+
| name | size |
+------+------+
| Etta | 9 |
+------+------+
1 row in set (0.00 sec)

As mentioned earlier, subqueries can be used to query results from multiple tables. To illustrate this with one final example, say you wanted to throw a surprise dinner for the group’s all-time best bowler. You could find which of your friends has the best bowling record and return their favorite meal with the following query:

SELECT name, entree, side, dessert
FROM dinners
WHERE name = (SELECT name FROM tourneys
WHERE wins = (SELECT MAX(wins) FROM tourneys));

Output+--------+--------+-------+-----------+
| name | entree | side | dessert |
+--------+--------+-------+-----------+
| Gladys | steak | fries | ice cream |
+--------+--------+-------+-----------+
1 row in set (0.00 sec)

Notice that this statement not only includes a subquery, but also contains a subquery within that subquery.


Conclusion

Issuing queries is one of the most commonly-performed tasks within the realm of database management. There are a number of database administration tools, such as phpMyAdmin or pgAdmin, that allow you to perform queries and visualize the results, but issuing SELECT statements from the command line is still a widely-practiced workflow that can also provide you with greater control.


Learn More

MySQL Databases With Python Tutorial

SQL vs NoSQL or MySQL vs MongoDB

Learn NoSQL Databases from Scratch - Complete MongoDB Bootcamp 2019

The Complete SQL Bootcamp

The Complete Oracle SQL Certification Course

SQL for Newbs: Data Analysis for Beginners

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

Originally published by Mark Drake at https://www.digitalocean.com

Laravel 5.8 Ajax CRUD tutorial using Datatable JS

Laravel 5.8 Ajax CRUD tutorial using Datatable JS

In this tutorial, i want to share with you create jquery ajax crud operations application using datatable js, modals in laravel 5.8. we will create insert update delete records with modal and pagination in laravel 5.8.

In this tutorial, i want to share with you create jquery ajax crud operations application using datatable js, modals in laravel 5.8. we will create insert update delete records with modal and pagination in laravel 5.8.

We will use yajra datatable to list a records with pagination, sorting and filter (search). we will use bootstrap modal for create new records and update new records. we will use resource routes to create crud (create read update delete) application in laravel 5.8.

I will provide you step by step guide to create ajax crud example with laravel 5.8. you just need to follow few step to get c.r.u.d with modals and ajax. you can easily use with your laravel 5.8 project and easy to customize it.

You can see bellow preview of ajax crud app.

List Page

Create Page

Edit Page

Step 1 : Install Laravel 5.8

first of all we need to get fresh Laravel 5.8 version application using bellow command, So open your terminal OR command prompt and run bellow command:

composer create-project --prefer-dist laravel/laravel blog

Step 2 : Install Yajra Datatable Package

We need to install yajra datatable composer package for datatable, so you can install using following command:

composer require yajra/laravel-datatables-oracle

After that you need to set providers and alias.

config/app.php

'providers' => [

	....

	Yajra\DataTables\DataTablesServiceProvider::class,

]

'aliases' => [

	....

	'DataTables' => Yajra\DataTables\Facades\DataTables::class,

]

Step 3: Update Database Configuration

In second step, we will make database configuration for example database name, username, password etc for our crud application of laravel 5.8. So let’s open .env file and fill all details like as bellow:

.env

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=here your database name(blog)

DB_USERNAME=here database username(root)

DB_PASSWORD=here database password(root)

Step 4: Create Table

we are going to create ajax crud application for product. so we have to create migration for “products” table using Laravel 5.8 php artisan command, so first fire bellow command:

php artisan make:migration create_products_table --create=products

After this command you will find one file in following path “database/migrations” and you have to put bellow code in your migration file for create products table.

<?php

 

use Illuminate\Support\Facades\Schema;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Database\Migrations\Migration;

  

class CreateProductsTable extends Migration

{

    /**

     * Run the migrations.

     *

     * @return void

     */

    public function up()

    {

        Schema::create('products', function (Blueprint $table) {

            $table->increments('id');

            $table->string('name');

            $table->text('detail');

            $table->timestamps();

        });

    }

  

    /**

     * Reverse the migrations.

     *

     * @return void

     */

    public function down()

    {

        Schema::dropIfExists('products');

    }

}

Now you have to run this migration by following command:

php artisan migrate

Step 5: Create Resource Route

Here, we need to add resource route for product ajax crud application. so open your “routes/web.php” file and add following route.

routes/web.php

Route::resource('ajaxproducts','ProductAjaxController');

Step 6: Create Controller and Model

In this step, now we should create new controller as ProductAjaxController. So run bellow command and create new controller.

So, let’s copy bellow code and put on ProductAjaxController.php file.

app/Http/Controllers/ProductAjaxController.php

<?php

         

namespace App\Http\Controllers;

          

use App\Product;

use Illuminate\Http\Request;

use DataTables;

        

class ProductAjaxController extends Controller

{

    /**

     * Display a listing of the resource.

     *

     * @return \Illuminate\Http\Response

     */

    public function index(Request $request)

    {

   

        if ($request->ajax()) {

            $data = Product::latest()->get();

            return Datatables::of($data)

                    ->addIndexColumn()

                    ->addColumn('action', function($row){

   

                           $btn = '<a href="javascript:void(0)" data-toggle="tooltip"  data-id="'.$row->id.'" data-original-title="Edit" class="edit btn btn-primary btn-sm editProduct">Edit</a>';

   

                           $btn = $btn.' <a href="javascript:void(0)" data-toggle="tooltip"  data-id="'.$row->id.'" data-original-title="Delete" class="btn btn-danger btn-sm deleteProduct">Delete</a>';

    

                            return $btn;

                    })

                    ->rawColumns(['action'])

                    ->make(true);

        }

      

        return view('productAjax',compact('products'));

    }

     

    /**

     * Store a newly created resource in storage.

     *

     * @param  \Illuminate\Http\Request  $request

     * @return \Illuminate\Http\Response

     */

    public function store(Request $request)

    {

        Product::updateOrCreate(['id' => $request->product_id],

                ['name' => $request->name, 'detail' => $request->detail]);        

   

        return response()->json(['success'=>'Product saved successfully.']);

    }

    /**

     * Show the form for editing the specified resource.

     *

     * @param  \App\Product  $product

     * @return \Illuminate\Http\Response

     */

    public function edit($id)

    {

        $product = Product::find($id);

        return response()->json($product);

    }

  

    /**

     * Remove the specified resource from storage.

     *

     * @param  \App\Product  $product

     * @return \Illuminate\Http\Response

     */

    public function destroy($id)

    {

        Product::find($id)->delete();

     

        return response()->json(['success'=>'Product deleted successfully.']);

    }

}

Ok, so after run bellow command you will find “app/Product.php” and put bellow content in Product.php file:

app/Product.php

<?php

  

namespace App;

  

use Illuminate\Database\Eloquent\Model;

   

class Product extends Model

{

    protected $fillable = [

        'name', 'detail'

    ];

}

Step 7: Create Blade Files

In last step. In this step we have to create just blade file. so we need to create only one blade file as productAjax.blade.php file.

So let’s just create following file and put bellow code.

resources/views/productAjax.blade.php

<!DOCTYPE html>

<html>

<head>

    <title>Laravel 5.8 Ajax CRUD tutorial using Datatable - ItSolutionStuff.com</title>

    <meta name="csrf-token" content="{{ csrf_token() }}">

    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />

    <link href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet">

    <link href="https://cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" rel="stylesheet">

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>  

    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>

    <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>

    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>

    <script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>

</head>

<body>

    

<div class="container">

    <h1>Laravel 5.8 Ajax CRUD tutorial using Datatable - ItSolutionStuff.com</h1>

    <a class="btn btn-success" href="javascript:void(0)" id="createNewProduct"> Create New Product</a>

    <table class="table table-bordered data-table">

        <thead>

            <tr>

                <th>No</th>

                <th>Name</th>

                <th>Details</th>

                <th width="280px">Action</th>

            </tr>

        </thead>

        <tbody>

        </tbody>

    </table>

</div>

   

<div class="modal fade" id="ajaxModel" aria-hidden="true">

    <div class="modal-dialog">

        <div class="modal-content">

            <div class="modal-header">

                <h4 class="modal-title" id="modelHeading"></h4>

            </div>

            <div class="modal-body">

                <form id="productForm" name="productForm" class="form-horizontal">

                   <input type="hidden" name="product_id" id="product_id">

                    <div class="form-group">

                        <label for="name" class="col-sm-2 control-label">Name</label>

                        <div class="col-sm-12">

                            <input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">

                        </div>

                    </div>

     

                    <div class="form-group">

                        <label class="col-sm-2 control-label">Details</label>

                        <div class="col-sm-12">

                            <textarea id="detail" name="detail" required="" placeholder="Enter Details" class="form-control"></textarea>

                        </div>

                    </div>

      

                    <div class="col-sm-offset-2 col-sm-10">

                     <button type="submit" class="btn btn-primary" id="saveBtn" value="create">Save changes

                     </button>

                    </div>

                </form>

            </div>

        </div>

    </div>

</div>

    

</body>

    

<script type="text/javascript">

  $(function () {

     

      $.ajaxSetup({

          headers: {

              'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')

          }

    });

    

    var table = $('.data-table').DataTable({

        processing: true,

        serverSide: true,

        ajax: "{{ route('ajaxproducts.index') }}",

        columns: [

            {data: 'DT_RowIndex', name: 'DT_RowIndex'},

            {data: 'name', name: 'name'},

            {data: 'detail', name: 'detail'},

            {data: 'action', name: 'action', orderable: false, searchable: false},

        ]

    });

     

    $('#createNewProduct').click(function () {

        $('#saveBtn').val("create-product");

        $('#product_id').val('');

        $('#productForm').trigger("reset");

        $('#modelHeading').html("Create New Product");

        $('#ajaxModel').modal('show');

    });

    

    $('body').on('click', '.editProduct', function () {

      var product_id = $(this).data('id');

      $.get("{{ route('ajaxproducts.index') }}" +'/' + product_id +'/edit', function (data) {

          $('#modelHeading').html("Edit Product");

          $('#saveBtn').val("edit-user");

          $('#ajaxModel').modal('show');

          $('#product_id').val(data.id);

          $('#name').val(data.name);

          $('#detail').val(data.detail);

      })

   });

    

    $('#saveBtn').click(function (e) {

        e.preventDefault();

        $(this).html('Sending..');

    

        $.ajax({

          data: $('#productForm').serialize(),

          url: "{{ route('ajaxproducts.store') }}",

          type: "POST",

          dataType: 'json',

          success: function (data) {

     

              $('#productForm').trigger("reset");

              $('#ajaxModel').modal('hide');

              table.draw();

         

          },

          error: function (data) {

              console.log('Error:', data);

              $('#saveBtn').html('Save Changes');

          }

      });

    });

    

    $('body').on('click', '.deleteProduct', function () {

     

        var product_id = $(this).data("id");

        confirm("Are You sure want to delete !");

      

        $.ajax({

            type: "DELETE",

            url: "{{ route('ajaxproducts.store') }}"+'/'+product_id,

            success: function (data) {

                table.draw();

            },

            error: function (data) {

                console.log('Error:', data);

            }

        });

    });

     

  });

</script>

</html>

Now you can test it by using following command:

php artisan serve

Now you can open bellow URL on your browser:

http://localhost:8000/ajaxproducts

I hope it can help you…

Securing RESTful API with Spring Boot, Security, and Data MongoDB

Securing RESTful API with Spring Boot, Security, and Data MongoDB

A comprehensive step by step tutorial on securing or authentication RESTful API with Spring Boot, Security, and Data MongoDB

A comprehensive step by step tutorial on securing or authentication RESTful API with Spring Boot, Security, and Data MongoDB. Previously, we have shown you how to securing Spring Boot, MVC and MongoDB web application. In this tutorial, the secure endpoint will restrict the access from an unauthorized request. Every request to secure endpoint should bring authorization token with it. Of course, there will be an endpoint for login which will get authorization token after successful login.

Table of Contents:

The following software, tools, and frameworks are required for this tutorial:

We assume that you already installed all required software, tools, and frameworks. So, we will not cover how to install that software, tools, and frameworks.

1. Generate a New Spring Boot Gradle Project

To create or generate a new Spring Boot Application or Project, simply go to Spring Initializer. Fill all required fields as below then click on Generate Project button.

The project will automatically be downloaded as a Zip file. Next, extract the zipped project to your java projects folder. On the project folder root, you will find build.gradle file for register dependencies, initially it looks like this.

buildscript {
&nbsp;&nbsp; &nbsp;ext {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;springBootVersion = '2.1.2.RELEASE'
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;repositories {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;mavenCentral()
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;dependencies {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
&nbsp;&nbsp; &nbsp;}
}

apply plugin: 'java'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

group = 'com.djamware'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

repositories {
&nbsp;&nbsp; &nbsp;mavenCentral()
}

dependencies {
&nbsp;&nbsp; &nbsp;implementation 'org.springframework.boot:spring-boot-starter-data-mongodb'
&nbsp;&nbsp; &nbsp;implementation 'org.springframework.boot:spring-boot-starter-security'
&nbsp;&nbsp; &nbsp;implementation 'org.springframework.boot:spring-boot-starter-web'
&nbsp;&nbsp; &nbsp;testImplementation 'org.springframework.boot:spring-boot-starter-test'
&nbsp;&nbsp; &nbsp;testImplementation 'org.springframework.security:spring-security-test'
}

Now, you can work with the source code of this Spring Boot Project using your own IDE or Text Editor. We are using Spring Tool Suite (STS). In STS, import the extracted zipped file as Existing Gradle Project.

Next, we have to add the JWT library to the build.gradle as the dependency. Open and edit build.gradle then add this line to dependencies after other implementation.

implementation 'io.jsonwebtoken:jjwt:0.9.1'

Next, compile the Gradle Project by type this command from Terminal or CMD.

./gradlew compile

Or you can compile directly from STS by right-clicking the project name then choose Gradle -> Refresh Gradle Project. Next, open and edit src/main/resources/application.properties then add these lines.

spring.data.mongodb.database=springmongodb
spring.data.mongodb.host=localhost
spring.data.mongodb.port=27017

2. Create Product, User and Role Model or Entity Classes

We will be creating all required models or entities for products, user and role. In STS, right-click the project name -> New -> Class. Fill the package with com.djamware.SecurityRest.models, the name with Product, and leave other fields and checkbox as default then click Finish Button.

Next, open and edit src/main/java/com/djamware/SecurityRest/models/Product.java then add this annotation above the class name that will point to MongoDB collection.

@Document(collection = "products")

Inside Product class, add these variables.

@Id
String id;
String prodName;
String prodDesc;
Double prodPrice;
String prodImage;

Add constructors after the variable or fields.

public Product() {
}

public Product(String prodName, String prodDesc, Double prodPrice, String prodImage) {
&nbsp;&nbsp; &nbsp;super();
&nbsp;&nbsp; &nbsp;this.prodName = prodName;
&nbsp;&nbsp; &nbsp;this.prodDesc = prodDesc;
&nbsp;&nbsp; &nbsp;this.prodPrice = prodPrice;
&nbsp;&nbsp; &nbsp;this.prodImage = prodImage;
}

Generate or create Getter and Setter for each field.

public String getId() {
&nbsp;&nbsp; &nbsp;return id;
}

public void setId(String id) {
&nbsp;&nbsp; &nbsp;this.id = id;
}

public String getProdName() {
&nbsp;&nbsp; &nbsp;return prodName;
}

public void setProdName(String prodName) {
&nbsp;&nbsp; &nbsp;this.prodName = prodName;
}

public String getProdDesc() {
&nbsp;&nbsp; &nbsp;return prodDesc;
}

public void setProdDesc(String prodDesc) {
&nbsp;&nbsp; &nbsp;this.prodDesc = prodDesc;
}

public Double getProdPrice() {
&nbsp;&nbsp; &nbsp;return prodPrice;
}

public void setProdPrice(Double prodPrice) {
&nbsp;&nbsp; &nbsp;this.prodPrice = prodPrice;
}

public String getProdImage() {
&nbsp;&nbsp; &nbsp;return prodImage;
}

public void setProdImage(String prodImage) {
&nbsp;&nbsp; &nbsp;this.prodImage = prodImage;
}

Using STS you can organize imports automatically from the menu Source -> Organize Imports then you can see the imports after the package name.

package com.djamware.SecurityRest.models;

import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.mapping.Document;

You can do the same way as the above step for User and Role class. Here’s the User class looks like.

package com.djamware.SecurityRest.models;

import java.util.Set;

import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.index.IndexDirection;
import org.springframework.data.mongodb.core.index.Indexed;
import org.springframework.data.mongodb.core.mapping.DBRef;
import org.springframework.data.mongodb.core.mapping.Document;

@Document(collection = "users")
public class User {

&nbsp;&nbsp; &nbsp;@Id
&nbsp;&nbsp; &nbsp;private String id;
&nbsp;&nbsp; &nbsp;@Indexed(unique = true, direction = IndexDirection.DESCENDING, dropDups = true)
&nbsp;&nbsp; &nbsp;private String email;
&nbsp;&nbsp; &nbsp;private String password;
&nbsp;&nbsp; &nbsp;private String fullname;
&nbsp;&nbsp; &nbsp;private boolean enabled;
&nbsp;&nbsp; &nbsp;@DBRef
&nbsp;&nbsp; &nbsp;private Set<Role> roles;
&nbsp;&nbsp; &nbsp;public String getId() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return id;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setId(String id) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.id = id;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public String getEmail() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return email;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setEmail(String email) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.email = email;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public String getPassword() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return password;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setPassword(String password) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.password = password;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public String getFullname() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return fullname;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setFullname(String fullname) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.fullname = fullname;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public boolean isEnabled() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return enabled;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setEnabled(boolean enabled) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.enabled = enabled;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public Set<Role> getRoles() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return roles;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setRoles(Set<Role> roles) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.roles = roles;
&nbsp;&nbsp; &nbsp;}

}

And the Role class will be like this.

package com.djamware.SecurityRest.models;

import org.springframework.data.annotation.Id;
import org.springframework.data.mongodb.core.index.IndexDirection;
import org.springframework.data.mongodb.core.index.Indexed;
import org.springframework.data.mongodb.core.mapping.Document;

@Document(collection = "roles")
public class Role {

&nbsp;&nbsp; &nbsp;@Id
&nbsp; &nbsp; private String id;
&nbsp; &nbsp; @Indexed(unique = true, direction = IndexDirection.DESCENDING, dropDups = true)

&nbsp; &nbsp; private String role;
&nbsp;&nbsp; &nbsp;public String getId() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return id;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setId(String id) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.id = id;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public String getRole() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return role;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setRole(String role) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.role = role;
&nbsp;&nbsp; &nbsp;}

}

3. Create Product, User and Role Repository Interfaces

Next steps to create Product, User, and Role Repository Interfaces. From the STS, right-click the project name -> New -> Interface then fill all required fields and checkboxes as below before click Finish button.

Next, open and edit src/main/java/com/djamware/SecurityRest/repositories/ProductRepository.java then add extends to MongoDB CRUD Repository.

public interface ProductRepository extends CrudRepository<Product, String> {

}

Inside the class name add this method.

@Override
void delete(Product deleted);

Organize all required imports.

import org.springframework.data.repository.CrudRepository;
import com.djamware.SecurityRest.models.Product;

The same way can be applied to User and Role repositories. So, the User Repository Interface will look like this.

package com.djamware.SecurityRest.repositories;

import org.springframework.data.mongodb.repository.MongoRepository;
import com.djamware.SecurityRest.models.User;

public interface UserRepository extends MongoRepository<User, String> {

&nbsp;&nbsp; &nbsp;User findByEmail(String email);
}

And the Role Repository Interface will look like this.

package com.djamware.SecurityRest.repositories;

import org.springframework.data.mongodb.repository.MongoRepository;
import com.djamware.SecurityRest.models.Role;

public interface RoleRepository extends MongoRepository<Role, String> {

&nbsp;&nbsp; &nbsp;Role findByRole(String role);
}

4. Create a Custom User Details Service

To implements authentication using existing User and Role from MongoDB, we have to create a custom user details service. From the STS, right-click the project name -> New -> Class File then fill all required fields and checkboxes as below before clicking the finish button.

Next, open and edit src/main/java/com/djamware/SecurityRest/services/CustomUserDetailsService.java then give an annotation above the class name and implement the Spring Security User Details Service.

@Service
public class CustomUserDetailsService implements UserDetailsService {
}

Next, inject all required beans at the first line of the class bracket.

@Autowired
private UserRepository userRepository;

@Autowired
private RoleRepository roleRepository;

@Autowired
private PasswordEncoder bCryptPasswordEncoder;

Add a method to find a user by email field.

public User findUserByEmail(String email) {
&nbsp; &nbsp; return userRepository.findByEmail(email);
}

Add a method to save a new user.

public void saveUser(User user) {
&nbsp; &nbsp; user.setPassword(bCryptPasswordEncoder.encode(user.getPassword()));
&nbsp; &nbsp; user.setEnabled(true);
&nbsp; &nbsp; Role userRole = roleRepository.findByRole("ADMIN");
&nbsp; &nbsp; user.setRoles(new HashSet<>(Arrays.asList(userRole)));
&nbsp; &nbsp; userRepository.save(user);
}

Override the Spring Security User Details to load User by email.

@Override
public UserDetails loadUserByUsername(String email) throws UsernameNotFoundException {

&nbsp; &nbsp; User user = userRepository.findByEmail(email);
&nbsp; &nbsp; if(user != null) {
&nbsp; &nbsp; &nbsp; &nbsp; List<GrantedAuthority> authorities = getUserAuthority(user.getRoles());
&nbsp; &nbsp; &nbsp; &nbsp; return buildUserForAuthentication(user, authorities);
&nbsp; &nbsp; } else {
&nbsp; &nbsp; &nbsp; &nbsp; throw new UsernameNotFoundException("username not found");
&nbsp; &nbsp; }
}

Add a method to get a set of Roles that related to a user.

private List<GrantedAuthority> getUserAuthority(Set<Role> userRoles) {
&nbsp; &nbsp; Set<GrantedAuthority> roles = new HashSet<>();
&nbsp; &nbsp; userRoles.forEach((role) -> {
&nbsp; &nbsp; &nbsp; &nbsp; roles.add(new SimpleGrantedAuthority(role.getRole()));
&nbsp; &nbsp; });

&nbsp; &nbsp; List<GrantedAuthority> grantedAuthorities = new ArrayList<>(roles);
&nbsp; &nbsp; return grantedAuthorities;
}

Add a method for authentication purpose.

private UserDetails buildUserForAuthentication(User user, List<GrantedAuthority> authorities) {
&nbsp; &nbsp; return new org.springframework.security.core.userdetails.User(user.getEmail(), user.getPassword(), authorities);
}

5. Configure Spring Boot Security Rest

Now, the main purpose of this tutorial is configuring Spring Security Rest. First, we have to create a bean for JWT token generation and validation. Right-click the project name -> New -> Class File. Fill the package name as com.djamware.SecurityRest.configs and the Class name as JwtTokenProvider then click the Finish button. Next, open and edit that newly created class file then give it an annotation above the class name.

@Component
public class JwtTokenProvider {
}

Add variables and injected bean inside the class bracket at the top lines.

@Value("${security.jwt.token.secret-key:secret}")
private String secretKey = "secret";

@Value("${security.jwt.token.expire-length:3600000}")
private long validityInMilliseconds = 3600000; // 1h

@Autowired
private CustomUserDetailsService userDetailsService;

Add a method for initialization.

@PostConstruct
protected void init() {
&nbsp; &nbsp; secretKey = Base64.getEncoder().encodeToString(secretKey.getBytes());
}

Add a method to create a JWT token.

public String createToken(String username, Set<Role> set) {
&nbsp; &nbsp; Claims claims = Jwts.claims().setSubject(username);
&nbsp; &nbsp; claims.put("roles", set);
&nbsp; &nbsp; Date now = new Date();
&nbsp; &nbsp; Date validity = new Date(now.getTime() + validityInMilliseconds);
&nbsp; &nbsp; return Jwts.builder()//
&nbsp; &nbsp; &nbsp; &nbsp; .setClaims(claims)//
&nbsp; &nbsp; &nbsp; &nbsp; .setIssuedAt(now)//
&nbsp; &nbsp; &nbsp; &nbsp; .setExpiration(validity)//
&nbsp; &nbsp; &nbsp; &nbsp; .signWith(SignatureAlgorithm.HS256, secretKey)//
&nbsp; &nbsp; &nbsp; &nbsp; .compact();
}

Add a method to load User by username.

public Authentication getAuthentication(String token) {
&nbsp; &nbsp; UserDetails userDetails = this.userDetailsService.loadUserByUsername(getUsername(token));
&nbsp; &nbsp; return new UsernamePasswordAuthenticationToken(userDetails, "", userDetails.getAuthorities());
}

Add a method to get the username by JWT token.

public String getUsername(String token) {
&nbsp; &nbsp; return Jwts.parser().setSigningKey(secretKey).parseClaimsJws(token).getBody().getSubject();
}

Add a method to resolve JWT token from request headers of Authorization that has a Bearer prefix.

public String resolveToken(HttpServletRequest req) {
&nbsp; &nbsp; String bearerToken = req.getHeader("Authorization");
&nbsp; &nbsp; if (bearerToken != null && bearerToken.startsWith("Bearer ")) {
&nbsp; &nbsp; &nbsp; &nbsp; return bearerToken.substring(7, bearerToken.length());
&nbsp; &nbsp; }
&nbsp; &nbsp; return null;
}

Add a method to validate a JWT token.

public boolean validateToken(String token) {
&nbsp; &nbsp; try {
&nbsp; &nbsp; &nbsp; &nbsp; Jws<Claims> claims = Jwts.parser().setSigningKey(secretKey).parseClaimsJws(token);
&nbsp; &nbsp; &nbsp; &nbsp; if (claims.getBody().getExpiration().before(new Date())) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return false;
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; return true;
&nbsp; &nbsp; } catch (JwtException | IllegalArgumentException e) {
&nbsp; &nbsp; &nbsp; &nbsp; throw new JwtException("Expired or invalid JWT token");
&nbsp; &nbsp; }
}

Finally, organize imports like below.

package com.djamware.SecurityRest.configs;

import java.util.Base64;
import java.util.Date;
import java.util.Set;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.stereotype.Component;

import com.djamware.SecurityRest.models.Role;
import com.djamware.SecurityRest.services.CustomUserDetailsService;

import io.jsonwebtoken.Claims;
import io.jsonwebtoken.Jws;
import io.jsonwebtoken.JwtException;
import io.jsonwebtoken.Jwts;
import io.jsonwebtoken.SignatureAlgorithm;

Next, create a JWT filter class with the name JwtTokenFilter in configs package that extends Spring GenericFilterBean. Replace all Java codes with these lines of codes.

package com.djamware.SecurityRest.configs;

import java.io.IOException;

import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;

import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.web.filter.GenericFilterBean;

public class JwtTokenFilter extends GenericFilterBean {

&nbsp;&nbsp; &nbsp;private JwtTokenProvider jwtTokenProvider;

&nbsp; &nbsp; public JwtTokenFilter(JwtTokenProvider jwtTokenProvider) {
&nbsp; &nbsp; &nbsp; &nbsp; this.jwtTokenProvider = jwtTokenProvider;
&nbsp; &nbsp; }

&nbsp; &nbsp; @Override
&nbsp; &nbsp; public void doFilter(ServletRequest req, ServletResponse res, FilterChain filterChain)
&nbsp; &nbsp; &nbsp; &nbsp; throws IOException, ServletException {
&nbsp; &nbsp; &nbsp; &nbsp; String token = jwtTokenProvider.resolveToken((HttpServletRequest) req);
&nbsp; &nbsp; &nbsp; &nbsp; if (token != null && jwtTokenProvider.validateToken(token)) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Authentication auth = token != null ? jwtTokenProvider.getAuthentication(token) : null;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SecurityContextHolder.getContext().setAuthentication(auth);
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; filterChain.doFilter(req, res);
&nbsp; &nbsp; }
}

Next, create a class with the name JwtConfigurer for JWT configuration in configs package then replace all codes with these lines of codes.

package com.djamware.SecurityRest.configs;

import org.springframework.security.config.annotation.SecurityConfigurerAdapter;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.web.DefaultSecurityFilterChain;
import org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter;

public class JwtConfigurer extends SecurityConfigurerAdapter<DefaultSecurityFilterChain, HttpSecurity> {

&nbsp;&nbsp; &nbsp;private JwtTokenProvider jwtTokenProvider;

&nbsp; &nbsp; public JwtConfigurer(JwtTokenProvider jwtTokenProvider) {
&nbsp; &nbsp; &nbsp; &nbsp; this.jwtTokenProvider = jwtTokenProvider;
&nbsp; &nbsp; }

&nbsp; &nbsp; @Override
&nbsp; &nbsp; public void configure(HttpSecurity http) throws Exception {
&nbsp; &nbsp; &nbsp; &nbsp; JwtTokenFilter customFilter = new JwtTokenFilter(jwtTokenProvider);
&nbsp; &nbsp; &nbsp; &nbsp; http.addFilterBefore(customFilter, UsernamePasswordAuthenticationFilter.class);
&nbsp; &nbsp; }
}

Finally, we have to configure the Spring Security by creating a Java class file inside configs package with the name WebSecurityConfig. Give annotations to this class and extends Spring WebSecurityConfigurerAdapter.

@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
}

Inject JWT token provider inside this class.

@Autowired
JwtTokenProvider jwtTokenProvider;

Add an override method to configure Authentication Manager Builder.

@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
&nbsp;&nbsp; &nbsp;UserDetailsService userDetailsService = mongoUserDetails();
&nbsp;&nbsp; &nbsp;auth.userDetailsService(userDetailsService).passwordEncoder(bCryptPasswordEncoder());

}

Next, add an override method to configure Spring Security Http Security.

@Override
protected void configure(HttpSecurity http) throws Exception {
&nbsp;&nbsp; &nbsp;http.httpBasic().disable().csrf().disable().sessionManagement()
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;.sessionCreationPolicy(SessionCreationPolicy.STATELESS).and().authorizeRequests()
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;.antMatchers("/api/auth/login").permitAll().antMatchers("/api/auth/register").permitAll()
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;.antMatchers("/api/products/**").hasAuthority("ADMIN").anyRequest().authenticated().and().csrf()
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;.disable().exceptionHandling().authenticationEntryPoint(unauthorizedEntryPoint()).and()
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;.apply(new JwtConfigurer(jwtTokenProvider));
}

Next, declare all required beans for this configuration.

@Bean
public PasswordEncoder bCryptPasswordEncoder() {
&nbsp;&nbsp; &nbsp;return new BCryptPasswordEncoder();
}

@Bean
@Override
public AuthenticationManager authenticationManagerBean() throws Exception {
&nbsp;&nbsp; &nbsp;return super.authenticationManagerBean();
}

@Bean
public AuthenticationEntryPoint unauthorizedEntryPoint() {
&nbsp;&nbsp; &nbsp;return (request, response, authException) -> response.sendError(HttpServletResponse.SC_UNAUTHORIZED,
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;"Unauthorized");
}

@Bean
public UserDetailsService mongoUserDetails() {
&nbsp;&nbsp; &nbsp;return new CustomUserDetailsService();
}

6. Create Product and Authentication Controllers

Now it’s time for REST API endpoint. All RESTful API will be created from each controller. Product controller will handle CRUD endpoint of product and Authentication controller will handle login and register endpoint. Right-click project name -> New -> Class then fill the package with com.djamware.SecurityRest.controllers and the class name as ProductController. Open and edit the newly created class file then replace all codes with these lines of codes.

package com.djamware.SecurityRest.controllers;

import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.djamware.SecurityRest.models.Product;
import com.djamware.SecurityRest.repositories.ProductRepository;

@RestController
public class ProductController {

&nbsp;&nbsp; &nbsp;@Autowired
&nbsp; &nbsp; ProductRepository productRepository;

&nbsp; &nbsp; @RequestMapping(method=RequestMethod.GET, value="/api/products")
&nbsp; &nbsp; public Iterable<Product> product() {
&nbsp; &nbsp; &nbsp; &nbsp; return productRepository.findAll();
&nbsp; &nbsp; }

&nbsp; &nbsp; @RequestMapping(method=RequestMethod.POST, value="/api/products")
&nbsp; &nbsp; public String save(@RequestBody Product product) {
&nbsp; &nbsp; &nbsp; &nbsp; productRepository.save(product);

&nbsp; &nbsp; &nbsp; &nbsp; return product.getId();
&nbsp; &nbsp; }

&nbsp; &nbsp; @RequestMapping(method=RequestMethod.GET, value="/api/products/{id}")
&nbsp; &nbsp; public Optional<Product> show(@PathVariable String id) {
&nbsp; &nbsp; &nbsp; &nbsp; return productRepository.findById(id);
&nbsp; &nbsp; }

&nbsp; &nbsp; @RequestMapping(method=RequestMethod.PUT, value="/api/products/{id}")
&nbsp; &nbsp; public Product update(@PathVariable String id, @RequestBody Product product) {
&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;Optional<Product> prod = productRepository.findById(id);
&nbsp; &nbsp; &nbsp; &nbsp; if(product.getProdName() != null)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prod.get().setProdName(product.getProdName());
&nbsp; &nbsp; &nbsp; &nbsp; if(product.getProdDesc() != null)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prod.get().setProdDesc(product.getProdDesc());
&nbsp; &nbsp; &nbsp; &nbsp; if(product.getProdPrice() != null)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prod.get().setProdPrice(product.getProdPrice());
&nbsp; &nbsp; &nbsp; &nbsp; if(product.getProdImage() != null)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prod.get().setProdImage(product.getProdImage());
&nbsp; &nbsp; &nbsp; &nbsp; productRepository.save(prod.get());
&nbsp; &nbsp; &nbsp; &nbsp; return prod.get();
&nbsp; &nbsp; }

&nbsp; &nbsp; @RequestMapping(method=RequestMethod.DELETE, value="/api/products/{id}")
&nbsp; &nbsp; public String delete(@PathVariable String id) {
&nbsp; &nbsp; &nbsp; &nbsp; Optional<Product> product = productRepository.findById(id);
&nbsp; &nbsp; &nbsp; &nbsp; productRepository.delete(product.get());

&nbsp; &nbsp; &nbsp; &nbsp; return "product deleted";
&nbsp; &nbsp; }
}

For login, we need to create a POJO to mapping required fields of User. Create a new class file with the name AuthBody inside controllers package then replace all Java codes with these lines of codes.

package com.djamware.SecurityRest.controllers;

public class AuthBody {

&nbsp;&nbsp; &nbsp;private String email;
&nbsp; &nbsp; private String password;

&nbsp;&nbsp; &nbsp;public String getEmail() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return email;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setEmail(String email) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.email = email;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public String getPassword() {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return password;
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;public void setPassword(String password) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;this.password = password;
&nbsp;&nbsp; &nbsp;}

}

Finally, create a controller for authentication with the name AuthController inside the controllers’ package. Open and edit that newly created file then replace all Java codes with these lines of codes.

package com.djamware.SecurityRest.controllers;

import static org.springframework.http.ResponseEntity.ok;

import java.util.HashMap;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.security.authentication.AuthenticationManager;
import org.springframework.security.authentication.BadCredentialsException;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.core.AuthenticationException;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.djamware.SecurityRest.configs.JwtTokenProvider;
import com.djamware.SecurityRest.models.User;
import com.djamware.SecurityRest.repositories.UserRepository;
import com.djamware.SecurityRest.services.CustomUserDetailsService;

@RestController
@RequestMapping("/api/auth")
public class AuthController {

&nbsp;&nbsp; &nbsp;@Autowired
&nbsp;&nbsp; &nbsp;AuthenticationManager authenticationManager;

&nbsp;&nbsp; &nbsp;@Autowired
&nbsp;&nbsp; &nbsp;JwtTokenProvider jwtTokenProvider;

&nbsp;&nbsp; &nbsp;@Autowired
&nbsp;&nbsp; &nbsp;UserRepository users;

&nbsp;&nbsp; &nbsp;@Autowired
&nbsp;&nbsp; &nbsp;private CustomUserDetailsService userService;

&nbsp;&nbsp; &nbsp;@SuppressWarnings("rawtypes")
&nbsp;&nbsp; &nbsp;@PostMapping("/login")
&nbsp;&nbsp; &nbsp;public ResponseEntity login(@RequestBody AuthBody data) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;try {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;String username = data.getEmail();
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;authenticationManager.authenticate(new UsernamePasswordAuthenticationToken(username, data.getPassword()));
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;String token = jwtTokenProvider.createToken(username, this.users.findByEmail(username).getRoles());
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Map<Object, Object> model = new HashMap<>();
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;model.put("username", username);
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;model.put("token", token);
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return ok(model);
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;} catch (AuthenticationException e) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;throw new BadCredentialsException("Invalid email/password supplied");
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;}

&nbsp;&nbsp; &nbsp;@SuppressWarnings("rawtypes")
&nbsp;&nbsp; &nbsp;@PostMapping("/register")
&nbsp;&nbsp; &nbsp;public ResponseEntity register(@RequestBody User user) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;User userExists = userService.findUserByEmail(user.getEmail());
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;if (userExists != null) {
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;throw new BadCredentialsException("User with username: " + user.getEmail() + " already exists");
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;userService.saveUser(user);
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Map<Object, Object> model = new HashMap<>();
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;model.put("message", "User registered successfully");
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;return ok(model);
&nbsp;&nbsp; &nbsp;}
}

7. Run and Test Spring Boot Security Rest using Postman

Before run and test the application, we have to populate a Role data first. Open and edit src/main/java/com/djamware/SecurityRest/SecurityRestApplication.java then add these lines of codes inside the initialization method.

@Bean
CommandLineRunner init(RoleRepository roleRepository) {

&nbsp; &nbsp; return args -> {

&nbsp; &nbsp; &nbsp; &nbsp; Role adminRole = roleRepository.findByRole("ADMIN");
&nbsp; &nbsp; &nbsp; &nbsp; if (adminRole == null) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Role newAdminRole = new Role();
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; newAdminRole.setRole("ADMIN");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; roleRepository.save(newAdminRole);
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; };

}

Next, make sure you have run the MongoDB server on your local machine then run the Gradle application using this command.

./gradlew bootRun

Or in STS just right-click the project name -> Run As -> Spring Boot App. Next, open the Postman application then change the method to GET and address to localhost:8080/api/products then click Send button.

You will see this response in the bottom panel of Postman.

{
&nbsp; &nbsp; "timestamp": "2019-03-07T13:16:34.935+0000",
&nbsp; &nbsp; "status": 401,
&nbsp; &nbsp; "error": "Unauthorized",
&nbsp; &nbsp; "message": "Unauthorized",
&nbsp; &nbsp; "path": "/api/products"
}

Next, change the method to POST then address to localhost:8080/api/auth/register then fill the body with raw data as below image then click Send button.

You will get the response in the bottom panel of Postman.

{
&nbsp; &nbsp; "message": "User registered successfully"
}

Next, change the address to localhost:8080/api/auth/login and change the body as below then click Send button.

{ "email":"[email&nbsp;protected]", "password": "q1w2we3r4" }

You will see this response in the bottom panel of Postman.

{
&nbsp; &nbsp; "username": "[email&nbsp;protected]",
&nbsp; &nbsp; "token": "eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJpbmZvQGRqYW13YXJlLmNvbSIsInJvbGVzIjpbeyJpZCI6IjVjODBjNjIzYjIwMTkxNGIyYTY5N2U4ZCIsInJvbGUiOiJBRE1JTiJ9XSwiaWF0IjoxNTUxOTY0OTc3LCJleHAiOjE1NTE5Njg1Nzd9.j5CHZ_LCmeQtdxQeH9eluxVXcOsHPWV1p8WnBn0CULo"
}

Copy the token then back to the GET product. Add a header with the name Authorization and the value that paste from a token that gets by login with additional Bearer prefix (with space) as below.

Bearer eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJpbmZvQGRqYW13YXJlLmNvbSIsInJvbGVzIjpbeyJpZCI6IjVjODBjNjIzYjIwMTkxNGIyYTY5N2U4ZCIsInJvbGUiOiJBRE1JTiJ9XSwiaWF0IjoxNTUxOTY0OTc3LCJleHAiOjE1NTE5Njg1Nzd9.j5CHZ_LCmeQtdxQeH9eluxVXcOsHPWV1p8WnBn0CULo

You should see this response after clicking the Send button.

[
&nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; "id": "5c80dc6cb20191520567b68a",
&nbsp; &nbsp; &nbsp; &nbsp; "prodName": "Dummy Product 1",
&nbsp; &nbsp; &nbsp; &nbsp; "prodDesc": "The Fresh Dummy Product in The world part 1",
&nbsp; &nbsp; &nbsp; &nbsp; "prodPrice": 100,
&nbsp; &nbsp; &nbsp; &nbsp; "prodImage": "https://dummyimage.com/600x400/000/fff"
&nbsp; &nbsp; }
]

You can test the POST product with the token in headers using the same way.

That it’s, the Securing RESTful API with Spring Boot, Security, and Data MongoDB tutorial. You can get the full source code from our GitHub.

Learn More

Build a Simple CRUD App with Spring Boot and Vue.js

Creating RESTful APIs with NodeJS and MongoDB Tutorial

MongoDB with Python Crash Course - Tutorial for Beginners

How to build RESTful APIs with ASP.NET Core

Understanding the basics of RESTful APIs

Developing RESTful APIs with Lumen (A PHP Micro-framework)

Java Programming Masterclass for Software Developers

Java In-Depth: Become a Complete Java Engineer!

JSP, Servlets and JDBC for Beginners: Build a Database App

JSP, Servlet, JSLT + Hibernate: A complete guide

Json Javascript database for Node.js, Electron and Browser

Json Javascript database for Node.js, Electron and Browser

JSON Javascript database for Node.js, Electron and the browser. Powered by Lodash. ⚡️

lowdb is a small local JSON database powered by Lodash (supports Node, Electron and the Browser)

Install

npm install lowdb

Alternatively, if you're using yarn

yarn add lowdb

A UMD build is also available on unpkg for testing and quick prototyping:

<script src="https://unpkg.com/[email protected]/lodash.min.js"></script>
<script src="https://unpkg.com/[email protected]/dist/low.min.js"></script>
<script src="https://unpkg.com/[email protected]/dist/LocalStorage.min.js"></script>
<script>
  var adapter = new LocalStorage('db')
  var db = low(adapter)
</script>

How to use LowDB

db.get('posts')
  .push({ id: 1, title: 'lowdb is awesome'})
  .write()
const low = require('lowdb')
const FileSync = require('lowdb/adapters/FileSync')

const adapter = new FileSync('db.json')
const db = low(adapter)

// Set some defaults (required if your JSON file is empty)
db.defaults({ posts: [], user: {}, count: 0 })
  .write()

// Add a post
db.get('posts')
  .push({ id: 1, title: 'lowdb is awesome'})
  .write()

// Set a user using Lodash shorthand syntax
db.set('user.name', 'typicode')
  .write()
  
// Increment count
db.update('count', n => n + 1)
  .write()

Data is saved to db.json

{
  "posts": [
    { "id": 1, "title": "lowdb is awesome"}
  ],
  "user": {
    "name": "typicode"
  },
  "count": 1
}

You can use any of the powerful lodash functions, like _.get and _.find with shorthand syntax.

// For performance, use .value() instead of .write() if you're only reading from db
db.get('posts')
  .find({ id: 1 })
  .value()

Lowdb is perfect for CLIs, small servers, Electron apps and npm packages in general.

It supports Node, the browser and uses lodash API, so it's very simple to learn. Actually, if you know Lodash, you already know how to use lowdb

Important lowdb doesn't support Cluster and may have issues with very large JSON files (~200MB).

API

low(adapter)

Returns a lodash chain with additional properties and functions described below.

db.[...].write() and db.[...].value()

write() writes database to state.

On the other hand, value() is just _.prototype.value() and should be used to execute a chain that doesn't change database state.

db.set('user.name', 'typicode')
  .write()

Please note that db.[...].write() is syntactic sugar and equivalent to

db.set('user.name', 'typicode')
  .value()

db.write()

db._

Database lodash instance. Use it to add your own utility functions or third-party mixins like underscore-contrib or lodash-id.

db._.mixin({
  second: function(array) {
    return array[1]
  }
})

db.get('posts')
  .second()
  .value()

db.getState()

Returns database state.

db.getState() // { posts: [ ... ] }

db.setState(newState)

Replaces database state.

const newState = {}
db.setState(newState)

db.write()

Persists database using adapter.write (depending on the adapter, may return a promise).

// With lowdb/adapters/FileSync
db.write()
console.log('State has been saved')

// With lowdb/adapters/FileAsync
db.write()
  .then(() => console.log('State has been saved'))

db.read()

Reads source using storage.read option (depending on the adapter, may return a promise).

// With lowdb/FileSync
db.read()
console.log('State has been updated')

// With lowdb/FileAsync
db.read()
  .then(() => console.log('State has been updated'))

Adapters API

Please note this only applies to adapters bundled with Lowdb. Third-party adapters may have different options.

For convenience, FileSync, FileAsync and LocalBrowser accept the following options:

  • defaultValue if file doesn't exist, this value will be used to set the initial state (default: {})
  • serialize/deserialize functions used before writing and after reading (default: JSON.stringify and JSON.parse)
const adapter = new FileSync('array.yaml', {
  defaultValue: [],
  serialize: (array) => toYamlString(array),
  deserialize: (string) => fromYamlString(string)
})

Guide

How to query

With lowdb, you get access to the entire lodash API, so there are many ways to query and manipulate data. Here are a few examples to get you started.

Please note that data is returned by reference, this means that modifications to returned objects may change the database. To avoid such behaviour, you need to use .cloneDeep().

Also, the execution of methods is lazy, that is, execution is deferred until .value() or .write() is called.

Reading from existing JSON file

If you are reading from a file adapter, the path is relative to execution path (CWD) and not to your code.

my_project/
  src/
    my_example.js
  db.json 

So then you read it like this:

// file src/my_example.js
const adapter = new FileSync('db.json')

// With lowdb/FileAsync
db.read()
  .then(() => console.log('Content of my_project/db.json is loaded'))

Examples

Check if posts exists.

db.has('posts')
  .value()

Set posts.

db.set('posts', [])
  .write()

Sort the top five posts.

db.get('posts')
  .filter({published: true})
  .sortBy('views')
  .take(5)
  .value()

Get post titles.

db.get('posts')
  .map('title')
  .value()

Get the number of posts.

db.get('posts')
  .size()
  .value()

Get the title of first post using a path.

db.get('posts[0].title')
  .value()

Update a post.

db.get('posts')
  .find({ title: 'low!' })
  .assign({ title: 'hi!'})
  .write()

Remove posts.

db.get('posts')
  .remove({ title: 'low!' })
  .write()

Remove a property.

db.unset('user.name')
  .write()

Make a deep clone of posts.

db.get('posts')
  .cloneDeep()
  .value()

How to use id based resources

Being able to get data using an id can be quite useful, particularly in servers. To add id-based resources support to lowdb, you have 2 options.

shortid is more minimalist and returns a unique id that you can use when creating resources.

const shortid = require('shortid')

const postId = db
  .get('posts')
  .push({ id: shortid.generate(), title: 'low!' })
  .write()
  .id

const post = db
  .get('posts')
  .find({ id: postId })
  .value()

lodash-id provides a set of helpers for creating and manipulating id-based resources.

const lodashId = require('lodash-id')
const FileSync = require('lowdb/adapters/FileSync')

const adapter = new FileSync('db.json')
const db = low(adapter)

db._.mixin(lodashId)

// We need to set some default values, if the collection does not exist yet
// We also can store our collection
const collection = db
  .defaults({ posts: [] })
  .get('posts')

// Insert a new post...
const newPost = collection
  .insert({ title: 'low!' })
  .write()

// ...and retrieve it using its id
const post = collection
  .getById(newPost.id)
  .value()

How to create custom adapters

low() accepts custom Adapter, so you can virtually save your data to any storage using any format.

class MyStorage {
  constructor() {
    // ...
  }

  read() {
    // Should return data (object or array) or a Promise
  }

  write(data) {
    // Should return nothing or a Promise
  }
}

const adapter = new MyStorage(args)
const db = low(adapter)

See src/adapters for examples.

How to encrypt data

FileSync, FileAsync and LocalStorage accept custom serialize and deserialize functions. You can use them to add encryption logic.

const adapter = new FileSync('db.json', {
  serialize: (data) => encrypt(JSON.stringify(data)),
  deserialize: (data) => JSON.parse(decrypt(data))
})

Changelog

See changes for each version in the release notes.

Limits

Lowdb is a convenient method for storing data without setting up a database server. It is fast enough and safe to be used as an embedded database.

However, if you seek high performance and scalability more than simplicity, you should probably stick to traditional databases like MongoDB.

Source Code

https://github.com/typicode/lowdb

First CRUD Node Express Js Mysql Example

First CRUD Node Express Js Mysql Example

In this node express js tutorial, we would love to share with you how to create a crud application in node express js with mysql

In this node express js tutorial, we would love to share with you how to create a crud application in node express js with mysql

Hello developers, Today we will discuss how to install node js framework express js and how to create crud (create, update, read, delete) application in node js using express js framework with mysql database. Today we would love to show you how to create crud application in node js.

We will create crud application in node express js framework with mysql database. we will do each thing step by step and easy.

In this node express js crud application. We will use ejs templating engine. It is very simple and easy to understand for everyone.

Let’s start create CRUD with Node.Js , Express, MySQL

Create Express js Project

Use the below command and create your express project with name expressfirst

express --view=ejs expressfirst

After successfully created expressfirst folder in your system. Next follow the below commands and install node js in your project :

cd expressfirst

npm install  

Next we need to install some required pacakges, go to terminal and use the below commands :

 npm install express-flash --save
 npm install express-session --save
 npm install express-validator --save
 npm install method-override --save
 npm install mysql --save

Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.

In this node js mysql crud tutorial express flash is used to display a warning, error and information message

Express-session is used to made a session as like in PHP. In this node js mysql crud tutorial, session is needed as the express requirement of express-flash.

Express validator is used to validate form data it is easy to use. express-validator highly effective and efficient way to accelerate the creation of applications.

NPM is used to run a DELETE and PUT method from an HTML form. In several web browsers only support GET and POST methods.

Driver to connect node.js with MySQL

Database Connection with Mysql

Next we need to create one folder name lib and create a new file name db.js inside this folder. We will connect node js to mysql using this file

lib/db.js
 var mysql=require('mysql');
 var connection=mysql.createConnection({
   host:'localhost',
   user:'your username',
   password:'your password',
   database:'your database name'
 });
connection.connect(function(error){
   if(!!error){
     console.log(error);
   }else{
     console.log('Connected!:)');
   }
 });  
module.exports = connection; 

Changes in app.js

We need to some changes in app.js file. go to app.js file and put some code here :

 var createError = require('http-errors');
 var express = require('express');
 var path = require('path');
 var cookieParser = require('cookie-parser');
 var logger = require('morgan');
 var expressValidator = require('express-validator');
 var flash = require('express-flash');
 var session = require('express-session');
 var bodyParser = require('body-parser');

 var mysql = require('mysql');
 var connection  = require('./lib/db');

 var indexRouter = require('./routes/index');
 var usersRouter = require('./routes/users');
 var customersRouter = require('./routes/customers');

 var app = express();

// view engine setup
 app.set('views', path.join(__dirname, 'views'));
 app.set('view engine', 'ejs');

 app.use(logger('dev'));
 app.use(bodyParser.json());
 app.use(bodyParser.urlencoded({ extended: true }));
 app.use(cookieParser());
 app.use(express.static(path.join(__dirname, 'public')));

 app.use(session({ 
     secret: '123456cat',
     resave: false,
     saveUninitialized: true,
     cookie: { maxAge: 60000 }
 }))

 app.use(flash());
 app.use(expressValidator());

 app.use('/', indexRouter);
 app.use('/users', usersRouter);
 app.use('/customers', customersRouter);

 // catch 404 and forward to error handler
 app.use(function(req, res, next) {
   next(createError(404));
 });

 // error handler
 app.use(function(err, req, res, next) {
   // set locals, only providing error in development
   res.locals.message = err.message;
   res.locals.error = req.app.get('env') === 'development' ? err : {};
 // render the error page
   res.status(err.status || 500);
   res.render('error');
 });
 module.exports = app;

Create Route

Next We need to create one route file name customers.js inside routes folder. After created this file, We will implement crud logic in this file. Go to routes/customers.js and use the below code :

var express = require('express');
var router = express.Router();
var connection  = require('../lib/db');
 
 
/* GET home page. */
router.get('/', function(req, res, next) {
      
 connection.query('SELECT * FROM customers ORDER BY id desc',function(err,rows)     {
 
        if(err){
         req.flash('error', err); 
         res.render('customers',{page_title:"Customers - Node.js",data:''});   
        }else{
            
            res.render('customers',{page_title:"Customers - Node.js",data:rows});
        }
                            
         });
        
    });
 
 
// SHOW ADD USER FORM
router.get('/add', function(req, res, next){    
    // render to views/user/add.ejs
    res.render('customers/add', {
        title: 'Add New Customers',
        name: '',
        email: ''        
    })
})
 
// ADD NEW USER POST ACTION
router.post('/add', function(req, res, next){    
    req.assert('name', 'Name is required').notEmpty()           //Validate name
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
  
    var errors = req.validationErrors()
     
    if( !errors ) {   //No errors were found.  Passed Validation!
         
     
        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
         
     connection.query('INSERT INTO customers SET ?', user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                     
                    // render to views/user/add.ejs
                    res.render('customers/add', {
                        title: 'Add New Customer',
                        name: user.name,
                        email: user.email                    
                    })
                } else {                
                    req.flash('success', 'Data added successfully!');
                    res.redirect('/customers');
                }
            })
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })                
        req.flash('error', error_msg)        
         
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/add', { 
            title: 'Add New Customer',
            name: req.body.name,
            email: req.body.email
        })
    }
})
 
// SHOW EDIT USER FORM
router.get('/edit/(:id)', function(req, res, next){
   
connection.query('SELECT * FROM customers WHERE id = ' + req.params.id, function(err, rows, fields) {
            if(err) throw err
             
            // if user not found
            if (rows.length <= 0) {
                req.flash('error', 'Customers not found with id = ' + req.params.id)
                res.redirect('/customers')
            }
            else { // if user found
                // render to views/user/edit.ejs template file
                res.render('customers/edit', {
                    title: 'Edit Customer', 
                    //data: rows[0],
                    id: rows[0].id,
                    name: rows[0].name,
                    email: rows[0].email                    
                })
            }            
        })
  
})
 
// EDIT USER POST ACTION
router.post('/update/:id', function(req, res, next) {
    req.assert('name', 'Name is required').notEmpty()           //Validate nam           //Validate age
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
  
    var errors = req.validationErrors()
     
    if( !errors ) {   
 
        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
         
connection.query('UPDATE customers SET ? WHERE id = ' + req.params.id, user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                     
                    // render to views/user/add.ejs
                    res.render('customers/edit', {
                        title: 'Edit Customer',
                        id: req.params.id,
                        name: req.body.name,
                        email: req.body.email
                    })
                } else {
                    req.flash('success', 'Data updated successfully!');
                    res.redirect('/customers');
                }
            })
         
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })
        req.flash('error', error_msg)
         
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/edit', { 
            title: 'Edit Customer',            
            id: req.params.id, 
            name: req.body.name,
            email: req.body.email
        })
    }
})
       
// DELETE USER
router.get('/delete/(:id)', function(req, res, next) {
    var user = { id: req.params.id }
     
connection.query('DELETE FROM customers WHERE id = ' + req.params.id, user, function(err, result) {
            //if(err) throw err
            if (err) {
                req.flash('error', err)
                // redirect to users list page
                res.redirect('/customers')
            } else {
                req.flash('success', 'Customer deleted successfully! id = ' + req.params.id)
                // redirect to users list page
                res.redirect('/customers')
            }
        })
   })
 
 
module.exports = router;

Create views

First we will create one foleder name customers inside the views folder.

Next we need to create three views file name add.ejs, edit.ejs and index.ejs. We will create three view files inside the views/customers folder.

Create first file index.ejs

Index.ejs file, we will display the list of customers.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <div>
    <a href="/" class="btn btn-primary ml-3">Home</a>  
    <a href="/customers/add" class="btn btn-secondary ml-3">New Customer</a> 
    <a href="/customers" class="btn btn-info ml-3">Customer List</a>
</div>    
<!--   <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %> -->
  
<% if (messages.success) { %>
    <p class="alert alert-success mt-4"><%- messages.success %></p>
<% } %>  
<br>
  <table class="table">
<thead>
  <tr>
    <th scope="col">#</th>
    <th scope="col">Name</th>
    <th scope="col">Email</th>
    <th width="200px">Action</th>
 
  </tr>
</thead>
<tbody>
  <% if(data.length){
 
  for(var i = 0; i< data.length; i++) {%>  
  <tr>
    <th scope="row"><%= (i+1) %></th>
    <td><%= data[i].name%></td>
    <td><%= data[i].email%></td>
    <td>
    <a class="btn btn-success edit" href="../customers/edit/<%=data[i].id%>">Edit</a>                       
    <a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../customers/delete/<%=data[i].id%>">Delete</a>                       
   </td>
  </tr>
  <% }
           
   }else{ %>
       <tr>
          <td colspan="3">No user</td>
       </tr>
    <% } %>    
  
</tbody>
</table>
</body>
</html>

Create second file name add.ejs

Add.ejs file, we will create form for sending to data in database.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %>
  
<% if (messages.success) { %>
    <p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/customers/add" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" name="email" class="form-control" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="">
</div>
 
<input type="submit" class="btn btn-primary" value="Add">
</form>
</body>
</html>

Create third file name edit.ejs

Next create last file edit.ejs, we will to edit data in this form.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<form action="/customers/update/<%= id %>" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="<%= name %>" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" class="form-control" name="email" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="<%= email %>">
  
</div>
 
<button type="submit" class="btn btn-info">Update</button>
</form>
</body>
</html>

Test Node js Crud app

run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/customers

Conclusion

In this node express js crud tutorial – We have created node js crud (create, read, update, delete) application with mysql database. We have also successfully install node js framework express with use ejs templating engine.

Learn More

MySQL Databases With Python Tutorial

Build a Basic CRUD App with Node and React

Build a Simple CRUD App with Python, Flask, and React

Build a Basic CRUD App with Laravel and Vue

Build a Simple CRUD App with Spring Boot and Vue.js

Build a Basic CRUD App with Laravel and Angular

Build a Basic CRUD App with Laravel and React

Express.js & Node.js Course for Beginners - Full Tutorial

Build a CRUD App with Angular and Firebase

Angular 7 + Spring Boot CRUD Example

The Complete Node.js Developer Course (3rd Edition)

Angular & NodeJS - The MEAN Stack Guide

NodeJS - The Complete Guide (incl. MVC, REST APIs, GraphQL)

Node.js: The Complete Guide to Build RESTful APIs (2018)

Building a full CRUD Angular 8 Universal and MongoDB SSR

Building a full CRUD Angular 8 Universal and MongoDB SSR

A comprehensive step by step tutorial to building a full CRUD Angular 8 Universal and MongoDB Server-side Rendering (SSR)

A comprehensive step by step tutorial to building a full CRUD Angular 8 Universal and MongoDB Server-side Rendering (SSR)

We will practically use Angular 8 universal for a CRUD (Create, Read, Update, Delete) operation application that store the data to the MongoDB server.

The Angular 8 application that we will build is a very simple application about Article CRUD. This might be a starting point to build your own complete blog engine or news engine because this application should be matched for SEO requirements.

Almost all codes in this application using Typescript and ES6 except a few files that still use plain ES5 code that uses by Express.js. For that, we need your suggestion for converting to working Angular 8/Typescript code styles. The following tools, frameworks, and modules are required for this tutorial:

As usual, before we move forward to the main steps of Angular 8 Universal and MongoDB Server-side Rendering (SSR). We have to check the latest Node and Angular versions then update them if necessary. Just type this command to see it.

ng version

You will see the currently installed Node, Angular and Angular CLI versions.

Angular CLI: 8.0.6
Node: 10.15.1
OS: darwin x64
Angular: 8.0.3
Create a New Angular 8 Application and Add Angular Universal

First, we will create a new Angular 8 application by type this command in the terminal.

ng new article-crud

Next, go to the newly created Angular 8 application folder then type this command to add Angular Universal SSR.

ng add @nguniversal/express-engine --clientProject article-crud

That command just added and updated a few files.

CREATE src/main.server.ts (361 bytes)
CREATE src/app/app.server.module.ts (427 bytes)
CREATE tsconfig.server.json (204 bytes)
CREATE webpack.server.config.js (1466 bytes)
CREATE server.ts (1980 bytes)
UPDATE package.json (1844 bytes)
UPDATE angular.json (4263 bytes)
UPDATE src/main.ts (432 bytes)
UPDATE src/app/app.module.ts (438 bytes)

Next, check the Angular Universal SSR application by running this application using these commands.

npm run build:ssr && npm run serve:ssr

Now, after go to localhost:4000 you see this standard Angular application page and feel the difference with the page that rendered in the browser.

Angular 8 Universal

Install and Configure Mongoose.js and Required Modules

To store and retrieve data from the MongoDB server, we will use Mongoose.js. To install it, just run these commands that including body-parser modules.

npm install --save mongoose body-parser

In this Angular 8 Universal SSR, body parser will use to parse the request body to the API. Next, open and edit server.ts in the root project folder then add these imports.

import * as mongoose from 'mongoose';
import bodyParser from "body-parser";

Next, add these lines of codes after the imports lines to connect to the MongoDB and use body-parser for every JSON requests.

mongoose.connect('mongodb://localhost/angular8-crud', { useNewUrlParser: true, useFindAndModify: false })
  .then(() =>  console.log('connection successful'))
  .catch((err) => console.error(err));

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
Create a Mongoose Model and Router

We will create a Mongoose model that represent MongoDB fields. Create a models folder and a file to holds all required Article fields.

mkdir models
touch models/article.ts

Next, fill that file with this Mongoose Schema.

import mongoose, { Schema } from 'mongoose';

const ArticleSchema: Schema = new Schema({
  title: { type: String, required: true },
  author: { type: String, required: true },
  description: { type: String, required: true },
  content: { type: String, required: true },
  updatedAt: { type: Date, default: Date.now }
});

export default mongoose.model('Article', ArticleSchema);

Next, we will create a route that accesses the MongoDB data via API. Create a new folder call routes and a file inside it.

mkdir routes
touch routes/article-route.ts

Open and edit that file then add these imports.

import { Request, Response, NextFunction } from 'express';
import Article from '../models/article';

Add these lines of codes to create a CRUD method of RESTful API. The

route path starts with /api/ that will be called from the Angular 8

application using full URL [http://localhost:4000/api/.](http://localhost:4000/api/`.)

export class ArticleRoute {

  public articleRoute(app): void {
    app.route('/api/').get((req: Request, res: Response, next: NextFunction) => {
      Article.find((err, articles) => {
        if (err) { return next(err); }
        res.json(articles);
      });
    });

    app.route('/api/:id').get((req: Request, res: Response, next: NextFunction) => {
      Article.findById(req.params.id, (err, article) => {
        if (err) { return next(err); }
        res.json(article);
      });
    });

    app.route('/api/').post((req: Request, res: Response, next: NextFunction) => {
      console.log(req.body);
      Article.create(req.body, (err, article) => {
        if (err) { return next(err); }
        res.json(article);
      });
    });

    app.route('/api/:id').put((req: Request, res: Response, next: NextFunction) => {
      Article.findByIdAndUpdate(req.params.id, req.body, (err, article) => {
        if (err) { return next(err); }
        res.json(article);
      });
    });

    app.route('/api/:id').delete((req: Request, res: Response, next: NextFunction) => {
      Article.findByIdAndRemove(req.params.id, req.body, (err, article) => {
        if (err) { return next(err); }
        res.json(article);
      });
    });
  }
}

Next, open and edit server.ts then add this import.

import { ArticleRoute } from './routes/article-route';

Declare a constant after the import lines.

const articleRoute: ArticleRoute = new ArticleRoute();

Add this line before the all regular route lines.

articleRoute.articleRoute(app);

Don't declare API route after all regular route otherwise the API URL won't working.

Test the Angular 8 Universal SSR RESTful API

We will test the Angular 8 Universal SSR RESTful API using the Postman app. First, run the app using this command.

npm run build:ssr && npm run serve:ssr

Open the postman app then change the method to GET and address to localhost:4000/api/ then press SEND button. If there's any data in your MongoDB server then it's will be similar like this.

MongoDB SSR

To save data, change the method to POST with the same address then choose RAW body with type JSON. Fill the RAW body with this example or your own data example.

{
    "title": "Odong",
    "author": "Odong",
    "description": "Odong",
    "content": "Odong"
}

You will see this result for successful POST data.

CRUD Angular 8

Next, you can test the rest of PUT and DELETE using your own data to make sure the Angular 8 Universal SSR API working.

Create Angular 8 Routes for Page Navigation

To create Angular 8 Routes for navigation between Angular 8 pages/component, add or generate all required component.

ng g component articles --skip-import
ng g component show-article --skip-import
ng g component add-article --skip-import
ng g component edit-article --skip-import

There's an additional --skip-import variable because there will be a conflict between 2 modules, Angular 8 and Server modules.

More than one module matches. Use skip-import option to skip importing the component into the closest module.

Next, we will manually add or register those components to the src/app/app.module.ts. Add these imports to that file.

import { ArticlesComponent } from './articles/articles.component';
import { ShowArticleComponent } from './show-article/show-article.component';
import { AddArticleComponent } from './add-article/add-article.component';
import { EditArticleComponent } from './edit-article/edit-article.component';

Then add those components to @NgModule declaration.

@NgModule({
  declarations: [
    ...
    ArticlesComponent,
    ShowArticleComponent,
    AddArticleComponent,
    EditArticleComponent
  ],
  ...
})

Next, open and edit src/app/app-routing.module.ts then add these imports.

const routes: Routes = [
  {
    path: 'articles',
    component: ArticlesComponent,
    data: { title: 'Articles' }
  },
  {
    path: 'show-article/:id',
    component: ShowArticleComponent,
    data: { title: 'Show Article' }
  },
  {
    path: 'add-article',
    component: AddArticleComponent,
    data: { title: 'Add Article' }
  },
  {
    path: 'edit-article/:id',
    component: EditArticleComponent,
    data: { title: 'Edit Article' }
  },
  { path: '',
    redirectTo: '/articles',
    pathMatch: 'full'
  }
];

Open and edit src/app/app.component.html and you will see the existing router outlet. Next, modify this HTML page to fit the CRUD page.


  

Open and edit src/app/app.component.scss then replace all SASS codes with this.

.container {
  padding: 20px;
}
Create an Angular 8 Service using HttpClient, Observable and RXJS

To access RESTful API from Angular 8, we need to create an Angular 8 service which will handle all POST, GET, UPDATE, DELETE requests. The response from the RESTful API emitted by Observable that can subscribe and read from the Components. For error handler and data Extraction, we will use RXJS Library. Before creating a service for RESTful API access, first, we have to install or register HttpClientModule. Open and edit src/app/app.module.ts then add this import.

import { FormsModule } from '@angular/forms';
import { HttpClientModule } from '@angular/common/http';

Add it to @NgModule imports after BrowserModule.

imports: [
  BrowserModule,
  FormsModule,
  HttpClientModule,
  AppRoutingModule
],

We will use type specifier to get a typed result object. For that, create a new Typescript file src/app/article.ts then add these lines of Typescript codes.

export class Article {
  _id: string;
  title: string;
  author: string;
  description: string;
  content: string;
  updatedAt: Date;
}

Next, generate an Angular 8 service by typing this command.

ng g service api

Next, open and edit src/app/api.service.ts then add these imports.

import { Observable, of, throwError } from 'rxjs';
import { HttpClient, HttpHeaders, HttpErrorResponse } from '@angular/common/http';
import { catchError, tap, map } from 'rxjs/operators';
import { Article } from './article';

Add these constants before the @Injectable.

const httpOptions = {
  headers: new HttpHeaders({'Content-Type': 'application/json'})
};
const apiUrl = "/api/";

Inject HttpClient module to the constructor.

constructor(private http: HttpClient) { }

Add the error handler function.

private handleError(operation = 'operation', result?: T) {
  return (error: any): Observable => {
    console.error(error); // log to console instead
    return of(result as T);
  };
}

Add the functions for all CRUD (create, read, update, delete) RESTful call of article data.

getArticles(): Observable {
  return this.http.get(apiUrl)
    .pipe(
      tap(articles => console.log('fetched Articles')),
      catchError(this.handleError('getArticles', []))
    );
}

getArticle(id: number): Observable {
  const url = `${apiUrl}/${id}`;
  return this.http.get(url).pipe(
    tap(_ => console.log(`fetched Article id=${id}`)),
    catchError(this.handleError(`getArticle id=${id}`))
  );
}

addArticle(article: Article): Observable {
  return this.http.post(apiUrl, article, httpOptions).pipe(
    tap((art: Article) => console.log(`added Article w/ id=${art._id}`)),
    catchError(this.handleError('addArticle'))
  );
}

updateArticle(id: any, article: Article): Observable {
  const url = `${apiUrl}/${id}`;
  return this.http.put(url, article, httpOptions).pipe(
    tap(_ => console.log(`updated Article id=${id}`)),
    catchError(this.handleError('updateArticle'))
  );
}

deleteArticle(id: any): Observable {
  const url = `${apiUrl}/${id}`;
  return this.http.delete(url, httpOptions).pipe(
    tap(_ => console.log(`deleted Article id=${id}`)),
    catchError(this.handleError('deleteArticle'))
  );
}

You can find more details about Angular 8 Observable and RXJS here.

Display List of Articles using Angular 8 Material

We will display the list of articles published from API Service. The data published from the API service read by subscribing as an Article model in the Angular 8 component. For that, open and edit src/app/articles/articles.component.ts then add these imports.

import { ApiService } from '../api.service';

Next, inject the API Service to the constructor.

constructor(private api: ApiService) { }

Next, for the user interface (UI) we will use Angular 8 Material and CDK. There's a CLI for generating a Material component like Table as a component, but we will create or add the Table component from scratch to existing component. Type this command to install Angular 8 Material.

ng add @angular/material

If there are questions like below, just use the default answer.

? Choose a prebuilt theme name, or "custom" for a custom theme: Purple/Green       [ Preview: h
ttps://material.angular.io?theme=purple-green ]
? Set up HammerJS for gesture recognition? Yes
? Set up browser animations for Angular Material? Yes

We will register all required Angular 8 Material components or modules to src/app/app.module.ts. Open and edit that file then add these imports.

import {
  MatInputModule,
  MatPaginatorModule,
  MatProgressSpinnerModule,
  MatSortModule,
  MatTableModule,
  MatIconModule,
  MatButtonModule,
  MatCardModule,
  MatFormFieldModule } from "@angular/material";

Also, modify FormsModule import to add ReactiveFormsModule.

import { FormsModule, ReactiveFormsModule } from '@angular/forms';

Register the above modules to @NgModule imports.

imports: [
  BrowserModule,
  FormsModule,
  HttpClientModule,
  AppRoutingModule,
  ReactiveFormsModule,
  BrowserAnimationsModule,
  MatInputModule,
  MatTableModule,
  MatPaginatorModule,
  MatSortModule,
  MatProgressSpinnerModule,
  MatIconModule,
  MatButtonModule,
  MatCardModule,
  MatFormFieldModule
],

Next, back to src/app/articles/articles.component.ts then add these imports.

import { Article } from '../article';

Declare the variables of Angular 8 Material Table Data Source before the constructor.

displayedColumns: string[] = ['title', 'author'];
data: Article[] = [];
isLoadingResults = true;

Modify the ngOnInit function to get list of articles immediately.

ngOnInit() {
  this.api.getArticles()
    .subscribe((res: any) => {
      this.data = res;
      console.log(this.data);
      this.isLoadingResults = false;
    }, err => {
      console.log(err);
      this.isLoadingResults = false;
    });
}

Next, open and edit src/app/articles/articles.component.html then replace all HTML tags with this Angular Material tags.


  <div class="example-loading-shade"
       *ngIf="isLoadingResults">
    
  
  
    add
  
  
    <table mat-table [dataSource]="data" class="example-table"
           matSort matSortActive="title" matSortDisableClear matSortDirection="asc">

      
      
        Title
        {{row.title}}
      

      
      
        Author
        $ {{row.author}}
      

      
      
    
  

Finally, to make a little UI adjustment, open and edit src/app/articles/articles.component.scss then add this CSS codes.

/* Structure */
.example-container {
  position: relative;
  padding: 5px;
}

.example-table-container {
  position: relative;
  max-height: 400px;
  overflow: auto;
}

table {
  width: 100%;
}

.example-loading-shade {
  position: absolute;
  top: 0;
  left: 0;
  bottom: 56px;
  right: 0;
  background: rgba(0, 0, 0, 0.15);
  z-index: 1;
  display: flex;
  align-items: center;
  justify-content: center;
}

.example-rate-limit-reached {
  color: #980000;
  max-width: 360px;
  text-align: center;
}

/* Column Widths */
.mat-column-number,
.mat-column-state {
  max-width: 64px;
}

.mat-column-created {
  max-width: 124px;
}

.mat-flat-button {
  margin: 5px;
}
Show and Delete an Article Details using Angular 8 Material

To show article details after click or tap on the one of a row inside the Angular 8 Material table, open and edit src/app/show-article/show-article.component.ts then add these imports.

import { ActivatedRoute, Router } from '@angular/router';
import { ApiService } from '../api.service';
import { Article } from '../article';

Inject above modules to the constructor.

constructor(private route: ActivatedRoute, private api: ApiService, private router: Router) { }

Declare the variables before the constructor for hold article data that get from the API.

article: Article = { _id: '', title: '', author: '', description: '', content: '', updatedAt: null };
isLoadingResults = true;

Add a function for getting Article data from the API.

getArticleDetails(id: any) {
  this.api.getArticle(id)
    .subscribe((data: any) => {
      this.article = data;
      console.log(this.article);
      this.isLoadingResults = false;
    });
}

Call that function when the component is initiated.

ngOnInit() {
  this.getArticleDetails(this.route.snapshot.params.id);
}

Add this function for delete article.

deleteArticle(id: any) {
  this.isLoadingResults = true;
  this.api.deleteArticle(id)
    .subscribe(res => {
        this.isLoadingResults = false;
        this.router.navigate(['/articles']);
      }, (err) => {
        console.log(err);
        this.isLoadingResults = false;
      }
    );
}

For the view, open and edit src/app/article-detail/article-detail.component.html then replace all HTML tags with this.


  <div class="example-loading-shade"
       *ngIf="isLoadingResults">
    
  
  
    list
  
  
    
      ## {{article.title}}

      {{article.author}} | {{article.updatedAt}}
    
    
      ### {{article.description}}

      
{{article.content}}

    
    
      edit
      delete
    
  

Finally, open and edit src/app/article-detail/article-detail.component.scss then add this lines of CSS codes.

/* Structure */
.example-container {
  position: relative;
  padding: 5px;
}

.example-loading-shade {
  position: absolute;
  top: 0;
  left: 0;
  bottom: 56px;
  right: 0;
  background: rgba(0, 0, 0, 0.15);
  z-index: 1;
  display: flex;
  align-items: center;
  justify-content: center;
}

.mat-flat-button {
  margin: 5px;
}
Add an Article using Angular 8 Material

To create a form for adding a Article, open and edit src/app/add-article/add-article.component.ts then add these imports.

import { Router } from '@angular/router';
import { ApiService } from '../api.service';
import { FormControl, FormGroupDirective, FormBuilder, FormGroup, NgForm, Validators } from '@angular/forms';

Inject above modules to the constructor.

constructor(private router: Router, private api: ApiService, private formBuilder: FormBuilder) { }

Declare variables for the Form Group and all of the required fields inside the form before the constructor.

articleForm: FormGroup;
title = '';
author = '';
description = '';
content = '';
isLoadingResults = false;

Add initial validation for each field.

ngOnInit() {
  this.articleForm = this.formBuilder.group({
    'title' : [null, Validators.required],
    'author' : [null, Validators.required],
    'description' : [null, Validators.required],
    'content' : [null, Validators.required]
  });
}

Create a function for submitting or POST article form.

onFormSubmit() {
  this.isLoadingResults = true;
  this.api.addArticle(this.articleForm.value)
    .subscribe((res: any) => {
        const id = res._id;
        this.isLoadingResults = false;
        this.router.navigate(['/show-article', id]);
      }, (err: any) => {
        console.log(err);
        this.isLoadingResults = false;
      });
}

Next, add this import for implementing ErrorStateMatcher.

import { ErrorStateMatcher } from '@angular/material/core';

Create a new class before the main class @Components.

/** Error when invalid control is dirty, touched, or submitted. */
export class MyErrorStateMatcher implements ErrorStateMatcher {
  isErrorState(control: FormControl | null, form: FormGroupDirective | NgForm | null): boolean {
    const isSubmitted = form && form.submitted;
    return !!(control && control.invalid && (control.dirty || control.touched || isSubmitted));
  }
}

Instantiate that MyErrorStateMatcher as a variable in main class.

matcher = new MyErrorStateMatcher();

Next, open and edit src/app/add-article/add-article.component.html then replace all HTML tags with this.


  <div class="example-loading-shade"
       *ngIf="isLoadingResults">
    
  
  
    list
  
  
    
      
        <input matInput placeholder="Title" formControlName="title"
               [errorStateMatcher]="matcher">
        
          Please enter Title
        
      
      
        <input matInput placeholder="Author" formControlName="author"
               [errorStateMatcher]="matcher">
        
          Please enter Author
        
      
      
        <input matInput placeholder="Description" formControlName="description"
               [errorStateMatcher]="matcher">
        
          Please enter Description
        
      
      
        <textarea matInput placeholder="Content" formControlName="content"
               [errorStateMatcher]="matcher">
        
          Please enter Content
        
      
      
        save
      
    
  

Finally, open and edit src/app/article-add/article-add.component.scss then add this CSS codes.

/* Structure */
.example-container {
  position: relative;
  padding: 5px;
}

.example-form {
  min-width: 150px;
  max-width: 500px;
  width: 100%;
}

.example-full-width {
  width: 100%;
}

.example-full-width:nth-last-child(0) {
  margin-bottom: 10px;
}

.button-row {
  margin: 10px 0;
}

.mat-flat-button {
  margin: 5px;
}
Edit an Article using Angular 8 Material

We have put an edit button inside the Article Detail component for call Edit page. Now, open and edit src/app/edit-article/edit-article.component.ts then add these imports.

import { Router, ActivatedRoute } from '@angular/router';
import { ApiService } from '../api.service';
import { FormControl, FormGroupDirective, FormBuilder, FormGroup, NgForm, Validators } from '@angular/forms';

Inject above modules to the constructor.

constructor(private router: Router, private route: ActivatedRoute, private api: ApiService, private formBuilder: FormBuilder) { }

Declare the Form Group variable and all of the required variables for the article form before the constructor.

articleForm: FormGroup;
_id = '';
title = '';
author = '';
description = '';
content = '';
isLoadingResults = false;

Next, add validation for all fields when the component is initiated.

ngOnInit() {
  this.getArticle(this.route.snapshot.params.id);
  this.articleForm = this.formBuilder.group({
    'title' : [null, Validators.required],
    'author' : [null, Validators.required],
    'description' : [null, Validators.required],
    'content' : [null, Validators.required]
  });
}

Create a function for getting article data that filled to each form fields.

getArticle(id: any) {
  this.api.getArticle(id).subscribe((data: any) => {
    this._id = data._id;
    this.articleForm.setValue({
      title: data.title,
      author: data.author,
      description: data.description,
      content: data.content
    });
  });
}

Create a function to update the article changes.

onFormSubmit() {
  this.isLoadingResults = true;
  this.api.updateArticle(this._id, this.articleForm.value)
    .subscribe((res: any) => {
        const id = res._id;
        this.isLoadingResults = false;
        this.router.navigate(['/show-article', id]);
      }, (err: any) => {
        console.log(err);
        this.isLoadingResults = false;
      }
    );
}

Add a function for handling the show article details button.

articleDetails() {
  this.router.navigate(['/show-article', this._id]);
}

Next, add this import for implementing ErrorStateMatcher.

import { ErrorStateMatcher } from '@angular/material/core';

Create a new class before the main class @Components.

/** Error when invalid control is dirty, touched, or submitted. */
export class MyErrorStateMatcher implements ErrorStateMatcher {
  isErrorState(control: FormControl | null, form: FormGroupDirective | NgForm | null): boolean {
    const isSubmitted = form && form.submitted;
    return !!(control && control.invalid && (control.dirty || control.touched || isSubmitted));
  }
}

Instantiate that MyErrorStateMatcher as a variable in main class.

matcher = new MyErrorStateMatcher();

Next, open and edit src/app/edit-article/edit-article.component.html then replace all HTML tags with this.


  <div class="example-loading-shade"
       *ngIf="isLoadingResults">
    
  
  
    info
  
  
    
      
        <input matInput placeholder="Title" formControlName="title"
               [errorStateMatcher]="matcher">
        
          Please enter Title
        
      
      
        <input matInput placeholder="Author" formControlName="author"
               [errorStateMatcher]="matcher">
        
          Please enter Author
        
      
      
        <input matInput placeholder="Description" formControlName="description"
               [errorStateMatcher]="matcher">
        
          Please enter Description
        
      
      
        <textarea matInput placeholder="Content" formControlName="content"
               [errorStateMatcher]="matcher">
        
          Please enter Content
        
      
      
        save
      
    
  

Finally, open and edit src/app/edit-article/edit-article.component.scss then add this lines of CSS codes.

/* Structure */
.example-container {
  position: relative;
  padding: 5px;
}

.example-form {
  min-width: 150px;
  max-width: 500px;
  width: 100%;
}

.example-full-width {
  width: 100%;
}

.example-full-width:nth-last-child(0) {
  margin-bottom: 10px;
}

.button-row {
  margin: 10px 0;
}

.mat-flat-button {
  margin: 5px;
}
Test and Run Angular 8 Universal and MongoDB Server-side Rendering (SSR)

It's time to test and try the performance of Angular 8 Universal and MongoDB Server-side Rendering (SSR). First, run the MongoDB server in the different terminal or command line.

mongod

Next, run the Angular 8 Universal SSR in the current terminal and project folder.

npm run build:ssr && npm run serve:ssr

After build finished for both client and server then open the

application in the browser [http://localhost:4200/](http://localhost:4200/`) and you will see and

feel this Angular 8 application as below.

That it's, the example of Angular 8 Universal and MongoDB Server-side Rendering (SSR). You can get the full source codes from our Github.