MySQL Create Database

Node.js MySQL Create Database: Provisioning Schemas Safely

When building setup scripts or test environments in Node.js, you can create a MySQL database and its initial tables programmatically using mysql2/promise. Connect to the MySQL server without specifying a database option first, execute CREATE DATABASE IF NOT EXISTS, and then create tables using utf8mb4 encoding.

Creating a Database and Sample Table Safely

Because SQL prepared parameters (?) cannot bind database or table names, validate any dynamic identifier against a strict alphanumeric regex and wrap it with pool.escapeId() (or backticks after allowlisting).

import mysql from 'mysql2/promise';

async function bootstrapSchema(dbName) {
  if (!/^[a-zA-Z0-9_]{1,64}$/.test(dbName)) {
    throw new Error('Invalid database name');
  }

  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || '127.0.0.1',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
  });

  try {
    await connection.query(
      `CREATE DATABASE IF NOT EXISTS \`${dbName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`
    );
    await connection.changeUser({ database: dbName });

    await connection.query(`
      CREATE TABLE IF NOT EXISTS products (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        sku VARCHAR(64) NOT NULL UNIQUE,
        title VARCHAR(160) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB
    `);

    console.log(`Database '${dbName}' and table 'products' are ready.`);
  } finally {
    await connection.end();
  }
}

bootstrapSchema('shop_sandbox').catch((err) => console.error('Setup failed:', err.message));

connection.query() vs connection.execute() for DDL

MySQL does not support server-side prepared statements for DDL commands like CREATE DATABASE or CREATE TABLE (ER_UNSUPPORTED_PS), so use connection.query() for schema setup and pool.execute() for SELECT/INSERT/UPDATE/DELETE queries.

Common Mistakes to Avoid

• Passing database: 'new_db' before creating it: Connecting to a nonexistent database fails immediately with ER_BAD_DB_ERROR. Omit database first, create it, then switch with changeUser() or open a new pool.
• Forgetting finally { await connection.end(); }: An unhandled SQL error leaves the setup script hanging forever because an open TCP socket keeps the Node.js event loop alive.
• Using CREATE DATABASE in live web request routes: Run schema provisioning and migrations in dedicated CLI scripts or deployment pipelines, not inside HTTP handlers.

Frequently Asked Questions

What does IF NOT EXISTS prevent?

It prevents MySQL from throwing ER_DB_CREATE_EXISTS or ER_TABLE_EXISTS_ERROR if your bootstrap script runs more than once.

Why use DECIMAL(10, 2) instead of FLOAT for prices?

DECIMAL stores exact base-10 decimals so $19.99 never turns into $19.989999 during totals and tax calculations.

Should I use raw CREATE TABLE statements or migrations in larger Node apps?

For production apps, use versioned migration tools like Knex, Prisma Migrate, or Drizzle Kit so schema changes stay tracked in Git.