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 write to Excel file” process – Read on for the example!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

 

QUICK NOTES

• Download and unzip into your project folder. For example, D:\xlsx
• Create a database for testing, and import 1-users.sql.
• Open 2-export.js, change the database settings to your own.
• Launch the terminal, navigate to your project folder cd D:\xlsx. Install the required modules – npm install mysql xlsx.
• Run the demo – node 2-export.js

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

 

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. Change the export process to “read row by row” and “append to the Excel file row by row” instead.

 

LINKS & REFERENCES

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