Auto Create Database on first lauch

[D3xime]

Hello Everyone,

i am new to AdonisJs, I am a french student, and PHP/Symfony developper. I just see the database doesn't automatically created on first launch of APP, and no command can be used to auto create DATABASE.

I have created a little file to accomplish that for postgres/mysql/mariadb with the use of Ace command. I think it can be optimized but it achieved its aim.

here is create_database.ts file

import mysql from 'mysql'
import pkg from 'pg' // Import the default export from the 'pg' package
const { Client: PgClient } = pkg // Extract the Client class from the default export

async function createDatabase() {
  const dbConnection = process.env.DB_CONNECTION || 'mysql'
  let clientMySQL: mysql.Connection
  let clientPg: pkg.Client
  let query: string
  let dbName: string
  try {
    switch (dbConnection) {
      case 'mysql':
        dbName = process.env.DB_DATABASE || 'adonis_db'
        query = `CREATE DATABASE IF NOT EXISTS \`${dbName}\`;`
        clientMySQL = mysql.createConnection({
          host: process.env.DB_HOST,
          port: Number(process.env.DB_PORT),
          user: process.env.DB_USER,
          password: process.env.DB_PASSWORD,
        })
        clientMySQL.query(query)
        break

      case 'pg':
        dbName = process.env.DB_DATABASE || 'adonis_db'
        query = `CREATE DATABASE ${dbName};`
        clientPg = new PgClient({
          host: process.env.DB_HOST,
          port: Number(process.env.DB_PORT),
          user: process.env.DB_USER,
          password: process.env.DB_PASSWORD,
        })
        await clientPg.connect()
        await clientPg.query(query)
        break

      default:
        console.error('Unsupported DB_CONNECTION type:', dbConnection)
        return
    }
  } catch (error) {
    console.error(error)
  }
}

createDatabase()

Here is my command

import { BaseCommand } from '@adonisjs/core/ace'
import type { CommandOptions } from '@adonisjs/core/types/ace'
import mysql from 'mysql'
import pkg from 'pg' // Import the default export from the 'pg' package
const { Client: PgClient } = pkg // Extract the Client class from the default export

export default class CreateDatabase extends BaseCommand {
  static commandName = 'create:database'
  static description = ''

  static options: CommandOptions = {}

  async run() {
    const dbConnection = process.env.DB_CONNECTION || 'mysql'
    let clientMySQL: mysql.Connection
    let clientPg: pkg.Client
    let query: string
    let dbName: string
    try {
      switch (dbConnection) {
        case 'mysql':
          dbName = process.env.DB_DATABASE || 'adonis_db'
          query = `CREATE DATABASE IF NOT EXISTS \`${dbName}\`;`
          clientMySQL = mysql.createConnection({
            host: process.env.DB_HOST,
            port: Number(process.env.DB_PORT),
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
          })
          clientMySQL.query(query)
          break

        case 'pg':
          dbName = process.env.DB_DATABASE || 'adonis_db'
          query = `CREATE DATABASE ${dbName};`
          clientPg = new PgClient({
            host: process.env.DB_HOST,
            port: Number(process.env.DB_PORT),
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
          })
          await clientPg.connect()
          await clientPg.query(query)
          break

        default:
          console.error('Unsupported DB_CONNECTION type:', dbConnection)
          return
      }
      console.log('Database checked/created successfully.')
      return
    } catch (error) {
      console.error('Error creating database:', error)
      this.logger.error('Hello world from "CreateDatabase"')
      console.error(error)
      return
    }
  }
}

Here is the providers to achieve the migration and the creation to DB

import type { ApplicationService } from '@adonisjs/core/types'
import ace from '@adonisjs/core/services/ace'

export default class DatabaseProvider {
  constructor(protected app: ApplicationService) {}

  /**
   * Register bindings to the container
   */
  register() {}

  /**
   * The container bindings have booted
   */
  async boot() {}

  /**
   * The application has been booted
   */
  async start() {
    // Run the database creation script
    await ace.exec('create:database', [])
    await ace.exec('migration:run', [])
  }

  /**
   * The process has been started
   */
  async ready() {}

  /**
   * Preparing to shutdown the app
   */
  async shutdown() {}
}

I hope it will be implemented natively to lucid, for all SGBD.
I will be in touch to answer you.

Keep it well.
Déxime -- French Student/Dev