Object-Relational Mapping (ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm.
An object-relational mapper (ORM) is a code library that automates the transfer of data stored in the database into objects that are more commonly used and understandable in application code, so you don't use SQL. e.g. sequelize, typeORM, Pony, etc.
You have an article class, you want to retrieve all the articles with the title "ORM". With SQL, it would look like this:
const query = "SELECT * FROM article WHERE title='ORM';";
const data = await pool.query(query);
With an ORM library, it would look like this:
const data= await Article.findOne({
where: { title: "ORM" },
});
In this article, we will be creating a simple Article CRUD app, writing raw SQL queries to manipulate data.
Pre-requisite
The following should be installed on your computer:
- PostgreSQL
- Node.js
- Postman
Database Set-Up
1) Launch SQL shell (psql)
2) Click on Enter after every line. Enter your password when prompted.
3) Create a database
CREATE DATABASE node_pg_test;
4) Connect to the database
\c node_pg_test
5) Create article table
CREATE TABLE article(
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
article TEXT NOT NULL,
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Run \dt to check all the tables we have.
That's all for our database. Let's head over to the text editor to write our code.
Project Structure
.env => Contains all the environment variables (e.g. PORT and database URL)
config.js=> Contains connection to the database.
controller.js=> Contains functions to handle specific operations.
index.js => The starting point that connects all parts of the app together.
route.js => Contains the routing for the endpoints.
Set-up your code and install the dependencies
1) Create a folder that you want to work in and cd into it
2) Run npm init -y. This will accept the default config.
3) Install all the required dependencies
npm install dotenv express pg
npm install -D nodemon
4) Update the start script in package.json
"scripts": {
"start":"node index.js",
"dev":"nodemon index.js"
},
5) Create a .env file
// .env
//DATABASE_URL_FORMAT= postgresql://{user}:{password}@{host}:{port}/{database}
DATABASE_URL = postgresql://postgres:{password}@localhost:5432/pg_node_test
6) Filling in the blanks
// index.js
const express = require("express");
const app = express();
const bodyParser = require("body-parser");
const dotenv = require('dotenv')
const articleRouter = require("./route");
dotenv.config();
app.use(bodyParser.json());
app.use(
bodyParser.urlencoded({
extended: true
})
);
const port = process.env.PORT || 3000
app.listen(port,() =>{
console.log('Node.js listening... ' + port);
})
app.use("/articles", articleRouter);
//config.js
const Pool = require("pg").Pool;
const dotenv = require("dotenv");
dotenv.config();
const pool = new Pool({
connectionString: process.env.DATABASE_URL
});
pool.on("connect", () => {
console.log("connected to the db");
});
module.exports = {
pool
};
// controller.js
const { pool } = require("./config");
const getArticles = async (req, res, next) => {
try {
const data = await pool.query(
"SELECT * FROM article ORDER BY created_on DESC;"
);
if (data.rowCount == 0)
return res.status(404).send("No article exists");
return res.status(200).json({
status:200,
message: "All articles:",
data:data.rows
});
} catch (error) {
return next(error);
}
};
const createArticle = async (req, res, next) => {
const { title, article } = req.body;
const query =
"INSERT INTO article(title, article) VALUES($1, $2) RETURNING *;";
const values = [title, article];
try{
const data = await pool.query(query, values);
return res.status(201).json({
status:201,
message: "Article added successfully",
data: data.rows
});
} catch (error) {
return next(error);
}
};
const getArticleById = async (req, res, next) => {
const id = parseInt(req.params.id);
const query = "SELECT * FROM article WHERE id=$1;";
const value = [id];
try {
const data = await pool.query(query, value);
if (data.rowCount == 0) return res.status(404).send("No article exists");
return res.status(200).json({
status:200,
message: "Article:",
data: data.rows
})
} catch (error) {
return next(error);
}
};
const updateArticle = async (req, res, next) => {
const id = parseInt(req.params.id);
const { title, article} = req.body;
const query =
"UPDATE article SET title=$1, article=$2 WHERE id=$3 RETURNING *;";
const value = [title, article, id];
try {
const data = await pool.query(query, value);
if (data.rowCount == 0) return res.status(404).send("Article does not exist");
return res.status(200).json({
status:200,
message: "Article updated successfully ",
data: data.rows
})
} catch (error) {
return next(error);
}
};
const deleteArticle = async (req, res, next) => {
const id = parseInt(req.params.id);
const value = [id];
const query = "DELETE FROM article WHERE id=$1;";
try {
const data = await pool.query(query, value);
if (data.rowCount == 0) return res.status(404).send("Article does not exist");
return res.status(200).json({
status:200,
message: "Article deleted successfully"
})
} catch (error) {
return next(error);
}
};
module.exports = {
getArticles,
createArticle,
getArticleById,
updateArticle,
deleteArticle
};
// route.js
const express = require("express");
const router = express.Router();
const {
getArticles,
createArticle,
getArticleById,
updateArticle,
deleteArticle
} = require("./controller");
router
.post("/", createArticle)
.get("/", getArticles);
router.get("/:id", getArticleById);
router.patch("/:id", updateArticle);
router.delete("/:id", deleteArticle);
module.exports = router;
Let's start the development server using npm run dev and test in Postman.
The base URL is http://localhost:4000/articles
Conclusion
Although using ORMs makes life much easier and your code cleaner, being able to write SQL queries is a very essential skill. Writing SQL queries makes using ORMs more understandable. Simple and complex data manipulation can be done using SQL.
Resources
1) Connect To a PostgreSQL Database Server
2) PostgreSQL CREATE DATABASE with Example
If you like this article, feel free to comment and share. You can also reach out to me on Twitter | LinkedIn | Github
Ciao👋🏼