Create Your Own AdonisJS Commands to Create and Drop Databases
Tags: node, postgres, adonisjs
Published: 2021-08-13 13:25:47

---
title: Create Your Own AdonisJS Commands to Create and Drop Databases
description: 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.
published: true
date: 2021-08-12 00:00:00 UTC
tags: [NodeJS, PostgreSQL, AdonisJS]
canonical_url: https://warrenwong.org/posts/create-drop-db-adonis-cli
---

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`](https://github.com/olalonde/pgtools) library to help us create and drop the database.

```bash
# 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.

```bash
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.

```ts
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`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise) since generated `run` method of our `DbCreate` class uses the [`async/await`](https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Asynchronous/Async_await) syntax. I also want to pass through the `err` and `res` arguments so the logger can log out those messages.

```ts
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`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/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:

```ts
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:

```bash
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.