SWORM ORM with NodeJS and MSSQL

What is ORM ?

Object-relational mapping (ORM) is a programming technique in which a metadata descriptor is used to connect object code to a relational database. It means this is a programming technique for converting data between incompatible type systems using object-oriented programming languages. ( TechOpedia and Wikipedia )

What is SWORM ?

A very lightweight write only Node.js ORM, with support for Microsoft SQL Server (MSSQL), PostgreSQL, MySQL, Oracle DB, Sqlite 3, Browser Web SQL. ( SWORM npm )

Create NodeJS Application

Using “npm init”, you can create a nodeJS application.

npm install sworm

After that install MSSQL

npm install mssql

Then install the below npm packages/Dependencies

  • npm i body-parser
  • npm i cors
  • npm i express

Create a server.js file in your root folder and import SWORM to server.js file

var sworm = require(‘sworm’);

After that your server.js file look like this.

var sworm = require(‘sworm’);
var express = require(‘express’);
const bodyParser = require(“body-parser”);
const cors = require(“cors”);
const app = express();

app.use(bodyParser.urlencoded({extended:true}));
app.use(bodyParser.json());
app.use(cors());

const port = process.env.Port || 5000;

app.listen(port, () => {
console.log(“Server is up and running on port number ” + port);
});

Create DB connection String – SWORM MSSQL DB Connection String

In my application I created a another file called “db.config.js” to implement SWORM MSSQL DB connection string. Here is the code. Copy the below code.

var sworm = require(‘sworm’);

const db = sworm.db({
driver: ‘mssql’,
config: {
user: ‘builditmasters’,
password: ‘builditmsaters’,
server: ‘localhost’,
database: ‘builditmasters’,
options:{
trustedconnection: false, encrypt: false, trustServerCertificate: false, enableArithAbort : false,
cryptoCredentialsDetails: { ca: ‘PEM Encoded self-signed certificate authority certificate goes here’ }
}

} }); db.connect(); module.exports = db;

Replace with your username and the password. And also rename the server name and the database name. You can select your driver.

Make sure to false the encrypt. Because it will give you an error called “self signed certificates failed”.

Let’s get into Code – Controllers and Models

You can create models like this.

class Port{
constructor(Id,PortCode,Name,ActiveYN, LastUpdated){
this.Id = Id;
this.PortCode = PortCode;
this.Name = Name;
this.ActiveYN = ActiveYN;
this.LastUpdated = LastUpdated;
}
}

module.exports = Port;

But you don’t need to create a model for this ORM. Because in the controller SWORM will called the database table directly. Let’s see that.

This is the create new Port function.

//CREATE a new port
addPort = async (req , res) => {

var port = db.model({table: ‘Port’});

req.body.id = uuidv4();

var bob = port(req.body);

bob.save().then(function () {
console.log(“success”, bob);
}).catch(err => {
console.log(“error”, err.message);
})

}

You can see SWORM insert data to the database without calling models.

I am going to create a controller for get All Ports from the database table.

//GET all Ports
getAllPorts = async (req , res) => {

let allPorts = await db.query(“SELECT * from Port”)

if(!allPorts){
data = { status : ‘Not Found’, code : 404, data : allPorts, message : ‘Not Found’ }
}else{
data = { status : ‘success’, code : 200, data : allPorts, message : ‘Success’ }
}

res.json(data);

to get one Port from the database.

//GET one Ports
getOnePort = async (req , res) => {

let onePort = await db.query(“SELECT * from Port WHERE Id = @id” , {id: req.params.id})

if(!onePort){
data = { status : ‘Not Found’, code : 404, data : onePort, message : ‘Not Found’ }
}else{
data = { status : ‘success’, code : 200, data : onePort, message : ‘ Success’ }
}

res.json(data);
}

Thank you.