Over the past week I’ve been working on a number of Adonis projects in development environments that have required lots of database work to get relationships between models working. Additionally, these projects use either PostgreSQL or MySQL databases. This requires me switching between PgAdmin and MySQL Workbench depending on the project I’m on.

With this heavy database work going on, I decided to make some custom commands to speed up development. They’re not perfect, but they work as intended and help to save a bit of time here and there when working with databases. Each command is built with Adonis’ Ace framework for building CLIs which made building the commands super quick as all the boilerplate is handle by the framework.

So lets get into the two commands;

tables:list

A simple command to list all the tables in the database. I’m yet to figure out why I added this command, but it seemed cool to add.

The code for it;

import { BaseCommand } from "@adonisjs/core/build/standalone";
import Database from "@ioc:Adonis/Lucid/Database";

export default class DbListTable extends BaseCommand {
  public static commandName = "tables:list";

  public static description = "";

  public static settings = {
    loadApp: true,
    stayAlive: false,
  };

  public async run() {
    const { default: Env } = await import("@ioc:Adonis/Core/Env");

    if (Env.get("NODE_ENV") !== "development") {
      return this.logger.error("Running in production; Exiting");
    }

    const tables = await Database.connection().getAllTables();

    this.logger.info(`Showing ${tables.length} Tables`);
    tables.forEach((table, index) => this.logger.log(`${index} - ${table}`));
  }
}

tables:view

Table view shows the first 5 rows in a table, but only limits the amount of columns shown to 5 as well. This has helped me quickly check that rows have been inserted into table when testing API endpoints. I think in the future, I’ll add some extra parameters to allow a variable amount of columns to be shown, and even querying.

The code for this command;

import { args, BaseCommand } from "@adonisjs/core/build/standalone";
import Database from "@ioc:Adonis/Lucid/Database";

export default class DbViewTable extends BaseCommand {
  public static commandName = "tables:view";
  public static description = "Drops the specified table from the main database";

  @args.string({ name: "table", description: "Name of the table to drop" })
  public table: string;

  public static settings = {
    loadApp: true,
    stayAlive: false,
  };

  public async run() {
    const { default: Env } = await import("@ioc:Adonis/Core/Env");

    if (Env.get("NODE_ENV") !== "development") {
      return this.logger.error("Running in production; Exiting");
    }

    const doesTableExist = await Database.connection().table(this.table);
    if (!doesTableExist) {
      return this.logger.error("Table doen't exist");
    }

    try {
      const results = await Database.connection().from(this.table).limit(10);

      if (!results.length) {
        return this.logger.info("No rows in table.");
      }

      const table = this.ui.table();

      table.head(
        Object.keys(results[0])
          .slice(0, 5)
          .filter((key) => typeof key === "string")
      );

      results.forEach((result) => {
        const values = Object.values(result);

        table.row(
          values.slice(0, 5).map((value) => {
            if (typeof value !== "string") {
              return "";
            }

            return String(value);
          })
        );
      });

      table.render();

      return;
    } catch (e) {
      console.log(e);
      return this.logger.error("Something went wrong");
    }
  }
}

These two commands have saved some time here and there whilst working with databases. I might expand them in the future as I continue to work on various database-intensive Adonis projects.

Thanks for reading and happy coding! 🙂