Better-sqlite3: Help me use SQLite!

[JoshuaWise]

General Help Thread

This thread is for anyone who needs help using SQLite or Node.js with regards to better-sqlite3.

It's common for issues to appear with titles like "Help me do ABC" or "How do I build an app like XYZ?". These types of issues aren't real issues with better-sqlite3, so they will be closed and considered a duplicated of this thread. Therefore, post your questions here instead. If you're lucky, friendly members of the community may chime in here to help you.

[JoshuaWise]

By default, SQLite is very bad at performing concurrent reads and writes. However, performance can be improved tremendously by activating WAL mode.

db.pragma('journal_mode = WAL');

For more information, read here.

[dimitry-ishenko]

Is it possible to insert multiple rows from an array of objects? Say I have this:

var data = [
    { foo: 1, bar: 2 },
    { foo: 3, bar: 4 }
];

I'd like to be able to do this:

db.prepare('INSERT INTO baz VALUES ($foo, $bar)').run(data);

Not sure if running INSERT statements in a loop is efficient.

As a side note, sqlite does does support insertion of multiple values.
Eg, INSERT INTO baz VALUES (1, 2), (3, 4) will insert 2 rows.
So, perhaps, it can somehow be adopted in the module?

[JoshuaWise]

Using a loop is quite efficient, @dimitry-ishenko.

There are two things to be aware of:

• you should prepare the statement only once, and run it multiple times
• you can further increase performance by wrapping the loop in a transaction

Here's an example of what (I believe) you're trying to do:

const insert = db.prepare('INSERT INTO baz VALUES ($foo, $bar)');
const insertMany = db.transaction((data) => {
  for (const obj of data) insert.run(obj);
});

insertMany([
    { foo: 1, bar: 2 },
    { foo: 3, bar: 4 }
]);

[andykais]

Hi, I am curious about the performance of the method you just mentioned @JoshuaWise. How does this compare to actually running a batch write? The tradeoff being the insertMany method below cannot use a prepared statement.

const insertMany = data => {
  const valuesStr = Array(data.length).fill('(?, ?)').join(',')
  const sql = `INSERT INTO baz VALUES ${valuesStr}`
  const flattened = data.reduce((acc, d) => {
    acc.push(Object.values(d))
    return acc
  }, [])
  db.run(sql, flattened)
}

when comparing the two executing directly like so cat insert-many.sql | sqlite3 I believe my method would win out. Wrapped in better-sqlite though, is it faster to run many inserts as opposed to batching to avoid preparing the statement each time?

[JoshuaWise]

@andykais, there's nothing stopping you from re-using prepared statements in the db.transaction() function.

[JoshuaWise]

One of the biggest performance penalties of using an async API is the inability to re-use prepared statements

[Nikumarekko]

Hi, can i just use the prepare statement + run statement for creating tables or creating trigger?

const statement = db.prepare('CREATE TABLE IF NOT EXISTS ? (?)');
{some foo}
statement.run(tableName, tableColumns);

or am i doing something wrong?

[JoshuaWise]

@Nikumarekko the only thing wrong with your code is that SQLite3 does not allow you to bind strings for identifier names (such as table or column names). You can only use bound values for actual run-time values.

// No bound parameters for table/column names
const createTable = db.prepare(`CREATE TABLE IF NOT EXISTS  ${tableName} (${tableColumns})`);
createTable.run();

// Use bound parameters for actual strings
const select = db.prepare(`SELECT * FROM ${tableName} WHERE foo = ?`);
const results = select.all('someString');

Keep in mind that because you have to use string interpolation for table and column names, they should only come from trusted sources.

[Nikumarekko]

@Nikumarekko the only thing wrong with your code is that SQLite3 does not allow you to bind strings for identifier names (such as table or column names). You can only use bound values for actual run-time values.

// No bound parameters for table/column names
const createTable = db.prepare(`CREATE TABLE IF NOT EXISTS  ${tableName} (${tableColumns})`);
createTable.run();

// Use bound parameters for actual strings
const select = db.prepare(`SELECT * FROM ${tableName} WHERE foo = ?`);
const results = select.all('someString');

Keep in mind that because you have to use string interpolation for table and column names, they should only come from trusted sources.

Thanks for your quick reply, helps me a lot

[bombard-kb]

hi @JoshuaWise

