DB-Migrate Simplified – How to Generate PosgreSQL Database From Node.js

In this tutorial, you will learn about migrations and how to use it to simplify database operations for your applications. We would be using Node.js with PostgreSQL database. However, the same applied to MySQL and other databases as well.

We would cover the following:

1. What are database migrations
2. Setup Node.js Connection to PostgreSQL
3. Create Migration using db-migrate
4. Generating scripts and Running Migrations

1. What are database migrations

Database migration allows you to make updates to your data model be executing a script in your application. This could be JavaScript or Typescript. The benefit is that your database would always be in sync with the models in your application. Also, when something changes in your database, you will be able to generate the changes. Another benefit is that you can used migrations to initialize a new database when application is deployed.

These script in turn executes SQL statement which run against your database and make the needed changes.

2. Setup Node.js Connection to PostgreSQL

Now we would create a PostgreSQL database, a Node.js application and connect the two. Follow the steps below to get this done.

Step 1 – Create a database in PostgreSQL using PgAdmin. I named it migrationdb. Add a table named Users. Add some data

Step 2 – Create a Node.js application.

Step 3 – home.js. The content is as follows:

const {Client} = require('pg')

const client = new Client({
    host: "localhost",
    port : 5432,
    user: "postgres",
    password: "password",
    database: "migrationdb"
})

client.connect();

let query = `Select * from "Users"`;

client.query(query, (err, res)=>{
    if(!err){
        console.log(res.rows);
    } else{
        console.log(err.message)
    }
    client.end;
})

At his point, you can run the index.js file using the node command, you will see that the data is displayed in the console. Now, we would add migration.

3. Create Migration using db-migrate

To create migration, we need to install the db-migrate module. To do that,

Step 1 – Run the commands below one after the other:

npm install -g db-migrate
npm install -g db-migrate-pg

Normally db-migrate also needs to connect to the database.

Step 2 – And to make this connection, you need to create a database.json file with the content

{
    "dev": {
      "driver": "pg",
      "user": "postgres",
      "password": "password",
      "host": "localhost",
      "database": "migrationdb"
    }
  }

Step 3 – Now run the command db-migrate –sql-file

db-migrate create initialize --sql-file

If the command runs successfully, you will notice that a migrations folder is created. This folder contains a sub-folder named sqls which contains two files ending with ..initialize-up.sql and initialize-down.sql. This is where you will write your sql queries.

4. Creating SQL Scripts and Running Migrations

Now if you know how to write SQL statements you can just write the create table command in the ..initialize-up.sql file. Then write the drop table command in the intialize-down.sql file. But now, let’s make life easy! Let generate the scripts. Follow the steps.

Step 1 – In PgAdmin, right-click on the Users table and choose Scripts > Create as shown below:

Step 2 – Copy the generated code into your …initialize-up.sql file. It should look like this:

-- Table: public.Users

-- DROP TABLE public."Users";

CREATE TABLE public."Users"
(
    id bigint,
    firstname character varying COLLATE pg_catalog."default",
    lastname character varying COLLATE pg_catalog."default"
)

TABLESPACE pg_default;

ALTER TABLE public."Users"
    OWNER to postgres;

Step 3 – Write the ‘Drop Table’ statement in the …initialize_down.sql file.

Step 4 – Now, you can delete the table from your database using PgAdmin

Step 5 – In node.js, run the command below:

db-migrate up initialize

We are not done yet! There’s still much to do with migrations. So I have decided to cover all this in the video tutorial. So I recommend you watch the DB-Migration Tutorial on my YouTube Channel.