If you found yourself logging into psql every time you needed to just drop and create a database while building your AdonisJS project, I have good news for you.

AdonisJS allows you to write your own custom node ace scripts to make your development process much smoother.

First off, we'll be using the pgtools library to help us create and drop the database.

# npm
npm i -D pgtools

# yarn
yarn add -D pgtools

Now, create a new command for creating and dropping the database. Since there's already a db:seed, I think db:create and db:drop will be appropriate.

node ace make:commmand db:create
node ace make:command db:drop

These scripts will generate two new files, commands/dbCreate.ts and commands/dbDrop.ts.

To create a database, we'll use pgtools.createdb, which takes a config object as the first argument, and the database name as the second. The third argument will be a NodeJS style callback.

import { BaseCommand } from "@adonisjs/core/build/standalone";
import * as pgtools from "pgtools";

const config = {
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD,
  port: process.env.PG_PORT,
  host: process.env.PG_HOST,
};

const createDb = async () =>
  await new Promise<any>((resolve, reject) => {
    pgtools.createdb(config, process.env.PG_DB_NAME, (err, res) => {
      if (res) {
        resolve(res);
      }
      if (err) {
        reject(err);
      }
    });
  });

The config object uses our environment variables to populate the values. Rather than use the pgtools.createdb function directly, we'll wrap it in a Promise since generated run method of our DbCreate class uses the async/await syntax. I also want to pass through the err and res arguments so the logger can log out those messages.

export default class DbCreate extends BaseCommand {
  /**
   * Command name is used to run the command
   */
  public static commandName = "db:create";

  /**
   * Command description is displayed in the "help" output
   */
  public static description = "Create database";

  public static settings = {
    /**
     * Set the following value to true, if you want to load the application
     * before running the command
     */
    loadApp: true,

    /**
     * Set the following value to true, if you want this command to keep running until
     * you manually decide to exit the process
     */
    stayAlive: false,
  };

  public async run() {
    try {
      const res = await createDb();
      this.logger.info(res.command);
    } catch (err) {
      this.logger.error(err);
    }
  }
}

We need to change the loadApp property to true because we need to load the app so that we can use the environment variables available through the .env file.

Within the run method, we will wrap the call to createDb in a try/catch block. If anything goes wrong, I want to log out the error, if everything is fine, I want to log out the command property that pgtools.createdb returned.

We'll use the same approach with db:drop here as well:

import { BaseCommand } from "@adonisjs/core/build/standalone";
import * as pgtools from "pgtools";

const config = {
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD,
  port: process.env.PG_PORT,
  host: process.env.PG_HOST,
};

const dropDb = async () =>
  await new Promise<any>((resolve, reject) => {
    pgtools.dropdb(config, process.env.PG_DB_NAME, (err, res) => {
      if (res) {
        resolve(res);
      }
      if (err) {
        reject(err);
      }
    });
  });

export default class DbDrop extends BaseCommand {
  /**
   * Command name is used to run the command
   */
  public static commandName = "db:drop";

  /**
   * Command description is displayed in the "help" output
   */
  public static description = "Drop database";

  public static settings = {
    /**
     * Set the following value to true, if you want to load the application
     * before running the command
     */
    loadApp: true,

    /**
     * Set the following value to true, if you want this command to keep running until
     * you manually decide to exit the process
     */
    stayAlive: false,
  };

  public async run() {
    try {
      const res = await dropDb();
      this.logger.info(res.command);
    } catch (err) {
      this.logger.error(err);
    }
  }
}

After creating the commands, run:

node ace generate:manifest

This will create a JSON index of all available commands.

Now you can use node ace db:create to quickly create a database and node ace db:drop to quickly drop one.