After a lot of research and testing, I present you with... da-dada-da: A Node.js, Express API with JWT Token Authentication and Seriate for MS SQL Database Integration
- MS SQL Server (2014 Exrpess was used)
- "app-root-dir": "^1.0.2"
- "body-parser": "^1.15.2"
- "express": "^4.14.0"
- "gulp": "^3.9.1"
- "gulp-nodemon": "^2.2.1"
- "jsonwebtoken": "^7.1.9"
- "seriate": "^0.8.0"
- Clone the repository
- Navigate to the repository and run npm install
- Use the nodeapi.sql create script to create the SQL database
- Run the API with node app.js or gulp from the repository root
- To obtain a token, POST to http://localhost:3000/authenticate
- Send Header with Key: Content-Type and Value: application/x-www-form-urlencoded
- Send Body values (application/x-www-form-urlencoded) Key: name and Value: apiuser, Key: password and Value: apipassword
- You should receive a token back in the response. Copy the token value and use it in subsequent API requests
- GET to http://localhost:3000/api/users
- Send Header with Key: x-access-token and Value: (Copied from authentication response)
- Provide a user registration API endpoint with password hashing
- Authenticate Users against database instead of hard coded user account in app/user.js
- Create controllers and move various logic out of the routes
- Demonstrate PUT and other common API functions besides GET
This project was built to demonstrate Node.js connectivity to an MS SQL database using Seriate. After looking into several options for SQL interaction within a Node.js environment, I chose Seriate for its simple implementation and native SQL query support. Please see this post for more information: http://developer.leankit.com/painless-sql-server-with-nodejs-and-seriate/
While NoSQL databases such as MongoDB are the trend in tutorials building out application interfaces such as this, many such systems implemented in existing business environments simply can't do away with MS SQL.