How to Querying SQL Server with Node.js

How to Querying SQL Server with Node.js

How to query SQL Server with Node.js and adding a Node web server.

Now we want to add a node Web Server so that we can Post data to the node web server, use it in the Database Query and return results back to a web page.

This will be a “first principles” exercise (you’ll understand later.)

As I stated before,

What We Want To Accomplish
  • Create a node Web Server.
  • Post Data from a Web Page to use in our SQL Server Database Query.
  • Send Results back to a Web Page.
What We Will Use

Along with existing the node packages and modules we will require only new ones.

  • express — a popular web framework
  • body parser — to assist in getting form data from a web page.

Other than that, we will keep things as plain (vanilla) JavaScript (first principles) as possible.

Why keep things as vanilla JavaScript? Because seeing how things can be done from “first principles” not only gives us appreciation for additional modules, frameworks and libraries out there (React, Vue, etc.) but may also make us realize, “hey, I don’t really need all that.”

We may eventually want the power of React or Vue but why carry that weight if we do not need to.

It is not necessary to read the prior article first as I’ll be going over the core of what we need, but it may be of benefit.

If nothing else, you may want to look at my Database Setup. Yours can be completely different but I have links to download some useful tools for free if you do not have access to a SQL Server.

Let’s Get Started

I will assume you have node.js installed but if not, it can be downloaded here.

We will start off by setting up our node.js Web Server and testing it. Then add similar code as before to Connect to and Query our SQL Server Database.

We will use the Minimalist Web Server framework for node.js, Express. It installs as an npm package.

As with many things, there is always more to learn so when I have you type a command, certainly read more about the command and it’s options.

Getting Set Up
  1. For the project, create a directory/folder.
  2. Open Visual Studio Code (VSCode) and open the folder.
  3. Open a new Terminal window and type,
    npm init
    Accepts the defaults to create your package.json.

4. Create a file in VSCode and save it as index.js. If you look in your package.json file, you will see where you can change the default “main” file.

Basic starting point

5. In the Terminal window type,
start npm This starts the node.js server.

The Web Server

In the Terminal window type,
npm install express

This installs the Web Framework.

We are ready to rock and roll!

The core of the Web Server is to require the express package and assign it to a holder (variable or const.) All references to the Web Server will be through this holder.

We have to start the web server and have it listen on an HTTP port, I’ll choose 5000. 8080 is common, just something available.

We will want the Web Server to at least respond to an HTTP get method. This will allow use to “kick the tires” and make sure it works. There is more in HTTP methods here on the MDN. We will use GET and POST.

  • GET is used to retrieve from a resource.
  • POST is used to submit to a resource.

Enter the following code and we will discuss it.

Web Server Example

The core set up.

  • We require the express package, storing the reference in express by convention, but name it as desired.
  • Initialize an instance of express, storing the reference in app by convention, but name it as desired.
  • In line 11, we store a reference to where our web server will be listening. Port 5000.
  • In line 5, we use the HTTP GET with out instance of the web server using app.get. This, by default will look to get data from the root directory (‘/’). The callback function is quite important as it contains the Request (req) and Response (res) objects. The names req and res are by convention.
Request and Response
  • Request will be coming from our browser’s url. More information can be found here.
  • Response is the response to a request. More can be found here.

In a nutshell, our browser will make a Request and we will send back a Response.

