Using CSV file in node-sqlite3 package

Question

I'm using the npm sqlite3 package in my web application for a mock-up restaurant (for learning purposes). In my current implementation, when my local server is started, I'm creating a menuItems table, like so:

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)")
});

However, I would like for my menuItems table to not be hard-coded in the js file and would like for the actual items to be separate from this js file. There are a few solutions that I can think of:

1. Use current implementation and hard-code commands to insert individual items into the table.

2. Use SQLite3 to import the csv file and insert it into the table.*

3. Read in items from a csv file and insert them via a loop.

4. Establish database before the server starts up and pass it into the sqlite3.Database() constructor.

^{*I'd like to go with option 2. However, since this is for learning purposes, I am open to any suggestions, including but not limited to using a different database-managing package or using a different kind of file (maybe just a txt file or something).}

I know there are some ways to import the csv file using SQLite3. I'm trying to do the same thing but from the sqlite3 npm package. When I try to import the file via the same command (which seems to be a sqlite3-specific command) 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 the error

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

Error: SQLITE_ERROR: near ".": syntax error

Trying to use 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 the error

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

Error: SQLITE_ERROR: near "BULK": syntax error

Is something in my syntax incorrect? Is there a better/working/more efficient method to do this? Option 3 seems like it would work, but I haven't tried it yet.

Answer 1

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.

Answer 2

try

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'
      + ')');

commands beginning with . don't need semicolon at end

Answer 3

There is a little example for read your csv into sqlite3 db.

const sql3 = require('better-sqlite3');
const   db = new sql3( 'memory.db' );
const  csv = require('csv-parser');
const   fs = require('fs');

// create table
db.exec( 'CREATE TABLE IF NOT EXISTS menuItems ( itemName TEXT, itemDescription TEXT, unitPrice REAL );' );
//db.exec( 'DROP TABLE menuItems;' );

const insrow = db.prepare( 'insert into menuItems ( itemName, itemDescription, unitPrice ) VALUES (?, ?, ?)' );


fs.createReadStream('C:/Users/path/to/csv/file.csv')
  .pipe(csv({"separator":";"}))
  .on('data', (row) => {
    
    insrow.run( row.itemName, row.itemDescription, row.unitPrice );
    console.log(row);
  })
  .on('end', () => {
    console.log('CSV file successfully processed');
    db.close();
  });
      

The database in example is memory.db, and the csv format is not comma-separated, but semicolon separated, change separator, if it necessary.

The csv in this case must be started with itemName;itemDescription;unitPrice header, and a row looks like Fish Filet;Yummy fish in a sandwich.;9.95 and so on.