Abed's Blog

Create a Database Build Script Using Node and PostgreSQL

Abedalrahman Shamia's photo
Abedalrahman Shamia
·

6 min read

Create a Database Build Script Using Node and PostgreSQL

Today I'm going to show you how to build a database script using Node and PostgreSQL from scratch.

I remember when I first started to learn about databases. I’d have to repeat the queries to build the database each time I needed to test it. It was super time consuming and sometimes frustrating. And in this simple guide I’ll cut out all the noise and show you exactly how to start creating your own build scripts to your website. Let's start!

What is a build script, exactly?

a Build script is a file that is started by a build plan. That would prepare the output from generated files. It will execute the same sequence of instructions exactly the same way, given the same input.

What are the benefits of using build script?

  • Provides a regular health-check for the database.
  • Minimize time and effort building a database.
  • Much more helpful for a team to work on the exact same schema.
  • For the purpose of testing the database.
  • And in the early phases of production when you need to visualize the data.

Now that you understand what build script is, it’s time for me to explain more about how to get started with it.

Before you start, you need to have:

  • Basic knowledge of Node.
  • Basic knowledge of SQL and PostgreSQL

Steps

File Structure and Modules required

Your first step is actually to require the modules that are needed.

Let's start by creating a folder for the purpose of this guide. Inside the folder

Run npm init -y in the terminal.

Once you have the package.json file let's install the required modules by running

npm install express pg

Express is a minimal framework over Node that allows us to easily handle various functionalities of node.

PG is a collection of Node.js modules for interfacing with your PostgreSQL database.

The project structure would be in this way

structure.png

Don't worry we will get into each file, write the code for it and explain what it does.

Starting with the app.js that will be responsible for creating and listening to server.

Inside the app.js write the following code

const express = require('express');
const app = express();
const PORT = process.env.PORT || 3000;

//Listen to server
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

In the terminal run node app.js and check if the server is running.

Now we made sure that server is ready, let's set up our PostgreSQL database configurations.

PostgreSQL Configurations

Inside the database folder, write the following code inside dbconfig.js

module.exports = {
  user: 'postgres', //Your username, it's postgres by default unless you changed it
  password: '', //Your password
  database: '', //Your database name that you want to create data in
  host: 'localhost', //Your host
  port: 5432, //Your port
};

Change the configurations of the file to match yours.

Inside the database folder, Write the following code in connection.js to create our connection pool.

const {Pool} = require('pg');
const {user, password, host, database, port} = require('./dbconfig');

const connection = new Pool({user, password, host, database, port});

module.exports = connection;

Connect the server with the database

Now that we have set the Connection configurations and settings, let's establish a connection between the server and the database.

In the app.js file write the following code

const connection = require('./database/connection');

//test connection
connection.connect(err => {
  if (err) {
    console.log(err);
    return;
  }
  console.log('Connected to the database');
});

Now run node app.js, and check for Connected to the database . Now we are ready to start building our build script.

Create the build script

Let's start by putting our SQL commands to create the tables we need in the build.sql , You can build your own as long as you know basic SQL, but for now let's stick to the guide.


BEGIN;

DROP TABLE IF EXISTS users, posts;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE  NOT NULL,
  created_at DATE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100)  NOT NULL,
  body TEXT NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  created_at DATE DEFAULT CURRENT_TIMESTAMP
);


INSERT INTO users (username, password, email ) VALUES
 ('John Doe', 'password', 'John@Doe.com'),
  ('Jane Doe', 'password', 'Jane Doe'), 
  ('John Smith', 'password', 'John@Smith.com');

INSERT INTO posts (title, body, user_id) VALUES
 ('Post 1', 'This is the first post', 1),
  ('Post 2', 'This is the second post', 2),
   ('Post 3', 'This is the third post', 3);

COMMIT;

Now that we have our SQL commands ready to be executed, let's make the build code in our build.js

const fs = require('fs');
const connection = require('./connection');

//Read file and parse it to string
const sqlQuery = fs.readFileSync('./database/build.sql', 'utf-8');

// Execute query
connection.query(sqlQuery, err => {
  if (err) {
    console.log(err);
    return;
  }
  console.log('Database built successfully');
});

// End connection
connection.end();

Finally we are done with the build code we can run it either by

node database/build.js

or to make it easier and more practical let's add specific scripts.

in package.json file under "scripts" , add

// to run the server 
"start" : "node app.js",
// to build the data in the database
"build" : "node database/build.js"

Run npm run build in terminal

If console logged Database built successfully then Congratulations you just made your first build script.

Check Data

Now to check the data you can either check it in PG Shell, or through the server.

Let's check it through the server.

In app.js add the following code 

app.get('/users', (req, res) => {
  connection.query('SELECT * FROM users', (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).send('Internal server error');
    }
    res.status(200).send(results.rows);
  });
});

Run npm start in the terminal

In browser go to http://localhost:3000/users and check for the data that has been retrieved.

This is the final version of app.js 

const express = require('express');
const app = express();
const connection = require('./database/connection');

//test connection
connection.connect(err => {
  if (err) {
    console.log(err);
    return;
  }
  console.log('Connected to the database');
});

//test query to database
app.get('/users', (req, res) => {
  connection.query('SELECT * FROM users', (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).send('Internal server error');
    }
    res.status(200).send(results.rows);
  });
});

const PORT = process.env.PORT || 3000;

//Listen to server
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

That's it everyone, now you don’t need to spend much time building a database for early production phase, mess the testing phase or not be able to visualize the data anymore. You have everything you need to get started all in one place.

Before you start building your first script, make sure to leave a quick comment to let me know what you think of Create a Database Build Script Using Node and PostgreSQL.

Node.jsPostgreSQLExpressdatabasenpm