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.

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.

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?

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

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?

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

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

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?

@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 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

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

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

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

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

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

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.

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 ???

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

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

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

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.

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

@JoshuaWise Thank you. Will give this a shot.

@JoshuaWise Worked. Thank you.

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

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

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.

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

@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()

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.

@nierchi TextChannel#send()'s first argument is content, which is of type 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]

• Object Object

stringify({}) -> [object Object]

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