In this Node Express PostgreSQL tutorial, we will use only the pg module only to access the PostgreSQL database. So, it will be faster instead of using ORM such as Sequelize.js which was introduced in our previous tutorial. But, we need more effort to create a PostgreSQL table and write SQL in our Express application.
This tutorial is divided into several steps:
- Step #1: Setup PostgreSQL Database and Tables
- Step #2: Create Express.js Application
- Step #3: Create REST Controller
- Step #4: Create a Router
- Step #5: Run and Test Node Express PostgreSQL using Postman
Let's get started with the main steps!
Step #1: Setup PostgreSQL Database and Tables
Before creating a Node Express application, first, we need to create a database and tables. We will create 2 tables with a one-to-many relationship.
To create a database, go to the PostgreSQL console from the terminal or cmd.
psql postgresNext, type this command for creating a new user with a password then give access for creating the database.
CREATE ROLE nodeuser WITH LOGIN PASSWORD '3xpr3ss_js';
ALTER ROLE nodeuser CREATEDB;Quit `psql` then log in again using the new user that was previously created.
\q
psql postgres -U nodeuserType this command to create a new database.
CREATE DATABASE node_catalogue;Then give that new user privileges to the new database and then use the database.
GRANT ALL PRIVILEGES ON DATABASE node_catalogue TO nodeuser;
\c node_catalogueCreate a sequence and table for Product with these commands.
CREATE SEQUENCE public.product_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
ALTER SEQUENCE public.product_id_seq
OWNER TO nodeuser;
CREATE TABLE public.product
(
id bigint NOT NULL DEFAULT nextval('product_id_seq'::regclass),
prod_desc character varying(255) COLLATE pg_catalog."default",
prod_image_url character varying(255) COLLATE pg_catalog."default",
prod_name character varying(255) COLLATE pg_catalog."default",
prod_price character varying(255) COLLATE pg_catalog."default",
CONSTRAINT product_pkey PRIMARY KEY (id)
)
TABLESPACE pg_default;
ALTER TABLE public.product
OWNER to nodeuser;Next, create a sequence and table for Variants with these commands.
CREATE SEQUENCE public.variant_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
ALTER SEQUENCE public.variant_id_seq
OWNER TO nodeuser;
CREATE TABLE public.variant
(
id bigint NOT NULL DEFAULT nextval('variant_id_seq'::regclass),
variant_color character varying(255) COLLATE pg_catalog."default",
variant_name character varying(255) COLLATE pg_catalog."default",
variant_price double precision,
product_id bigint NOT NULL,
CONSTRAINT variant_pkey PRIMARY KEY (id),
CONSTRAINT fkjjpllnln6hk6hj98uesgxno00 FOREIGN KEY (product_id)
REFERENCES public.product (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
TABLESPACE pg_default;
ALTER TABLE public.variant
OWNER to nodeuser;Next, populate the initial data for the Product table.
INSERT INTO public.product(
prod_desc, prod_image_url, prod_name, prod_price)
VALUES ('My space ship for the future', 'https://www.gannett-cdn.com/presto/2018/09/18/PBRE/de0dcc9c-2d01-46e4-92b4-584807e1ffa0-Screen_Shot_2018-09-18_at_00.40.05.png', 'My Space Ship', 129000000);Next, populate the initial data for the Variant table.
INSERT INTO public.variant(
variant_color, variant_name, variant_price, product_id)
VALUES ('silver', 'bright mode ship', 129000100, 1);
INSERT INTO public.variant(
variant_color, variant_name, variant_price, product_id)
VALUES ('black', 'dark mode ship', 129000200, 1);
INSERT INTO public.variant(
variant_color, variant_name, variant_price, product_id)
VALUES ('blue', 'sky mode ship', 129000300, 1);Quit PostgreSQL console.
\qNow, the database and table is ready to use in our Node Express application.
Step #2: Create Express.js Application
Open your terminal or node command line the go to your projects folder. First, install express-generator using this command.
sudo npm install express-generator -gNext, create an Express.js app using this command.
express node-catalogueNext, go to the newly created project folder then install node modules.
cd node-catalogue && npm installYou should see the folder structure like this.
.
├── app.js
├── bin
│ └── www
├── node_modules
├── package-lock.json
├── package.json
├── public
│ ├── images
│ ├── javascripts
│ └── stylesheets
│ └── style.css
├── routes
│ ├── index.js
│ └── users.js
└── views
├── error.jade
├── index.jade
└── layout.jadeThere's no view yet using the latest Express generator. We don't need it because we will create a RESTful API. Next, add the pg, pg-promise, and bluebird modules to this application.
npm i --save pg pg-promise bluebirdOpen this project with your IDE or Text Editor. Next, add a new Javascript file at the root of the project folder for the configuration with the name config.js. Fill that file with these configuration codes.
const config = {
db: {
host: 'localhost',
port: '5432',
user: 'nodeuser',
password: '3xpr3ss_js',
database: 'node_catalogue',
},
rowsPerPage: 10,
};
module.exports = config;As you can see, there is a PostgreSQL configuration that was previously created and an additional configuration to limit the result list.
Step #3: Create REST Controller
We will put database queries and some logic for both tables in a controller file. So, the list, retrieve, create, and an update will be done for both Product and Variant in the same queries except the delete operation will use a separate query. Create a new folder and javascript file for a controller.
mkdir controllers
touch controllers/product.jsNext, open and edit that file then add these required modules and config.
const promise = require('bluebird');
const initOptions = {
promiseLib: promise // overriding the default (ES6 Promise);
};
const pgp = require('pg-promise')(initOptions);
const config = require('../config');
const db = pgp(config.db);Create an asynchronous function to get a list of products including an array of variants.
async function getProducts(page = 1) {
const offset = (page - 1) * [config.rowsPerPage];
return db.task(async t => {
const data = await t.any("SELECT id, prod_name, prod_desc, prod_image_url, " +
"prod_price, (SELECT array_to_json(array_agg(row_to_json(t))) " +
"FROM (SELECT id, variant_name, variant_color, variant_price " +
"FROM variant WHERE product_id=product.id) t) AS variants FROM product OFFSET $1 LIMIT $2", [offset, config.rowsPerPage]);
const meta = {page};
return {
data,
meta
}
});
}As you see, there is only a single query to get both related tables (one-to-many) because we are using PostgreSQL array_to_json function. So, the query for variant set as JSON array field for a product. Next, similar to getting a list of products, we will create get a product by ID. The difference with getting the list, just the pg-promise function use one instead of any function.
async function getProductById(id) {
return db.task(async t => {
const data = await t.one("SELECT id, prod_name, prod_desc, prod_image_url, " +
"prod_price, (SELECT array_to_json(array_agg(row_to_json(t))) " +
"FROM (SELECT id, variant_name, variant_color, variant_price " +
"FROM variant WHERE product_id=product.id) t) AS variants FROM product WHERE id = $1", [id]);
return {
data
}
});
}Next, create a function to save a table product and its variants.
async function createProduct(body) {
return db.tx(async t => {
const prod = await t.one("INSERT INTO product(prod_name, prod_desc, prod_image_url, prod_price) " +
"VALUES($1, $2, $3, $4) RETURNING id", [body.prod_name, body.prod_desc, body.prod_image_url, body.prod_price]);
body.variants.map(v => {
return db.tx(async t2 => {
await t2.one("INSERT INTO variant(variant_name, variant_color, variant_price, product_id) " +
"VALUES($1, $2, $3, $4) RETURNING id", [v.variant_name, v.variant_color, v.variant_price, prod.id]);
});
});
return {
prod
}
});
}First, insert product data with ID as a returning value then loop through the variants array to insert each variant. Next, create a function to update both the table product and it's variants by product ID.
async function updateProduct(id, body) {
return db.task(async t => {
const data = await t.one("UPDATE product SET prod_name=$1, prod_desc=$2, prod_image_url=$3, " +
"prod_price=$4 WHERE id=$5 RETURNING id", [body.prod_name, body.prod_desc, body.prod_image_url, body.prod_price, id]);
const varIds = body.variants.map(v => parseInt(v.id) | null);
const varToRemove = await t.any("SELECT id FROM variant WHERE product_id = $1 AND id <> ALL($2)", [data["id"], varIds]);
console.log(varToRemove.map(vtr => parseInt(vtr.id)));
const deleted = await t.any("DELETE FROM variant WHERE id = ANY($1)", [varToRemove.map(vtr => parseInt(vtr.id))]);
body.variants.map(v => {
return db.tx(async t2 => {
if(v.id) {
await t2.one("UPDATE variant SET variant_name=$1, variant_color=$2, variant_price=$3 WHERE id=$4 RETURNING id", [v.variant_name, v.variant_color, v.variant_price, v.id]);
} else {
await t2.one("INSERT INTO variant(variant_name, variant_color, variant_price, product_id) " +
"VALUES($1, $2, $3, $4) RETURNING id", [v.variant_name, v.variant_color, v.variant_price, id]);
}
});
});
return {
data
}
});
}This update function is a little tricky, the body of variants should use the variant ID to update and without ID to add a new variant. The variant ID that is not found in the body will be removed. Next, create a function to delete the product and its variants by ID.
async function deleteProduct(id) {
return db.task(async t => {
const data = await t.one("DELETE FROM variant WHERE product_id = $1; " +
"DELETE FROM product WHERE id = $1 RETURNING *;", [id]).catch(() => {
const error = new Error("Product not found");
error.status = 404;
throw error;
});
return {
data
}
});
}Next, create a function to delete a variant by ID.
async function deleteVariant(id) {
return db.task(async t => {
const data = await t.one("DELETE FROM variant WHERE id = $1 RETURNING *", [id]).catch(() => {
const error = new Error("Variant not found");
error.status = 404;
throw error;
});
return {
data
}
});
}Finally, register all of those functions by the export statement.
module.exports = {
getProducts,
getProductById,
createProduct,
updateProduct,
deleteProduct,
deleteVariant
}
Step #4: Create a Router
To handle requests and responses of the REST API, we will use a router file. Create a new file inside the routes folder.
touch routes/product.jsOpen that file then add these lines of router functions.
const express = require('express');
const router = express.Router();
const { getProducts, getProductById, createProduct, updateProduct, deleteProduct, deleteVariant } = require('../controllers/product');
/* GET product list */
router.get('/', async function(req, res, next) {
try {
res.json(await getProducts(req.query.page));
} catch (err) {
next(err);
}
});
/* GET product by ID */
router.get('/:id', async function(req, res, next) {
try {
res.json(await getProductById(req.params.id));
} catch (err) {
next(err);
}
});
/* POST product */
router.post('/', async function(req, res, next) {
try {
res.json(await createProduct(req.body));
} catch (err) {
next(err);
}
});
/* UPDATE product */
router.put('/:id', async function(req, res, next) {
try {
res.json(await updateProduct(req.params.id, req.body));
} catch (err) {
next(err);
}
});
/* DELETE product BY ID */
router.delete('/:id', async function(req, res, next) {
try {
data = await deleteProduct(req.params.id);
console.log(data);
res.json(data);
// res.send(await deleteProduct(req.params.id));
} catch (err) {
next(err);
}
});
/* DELETE variant BY ID */
router.delete('/variant/:id', async function(req, res, next) {
try {
res.send(await deleteVariant(req.params.id));
} catch (err) {
next(err);
}
});
module.exports = router;Those router functions are GET, POST, PUT, and DELETE methods. Each function calls the function from the controller. Next, register this router file to the app.js.
var productRouter = require('./routes/product');app.use('/api/v1/product', productRouter);Next, modify the error response to show the error as a REST API response instead of an HTML page.
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500);
res.send(err.message);
});
Step #5: Run and Test Node Express PostgreSQL using Postman
Before testing, this application using Postman, run this application first.
nodemonStart Postman then create a new request with method POST then the headers with Content-Type=application/json and the URL is http://localhost:3000/v1/api/product.
And the request body is raw JSON.
Then click the Send button and the response is like this.
To get the Products and Variants change the method to GET and disable the body then click the Send button. The response should be like this.
{
"data": [
{
"id": "1",
"prod_name": "Samsung Galaxy S22",
"prod_desc": "The latest Samsung Galaxy series with large display and high performance CPU",
"prod_image_url": "https://images.samsung.com/id/smartphones/galaxy-s22-ultra/images/galaxy-s22-ultra_highlights_kv_img.jpg",
"prod_price": "1000",
"variants": [
{
"id": 50,
"variant_name": "Black Case",
"variant_color": "Black",
"variant_price": 1000
},
{
"id": 49,
"variant_name": "Silver Case",
"variant_color": "Silver",
"variant_price": 1000
}
]
}
],
"meta": {
"page": 1
}
}To UPDATE the products and variants change the method to PUT and URL to http://localhost:3000/v1/api/product/1 and body payload to this.
{
"prod_name": "Samsung Galaxy S22",
"prod_desc": "The latest Samsung Galaxy series with large display and high performance CPU",
"prod_price": 1000,
"prod_image_url": "https://images.samsung.com/id/smartphones/galaxy-s22-ultra/images/galaxy-s22-ultra_highlights_kv_img.jpg",
"variants": [
{
"id": 45,
"variant_name": "Silver Bright Case",
"variant_color": "Silver Bright",
"variant_price": 1000
},
{
"id": 46,
"variant_name": "Yellow Case",
"variant_color": "Yellow",
"variant_price": 1000
},
{
"variant_name": "Shapire Case",
"variant_color": "Shapire",
"variant_price": 1000
}
]
}The response should be like this.
{
"data": {
"id": "1"
}
}To delete a product by ID just change the method to DELETE. To delete a variant just change the URL to http://localhost:3000/v1/api/product/variant/1.
That it's, the Node Express PostgreSQL Faster CRUD REST API. You can get the full source from our GitHub.
That just the basic. If you need more deep learning about Node.js, Express.js, PostgreSQL, Vue.js and GraphQL or related you can take the following cheap course:
- Node. JS
- Learning Express JS: Creating Future Web Apps & REST APIs
- Angular + NodeJS + PostgreSQL + Sequelize
- Build 7 Real World Applications with Vue. js
- Full-Stack Vue with GraphQL - The Ultimate Guide
Thanks!