DEV Community

Cover image for Database seeding in Node.js
Lars Wächter
Lars Wächter

Posted on

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', ?);
Enter fullscreen mode Exit fullscreen mode

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()
})
Enter fullscreen mode Exit fullscreen mode

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"
    // ...
  }
}
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (7)

Collapse
 
momander profile image
Martin Omander

I also find the faker.js npm package useful when I need to put large amounts of dummy data in a test database.

Collapse
 
ausmurp profile image
Austin Murphy

I think you should take this article a step further. The most popular use case for seeding is so you know the ID of lookup table values, and can then use enums to account for those value checks in code, like this:

if (someObject.statusId === SomeObjectStatus.Active) {
...
Enter fullscreen mode Exit fullscreen mode
Collapse
 
ausmurp profile image
Austin Murphy

Also, how do you account for when another record needs added to this lookup table, another status. How can the seed script run and only insert the new values? This is built into some ORMs like Entity Framework, but it would be nice to see this in node as a lot of those ORMs do not have that capability.

Collapse
 
ali_ah profile image
Ali Ahmad

I had to do something similar in one of my Dev projects and now i know what it called 😁 soo "database seeding" thank you! ❤

Collapse
 
arnoldomondi7 profile image
Arnold Omondi

why not use postman instead?

Collapse
 
kimah1999 profile image
Kimberly Aguilar Hermoso

Postman is more known for API Development data vs Node where the run time is faster for created data, best for testing.