How to create thousands of tables in a database quickly

Recently, as part of my job, I had to test some code against thousands of tables. Of course, I didn’t want to create them manually. Before delving into further research, I thought, why not ask ChatGPT first to help me with this? So, I asked ChatGPT to create a NodeJS module for generating 10k tables in a MariaDB database. Surprisingly, it did a pretty good job. I made some minor tweaks to the code, and it worked like a charm. Now, I’m going to share that code.

I created a folder named create-tables and within it, I added two files: index.js and package.json. The index.js file contains the code for table creation, while the package.json file contains the dependencies for the project.

File index.js:

const mariadb = require('mariadb');

// Modify this based on your database config
const pool = mariadb.createPool({
  host: 'localhost',
  user: '',
  password: '',
  database: '',
  connectionLimit: 5
});

async function createTables() {
  let connection;
  try {
    connection = await pool.getConnection();

    for (let i = 1; i <= 10000; i++) {
      const tableName = `test_table_${i}`;
      const createTableQuery = `
        CREATE TABLE ${tableName} (
          id INT,
          name VARCHAR(50),
          email VARCHAR(255),
          address VARCHAR(100)
        )
      `;
      await connection.query(createTableQuery);
      console.log(`Created table ${tableName}`);
    }
  } catch (error) {
    console.error('Error creating tables:', error);
  } finally {
    if (connection) {
      connection.release();
      process.exit();
    }
  }
}

createTables();

JavaScript

File package.json:

{
  "name": "create-mariadb-tables",
  "version": "1.0.0",
  "description": "Node.js module to create 10000 tables in a MariaDB database",
  "main": "index.js",
  "scripts": {
    "start": "node index.js"
  },
  "author": "Kowsar Hossain",
  "license": "MIT",
  "dependencies": {
    "mariadb": "^2.5.4"
  }
}

JSON

To use this code, you need to install the dependencies first. To do so, open the terminal and navigate to the folder where you created the files. Then run the command: npm install. This will install all the dependencies. Finally, run the command: npm start. It will start creating the tables. Once the process is complete, you can check the database to see if all the tables have been created or not.

Leave a ReplyCancel Reply