I'm trying to use this module with Express.js but for some reason when I call run on the statement, the http connection gets closed without any error, just upstream prematurely closed connection while reading response header from upstream in nginx log

code is pretty simple

router.post("/path1", function(req, res, next) {
  db.prepare(sqlQuery).run(params);
  res.redirect("/path2");
})

any suggestion how to find the cause of the problem?

thanks

[JoshuaWise]

@bombard-kb better-sqlite3 and express don't interact in any way whatsoever. It could be related to a timeout occurring in express, but it likely is completely unrelated to better-sqlite3.

[bombard-kb]

@JoshuaWise it looks like I was trying to use "on conflict statement" with sqlite3 version, which doesn't support it

[JefStat]

How do I use better-sqlite3 with a precompile version of SQLite?

[JoshuaWise]

@JefStat better-sqlite3 has to be compiled with SQLite as a static library, not a precompiled dynamic library. You can, however, use a custom amalgamation with your own custom compile-time options. Learn how to here.

[fakob]

Hi

Can I store an image as a Blob in sqlite/better-sqlite3 to then use URL.createObjectURL(blob) for displaying the image in the render window?
As I am a newbie with sqlite, it might as well be a stupid question :-)

I am developing an electron/desktop app where I am storing a lot of images. Before I have used the browsers internal indexeddb where the images were stored as a Blob. I then created an objectURL which I used to display the images in the render window.

As indexeddb is limited in some ways, I came across better-sqlite3. Storing and reading the images as base64 worked like a charm, but I would have hoped that I do not have to shuffle around so much data and can make use of objectURLs.

[andrewkolos]

Can I have table name as a query parameter? For example, in mysql, I can do CREATE TABLE ?? (col1 varchar, ...) with a parameter array of [tableName]. I saw in the API doc you can insert parameters with the standard ? in a query, but how about parameters that are table names? Does that have it's own symbol, like how mysql has ???

[JoshuaWise]

Can I store an image as a Blob in sqlite/better-sqlite3

@fakob, you can store any arbitrary data in a Blob, so the answer is yes.

Can I have table name as a query parameter?

@andrewkolos, SQLite3 provides no way of using parameters for table/column names. However, if you properly quote/escape the table name, you can use JavaScript's string interpolation:

function quote(name) {
  return '\x60' + String(name).replace(/\x60/g, '\x60\x60') + '\x60';
}

const stmt = db.prepare(`SELECT * FROM ${quote(tableName)}`);

[fakob]

@JoshuaWise
Thanks for the answer. Though there might be a difference between Blobs stored in sqlite3 compared to the indexedDB database. From Blobs stored in sqlite3 I was not able to create objectURLs with URL.createObjectURL(blob). But maybe I have misunderstood something.

[shadeofgod]

so how you guys generally use this in your app?

Is there any tools like query builder so I don't have to maintain sql strings by hand which is error prone imo. I found knex.js only supports node-sqlite3.

cc @JoshuaWise

[mygithubid1]

Hi there,
How would I use a trigger function using better-sqlite to perform a file delete once the row is deleted?

Here's the scenario:

Table: Students
Columns:
id integer (primary key not null)
Name Varchar(30) not null

Table : StudentFiles
Columns :
id integer (primary key not null)
studentFilePath: varchar(200) // this is stored as a json string.
studentId: foreign key references Student.id on delete cascade on update cascade

When a row is deleted from the Student table, the corresponding row will also be removed from the StudentFiles table.
How would I create a trigger to call my javascript function to delete the file as pointed by studentFilePath before the row inside StudentFiles disappears?

Thanks.

[JoshuaWise]

@mygithubid1, Here's the basic idea:

const fs = require('fs');
const db = require('better-sqlite3')('data.db');

db.function('delete_file', (filename) => {
  fs.unlinkSync(String(filename));
});

db.exec(`
  CREATE TEMPORARY TRIGGER delete_student_files
  AFTER DELETE ON StudentFiles
  BEGIN
    SELECT delete_file(old.studentFilePath);
  END;
`);

Note that it will only work if the DELETE statement is run by that specific database connection (db). For example, it won't work when deleting rows from the sqlite3 command-line interface.

[mygithubid1]

@JoshuaWise Thank you. Will give this a shot.

[mygithubid1]

@JoshuaWise Worked. Thank you.

[vurdeljica]

Hello,

