Hi, Thank you for you checking out this post!, I'm Ally and in this blog, I will guide you through setting up Express.js, PostgreSQL, and Sequelize as an ORM for production-grade applications.
Introduction
Express.js, PostgreSQL, and Sequelize form a powerful stack for building robust, scalable web applications. This combination offers:
- Express.js: A minimal and flexible Node.js web application framework
- PostgreSQL: A powerful, open-source relational database
- Sequelize: An ORM for Node.js that supports PostgreSQL (among others)
This stack is particularly well-suited for production environments due to its performance, reliability, and extensive feature set.
In this tutorial, we'll explore how to configure everything from scratch, and the source code will be provided. Here's what we will cover:
- How to set up an Express.js server
- Setting up Babel transpiler to work with ES6 syntax
- What Sequelize CLI and ORM are in general
- How to integrate PostgreSQL and Sequelize ORM
This guide is aimed at beginner to intermediate Node.js developers who want to level up their backend development skills.
Prerequisites
Before we begin, make sure you have:
- Basic knowledge of SQL (W3Schools SQL Tutorial)
- Express.js basics (Express.js Documentation)
- Terminal basic commands
To follow along with this tutorial, you should have these tools installed on your local machine:
- Node.js 16.x or higher (I'm using v20.14.0)
- Yarn or NPM (I'm using NPM v10.8.3)
- A text editor (I'm using VSCode)
- A local installation of PostgreSQL (Download PostgreSQL)
Project setup and package installation
mkdir express-app
cd express-app
mkdir src
cd src
mkdir controllers database routes services utils
This creates the following folder structure:
express-app/
└── src/
├── controllers/
├── database/
├── routes/
├── services/
└── utils/
Congratulations! You're now ready to set up a production-ready Express.js and PostgreSQL application.
Configure the project to work with ES6 with Babel
Babel: Babel is a transpilation tool that helps convert our ES6 syntax into a version of JavaScript that is compatible with all browsers.
In your root directory, initialize npm by running the following command in your terminal:
npm init -y
Now, install all the packages we'll need throughout this tutorial:
npm install @babel/cli @babel/core @babel/node @babel/preset-env dotenv express path pg pg-hstore sequelize
We also need to install some packages as dev dependencies:
npm install -D nodemon sequelize-cli
After all the packages have been installed, add the Nodemon script for the development environment. We use Nodemon to automatically restart the server every time we make changes to our codebase.
Add the following line to the scripts section in your package.json:
"dev": "nodemon --exec npx babel-node src/server"
Finally, add a .env file to keep track of our environment variables locally, avoiding any risk of them being leaked. In the root directory, create a file called .env.
Important: Never commit your .env file to version control. Add it to your .gitignore file to prevent accidental commits.
Next, we will add Babel to the configuration. In the root directory, create a file called .babelrc and add the following configuration:
{
"presets": ["@babel/preset-env"],
"env": {
"test": {
"plugins": ["@babel/plugin-transform-modules-commonjs"]
}
}
}
Finally, add a .env file to keep track of our environment variables locally, avoiding any risk of them being leaked.
In the root directory, also add a file called .env.
If you managed to complete the previous steps and you're using VS Code, it should look like this! Now grab some water, and let's start writing some code.
Setup express.js server
In this section, I will walk you through the process of setting up an Express.js server efficiently. All code will be refactored for better organization.
To get started, go to /[root]/src and create two JavaScript files: app.js and server.js. You can create them manually in your code editor or run the following command in your terminal: touch app.js server.js. Make sure your current directory is src; otherwise, run cd src.
Now add this code in app.js file:
//import express to use express methods and properties
import express from 'express';
//create an instance of express
const app = express();
//use express.json() to parse incoming requests with JSON payloads
app.use(express.json());
app.use(express.urlencoded({ extended: true })); //parse incoming requests with urlencoded payloads
//catch all routes that are not defined to avoid running into errors when a route is not defined
app.use('*', (req, res) => {
res.status(404).json({ error: 'Path does not found, try again' });
});
export default app; //export app to be used in other files
In the server.js file, add the following code, where we utilize the built-in HTTP method from the Express framework to implement the server.
import http from 'http'; // import the http module from node.js core to create a server instance
import app from './app'; // import the express app from the app.js file
const PORT = process.env.PORT || 3000; // set the port number to be used by the server instance
/* create a server instance using the http.createServer method
* there is no right or wrong way to create a server instance wether using the http.createServer method or the app.listen method
* I usualy use the http.createServer method because it gives me more control over the server instance like adding a socket.io instance to the server instance
*/
const server = http.createServer(app);
/* there is two we can implement the server.listen method
* 1. using the server.listen method directly
* 2. using the server.listen method with a callback function
*/
// Adding server instance inside a callback function also gives a flexibility to add more logic before starting the server
const startServer = () => {
// Listen to the server instance on the specified port number
server.listen(PORT, () => {
console.log(`Server is running on port ${PORT}`);
});
};
startServer(); // start the server instance
Integrating Postgres and sequelize ORM
In this section, you will learn how to add Sequelize to your codebase and configure it to use PostgreSQL.
If you don't have PostgreSQL installed on your local machine, you can download it from this link.
In your root directory, create a file called .sequelizerc. This file will manage our directory related to the database to keep everything organized.
Add this following code into .sequerizerc.
const path = require('path');
module.exports = {
config: path.resolve('./src/database/config', 'db.config.js'),
'models-path': path.resolve('./src/database/models'),
'seeders-path': path.resolve('./src/database/seeders'),
'migrations-path': path.resolve('./src/database/migrations'),
};
Now you can initialize Sequelize in your project by running the following command in your terminal:
npx sequelize init
This will create different directory in the under src/database.
In config directory create this two files ** db.config.js** and sequelize.js.
Add this snippet code into db.config.js
const dotenv = require('dotenv');
const { logger } = require('sequelize/lib/utils/logger');
dotenv.config();
// configuration for the database connection based on the environment (development, test, production)
module.exports = {
development: {
use_env_variable: 'DEV_DATABASE_URL',
},
test: {
use_env_variable: 'TEST_DATABASE_URL',
// dialect: "postgres",
dialectOptions: {
ssl: process.env.NODE_ENV === 'production', // set this value based on your environment
},
},
production: {
use_env_variable: 'DATABASE_URL',
dialectOptions: {
ssl: {
// enable this for production environment only if using a secure connection
require: true,
rejectUnauthorized: false,
},
},
},
};
In this sequelize.js add this code:
import dotenv from 'dotenv';
import { Sequelize } from 'sequelize';
dotenv.config();
// Extract the environment variables and assign them to the variables
const { NODE_ENV, DATABASE_URL, DEV_DATABASE_URL, TEST_DATABASE_URL } =
process.env;
let url =
NODE_ENV === 'development'
? DEV_DATABASE_URL
: NODE_ENV === 'test'
? TEST_DATABASE_URL
: DATABASE_URL;
// Create a new instance of Sequelize and pass the database URL
const sequelize = new Sequelize(String(url), {
timezone: '+09:00',
define: {
charset: 'utf8mb4',
collate: 'utf8mb4_general_ci',
underscored: false,
freezeTableName: true,
},
pool: {
//explain this line of code? this means that the connection pool will have a minimum of 0 connections and a maximum of 5 connections
min: 0,
max: 5,
},
logQueryParameters: NODE_ENV === 'development', // this line of code will log the query parameters if the environment is development
benchmark: true,
});
export default sequelize;
In src/database/model, there is a file called index.js. For now, you can delete it, as it is the default configuration that Sequelize has generated.
Instead in src/database create a file called index.js and add this code:
import sequelize from './config/sequelize';
const DB = {
sequelize,
};
export default DB;
after that if your project need some relationship or association we can go ahead in src/database and create a folder called relatioships and inside create a file index.js and create function inside this file that will be use to associate our database.
import DB from "../index";
const associate = () => {
//TODO: Add all associations here
};
export default associate;
Authenticate to postgres.
To keep our folder structure in use in src/service and create a file, you can call it connectDB.js and add this code:
import DB from '../database';
import sequelize from '../database/config/sequelize';
import associate from '../database/relationships';
// Connect to the database and log a message to the console
const connectDB = async () => {
try {
await sequelize.authenticate();
console.log('Connection has been established successfully🔥');
// Synchronize the database with the models without need of dropping the tables
await DB.sequelize.sync({
force: false,
});
associate(); // Call the associate function to create the relationships between the models
} catch (error) {
console.error('Unable to connect to the database:', error);
process.exit(1); // Exit the process if the connection is not successful
}
};
export default connectDB;
After completing this update your server.js to invoke this database connection.
import http from 'http'; // import the http module from node.js core to create a server instance
import app from './app'; // import the express app from the app.js file
import connectDB from './services/connectDB';
const PORT = process.env.PORT || 3000; // set the port number to be used by the server instance
/* create a server instance using the http.createServer method
* there is no right or wrong way to create a server instance weather using the http.createServer method or the app.listen method
* I usualy use the http.createServer method because it gives me more control over the server instance like adding a socket.io instance to the server instance
*/
const server = http.createServer(app);
// Adding server instance inside a callback function also gives a flexibility to add more logic before starting the server
const startServer = () => {
// Listen to the server instance on the specified port number
connectDB(); // connect to the database before starting the server
server.listen(PORT, () => {
console.log(`Server is running on port ${PORT}`);
});
};
startServer(); // start the server instance
In your .env file add database url:
DEV_DATABASE_URL=postgresql://<USER>:<PASSWORD>@localhost:5432/<DATABASENAME>
Now you can start the server by simply running:
npm run dev
Congratulations! You’ve completed the setup for this tutorial.
conclusion
Sequelize with PostgreSQL is truly the best combination for building APIs alongside Node.js and Express. However, we have only covered how to set up the project. If you're new to this, I understand that you want to see everything in action, like building a real app.
I didn’t want this blog to be overwhelming, here is the github link for simple project I crafted to demonstrate how to use this setup: https://github.com/leandreAlly/express-postgres-sequelize-setup/tree/project-management. The detail for this blog will be published in part 2.
If you run into issues while setting this up, you can check out the complete GitHub source code below. Also, please give it a star if you found it informative, and share it with your friends.
Github link:https://github.com/leandreAlly/express-postgres-sequelize-setup
Top comments (2)
great
You seem to be fluent in NodeJS. Please check the WebForms example for NodeJS (Express framework) in the following article:
dev.to/elanatframework/webforms-co...
Awesome, I learnt a lot