Inquirer.js populate list choices from sql database

Question

I"m using inquirer.js to build a commandline employee database. Is it possible to populate the choices array dynamically from a sql database in inquirer.js?

for example

inquirer.prompt([
        {   type: 'list',
            message: "Choose department to add Role to",
            name: "department",
            choices: [
                `SELECT * FROM departments`
            ]
        }
    ])

deparments is the table I want to acceess I'd want it to return the list of all departments as the choices

Answer 1

I actually ran into this exact problem recently and have a functioning solution!!

Inquirer's choices key can be defined using a function but expects the function to return an array of objects; each object represents a choice and has a name: key to be shown to the user as an option for the choice, and a value: key which will be what inquirer stores as the answer value in the answer object (like a number, string, or boolean etc.). Here is an example of a structure the choices key expects to be returned from the function:

[
   {
      name: 'Choice 1',
      value: 1,
   },
   {
      name: 'Choice 2',
      value: 'Something',
   },
   {
      name: 'Choice 3',
      value: false,
   }
]

Make sure that you check the data that your function returns and ensure that you have this format so that inquirer's choice key accepts it!

Here is how I implemented it with a database:

I am using a .promise() on my connection so that I can use async/await syntax. Here is my connection setup for mysql2:

// use .promise() so that we can use await on connection
const connection = mysql.createConnection({
    host: 'localhost',
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: 'employees_db',
}).promise();

Here is the particular question object that has choices: being defined by a function called departmentChoices() that is getting all the department choices from mysql database. Make sure this is defined in an async function as well so you can use await.

        {
            message: "Which Department's budget do you want to see?",
            name: 'id',
            type: 'list',
            choices: await departmentChoices(),
            when(answers) {
                return answers.task === 'View a Department Budget';
            },
        },

Here is the function definition. I chose to use async/await for the database query for easy of readability. You should probably wrap it in a try/catch for better error handling and best practices. Notice I used an alias value for the id as this is the value I want to use for the value: key in the choice object. name did not need an alias as it is the value I want stored under the name: key in the choice object.

const departmentChoices = async () => {
    const departmentQuery = `SELECT id AS value, name FROM department;`;
    const departments = await connection.query(departmentQuery);
    return departments[0];
};

This is the array this function will return from my database:

[
  { value: 1, name: 'Hair and Makeup' },
  { value: 2, name: 'Finance' },
  { value: 3, name: 'Set Design' },
  { value: 4, name: 'Wardrobe' }
]