execute mysql dump file to create table and feed data in nodejs project

Question

I am working on nodejs project. I have following mysql dump file. Rather go on and creating that table manually and feed data manually using mysql queries, I want to execute following dump file which will create the table and feed/insert the data in that table. How can I do that with command?

  CREATE TABLE employees (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(50),
    location varchar(50),
    PRIMARY KEY (id)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

  INSERT INTO employees (id, name, location) VALUES
  (1, 'Jasmine', 'Australia'),
  (2, 'Jay', 'India'),
  (3, 'Jim', 'Germany'),
  (4, 'Lesley', 'Scotland');

UPDATE

I want something similar in node js like below is in Ruby on rails

Populating database using seeds.rb

Or in Laravel is here

Is it something that you want to execute it one-off or do you need something to manage your database change in general? — Stavros Zavrakas
– Stavros Zavrakas, Commented Oct 7, 2016 at 23:35
All I need to execute that file which should create the table and insert the data. Thats all. — Om3ga
– Om3ga, Commented Oct 8, 2016 at 0:08
are you looking for a script to convert mysql data dump to a node.js seed code? — Sergiy Tytarenko
– Sergiy Tytarenko, Commented Oct 25, 2016 at 0:57

Community · Accepted Answer · 2017-05-23 12:19:16Z

8

The way to run an SQL script is by opening a process to execute the mysql command-line client with the SQL script as input.

Some people try to split the SQL file, and run statements one at a time. But this doesn't work in general. There are some statements in a dump file (e.g. DELIMITER) that cannot be run against the server, because they're builtin commands for the mysql client.

Here's a past Stack Overflow question that shows how to use child_process to spawn the mysql client:

Spawn a mysql process to import a database using node

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Oct 7, 2016 at 23:43

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stavros Zavrakas · Accepted Answer · 2016-10-26 13:19:37Z

A tool that you can use is the node-db-migrate but I don't know if it is an overkill for your purpose. In general, it is used to manage database migrations but it seems that you can dump sql files and they will be executed. Here you can find the documentation. Here are the steps:

npm install db-migrate --save
npm install db-migrate-mysql --save
mkdir migrations

After that, you have to create a config file that will manage your database connections in different environments (the default name that the module will look for is database.json). It should be something like that:

{
  "dev": {
    "host": "localhost",
    "database": "your_db",
    "driver": "mysql",
    "user": "db_user",
    "password": "your_pass"
  },
  "prod": {
    "host": "localhost",
    "database": "your_db",
    "driver": "mysql",
    "user": "db_user",
    "password": "your_pass"
  }
}

After that, you have to create the migration file:

node_modules/.bin/db-migrate create employees-seed --sql-file

The command above will create the following files:

migrations/20161026123242-employees.js
migrations/sqls/20161026123242-employees-up.sql
migrations/sqls/20161026123242-employees-down.sql

You just need to dump your sql commands into the sql files and run the migrations like that. (Possibly you have to split your mysql commands into different migrations files):

node_modules/.bin/db-migrate up

O_Z · Accepted Answer · 2016-10-31 12:50:53Z

0

You can read the dump file into one big string in node, and run it with your mysql driver in a single command. The driver should be able to handle it.

answered Oct 31, 2016 at 12:50

O_Z

1,5639 silver badges11 bronze badges

Comments

Jose Manuel Gomez Alvarez · Accepted Answer · 2016-10-31 18:05:59Z

0

There seems to be already a NPM module to integrate with MySql

Check this out https://www.sitepoint.com/using-node-mysql-javascript-client/

answered Oct 31, 2016 at 18:05

Jose Manuel Gomez Alvarez

2,30425 silver badges34 bronze badges

Collectives™ on Stack Overflow

execute mysql dump file to create table and feed data in nodejs project

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related