Skip to main content

Database Setup & Connection

We have our Repository layer, but it's currently pushing data into a JavaScript array. If you restart the server, all data is lost.

It is time to integrate PostgreSQL using Sequelize (a popular ORM for Node.js).

Prerequisites​

Ensure you have PostgreSQL installed and running on your machine.

Create a database for this project (e.g., using a GUI like pgAdmin or TablePlus, or the command line):

CREATE DATABASE express_tutorial_dev;

Step 1: Install Dependencies​

We need the Sequelize core, the PostgreSQL driver (pg), and the Sequelize CLI tool.

# Runtime dependencies
npm install sequelize pg pg-hstore

# Development dependencies (for running migrations)
npm install --save-dev sequelize-cli

Step 2: Configure Sequelize CLI (.sequelizerc)​

By default, Sequelize puts folders in the project root. We want to force it to respect our src/ structure.

Create a file named .sequelizerc (no extension) in your root directory:

// .sequelizerc
const path = require("path");

module.exports = {
    config: path.resolve("src", "config", "database.js"),
    "models-path": path.resolve("src", "models"),
    "seeders-path": path.resolve("src", "database", "seeders"),
    "migrations-path": path.resolve("src", "database", "migrations"),
};

This tells the CLI exactly where to look.

Step 3: Create the Database Config​

Sequelize expects a specific configuration file. We will create a dynamic one that reads from our existing src/config/index.js (so we don't repeat ourselves).

Create src/config/database.js:

// src/config/database.js
const config = require("./index"); // Import our main env config

module.exports = {
    development: {
        username: process.env.DB_USER || "postgres",
        password: process.env.DB_PASSWORD || "postgres",
        database: process.env.DB_NAME || "express_tutorial_dev",
        host: process.env.DB_HOST || "127.0.0.1",
        dialect: "postgres",
        logging: false, // Set to console.log to see SQL queries
    },
    test: {
        username: process.env.DB_USER || "postgres",
        password: process.env.DB_PASSWORD || "postgres",
        database: process.env.DB_NAME_TEST || "express_tutorial_test",
        host: process.env.DB_HOST || "127.0.0.1",
        dialect: "postgres",
        logging: false,
    },
    production: {
        username: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
        host: process.env.DB_HOST,
        dialect: "postgres",
        logging: false,
    },
};

Important: Update your .env file with these matching keys:

# .env
DB_USER=postgres
DB_PASSWORD=yourpassword
DB_NAME=express_tutorial_dev
DB_HOST=127.0.0.1

Step 4: Initialize Sequelize​

Now that we've told Sequelize where to go, let's run the init command to generate the folder structure.

npx sequelize-cli init

You will see new folders created inside src/:

  • src/database/migrations

  • src/database/seeders

  • src/models (contains an index.js)

Step 5: Connect and Test​

The file src/models/index.js (generated by Sequelize) is special. It automatically reads your config, connects to the database, and loads all your models.

Let's test if the connection works. Open src/server.js and import the database before starting the server.

// src/server.js
const config = require("./config");
const app = require("./app");
const db = require("./models"); // <--- Import the DB connection

const PORT = config.app.port;

// Check DB connection first
db.sequelize
    .authenticate()
    .then(() => {
        console.log("✅ Database connected successfully.");

        // Only start the server if DB connects
        app.listen(PORT, () => {
            console.log(`Server is running on port ${PORT}`);
        });
    })
    .catch((err) => {
        console.error("❌ Unable to connect to the database:", err);
    });

Run npm run dev.

If everything is correct, you should see:

✅ Database connected successfully.
Server is running on port 3001

Next Steps​

We are connected! But our database is empty. In the next section, we will define our Post Model so Sequelize understands the structure of our data.