Photo by Tobias Fischer on Unsplash

Level: Web Developer Beginner

If you are new to the web developer world, you may be wondering what comes after the HTML and JavaScript track on w3schools. The static website project has a couple of interactive buttons and animations, but how do we display dynamic data?

This article is for you! We will cover the following:

Setting up a local MySQL database on your laptop with Docker.

Installing a database package into your express app.

Setting up Database connection on startup.

Reading from the Database when a request happens.

Writing into the Database when a request happens.

Part 2: what if we are using TypeScript? Or another web framework?

Step 0: The Setup

Pick a nice Editor. I use WebStorm with Prettier enabled. Install Docker on your local environment. You can find a direct download link for Mac here: https://download.docker.com/mac/stable/Docker.dmg Is Node available? Check with running node -v . If you do not have Node.JS yet, check out https://github.com/nvm-sh/nvm Is Git available? Check with running git in the command line. On Mac, typing git without it installed yet will prompt you to install it. Have you signed up with Github, and go through with the setup? We will be cloning an existing example from there! Ok, find a local folder, and git clone git@github.com:teamzerolabs/config-service-reference.git

Step 1: Spinning up the database

Go to the db-setup folder, you will find two files:

.env.template docker-compose.yml

Make a copy of the .env.template file, call it .env . And, open it up in the IDE and you should see:

MYSQL_ROOT_PASSWORD=pick_a_password_here

Remember to change this to your own password! We will be using this one to connect into the database.

In the same folder, type docker-compose up -d . This will read the docker-compose.yml and .env . It will download the docker image of mysql8 , and set it up for you.

Afterwards, you should be able to check if mysql is running or not like this:

➜ config-service-reference git:(master) docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

a3138281dc6a mysql:8.0 "docker-entrypoint.s…" 4 weeks ago Up 7 days 0.0.0.0:3306->3306/tcp, 33060/tcp harmonize-portal_mysql_1

Ok, we can see mysql:8.0 when we type docker ps . Next, try to connect into it with a database client, I recommend downloading the trial of DataGrip.

Click the plus sign/Data Source/MySQL

Give the Data Source a name: “config-test”, copy the password you have defined earlier, and click on Test Connection.

Yep, this means the setup is successful. Click OK

At this point, MySQL is running, but we do not have a database inside yet. Each MySQL process can host multiple databases. You can think of them as a folder with many Excel files. Each Excel file contains one or many tables, and each table contains one or many rows.

Step 2: Defining Tables with Node.JS and Sequelize

We are now ready to connect into the database from Node.JS! Go to the node-starthere folder, and take a look at the package.json file:

{

"name": "node-starthere",

"version": "1.0.0",

"main": "main.js",

"scripts": {

"start": "node main.js"

},

"license": "MIT",

"dependencies": {

"express": "^4.17.1",

"mysql2": "^2.1.0",

"sequelize": "^5.21.3"

}

}

3 dependencies are there:

express — The da facto server framework in Node.JS, it will help with running the web server on a port, and run code when receiving requests.

mysql2 — We use this to connect into the database. It also allows us to run Raw SQL queries (with out model definitions) on it. We will cover how to do so in future articles!

sequelize — An ORM database framework, we use this to define tables, and work with those tables.

Run yarn install , or npm install to pull the dependencies.

Let’s look at the database connection and model definition, open up models/index.js

const { Sequelize, Model, DataTypes } = require("sequelize");



const sequelize = new Sequelize(

"configexample",

"root",

"<password_we_picked_earlier>",

{

dialect: "mysql",

host: "127.0.0.1",

port: 3306

}

);



class Book extends Model {}

Book.init(

{

name: DataTypes.STRING,

author: DataTypes.STRING,

publishedDate: DataTypes.DATE

},

{ sequelize, modelName: "book" }

);