I encountered a problem while executing ATTACH statement.

const attach = this.db.prepare("ATTACH DATABASE '" + databasePath + "' AS temp;")
this.db.exec(attach)

Error 'expected first argument to be a string' is reported. Does anybody know what is the issue?
databasePath is relative

Best regards,
Igor

[JoshuaWise]

@vurdeljica, the db.prepare() function returns a Statement object. To execute a statement, you should use statement.run(). The db.exec() function you're using expects an SQL string, not a statement object.

This is the correct code:

const attach = this.db.prepare('ATTACH DATABASE ? AS temp;')
attach.run(databasePath)

Please read the very detailed documentation to learn how to use better-sqlite3.

[s7130457]

Hi, How would I use transaction and call model layer function in my controller layer file?

My project use MVC architecture.
I have two model file: preset.model and task.model, and one controller file.
The preset schema only save name field, task schema has name and presetUid fields
I need to create preset first step and get presetUid, so that I can add the task.

preset.model.js

function add(preset) {
    const sql = `INSERT INTO preset (name) VALUES ($name)`
    const result = db.prepare(sql).run(preset.name)
    return result
}

task.model.js

function add(task) {
    const sql = `INSERT INTO preset (name, presetUid) VALUES ($name, $presetUid)`
    const param = {
        name: task.name,
        presetUid: task.presetUid
    }
    const result = db.prepare(sql).run(param)
    return result
}

In my controller.js file, I want to use preset.add and task.add function in model file
like this:
controller.js

import preset from './preset.model'
import task from './task.model'
import db from './db'

async function combo() {
    const data = { name: 'preset01'}
    try {
       await db.beginTransaction()   //  I want to start for transaction
       const preset = await preset.add(data)
       const task = {
            name: 'task01',
            presetUid: preset.lastInsertRowid
       }     
       await task.add(task)
       await db.commit()  // I want to do commit
    } catch(error) {
        await db.rollback() // I want to rollback
    }
}

I know the documentation of better-sqlite does not have db.beginTransaction(), db.commit() and db.rollback() api. They are example to show what I want to do.
My problem is how can I do then I will get the same feature in my controller file.

Thank you.

[mygithubid1]

@s7130457

function combo() {
    const data = { name: 'preset01'}
    try {
       db.exec("BEGIN TRANSACTION;");
       const preset = preset.add(data)
       const task = {
            name: 'task01',
            presetUid: preset.lastInsertRowid
       }     
       task.add(task)
       db.exec("END TRANSACTION;");
    } catch(error) {
        db.exec("ROLLBACK TRANSACTION;");
    }
}

https://www.sqlite.org/lang_transaction.html
One more thing. There's no need for await in your functions. The library is synchronous.

[andykais]

@s7130457 @mygithubid1 so there is actually a transaction higher order function as part of the api https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/api.md#transactionfunction---function that was added in v5.

usage:

import preset from './preset.model'
import task from './task.model'
import db from './db'

function comboInserts() {
  const data = { name: 'preset01' }
  const presetReturn = preset.add(data)
  const taskData = { name: 'task01', presetUid: presetReturn.lastInsertRowId }
  task.add(taskData)
}
const comboTransaction = db.transaction(comboInserts)

// usage
comboTransaction()

[nierchi]

I’m new to this so I hope you can help me..

I am trying to display all the datas I have under the table list in the names column but can’t make it work., this is my code:

row = db.prepare(SELECT names FROM list).all()
message.channel.send(row);

It displays:
[object Object]

the [object Object] repeats depending on how many entry is in the db, I just wanted it to display the all the names in the list table, I’m still a noob so hopefully you can help me.

Thank you.

[ayntee]

@nierchi
Channel#send's first argument is content, which should be a string, however,
you provided an array of objects.
The default serialization/conversion for a string to an object.

• Function Object:

stringify(function (){}) -> [object Function]

• Array Object:

stringify([]) -> [object Array]

• RegExp Object:

stringify(/x/) -> [object RegExp]

• Date Object

stringify(new Date) -> [object Date]

• Object Object

stringify({}) -> [object Object]

• Several More!

In conclusion, you have to map the property or use for loop.

[nierchi]

@chroventer ohh.. alright thank you for giving me that idea, I’ll tweak my code.
Thanks for the help 😊

[bog-sb]

Hi! Are there any performance benefits in preparing a statement once and then using it multiple times? E.g. i now have this:

