Export MySQL Database To Excel In NodeJS (Simple Example)

Welcome to a tutorial on how to export data from a MySQL database to an Excel file in NodeJS. So you need to generate a report from the database, or create a list? Well, it is actually a simple “fetch from database and writes to Excel file” process – Read on for the example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

QUICK SETUP

Run npm i mysql xlsx to install the required modules.

 

 

PART 1) DUMMY DATABASE

-- (A) USERS TABLE
CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_name` (`user_name`);
 
ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
 
-- (B) DUMMY USERS
INSERT INTO `users` (`user_id`, `user_name`, `user_email`) VALUES
(1, 'Jo Doe', 'jo@doe.com'),
(2, 'Job Doe', 'job@doe.com'),
(3, 'Joe Doe', 'joe@doe.com'),
(4, 'Jog Doe', 'jog@doe.com'),
(5, 'Joh Doe', 'joh@doe.com'),
(6, 'Joi Doe', 'joi@doe.com'),
(7, 'Jol Doe', 'jol@doe.com'),
(8, 'Jon Doe', 'jon@doe.com'),
(9, 'Jou Doe', 'jou@doe.com'),
(10, 'Joy Doe', 'joy@doe.com');

First, let us start by creating a simple users database and defining some dummy users.

• user_id User ID, primary key, and auto-increment.
• user_name The user’s name.
• user_email The user’s email.

 

 

PART 2) EXPORT TO EXCEL FILE

// (A) LOAD MODULES
// npm install mysql xlsx
// https://www.npmjs.com/package/mysql
// https://www.npmjs.com/package/xlsx
const mysql = require("mysql"),
      xlsx = require("xlsx");
 
// (B) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "test"
});
db.connect();
 
// (C) EXPORT TO EXCEL
db.query("SELECT * FROM `users`", (error, results) => {
  // (C1) EXTRACT DATA FROM DATABASE
  if (error) throw error;
  var data = [];
  results.forEach(row => {
    data.push([row["user_name"], row["user_email"]]);
  });
 
  // (C2) WRITE TO EXCEL FILE
  var worksheet = xlsx.utils.aoa_to_sheet(data),
      workbook = xlsx.utils.book_new();
  xlsx.utils.book_append_sheet(workbook, worksheet, "Users");
  xlsx.writeFile(workbook, "demo.xlsx");
});
 
// (D) DONE - CLOSE DB CONNECTION
db.end();

Lastly, I don’t think this needs a line-by-line explanation…

1. Load the necessary modules. Yes, NodeJS does not have the native capabilities to connect to MySQL and generate Excel files. Just make sure that you have properly installed mysql and xlsx.
2. Connect to the MySQL Database. A reminder to change the settings to your own.
3. Extract data from the database, and write them into a new Excel file.
4. Close the database connection.

The end.

 

 

 

EXPORT PERFORMANCE & EFFICIENCY

Take note of how the export works – We are reading all the database entries into the data array first, then writing them into the Excel file in one shot. While this works for small to medium datasets, this will probably run into performance issues while exporting massive amounts of data. So do your own due diligence if you have to deal with large exports:

• Do not read thousands of rows into an array at once; Split into multiple batches if you must.
• Export by batches if you must.
• Split into multiple Excel files if you must.

 

LINKS & REFERENCES

• SheetJS – NPM
• MySQL – NPM
• Import Excel Into Database In NodeJS – Code Boxx