Side Note: I actually prefer the ES6 syntax in lines 5 and 11 above. By this I mean, I prefer Arrow functions. For example, in line 5, I would typically not write app.get(‘/’, function(res,resp){… but write app.get(‘/’),(res,resp)=>{… and in line 11, instead of app.listen(5000,function(){…I would write app.listen(5000,()=>{

Kick the Tires

  1. In VSCode, hit F5 (short for Start Debugging in the Debug menu.) Your console should look as follows.

Look for the console message. The top has controls for stopping/refreshing, etc.

2. Go to your Browser and type LocalHost:5000 and hit Enter. You should see the following.

Yay!

Your browser made a GET Request and we sent back a Response using res.send.

Node SQL Server Connection

(Optional if done before.) In the Terminal window, type
npm install mssqlThis installs the SQL Server Drivers.

Database Example

This is the exact same starting code as my prior article so I will not be going over it in detail. However, it’s position in the app object will be important.

A couple of things to note that will be different from before, after our initial test,

  • the PerformanceRating of our Employees will be sent from a Web Page (POSTed.)
  • the result of the Query will be output to a Web Page using Response.
Database Connection/Query Example

Enter the code below.

Something important to note is we have a Web Server and a Database connection and Query. But they do not interact. Yet!

Disconnected Web Server and Database Connection

Run it with by stopping and starting the web server or refreshing

Refesh/Stop and other options

and you will see the web server is running and there is console output of the data from the query. But we are not getting anything from the web server nor sending anything out to a web page.

Our web server note and Database Query Output

The HTML

We need a page to serve up when the user goes to our site. This page will allow them to send a parameter (PerformanceRating) that can be used in the Database Query.

Note: I am not going to be concerned about styling our web page. It will be simple and to the point.

We are also saving everything in the root. A best practice? No way! But we are going for functionality. Clean up can happen later.

  1. Create the following web pages and name them index.html and Employees.html.
  • index.html will be our main source page.
  • Employees.html will be where our result ends up.

Simple startup page, index.html

Note the <form action=”/Employees…
This is where our output will go.

Add the following to Employees.html

2. This next step is going to require a couple of things. One of which is how I am choosing to serve up index.html. Other sources you read may get in to routing. Routing is a very important area, but outside our scope. Our way is just one of many ways. The new additions to our code will be,

  • path — this will help us used res.sendFile to server up the file we want when a visitor comes to our site.
  • bodyParser — makes form data available once a POST is done
  • sendFile — allows you to serve up the desired default page.

Make the following changes to your code,

path, bodyParser and sendFile

Run it with by stopping and starting the web server or refreshing. In your browser, after refreshing the browser,you should see,

index.html served up by default

Using the HTML Form Data In Our Query

For this, we need to add an app.post. This will engage whatever we desire when the index.html for is POSTed from the web page.

This is an important step. Our Database Connection and Query need to be inside the app.post callback function.

Note how our Database components go in the app.get

  • Line 13 starts the app.post. In the body of the callback are our Database components.
  • Line 52 closes the app.post.

Could we structure all this differently, yes. I’m doing it this way to give us a foundation that will work. Then we/you can explore other options

Using the Form Data

Using the form data will be easy.

Lines 35, 37, 50 and 55

  • Line 35, get the form data from the Select element. bodyParser is what is helping us here. req.body.elementname.
  • Line 37, the Query, uses the value from the form.
  • Line 50 is sending the raw queried data to Employees.html.

Run it by stopping and starting the web server or refreshing.

  1. Go to your web page and refresh.

2. Choose “Average” from the rating drop down and click “Get Results”

3. You should get output in Employees.html. Not attractive, but it’s a start.

Json output from res.send

Last Steps — Clean Up The Output (a bit.)

This is the easiest and hardest part. Hardest because there are so many options. We are going to go with the basics of the basics so we get a glimpse of what is happening deep down inside.

This is an area you are going to want to explore in much more in detail. Not just additional methods of res, but the idea of a “view engine”.

And I will be exploring some of these in future articles. In particular, one of my favorites, express-handlebars.

For now, we are going to construct our output. Yes. Construct it ourselves. Build a table of our data in code and send it! Just loop through our data.recordset and build.

See the following

Building our output

You will want to add lines 50–57.

The output will be as follows when Querying for “Average”

Built by us with code.

What To Do Next (yes you)

Play with it, add more, change the structure. Work starting from basics, and work up.

Conclusion

We are working with first principles to help us understand what goes on behind some of the layers that are added by JavaScript templates, frameworks and libraries these days.

We have built on our own, a full-stack system. Yes, believe it or not we have.

What needs the most work, the output. But this whole exercise opens up a world of exploration and opportunities. So we want to expand the concepts of,

  • More CRUD operations
  • Routing
  • View engines/templates

Thank you for taking this journey and we will explore more.

What are the differences between Standard SQL and Transact-SQL?

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

#1 Names of Database Objects

In relational database systems, we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL, you can place this kind of name in quotation marks (""), but in T-SQL, you can also place it in brackets ([]). Look at these examples for the name of a table in T-SQL:

CREATE TABLE dbo.test.“first name” ( Id INT, Name VARCHAR(100));
CREATE TABLE dbo.test.[first name]  ( Id INT, Name VARCHAR(100));

Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard.

What Is Different in a SELECT Statement?#2 Returning Values

The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL, it looks like the example below:

SELECT 12/6 ;

In this expression, we don’t need a table to evaluate 12 divided by 6, therefore, the FROM statement and the name of the table can be omitted.

#3 Limiting Records in a Result Set

In the SQL standard, you can limit the number of records in the results by using the syntax illustrated below:

SELECT * FROM tab FETCH FIRST 10 ROWS ONLY

T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax:

SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

As you notice, this uses an ORDER BY clause. Another way to select rows, but without ORDER BY, is by using the TOP clause in T-SQL:

SELECT TOP 10 * FROM tab;
#4 Automatically Generating Values

The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below:

CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);

In T-SQL we can also automatically generate values, but in this way:

CREATE TABLE tab (id INTEGER IDENTITY);
#5 Math Functions

Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don’t find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL, you use the GREATEST(list) and LEAST(list) functions.

T-SQL function ROUND:

SELECT ROUND(col) FROM tab;

#6 Aggregate Functions

We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values so that rows are counted only if the values are different from other rows. The SQL standard doesn't allow for the use of DISTINCT in these functions.

Standard SQL:
SELECT COUNT(col) FROM tab;

T-SQL:
SELECT COUNT(col) FROM tab;

SELECT COUNT(DISTINCT col) FROM tab;

But in T-SQL we don’t find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard.

#7 Retrieving Parts of Dates and Times

Most relational database systems deliver many functions to operate on dates and times.

In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x).

There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server, there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type.

#8 Operating on Strings

Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL, there is the TRIM function, but in T-SQL, there are several related functions: TRIM (removing trailing and leading spaces), LTRIM (removing leading spaces), and RTRIM (removing trailing spaces).

Another very-often-used string function is SUBSTRING.

The standard SQL syntax for the SUBSTRING function looks like:

SUBSTRING(str FROM start [FOR len])

but in T-SQL, the syntax of this function looks like:

SUBSTRING(str, start, length)

There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this:

As you can see, this syntax makes use of the || operator to add one string to another.

But the equivalent operator in T-SQL is the plus sign character. Look at this example:

SELECT col1 + col2  FROM tab;

In SQL Server, we also have the possibility to use the CONCAT function concatenates a list of strings:

SELECT CONCAT(col1, str1, col2, ...)  FROM tab;

We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example:

SELECT  REPLICATE(str, x);

where x indicates how many times to repeat the string or character.

#9 Inequality Operator

During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator:

SELECT col3 FROM tab WHERE col1 != col2;
#10 ISNULL Function

In T-SQL, we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard.

SELECT ISNULL(col1) FROM tab;
Which Parts of DML Syntax Are Different?

In T-SQL, the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let’s look at them.

#11 OUTPUT Keyword

The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL.

Using T-SQL, we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information, we have to use prefixes in INSERT, UPDATE, and DELETE.

UPDATE tab SET col='new value'
OUTPUT Deleted.col, Inserted.col;

We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature.

#12 Syntax for INSERT INTO ... SELECT

Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

It is not a standard feature but a feature characteristic of SQL Server.

#13 FROM Clause in DELETE and UPDATE

SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name). Here is an example:

DELETE FROM Book
FROM Author
WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

UPDATE Book
SET Book.Price=Book.Price*0.2
FROM Author
WHERE Book.AuthorId=Author.Id AND Author.Id=12;

The SQL standard doesn’t provide this syntax.

#14 INSERT, UPDATE, and DELETE With JOIN

You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is:

DELETE ItemOrder FROM ItemOrder
JOIN Item ON ItemOrder.ItemId=Item.Id
WHERE YEAR(Item.DeliveredDate) <= 2017;

This feature is not in the SQL standard.

Summary

This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn’t implemented by MS SQL Server.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Originally published on https://dzone.com


How to get started Internationalization in JavaScript with NodeJS

How to get started Internationalization in JavaScript with NodeJS

Tutorial showing how to use the Intl JS API in NodeJS (i18n). We'll install a module to unlock the Intl API languages for Node and test out RelativeTimeFormat to translate and localise relative times in JavaScript.

Tutorial showing how to use the Intl JS API in NodeJS (i18n). We'll install a module to unlock the Intl API languages for Node and test out RelativeTimeFormat to translate and localise relative times in JavaScript. I'll tell you how to get started with the built-in internationalization library in JS for Node 12 and higher. We'll change the locale to see how the translation works and test different BCP 47 language tags.

Internationalization is a difficult undertaking but using the Intl API is an easy way to get started, it's great to see this new API in the JS language and available for use. Soon, you'll be able to have confidence using it in the browser as modern browsers support the major Intl features. Have a look at the browser compatibility charts to see which browsers and versions of node are supported.

Use Intl.RelativeTimeFormat for language-sensitive relative time formatting.
#javascript #nodejs #webdevelopment

MDN Documentation:

https://developer.mozilla.org/en-US/d...

Full ICU NPM package:

https://www.npmjs.com/package/full-icu

How to Build a Vanilla JavaScript + NodeJS GUI Content Tool

How to Build a Vanilla JavaScript + NodeJS GUI Content Tool

Learn how to build a vanilla JavaScript + NodeJS GUI content tool that creates HTML files, with this beginner-friendly tutorial.

Learn how to build a vanilla JavaScript + NodeJS GUI content tool that creates HTML files, with this beginner-friendly tutorial.

This tutorial includes:

  1. Project File Structure 1:35
  2. How to Create the HTML form 2:52
  3. How to write the vanilla front end JavaScript 7:28
  4. NPM / Install Instructions 11:31
  5. Back end NodeJS + ExpressJS 12:52
  6. How to Test the App 19:54
  7. Suggested features to add to the tool 22:04

NodeJS + Vanilla JavaScript GUI Content Tool Code:
https://www.codingcommanders.com/guijs/