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:
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.
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.