How to create a Postgres Database with NodeJS

Introduction on how to create a Postgres database using NodeJS

If you’re using a NodeJS application to interact with PostgreSQL, you’ll soon find that you can perform a wide variety of database operations from your code. Not only can you query tables and update them with new records, but you can even create a new database. In this article, we’ll show you how to create a Postgres database with NodeJS using a simple application as our example.

Prerequisites to using PostgreSQL and NodeJS

Before we can dive into the NodeJS code, there are a few essential prerequisites that need to be in place for this tutorial:

  • You’ll need to have PostgreSQL installed on your machine. You can use the command service postgresql status to see if Postgres is installed– the command will tell you if the status is active.

  • You’ll also need to have psql installed on your machine. If you’re not sure whether this command-line interface for PostgreSQL is installed, simply use the command psql -V to find out.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Install NodeJS on macOS, Windows, and Linux

We’ll need to install NodeJS in order to build an app that will create a new PostgreSQL database. The installation process will vary depending on your operating system. On Windows, you can just download the interactive installer for Node.

Install NodeJS on Linux

You can install NodeJS using the following command in the terminal if you’re using an Ubuntu distribution of Linux that utilizes the APT-GET repository:

sudo apt-get install nodejs

Otherwise, you can use the YUM repository if you’re using a Red Hat distribution of Linux such as CentOS or Fedora. You’ll need to use the following command:

sudo yum install nodejs

Install NodeJS on macOS using Homebrew

If you’re running a localhost Node server on macOS, you can install NodeJS using Homebrew’s brew command:

brew install node

Verify that NodeJS and its ‘npm’ package manager are installed

If you already have NodeJS installed, you can see what version you have by typing the command node -v. The output will look like this:

v12.10.0

You can also use the -v option to have Node return its current installed version of npm:

npm -v

Set up the NodeJS project directory for the Postgres app

The next thing we need to do is set up a project directory for our application. To do this, we’ll open a terminal or command prompt window and use the mkdir command to create a new directory for our Node project. After creating that directory, we can then change into it using cd.

The quickest way to set up a Node project is to run the npm init -y command inside your project directory.

Screenshot of a Node project for Postgres being initialized

This will create the package.json that you’ll need for your Node project.

Use the Node Package Manager to install the ‘pg’ Postgres packages

You’ll also need to install the pg client for PostgreSQL and add this package to your dependencies. You can do this by running the following command in your project directory:

npm i pg

NOTE: You can also use the command npm install pg.

The command will return a response that looks like the following:

+ pg@7.12.1
updated 1 package and audited 16 packages in 1.665s
found 0 vulnerabilities

You’ll also need the pgtools library– this will allow you to modify databases on your PostgreSQL server. You can use the npm package manager to install that as well:

npm i pgtools

Create a JavaScript file for the NodeJS project

At this point, we’re ready to create our Javascript file. Using Notepad, Text Editor or a third-party application such as VSCODE or SUBLIME, create a file with a JavaScript extension (.js). This will be used to run the Node server. For this example, our file will be named test.js.

test.js

var pgtools = require("pgtools");
const config = {
  user: "postgres",
  host: "localhost",
  password: "1234",
  port: 5432
};

pgtools.createdb(config, "some_db", function(err, res) {
  if (err) {
    console.error(err);
    process.exit(-1);
  }
  console.log(res);
});

Now, go to the directory where this file was created and saved. Use the command node test.js to run the code. The output should look like the following:

Result {
command: 'CREATE',
rowCount: NaN,
oid: null,
rows: [],
fields: [],
_parsers: [],
RowCtor: null,
rowAsArray: false,
_getTypeParser: [Function: bound ]
}

It seems like our database has been created, but we can check PostgreSQL to see if it’s actually been saved there. Use the \l command while connected to PostgreSQL in the psql command-line interface, and look for the name of the new database.

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
sample | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
sampledb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
some_db | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
template0 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
test-db | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
testdb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
(10 rows)

Conclusion on how to create a PostgreSQL database using NodeJS

If you’re a NodeJS developer, you’ll find that you can interact with PostgreSQL from a Node application in a wide variety of ways. In this article, we looked at one database operation in particular– creating a new database. With our example code to serve as a guide, you’ll be able to create a Postgres database with NodeJS in your own applications.

Just the Code

Here’s all the code we used in this tutorial to create a Postgres database with NodeJS:

var pgtools = require("pgtools");
const config = {
  user: "postgres",
  host: "localhost",
  password: "1234",
  port: 5432
};
pgtools.createdb(config, "some_db", function(err, res) {
  if (err) {
    console.error(err);
    process.exit(-1);
  }
  console.log(res);
});

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.