This sample doesn't work at all with current sqlserver 2019 docker version

[nemethmik]

I reimplemented the server.js with using the nodejs package mssql instead of tedious.
The trustServerCertificate: true option must be added for local connection to avoid certificate error.

Actually, two things should be changed only:

• Replace the line RUN npm install tedious in Dockerfile with RUN npm install mssql
• Replace server.js with this block of text

var express = require("express");
var app = express();
var sql = require('mssql')

app.get('/', async function (req, res) {
    console.log("********** SERVER.JS app.get")
    var config = {
        server: 'localhost',
        user: 'sa',
        password: 'Yukon900', // update me
        database: 'DemoData', // doesn't seem to work?
        options: {
            encrypt: true, // for azure
            trustServerCertificate: true // change to true for local dev / self-signed certs        
        },
    };
    var pool
    try {
        pool = await sql.connect(config)
        console.log("********** SERVER.JS connected")
        const result = await sql.query`SELECT * FROM DemoData.dbo.Products` // FOR JSON AUTO
        console.dir(result)
        res.send(result?.recordset);
    } catch (err) {
        console.log(err)
        res.send(JSON.stringify(err))
    } finally {
        pool?.close()
    }
})  

var server = app.listen(8080, function () {
    console.log(`========== Listening on http://localhost:${server.address().port}`);
});

I have provided an updated readme, too.

[nemethmik]

Overview

This is a demo application created to show how SQL Server can operate in a DevOps scenario where an application developer can check in code to GitHub and then trigger a build in Red Hat Open Shift to deploy the changes automatically as pods (containers). This demo was first shown at the Nordic Infrastructure Conference (NIC) 2017 in Oslo, Norway on Feb 3, 2017. This demo application is notable for showing a few things:

• An entrypoint CMD which executes a import-data.sh script at runtime to use sqlcmd to execute a .sql script to create a database and populate initial schema into it.
• The import-data.sh script also uses bcp to bulk import the data found in the Products.csv file.
• A simple node application that acts as a web service to get the data out of the SQL Server database using FOR JSON auto to automatically format the data into JSON and return it in the response.

IMPORTANT: This project has been tested with SQL Server v.Next version CTP 1.4 (March 17, 2017 release).
IMPORTANT This project was revamped and overhauled for SQL Server 2019 Latest in Aug, 2021.

Running the Demo

Setting up the application and building the image for the first time

First, create a folder on your host and then git clone this project into that folder:

git clone https://github.com/twright-msft/mssql-node-docker-demo-app.git

To run the demo you just need to build the container:

docker build -t node-web-app .

Then, you need to run the container:

docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=Yukon900 -p 1433:1433 -p 8080:8080 -d node-web-app

Note: make sure that your password matches what is in the import-data.sh script.

Then you can connect to the SQL Server in the container by running a tool on your host or you can docker exec into the container and run sqlcmd from inside the container.

docker exec -it <container name|ID> /bin/bash
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Yukon900

To show the web service response, open up a browser and point it to http://localhost:8080.

Now that you have the demo application prepared, you can setup and configure OpenShift.

Setting up OpenShift

For demo purposes, you can deploy an OpenShift environment into any major cloud provider using templates available in the marketplaces, you can use OpenShift Online, or you can deploy a Virtual Box-based OpenShift environment called minishift on your local development/demo machine.

You will also need to get the oc commandline utility installed on your host/demo machine.

Once you have OpenShift set up and you have the oc command line utility installed, you need to login to your OpenShift environment as a cluster administrator, create a new project, and set the permissions to allow any user identity to run as root (required by the mssql user that runs the sqlservr.sh script in a container for now).

oc login
oc new-project demo
oadm policy add-scc-to-user anyuid -z default

Now that you have OpenShift set up, you are ready to do an initial test run of deploying into OpenShift.

Deploying into OpenShift

At a terminal prompt in the root of the application folder enter this command:

oc new-app .

That will deploy the app into OpenShift. You can now log into OpenShift and see a few artifacts.

Detailed Explanation

Here's a detailed look at each of the files in the project.

Dockerfile

The Dockerfile defines how the image will be built. Each of the commands in the Dockerfile is described below.

