node.jssqlitenode-sqlite3

Import CSV file in node-sqlite3


I'm using the npm sqlite3 package in my web application for a mock-up restaurant. When my local server is started I'm creating a menuItems table:

var db = new sqlite3.Database(':memory:');
db.serialize(function() {
  db.run('CREATE TABLE menuItems ('
          + 'itemName VARCHAR(255),'
          + 'itemDescription VARCHAR(255),'
          + 'unitPrice REAL'
          + ');')
  .run("INSERT INTO menuItems (itemName, itemDescription, unitPrice) VALUES"
          + " ('Fish Filet', 'Yummy fish in a sandwich.', 9.95)")
});

I would like menuItems table to be separate from this js file. Options I can think of:

  1. Use current hard-coded commands to insert individual items into the table.
  2. Use SQLite3 to import the CSV file and insert it into the table.
  3. Read items from a CSV file and insert them in a loop.
  4. Establish database before server starts and pass it into sqlite3.Database() constructor.

I'd like to go with option 2. However, I am open to any suggestion. There are ways to import the CSV file using SQLite. I'm trying to do this but from sqlite3 npm package. When I try to import the file via the same command (which seems to be sqlite3-specific) from the linked page:

db.run('.import "C:/Users/path/to/csv/file.csv"'
      + 'INTO TABLE menuItems'
      + 'FIELDS TERMINATED BY ","'
      + 'ENCLOSED BY "\'"'
      + 'LINES TERMINATED BY \'\\n\''
      + 'IGNORE 1 ROWS'
      + ');');

I receive:

events.js:183
  throw er; // Unhandled 'error' event
  ^

Error: SQLITE_ERROR: near ".": syntax error

Trying normal SQL syntax:

.run('BULK INSERT menuItems'
      + 'FROM "C:/Users/path/to/csv/file.csv" '
      + 'WITH '
      + '{ '
      + 'FIRSTROW = 2,'
      + 'FIELDTERMINATOR = ","'
      + 'ROWTERMINATOR = "\n",'
      + 'ERRORFILE = "C:/Users/path/to/csv/error_file.csv" '
      + 'TABLOCK'
      + '}')

I receive:

events.js:183
      throw er; // Unhandled 'error' event
      ^

Error: SQLITE_ERROR: near "BULK": syntax error

Is my syntax incorrect? Is there a better way? Option 3 I haven't tried.


Solution

  • I ended up going with Option 3. Namely, I used the fs package to read in the CSV file, did some parsing, returned the results within a Promise object, and did the database insertions within the then of said Promise object.