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

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

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

 

EXPORT DATABASE TO EXCEL

All right, let us now get into the example of exporting data from the MySQL database to writing it to an Excel file.

 

QUICK SETUP

Run npm i mysql xlsx to install the required modules.

 

 

PART 1) DUMMY DATABASE

1-users.sql
-- (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

2-export.js
// (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.

 

 

EXTRAS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

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

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

2 thoughts on “Export MySQL Database To Excel In NodeJS (Simple Example)”

  1. So do your own due diligence if you have to deal with large exports. Change the export process to “read row by row” and “append to the Excel file row by row” instead.

    how will i do that

    1. After reading a little more on SheetJS – It seems that they don’t have solutions for large files as well. The only way is to “export in batches” and/or split into multiple Excel files.

Comments are closed.