Database seeding in Node.js

This post was originally published on my blog.

In this article I'd like to talk about database seeding using Node.js and MySQL: what it is and how to implement it. You'll notice that it's actually quite easy and straightforward. For this tutorial I chose MySQL as database system but this procedure should also work with any other one. In this case there might be some small modifications necessary regarding the SQL query execution.

Let's start with an explanation of database seeding. Source

Database seeding is the initial seeding of a database with data. Seeding a database is a process in which an initial set of data is provided to a database when it is being installed. It is especially useful when we want to populate the database with data we want to develop in future.

So our goal is to "feed" the database with dummy data on its initialization. This can be very helpful especially during the development process or for onboarding new employees that run the development environment (database) locally on their machine.

First of all we create a new SQL script that includes the queries for inserting the dummy data. This script will be later executed using Node.js.

The seeding script: ./db/seeding.sql

/* Insert admin account */
INSERT INTO user (email, firstname, lastname, password)
VALUES ('admin@email.com', 'John', 'Doe', ?);

This SQL command inserts a new data set into the user table. The ? is a placeholder that gets replaced with a variable, the initial password, when calling the script using Node.js. You can add more queries here of course.

Make sure that the database tables were created before running the script otherwise it'll fail. Most ORMs take care of that.

Next, we write the Node.js script, which is required to establish a database connection and execute the SQL snippet we just created.

The Node.js script: ./db/index.js

const mysql = require("mysql2")
const fs = require("fs")
const bcrypt = require("bcryptjs")

// Load .env variables
require("dotenv").config()

// Read SQL seed query
const seedQuery = fs.readFileSync("db/seed.sql", {
  encoding: "utf-8",
})

// Connect to database
const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  multipleStatements: true, // IMPORTANT
})

connection.connect()

// Generate random password for initial admin user
const psw = Math.random()
  .toString(36)
  .substring(2)
const hash = bcrypt.hashSync(psw, 10)

console.log("Running SQL seed...")

// Run seed query
connection.query(seedQuery, [hash], err => {
  if (err) {
    throw err
  }

  console.log("SQL seed completed! Password for initial admin account: " + psw)
  connection.end()
})

What happens here?

1. Load the environment variables using dotenv
2. Read the SQL snippet
3. Connect to the database (MySQL)
4. Generate a random password (hashed)
5. Execute the SQL snippet

At the end the admin password is logged to the console if everything worked fine. Instead of using dotenv you can also use fs.readFileSync to load your database credentials from any other file.

If you're not using MySQL you simply have to modify the database connection setup. I'm sure there's a npm package for your database system that can handle this.

Last but not least let's extend the package.json scripts to simplify the execution of the Node script.

{
  "scripts": {
    // ...
    "seed": "node db/index.js"
    // ...
  }
}

Now you can run the database seeding with a single command from the terminal: npm run seed. That's it! You database is filled with dummy data and you can focus on the important stuff.

There's also a GitHub repository available including an example application that makes use of database seeding. Have a look.