sequelize.sync().then(async () => {

const bookCount = await Book.count();



if (bookCount > 0) {

return;

}



Book.bulkCreate([

{

name: "The Book of Five Rings",

author: "Miyamoto Musashi",

publishedDate: new Date(1645, 0, 1)

},

{

name: "The Art of War",

author: "Sun Tzu",

// Right, I know this was published in BC, but mysql doesn't handle that!

publishedDate: new Date(500, 0, 1)

},

{

name: "Language in Thought and Action",

author: "S. I. Hayakawa",

publishedDate: new Date(1949, 0, 1)

}

]);

});



module.exports.Book = Book;

(The configuration is hard coded in the beginner example, but if you follow the configuration article, you will see that we eventually move those out to environment variables!)

This file does 4 things:

Setup connection, notice that I am calling the Database “configexample” above. You can change this to any name, of course. Define Table “Book”, with 3 fields inside. Create the table via sequelize.sync() . And, insert 3 book records into the table. Export the Model class Book

Let’s run the project and see what happens! Run node main.js

Hi, we will spin up a little server that loads books from a local database

Book Example app listening on port 3000!

Executing (default): CREATE TABLE IF NOT EXISTS `books` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `author` VARCHAR(255), `publishedDate` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

Executing (default): SHOW INDEX FROM `books`

Executing (default): SELECT count(*) AS `count` FROM `books` AS `book`;

Executing (default): INSERT INTO `books` (`id`,`name`,`author`,`publishedDate`,`createdAt`,`updatedAt`) VALUES (NULL,'The Book of Five Rings','Miyamoto Musashi','1645-01-01 07:52:58','2020-02-16 16:11:41','2020-02-16 16:11:41'),(NULL,'The Art of War','Sun Tzu','0500-01-01 07:52:58','2020-02-16 16:11:41','2020-02-16 16:11:41'),(NULL,'Language in Thought and Action','S. I. Hayakawa','1949-01-01 07:00:00','2020-02-16 16:11:41','2020-02-16 16:11:41');

Ok, now the server is up and running. You can see the books being returned by opening a new tab in Chrome and going to http://localhost:3000/books :

[

{

"id": 1,

"name": "The Book of Five Rings",

"author": "Miyamoto Musashi",

"publishedDate": "1645-01-01T07:52:58.000Z",

"createdAt": "2020-02-16T16:11:41.000Z",

"updatedAt": "2020-02-16T16:11:41.000Z"

},

{

"id": 2,

"name": "The Art of War",

"author": "Sun Tzu",

"publishedDate": "0500-01-01T07:52:58.000Z",

"createdAt": "2020-02-16T16:11:41.000Z",

"updatedAt": "2020-02-16T16:11:41.000Z"

},

{

"id": 3,

"name": "Language in Thought and Action",

"author": "S. I. Hayakawa",

"publishedDate": "1949-01-01T07:00:00.000Z",

"createdAt": "2020-02-16T16:11:41.000Z",

"updatedAt": "2020-02-16T16:11:41.000Z"

}

]

Weird, we only defined 3 fields: name, author, and publishedDate, but 3 more got added:

id

createdAt

updatedAt

Turns out Sequelize was adding these fields for us by default!

We can also see the tables via DataGrip, go back to the connection you have setup, change the properties on it.

We can now specify a Database name, and Test Connection on it.

Click Apply and then OK.

Now we have tables under schemas:

You can modify the values here as well, try changing The Art of War to The Art of Peacekeeping .

First edit the field, and then right click to Submit

Refresh the browser tab, and new data will show up!

[

{

"id": 1,

"name": "The Book of Five Rings",

"author": "Miyamoto Musashi",

"publishedDate": "1645-01-01T07:52:58.000Z",

"createdAt": "2020-02-16T16:11:41.000Z",

"updatedAt": "2020-02-16T16:11:41.000Z"

},

{

"id": 2,

"name": "The Art of Peacekeeping",

"author": "Sun Tzu",

"publishedDate": "0500-01-01T07:52:58.000Z",

"createdAt": "2020-02-16T16:11:41.000Z",

"updatedAt": "2020-02-16T16:11:41.000Z"

},

{

"id": 3,

"name": "Language in Thought and Action",

"author": "S. I. Hayakawa",

"publishedDate": "1949-01-01T07:00:00.000Z",

"createdAt": "2020-02-16T16:11:41.000Z",

"updatedAt": "2020-02-16T16:11:41.000Z"

}

]