The Dockerfile can define a base image as the first layer. In this case, the Dockerfile uses the official Microsoft SQL Server Linux image that can be found on Docker Hub. The Dockerfile will pull the image with the 'latest' tag. This image requires two environment variables to be passed to it at run time - ACCEPT_EULA and SA_PASSWORD. The Microsoft SQL Server Linux image is in turn based on the official Ubuntu Linux image Ubuntu:16.04.

FROM microsoft/mssql-server-linux:latest

This RUN command will update all the installed packages in the image, install the curl utility if it is not already there and then install node. This command must be run with sudo privileges so the user is switched to root.

USER root
RUN apt-get -y update  && \
        apt-get install -y curl && \
        curl -sL https://deb.nodesource.com/setup_14.x | bash - && \
        apt-get install -y nodejs && \
        apt-get install -y dos2unix

This installs the tedious driver for SQL Server which allows node applications to connect to SQL Server and run SQL commands. This is an open source project to which Microsoft is now one of the main contributors.

NPM package details

Source Code

RUN npm install mssql

This RUN command creates a new directory inside the container at /usr/src/app and then sets the working directory to that directory.

RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

Then this command copies the package.json file from the source code in this project to the /usr/src/app directory inside the container. The RUN command npm install will install all the dependencies defined in the package.json file.

COPY package.json /usr/src/app/
RUN npm install

Then all the source code from the project is copied into the container image in the /usr/src/app directory and apply the necessary CRLF conversion.

COPY . /usr/src/app
RUN dos2unix *

In order for the import-data.sh script to be executable you need to run the chmod command to add +x (execute) to the file.

RUN chmod +x /usr/src/app/import-data.sh

The EXPOSE command defines which port the application will be accessible at from outside the container.

EXPOSE 8080

Lastly, the CMD command defines what will be executed when the container starts. In this case, it will execute the entrypoint.sh script contained in the source code for this project. The source code including the entrypoint.sh is contained in the /usr/src/app directory which has also been made the working directory by the commands above. The user is also switched back to the mssql user for security reasons.

USER mssql
CMD /bin/bash ./entrypoint.sh

entrypoint.sh

The entrypoint.sh script is executed every time the container is started. The side effect is that whenever you restart the container the data are imported again, and since there is no unique index on the Product.ID the data are replicated. This could have been done better, but for this demo purposes it's OK.

The script kicks off three things simultaneously:

• Start SQL Server using the sqlservr.sh script. This script will look for the existence of the ACCEPT_EULA and SA_PASSWORD environment variables. Since this will be the first execution of SQL Server the SA password will be set and then the sqlservr process will be started. Note: Sqlservr runs as a process inside of a container, not as a daemon.
• Executes the import-data.sh script contained in the source code of this project. The import-data.sh script creates a database, populates the schema and imports some data.
• Runs npm start which will start the node application.

/opt/mssql/bin/sqlservr.sh & /usr/src/app/import-data.sh & npm start 

import-data.sh

The import-data.sh script is a convenient way to delay the execution of the SQL commands until SQL Server is started. Typically SQL Server takes about 5-10 seconds to start up and be ready for connections and commands. Bringing the SQL commands into a separate .sh script from entrypoint.sh creates modularity between the commands that should be run at container start up time and the SQL commands that need to be run. It also allow for the container start up commands to be run immediately and the SQL commands to be delayed.

This command causes a wait to allow SQL Server to start up. To handle this, the script implements a brute force attempt to excute the sqlcmd command in a loop until it is successful or it has attempted 50 times 1 second apart.

The sqlcmd command uses the SQL Server command line utility sqlcmd to execute some SQL commands contained in the setup.sql file. The commands can also be passed directly to sqlcmd via the -q parameter. For better readability if you have lots of SQL commands, it's best to create a separate .sql file and put all the SQL commands in it.

for i in {1..50};
do
    /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Yukon900 -d master -i setup.sql
    if [ $? -eq 0 ]
    then
        echo "setup.sql completed"
        break
    else
        echo "not ready yet..."
        sleep 1
    fi
done

IMPORTANT: Make sure to change your password here if you use something other than 'Yukon900'.

