Getting started with Node, Docker, and MySQL

Now a days, Developers facing issues with application development using multiple stacks like-

1. Installing different databases server on one single PC like MongoDB, MySQL is difficult due to dependency of other libraries.
2. Running multiple terminals for different servers like one for frontend, one for backend, one for database, and so on. Due to which PC slows down and development slows down.

Now, we have a solution for all such issues — Docker

First we will install MySQL using docker-compose

I will assume that you have already installed docker and docker-compose on your machine.

Now we will first pull SQL Server 2019 Linux container image from Docker Hub.

sudo docker pull mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

Now we will run the container image with Docker by following command-

sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 \
   -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

Password should follow SQL Server default password policy and should have at least 8 characters long and contain characters from 3 of the following 4 sets: Uppercase letters, Lowercase letters, Base 10 digits, and symbols.

Now we can connect to our SQL server.

First start an interactive bash shell to connect with running container

sudo docker exec -it sql1 "bash"

After connecting with container, connect locally with sqlcmd

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourNewStrong@Passw0rd>"

After successful completion of above command, we will get a sqlcmd command prompt: 1>

Now its time to create and query data

Note: All of the following SQL commands will not execute immediately, we will have to type ```GO``` to execute after each SQL command.

Create Database — CREATE DATABASE TestDB;

Switch to new Database — USE TestDB;

Create the table — CREATE TABLE Employees (id INT, name NVARCHAR(50), position NVARCHAR(50));

Now insert data into the new table — INSERT INTO Employees VALUES (1, 'sudhir meena', 'software developer'); INSERT INTO Employees VALUES (2, 'sunny singh', 'problem setter');

To end your sqlcmd session — QUIT

Now it’s turn to connect Nodejs application with above created SQL Server.

I will assume you will have any good IDE like VS Code (Can be downloaded from here) and Nodejs (Can be downloaded from here) is already installed locally.

1. Create a folder and open that folder in VS code using terminal.
2. Type npm init to create a node project. It will create a package.json file.
3. Type following command to install the drivers- npm install mssql.
4. Since our package.json is using index.js as the main file, create a file named index.js. (Feel free to change value of main in package.json and name file according to it).
5. Entire code for index.js file is given below with explanation.

First we accessed the database drivers for SQl Server, line 2.

Next, lines 5–11, we set up the configuration required to tell our code how to find our database, the necessary credentials to access it and the database we wanted to access.

The next step, line 13, makes the connection.

To create the query, we create a Request Object. We specify the required parameters for the query method of the Request Object.

The last thing we do is to close the connection. Keep it open if you want to perform more operations.

Run code using

Now we have done our needed job.

Later we can stop and remove the container.

sudo docker stop sql1
sudo docker rm sql1

Conclusion

We installed the SQL Server using docker-compose, setup Nodejs project, installed drivers, created index.js file and connect to MySQL and fetch data.

Thank you for taking the time to read and code along.

If any problems or queries you have, send a question or comment. My goal is your success.