Export From Database To CSV In NodeJS (Simple Example)

Welcome to a quick tutorial on how to export from a database to a CSV file with NodeJS. Need to generate a CSV report? Want to export a list from the database? Well, let us walk through a simple example – Read on!

 

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

TABLE OF CONTENTS

 

 

NODEJS EXPORT DATABASE TO CSV

All right, let us now get into the example of exporting database data to a CSV file.

 

QUICK SETUP

Run npm i sqlite3 csv-stringify to install the required modules.

 

PART 1) DUMMY DATA

1A) DUMMY USER DATABASE

1-users.sql
CREATE TABLE users (
  id INTEGER,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  PRIMARY KEY("id" AUTOINCREMENT)
);

INSERT INTO users (name, email) VALUES 
("Joa Doe", "joa@doe.com"),
("Job Doe", "job@doe.com"),
("Joe Doe", "joe@doe.com"),
("Jog Doe", "jog@doe.com"),
("Joi Doe", "joi@doe.com"),
("Jon Doe", "jon@doe.com"),
("Jor Doe", "jor@doe.com"),
("Jos Doe", "jos@doe.com"),
("Jou Doe", "jou@doe.com"),
("Joy Doe", "joy@doe.com");

For this example, we will be working with this dummy user table. This should be pretty self-explanatory:

  • id User ID. Primary key, auto-increment.
  • name User name.
  • email User email.

 

 

1B) CREATE DATABASE

1b-create.js
// (A) REQUIRED MODULES
const fs = require("fs"),
sqlite = require("sqlite3");

// (B) CREATE DUMMY DATABASE
const db = new sqlite.Database("users.db", err => {
  if (err) { console.log(err); }
  else {
    db.exec(fs.readFileSync("1a-users.sql", "utf8"));
    db.close();
    console.log("Database created");
  }
});

Next, this little snippet will read the above SQL and create the actual database file.

 

PART 3) EXPORT DATABASE TO CSV

2-export.js
// (A) LOAD REQUIRED MODULES
// https://www.npmjs.com/package/csv-stringify
const sqlite = require("sqlite3"),
fs = require("fs"),
csv = require("csv-stringify");
 
// (B) EXPORT USERS
const db = new sqlite.Database("users.db");
db.all("SELECT * FROM users", (err, rows) => rows.forEach(row => {
  row = Object.values(row);
  csv.stringify([row], (err, output) => {
    console.log(row);
    fs.appendFileSync("demo.csv", output);
  });
})); 
db.close();

Sorry to the people who are expecting “hacker code”, this is all we need… Do a “regular select” from the database, and append rows to the CSV file. The end.

 

 

DOWNLOAD & NOTES

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

 

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

 

EXAMPLE CODE DOWNLOAD

Click here for the 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.

 

EXTRA BITS & LINKS

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

 

HOW ABOUT OTHER DATABASES?

MYSQL, MSSQL, POSTGRESQL, MongoDB – The connection and libraries are different from each other. But the “select from the database and write to CSV” core process remains the same… You will have to do your own homework on the database basics, it’s impossible to cover everything in this tutorial.

 

 

LINKS & REFERENCES

 

INFOGRAPHIC CHEAT SHEET

NodeJS Export Database To CSV (click to enlarge)

 

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!

Leave a Comment

Your email address will not be published. Required fields are marked *