function select(params){
    return db.prepare('...').run(params)
}

would the following have better performance (or are there any drawbacks)?

const statement = db.prepare('...');
function select(params){
   return statement.run(params); 
}

Thanks in advance!

[mygithubid1]

db.prepare returns a statement.
Part of this operation involves reaching out to the sqlite3 engine with a call to sqlite_prepare_v3 to compile the sql.

https://github.com/JoshuaWise/better-sqlite3/blob/master/docs/api.md#class-statement

[JoshuaWise]

Hi! Are there any performance benefits in preparing a statement once and then using it multiple times? E.g. i now have this:

function select(params){
    return db.prepare('...').run(params)
}

would the following have better performance (or are there any drawbacks)?

const statement = db.prepare('...');
function select(params){
   return statement.run(params); 
}

Thanks in advance!

Yes, if your query is relatively fast, reusing the prepared statement will result in a huge performance gain.

[jdkdev]

Hi,

Has anyone out there used this to create something similar to knex.js? Would it slow it down, are there any pros or cons to consider.

Essentially I had something that was using sqlite and knex.js and wanted to hot swap better-sqlite3 out to see if there were performance gains.

Any thoughts?

Best,
Jordan

[ruquay]

UPDATE I'll leave this here for anyone experiencing a similar problem, but I found my issue. The concatenation operator "||" causes the type of the data to be converted to a string. Apparently the concatenation is "not guaranteed" to work if the BLOB is not valid Unicode (I think?), but it does, in fact work.

UPDATE 2 Forgot to add that using CAST(binlog AS BLOB) in the SQL below avoids the need for hex().

Hi Joshua, first of all many thanks for this library!

I need to access binary data and wonder if there's a more direct way than using the SQLite hex() function. Currently when I SELECT the data, it comes back as a string. However, I write data using Buffer instances (and SQLite concatenation).

For example (simplified from my real use case):

SQL for table and statements

CREATE TABLE devlog (deviceId INTEGER UNIQUE, binlog BLOB);
-- theUpsertSQL in JS below
INSERT INTO devlog (deviceId, binlog) VALUES (@deviceId, @binlog)
  ON CONFLICT (deviceId) DO UPDATE SET binlog = binlog || excluded.binlog
-- theSelectSQL in JS below
SELECT binlog FROM devlog WHERE deviceId = @deviceId

and I'm trying to access like so:

const upsert = db.prepare(theUpsertSQL)
upsert.run({ deviceId, binlog: Buffer.from(outerSpace) })

const select = db.prepare(theSelectSQL)
console.log(select.columns()) // here the type for binlog is BLOB, as expected
const row = select.get({ deviceId })
console.log(typeof row.binlog) // 'string'

I've been able to confirm that data gets into the database properly: I can use the CLI sqlite3 client, select e.g. hex(binlog), then copy that in a script with Buffer.from(theHexString, 'hex'), and verify the data is as expected.

Trying to query normally and using Buffer.from(binlog, 'binary') corrupts the data.

I assume I can use the hex() method in SQL to get at the binary data and will do this if that's the right way.

I've looked through the docs and this thread, searching for "blob", "binary", "buffer". And of course I've tried to search the internet using various terms, but nothing has come up. Sorry if I've overlooked something, I'm only asking here because I cannot figure it out on my own.