Step 3: Looking at Reading operation

Here is the main.js :

console.log(

"Hi, we will spin up a little server that loads books from a local database"

);



const models = require("./models");



const express = require("express");

const app = express();

const port = 3000;



app.get("/books", async (req, res) => {

res.json(await models.Book.findAll());

});



app.listen(port, () =>

console.log(`Book Example app listening on port ${port}!`)

);

We import the models with const models = require(“./models”); , and grab the data by models.Book.findAll() .

Let’s do a little exercise and hide the return value for id , createdAt , and updatedAt because they are database details.

Do the filtering in Node.JS:

app.get("/books", async (req, res) => {

const books = await models.Book.findAll();

res.json(books.map((book) => {

return {

name: book.name,

author: book.author,

publishedDate: book.publishedDate

}

}));

});

Re-run the example, and refresh in the browser, and see the result!

[

{

"name": "The Book of Five Rings",

"author": "Miyamoto Musashi",

"publishedDate": "1645-01-01T07:52:58.000Z"

},

{

"name": "The Art of Peacekeeping",

"author": "Sun Tzu",

"publishedDate": "0500-01-01T07:52:58.000Z"

},

{

"name": "Language in Thought and Action",

"author": "S. I. Hayakawa",

"publishedDate": "1949-01-01T07:00:00.000Z"

}

]

Ok, how about filtering it with Sequelize?

app.get("/books", async (req, res) => {

const books = await models.Book.findAll({

attributes: ["name", "author", "publishedDate"]

});

res.json(books);

});

Source: https://stackoverflow.com/questions/8039932/specifying-specific-fields-with-sequelize-nodejs-instead-of

Which should get you the same result.

Filtering during the database lookup is better than filtering it out in Node.JS, because we transfer less data between the database and Node.

Step 4: Looking at a basic writing operation

We modified the name of a book in the last step, we can also setup Express to insert new records into the database. In the main.js , insert the following:

// Import body parser

const bodyParser = require("body-parser");

const app = express();



// Apply this middleware

app.use(bodyParser.json()); // Add this new route after the get

app.post("/books", async (req, res) => {

const { name, author, publishedDate } = req.body;

const book = await models.Book.create({

name,

author,

publishedDate

});

res.json(book);

});

Re-run the server, and now we are ready to insert new books into the database, open up Postman:

Type in localhost:3000/books for url

for url Select POST

Put in the book details (name, author, publishedDate) under Body, and make sure to select JSON in the drop down.

in the drop down. Hit the Send button!

You will see a return value like the above. If you refresh your browser, you will see the new book show up!

[

{

"name": "The Book of Five Rings",

"author": "Miyamoto Musashi",

"publishedDate": "1645-01-01T07:52:58.000Z"

},

{

"name": "The Art of Peacekeeping",

"author": "Sun Tzu",

"publishedDate": "0500-01-01T07:52:58.000Z"

},

{

"name": "Language in Thought and Action",

"author": "S. I. Hayakawa",

"publishedDate": "1949-01-01T07:00:00.000Z"

},

{

"name": "The Postmortal",

"author": "Drew Magary",

"publishedDate": "2011-08-30T07:00:00.000Z"

}

]

With that, you now can connect/define/read/write MySQL database records via Rest Api Calls.

In future articles, we will cover details on how to work with multiple tables/database, securing databases, database migrations, and more.

Did you get stuck somewhere?

No problem at all, seeing error messages and troubleshooting it is the bread and butter of all Web Developers. We eat error messages for breakfast. Let us know if you run into any blockers (on this thread), or you can reach us at info@teamzerolabs.com too.

Go forth and make more tables!