The setup.sql script will create a new database called DemoData and a table called Products in the default dbo schema. This bcp command will import the data contained in the source code file Products.csv.
IMPORTANT: If you change the names of the database or the table in the setup.sql script, make sure you change them here too.
IMPORTANT: Make sure to change your password here if you use something other than 'Yukon900'.

bcp DemoData.dbo.Products in "/usr/src/app/Products.csv" -c -t',' -S localhost -U sa -P Yukon900

setup.sql

The setup.sql defines some simple commands to create a database and some simple schema. You could use a .sql file like this for other purposes like creating logins, assigning permissions, creating stored procedures, and much more. When creating a database in production situations, you will probably want to be more specific about where the database files are created so that the database files are stored in persistent storage. This SQL script creates a table with two columns - ID (integer) and ProductName (nvarchar(max)).
Primary key constraint is not defined on ID, so Id is not unique in this table.

CREATE DATABASE DemoData;
GO
USE DemoData;
GO
CREATE TABLE Products (ID int, ProductName nvarchar(max));
GO

Products.csv

This CSV data file contains some sample data to populate the Products table. It has two columns - ID and ProductName separated by a comma. The bcp command in the import-data.sh script uses this file to import the data into the Products table created by the setup.sql script file.

1,Car
2,Truck
3,Motorcycle
4,Bicycle
5,Horse
6,Boat
7,Plane
8,Scooter
9,Gopher
.... more data if you want ....

server.js

The server.js file defines the node application that exposes the web service and retrieves the data from SQL Server and returns it to the requestor as a JSON response.

The require statements at the top of the file bring in some libraries like mssql (using tedious behind the scene) and express and define some global variables which can be used by the rest of the application.

var express = require("express");
var app = express();
var sql = require('mssql')

The app.get defines the route for this application. Any GET request that comes to the root of this application will be handled by this async function. This effectively creates a simple REST-style interface for returing data in JSON from a GET request.

app.get('/', async function (req, res) {

The next set of commands define the connection parameters and creates a connection object.

IMPORTANT: Make sure to change your password here if you use something other than 'Yukon900'.

IMPORTANT: If you change the names of the database in the setup.sql script, make sure you change it here to.

    console.log("********** SERVER.JS app.get")
    var config = {
        server: 'localhost',
        user: 'sa',
        password: 'Yukon900', // update me
        database: 'DemoData', 
        options: {
            encrypt: true, // for azure
            trustServerCertificate: true // change to true for local dev / self-signed certs        
        },
    };

The commands in the try part are pretty straightforward:

• connect
• query
• send recordset array from the result object bact to the HTTP response
• close the connection
  No need to use SQL Server's built in JSON functions to retrieve the data in JSON format sine mssql does it excellently.

    var pool
    try {
        pool = await sql.connect(config)
        console.log("********** SERVER.JS connected")
        const result = await sql.query`SELECT * FROM DemoData.dbo.Products` // FOR JSON AUTO
        console.dir(result)
        res.send(result?.recordset);
    } catch (err) {
        console.log(err)
        res.send(JSON.stringify(err))
    } finally {
        pool?.close()
    }

This command starts the app listening on port 8080.
IMPORTANT: If you change the port number in the Dockerfile EXPOSE command make sure you change it here too.

var server = app.listen(8080, function () {
    console.log(`========== Listening on http://localhost:${server.address().port}`);
});

The decorations in the console.log commands are to help analysing the log when using

docker logs <container_id>

Alternative approach using SQL scripts for seeding schema and data

So far, the steps above have described how to create a docker image of sqlserver that starts seeding on first run.
However, if we have a huge database to be seeded, the setup.sql could contain all the
CREATE/INSERT SQL statements that are needed for seeding the database. We do not have to import it from csv file.

You can either add the CREATE/INSERT statements to setup.sql and have those run each time a container is created or you can create a new image that has the schema and data captured inside of it. In that case, after starting a new container and executing the .sql script, we can commit the newly running container with seeded db as a new image using "docker commit" command.

docker commit <container_id> <docker image tag>

We can now use this new image in a new docker based project including in a Docker Compose app using a docker-compose.yml file.

Also node.js dependency can be removed in this case. Node.js is only used here as an example web service to show the data can be retrieved from the SQL Server.

[alogwe]

You should submit this as a PR! Thank you, it worked for me after making those changes.