In case the binary concatenation seems weird: the incoming data is 2 BE bytes indicating record length, the remaining portion is of that length and is Protocol Buffer formatted data which can itself contain multiple records (it makes sense for the kind of data I'm logging).

[JoshuaWise]

Has anyone out there used this to create something similar to knex.js? Would it slow it down, are there any pros or cons to consider.

@jdkdev, the main disadvantage is that you wouldn't be able to take advantage of re-used prepared statements. You'd have to prepare a new statement before each query, which would slow things down. Generally, the better pattern with SQLite3 is to prepare all your needed statements at startup, rather than building queries dynamically. As far as I'm aware though, no existing framework exists for this in Node.js.

[mceachen]

Generally, the better pattern with SQLite3 is to prepare all your needed statements at startup, rather than building queries dynamically. As far as I'm aware though, no existing framework exists for this in Node.js.

I'm using a simple cache to store statements, and using knex to build my SQL (rather than having Knex actually run the query).

const m = new Map<string, sqlite.Statement>()
export function prepare(sql: string): sqlite.Statement {
  const prior = m.get(sql)
  if (prior != null) return prior
  const s = db().prepare(sql)
  m.set(sqlite, s)
  return s
}

Note that Knex's QueryBuilder has a toSQL() that returns the native sql, along with bindings.

[manishrana87]

Hi @JoshuaWise ,
I am new to this, so have few questions. How i can get the response back from the transaction which happen. For e,g.

function doMultipleTransaction(sqlQuery, sqlData) {
  try {
        log.info("SQl Query : ", sqlQuery);
        log.info("SQl Data : ", sqlData);
        log.info("Transaction Started");
        const insert = db.prepare(sqlQuery);
        const insertMany = db.transaction((sqlDataRows) => {
             for (const sqlDataRow of sqlDataRows) {
                  insert.run(sqlDataRow);
              }
       });
       return insertMany(sqlData);
  } catch (err) {
    // (transaction was forcefully rolled back) if any error while doing transaction
    if (!db.inTransaction) throw err;
  }
}

From the above function, how can i get the response back of all the inserted records.
Or is there any better way of doing this.

[andykais]

@manishrana87 you can return values from the transaction function. This will work:

function doMultipleTransaction(sqlQuery, sqlData) {
  try {
        log.info("SQl Query : ", sqlQuery);
        log.info("SQl Data : ", sqlData);
        log.info("Transaction Started");
        const insert = db.prepare(sqlQuery);
        const insertMany = db.transaction((sqlDataRows) => {
             return sqlDataRows.map(sqlDataRow => insert.run(sqlDataRow))
       });
       return insertMany(sqlData);
  } catch (err) {
    // (transaction was forcefully rolled back) if any error while doing transaction
    if (!db.inTransaction) throw err;
  }
}

I believe this is an undocumented feature

[mceachen]

@manishrana87 I was about to reply exactly as @andykais did, but he beat me to it: Either collect the results into an array or into an object and return. .transaction() returns what it received.

[manishrana87]

Hi,

Thanks andykais and mceachen for the earlier post.

I am having another question.

I have a array of item ["001", "002", "003"].
These are primary key from a table "image". If I want to fetch the details from the "image" table.

In normal SQL editor, the query is,

Select * from image where image_id in ("001", "002", "003")

How I can implement the same in terms of better-sqlite3.

[JoshuaWise]

@manishrana87, If your array always has three items, you can do this:

// First, open the database
const db = require('better-sqlite3')('my-database.db');

// Then, create a prepared statement
const statement = db.prepare("Select * from image where image_id in (?, ?, ?)");

// Lastly, run the query to get the results
const results = statement.all(["001", "002", "003"]);

However, if your array can have any number of items, you should do this:

// Same as before
const db = require('better-sqlite3')('my-database.db');

// This time, the statement only fetches a single item
const statement = db.prepare("Select * from image where image_id = ?");

// Run the query once for each item in the array
const results = array.map(item => statement.get(item));

You might be worried that running a query multiple times would be bad for performance, and while that's true for RDBMS databases, it's completely fine in SQLite3 because querying does not involve network latency. Specifically in your case above, it will be very fast because each query will likely put the results of the next query in cache (both filesystem cache and SQLite3's own memory cache).

[andykais]

@JoshuaWise, If I know I am working with a single nodejs process accessing my sqlite database, do I need to put a transaction around something like this:

const db = require('better-sqlite3')('my-database.db')

const selectStmt = db.prepare('SELECT * FROM cache WHERE cacheKey = ?')
const insertStmt = db.prepare('INSERT INTO cache (data, cacheKey) VALUES (?, ?)')

const ee = new EventEmitter()
ee.on('event', data => {
  // I want the select then insert to happen transactionally
  const cacheKey = JSON.stringify(data)
  const row = selectStmt.get(cacheKey)
  if (row === undefined) insertStmt.run(data, cacheKey)
})

// imagine these events are coming in asynchronously at any time 
ee.emit('event', {some:'data'})
ee.emit('event', {some:'moredata'})

side note: I specifically cannot use a unique index here because sometimes I will insert into the database and not want the inserts to be unique, partial indexes will not work here because when I want to use the cache, I may access older rows which were created when the cache was not active.

[mygithubid1]

@andykais Hi there. Why should your cache should support duplicate keys?

[JoshuaWise]

@andykais Since your SQL statements are executed synchronously (within the same event-loop tick), you don't need to worry about other events happening at the same time (assuming no other thread or process is accessing the same database, as you mentioned).

[andykais]

Why should your cache should support duplicate keys?

@mygithubid1 its a bit of a weird use case. This table serves both as a cache and storage for request/responses. Sometimes I want to use the cache (e.g. if a request has already been made with those parameters, grab the response from the database), and sometimes I do not want to use the cache (e.g. I know hitting the same endpoint will return different data each time, so I just want to store the responses in the database).

I dont want to derail this thread with more detailed explanations though, so lets leave it at that.

[DefCheese]

Hi,

My INTEGERS passed into db.prepare().run() are being cast/translated to REALs when executed.

I have a messages table (id INTEGER, json TEXT) that I want to update to show when a user has an unread or read message.

The json text/object is v. simple

id | json
-- | ----
1 | '{"id": 1, "isRead": 0, ......}'
2 | '{"id": 2, "isRead": 1, ......}'

where isRead is a 0 or 1 flag.

To update the isRead property within the json field, I pass a simple object containing only integers

msgObj = {
  isRead: 1,
  id: 1
}

But when I run this

 db.prepare(`
    UPDATE messages
    SET json = json_set(json, '$.isRead', :isRead)
    WHERE id = :id
  `).run({
    isRead: msgObj.isRead,
    id: msgObj.id
  })

isRead becomes {..."isRead": 1.0, ...} within the json text field.

I turned on {verbose: console.log} in my db require and the console shows me that the generated SQL is

    UPDATE messages
    SET json = json_set(json, '$.isRead', 1.0)
    WHERE id = 1.0

The id still identifies the correct row, as the UPDATE works, so the id field in the WHERE clause must be equating 1.0 with the INTEGER id of 1 in the table.

Is this normal/correct behaviour, or am I missing something?

[simeon9696]

Apologies if this seems straightforward but how do I create a table with a dynamic name?

Originally I thought I could do

const stmt= db.prepare(`CREATE TABLE ${MY_CUSTOM_NAME} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    some_property TEXT NOT NULL
)`);

const i = stmt.run();

But that gives me the following error, assuming MY_CUSTOM_NAME was passed in as '2'

SqliteError {message: "near "2": syntax error", stack: "SqliteError: near "2": syntax error↵    at VueComp…ents/NacPage.vue?vue&type=script&lang=js&:165:10)", code: "SQLITE_ERROR", __ob__: Observer}
code: "SQLITE_ERROR"
message: "near "2": syntax error"
stack: "SqliteError: near "2": syntax error↵    at VueComponent._callee$ (webpack-internal:///./node_modules/babel-loader/lib/index.js!./node_modules/vue-loader/lib/index.js?!./src/renderer/components/NacPage.vue?vue&type=script&lang=js&:130:35)↵    at tryCatch (webpack-internal:///./node_modules/regenerator-runtime/runtime.js:62:40)↵    at Generator.invoke [as _invoke] (webpack-internal:///./node_modules/regenerator-runtime/runtime.js:296:22)↵    at Generator.prototype.<computed> [as next] (webpack-internal:///./node_modules/regenerator-runtime/runtime.js:114:21)↵    at step (webpack-internal:///./node_modules/babel-runtime/helpers/asyncToGenerator.js:17:30)↵    at eval (webpack-internal:///./node_modules/babel-runtime/helpers/asyncToGenerator.js:35:14)↵    at new Promise (<anonymous>)↵    at new F (webpack-internal:///./node_modules/core-js/library/modules/_export.js:36:28)↵    at eval (webpack-internal:///./node_modules/babel-runtime/helpers/asyncToGenerator.js:14:12)↵    at VueComponent.addDrug (webpack-internal:///./node_modules/babel-loader/lib/index.js!./node_modules/vue-loader/lib/index.js?!./src/renderer/components/NacPage.vue?vue&type=script&lang=js&:165:10)"
__ob__: Observer {value: SqliteError, dep: Dep, vmCount: 0}
__proto__: Error

EDIT: It seems that the issue is not the statement is not the issue, I just tried it with text instead of just a number and it worked. However I need the table name to be the name of a drug which have numbers in them like "ciprofloxacin 500 mg". Using this drug name yields the following error.

SqliteError {message: "near "500": syntax error", stack: "SqliteError: near "500": syntax error↵    at VueCo…ents/NacPage.vue?vue&type=script&lang=js&:167:10)", code: "SQLITE_ERROR", __ob__: Observer}
code: "SQLITE_ERROR"
message: "near "500": syntax error"
stack: "SqliteError: near "500": syntax error↵    at VueComponent._callee$ (webpack-internal:///./node_modules/babel-loader/lib/index.js!./node_modules/vue-loader/lib/index.js?!./src/renderer/components/NacPage.vue?vue&type=script&lang=js&:132:35)↵    at tryCatch (webpack-internal:///./node_modules/regenerator-runtime/runtime.js:62:40)↵    at Generator.invoke [as _invoke] (webpack-internal:///./node_modules/regenerator-runtime/runtime.js:296:22)↵    at Generator.prototype.<computed> [as next] (webpack-internal:///./node_modules/regenerator-runtime/runtime.js:114:21)↵    at step (webpack-internal:///./node_modules/babel-runtime/helpers/asyncToGenerator.js:17:30)↵    at eval (webpack-internal:///./node_modules/babel-runtime/helpers/asyncToGenerator.js:35:14)↵    at new Promise (<anonymous>)↵    at new F (webpack-internal:///./node_modules/core-js/library/modules/_export.js:36:28)↵    at eval (webpack-internal:///./node_modules/babel-runtime/helpers/asyncToGenerator.js:14:12)↵    at VueComponent.addDrug (webpack-internal:///./node_modules/babel-loader/lib/index.js!./node_modules/vue-loader/lib/index.js?!./src/renderer/components/NacPage.vue?vue&type=script&lang=js&:167:10)"
__ob__: Observer {value: SqliteError, dep: Dep, vmCount: 0}
__proto__: Error

So I suppose my new question is how can I include numbers in table names?

[DefCheese]

@simeon9696

According to the docs at https://www.sqlite.org/lang_createtable.html, , the only names forbidden are those that begin with sqlite_

However, if a table name contains a space or starts with a number, it needs to be in double quotes https://www.sqlite.org/lang_keywords.html, so

const stmt= db.prepare(`CREATE TABLE "${MY_CUSTOM_NAME}" (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    some_property TEXT NOT NULL
)`);

should work.

[simeon9696]

@DefCheese works like a charm! Thanks for pointing out the documentation for me!

[simeon9696]

So I'm having a bit of difficulty understanding how the INSERT statement works with parameter binding. From the docs:

// The following are equivalent.
const stmt = db.prepare('INSERT INTO people VALUES (@firstName, @lastName, @age)');
stmt.run({
  firstName: 'John',
  lastName: 'Smith',
  age: 45
});

This means that @firstName = John etc but does firstNamecorrespond to a column in the table? If the first column was named cities and the second towns and the above statement was run. Would the entry in cities be John and towns be Smith because that's the order of the data in the INSERT statement?

TL:DR: Are bindings in INSERT statements bound to column names in a table or the order in which the columns appear in the table.

I ask because rn I'm inserting data with bound parameters and the object has the keys and values mapped correctly but some of the column data is swapped around when it comes out.

[DefCheese]

@simeon9696
INSERT can be used on specific column names in your table, or all of them
Assuming your table is

CREATE TABLE locations (
  street TEXT,
  town TEXT,
  zip TEXT
);

then INSERT can be used on all columns like this:

const stmt = db.prepare('INSERT INTO locations VALUES (@a, @b, @c)');
stmt.run({
  a: 'High Street',
  b: 'BigTown',
  c: '12345'
});

...and the values would be inserted into the columns in the order they were defined in the CREATE TABLE above (a goes into street, b to town, etc.)
However, you can also specify the order yourself and even omit columns) using

const stmt = db.prepare('INSERT INTO locations (zip, town) VALUES (@a, @b)');
stmt.run({
  a: '12345',
  b: 'BigTown'
});

street would then be NULL for this inserted row.
The bind names you use (a, b, c) don't relate directly to the column names, just the order you use them in the SQL statement.
So

const stmt = db.prepare('INSERT INTO locations (zip, town) VALUES (@b, @a)');
stmt.run({
  a: 'BigTown',
  b: '12345'
});

will also work fine. Obviously, using meaningful bind variable names is preferred over a, b, c, etc. :)