MySQL Database Creation

2. Create Database Using MySQL Workbench

Step 1: Connect to MySQL Instance

• Open MySQL Workbench
• Click on your MySQL connection
• Enter your password if prompted

Step 2: Create New Database

1. In the Navigator panel, right-click in the Schemas section
2. Select Create Schema...
3. Enter database name (e.g., my_nextjs_app)
4. Select character set: utf8mb4
5. Select collation: utf8mb4_unicode_ci
6. Click Apply

Step 3: Review and Execute

Workbench will show the SQL script that will be executed:

CREATE SCHEMA `my_nextjs_app` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci ;

Click Apply to create the database.

Step 4: Create User and Privileges

1. Go to Server menu → Users and Privileges
2. Click Add Account
3. Set login name: nextjs_user
4. Set password and confirm
5. Go to Schema Privileges tab
6. Click Add Entry, select your database
7. Select SELECT, INSERT, UPDATE, DELETE, etc. or ALL
8. Click Apply

3. Create Database Programmatically with Next.js

Step 1: Install MySQL Driver

npm install mysql2

Step 2: Create Database Configuration

Create lib/db.js:

import mysql from 'mysql2/promise';

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  // Note: We don't specify database here for creation
};

export async function createConnection() {
  return await mysql.createConnection(dbConfig);
}

Step 3: Create API Route for Database Creation

Create pages/api/admin/create-database.js:

import { createConnection } from '../../../lib/db';

export default async function handler(req, res) {
  // Add authentication/authorization check here
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }

  const { dbName } = req.body;
  
  if (!dbName) {
    return res.status(400).json({ message: 'Database name is required' });
  }

  let connection;
  try {
    connection = await createConnection();
    
    // Create database
    await connection.execute(
      `CREATE DATABASE IF NOT EXISTS ${dbName} 
       CHARACTER SET utf8mb4 
       COLLATE utf8mb4_unicode_ci`
    );
    
    res.status(200).json({ 
      message: `Database ${dbName} created successfully` 
    });
    
  } catch (error) {
    console.error('Database creation error:', error);
    res.status(500).json({ 
      message: 'Error creating database', 
      error: error.message 
    });
  } finally {
    if (connection) await connection.end();
  }
}

Step 4: Create Frontend Component

Create a component to trigger database creation:

import { useState } from 'react';

export default function CreateDatabaseForm() {
  const [dbName, setDbName] = useState('');
  const [message, setMessage] = useState('');
  const [loading, setLoading] = useState(false);

  const handleSubmit = async (e) => {
    e.preventDefault();
    setLoading(true);
    setMessage('');

    try {
      const response = await fetch('/api/admin/create-database', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({ dbName }),
      });

      const data = await response.json();
      
      if (response.ok) {
        setMessage(`✅ ${data.message}`);
        setDbName('');
      } else {
        setMessage(`❌ ${data.message}`);
      }
    } catch (error) {
      setMessage(`❌ Error: ${error.message}`);
    } finally {
      setLoading(false);
    }
  };

  return (
    <div className="card">
      <div className="card-body">
        <h5 className="card-title">Create New Database</h5>
        <form onSubmit={handleSubmit}>
          <div className="mb-3">
            <label htmlFor="dbName" className="form-label">
              Database Name
            </label>
            <input
              type="text"
              className="form-control"
              id="dbName"
              value={dbName}
              onChange={(e) => setDbName(e.target.value)}
              placeholder="Enter database name"
              required
            />
          </div>
          <button 
            type="submit" 
            className="btn btn-primary"
            disabled={loading}
          >
            {loading ? 'Creating...' : 'Create Database'}
          </button>
        </form>
        {message && (
          <div className="mt-3 alert alert-info">{message}</div>
        )}
      </div>
    </div>
  );
}