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

[danielepolencic]

I have a general query. Is there any overhead in having many SQLite databases open at the same time?

I'm trying to understand if we should have a single DB or several smaller DBs (e.g 100s individual dbs).

We are currently having a single database and prefixing the tables with customer_id. But it'd be a lot easier if we could have a db per customer.

[StoneCypher]

I have a general query. Is there any overhead in having many SQLite databases open at the same time?

A small, probably unimportant amount. SQLite needs several hundred K of ram.

 

I'm trying to understand if we should have a single DB or several smaller DBs (e.g 100s individual dbs).

You almost certainly should have a single database. This is not related to needs of the tool. You can treat the single database like it's distinct; the converse is not true. One will eventually need to be retooled; the other will not.

 

prefixing the tables with customer_id.

Use a single table with a defining column instead.

 

But it'd be a lot easier if we could have a db per customer.

You absolutely can. You just shouldn't. You will lose out on the ability to make any decisions across the set.

[driverjb]

@danielepolencic The correct way of handling this would be to have a single database that has a table that contains your customers with the primary key of say, customer_id. Then all your other tables have a foreign key matching up to that customer_id so that a given customer’s data can be selected from the table. This allows a single table for each set of data instead of juggling what sounds like a nightmare scenario of tables all with different names that represent the same data sets for different customers.

[Dezaku]

Hi,
I wrote this code:
let statement = db.prepare(`SELECT COUNT(*) FROM main WHERE userId = ?`); let row = statement.get(user.id); console.log(row);
which returns { 'COUNT(*)': 1 } when there is one
and { 'COUNT(*)': 0 } if there is none.

I made it so I can check easier if a row with that userId already exist or not. But how would I check for the count since row.count(*) won't work?

[driverjb]

@Dezaku I would change it to this:

function userExists(userId) {
  const statement = db.prepare(`SELECT 1 FROM main WHERE userId = ?`); 
  const row = statement.get(user.id);
  return row !== undefined
}

const doTheyExist = userExists(user.id)

[Dezaku]

@Dezaku I would change it to this:

function userExists(userId) {
  const statement = db.prepare(`SELECT 1 FROM main WHERE userId = ?`); 
  const row = statement.get(user.id);
  return row !== undefined
}

const doTheyExist = userExists(user.id)

Thats a way easier way, thank you!

[driverjb]

That was the long version that makes it clear what's happening. You could shorten it to this:

function userExists(userId) {
  return db.prepare('SELECT 1 FROM main WHERE userId = ?').get(userId) !== undefined
}

const doTheyExist = userExists(user.id)

[Dezaku]

That was the long version that makes it clear what's happening. You could shorten it to this:

function userExists(userId) {
  return db.prepare('SELECT 1 FROM main WHERE userId = ?').get(userId) !== undefined
}

const doTheyExist = userExists(user.id)

Alright, thank you!

[StoneCypher]

repeatedly preparing the query is wasteful

const exists_q = db.prepare('select 1 from main where userid = ?'),
      userExists = userId => exists_q.get(userId) !== undefined;

[TheColorman]

Is there a way to wait for a database entry to appear?
Say my code looks like this:

events.on('someEvent', event => {
    db.prepare('INSERT INTO events (id, someValue) VALUES (?, ?)')
        .run(event.id, event.someValue);
});

events.on('otherEvent', event => {
    const previousEvent = db.prepare('SELECT * FROM events WHERE id = ?')
        .get(event.referenceId);
});

Now say that there's some code in someEvent that takes a bit of time to finish before inserting into the database. Is there some way to wait for event.referenceId to appear in the database, potentially with a timeout if it never does?

[kentcdodds]

I'm uploading images to my node.js server and saving those in a table in my sqlite db. I'd like to stream that file upload straight to the table rather than reading the whole image in memory before doing the insert.

I saw that SQLite should support this via blob_write and better-sqlite3 appears to support blob_write, however, I'm not certain how to make use of this.

What I have is a data type of AsyncIterable<Uint8Array>. I'm not sure how to convert this to whatever better-sqlite3 needs for my insert:

imageDb
	.prepare(
		/* sql */ `
		INSERT INTO "Images"
			(updatedAt, contentType, blob)
			VALUES (@updatedAt, @contentType, @blob)
	`,
	)
	.run({updatedAt: new Date().toISOString(), contentType, blob: data}) // <-- data is the Uint8Array in an AsyncIterable

Tips are appreciated. Thanks!

[oceanwap]

@JoshuaWise

I am using verbose function but it logs queries even when there is an error like Unique constraint violation, in my use case I want to read queries if it's executed successfully. It would be a great help if you have any suggestion.

[pc-magas]

Hello, I am inserting some values into a table:

const sqlite3 = require('better-sqlite3');

const db = sqlite3(":memory:");
db.pragma('journal_mode = WAL');

db.exec("CREATE TABLE mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT NOT NULL)")

const sql =`
  INSERT INTO mytable (value) VALUES (:value)
`;

const stmt = db.prepare(sql);
stmt.run({'value'=>'some text'});

// Get the generated mytable.id here

How I can retrieve the inserted id?

[StoneCypher]

@pc-magas - sqlite3 .run returns an object with two properties. one is the count of changed rows; the other is the last insert id.

const stmt = db.prepare('INSERT INTO cats (name, age) VALUES (?, ?)');
const info = stmt.run('Joey', 2);

console.log(info.changes); // => 1
console.log(info.lastInsertId); // => if first cat, 1; otherwise higher

[janith-dev]

SELECT
    COUNT(progress_id) total_done,
    SUM(session_length)
FROM
    progress_table
WHERE
    strftime("%Y-%m-%d",time_stamp,"localtime") > (SELECT DATE("now", "-7 day","localtime"))

What I am doing wrong here, In "DB Browser for SQLITE", this query does not throw any error, How ever in better-sqlite, node is throwing

javascript SqliteError: no such column: now

Is there a different way of writing this query for better-sqlite to work?.

[elyobo]

Use single quotes for strings and you'll be fine.

SQL strings are generally single quoted, double quotes generally used for quoting identifiers e.g. if you have a column name with unsafe chars like a space in them. I've noticed that SQLite is inconsistently forgiving in this regard (there's presumably a pattern to it, but I haven't looked - I just use single quotes by habit anyway, but a colleague ran into this in a subtle and confusing bug).

[janith-dev]

Use single quotes for strings and you'll be fine.

SQL strings are generally single quoted, double quotes generally used for quoting identifiers e.g. if you have a column name with unsafe chars like a space in them. I've noticed that SQLite is inconsistently forgiving in this regard (there's presumably a pattern to it, but I haven't looked - I just use single quotes by habit anyway, but a colleague ran into this in a subtle and confusing bug).

Thank you so much for your quick reply, it worked.

[ivan-kleshnin]

Idiomatic connection approach with a web-server (e.g. Express)?

Most other DBs rely on connection pools that handle reconnects for us (allowing concurrent reads as well).
Hovewer, for SQLite it's not recommended to use pooling:

I personally wouldn't use better-sqlite-pool. It's possible there's something I'm missing, but it seems like an anti-pattern. The best way to use better-sqlite3 in a single-threaded environment such as Node.js is to only use a single connection for the entire application, and to only run transactions synchronously (within a single event-loop tick).
#234

Then the question arises: how to handle DB disconnects?

According to the docs new Database() creates a new connection. If I just reconnect per HTTP request, I can't benefit from prepare statements, not to say it's likely a waste of CPU to reconnect so often...

But, if I connect once, without a connection pool, the connection will eventually be lost and the app will crash.
Or does better-sqlite3 reconnect automatically, like e.g. node-redis?!

Surprisingly, I couldn't find more information about reconnects and proper connection management within a web server setup. So asking it here 🤔 Seems like an important topic to reflect in the docs though.

[StoneCypher]

@ivan-kleshnin , sqlite is not a server client model. it's a flat file parser local library. there aren't many realistic situations under which it would "disconnect."

[eslachance]

I'm looking to use db.aggregate() to loop through data in my table but I can't seem to figure out how to pass 2 fields to the step function. I have 2 row, key and value, that I wish to have access to within that aggregate.

Here's an example of one of those functions which currently only provides a value but not a key to the predicate function:

  filter2(predicate) {
    this.#db.aggregate('filter', {
      start: [],
      step: (accumulator, currentValue) => {
        const parsed = JSON.parse(currentValue);
        if (predicate(parsed)) {
          accumulator.push(parsed);
        }
        return accumulator;
      },
      result: (accumulator) => JSON.stringify(accumulator),
    });
    const results = this.#db
      .prepare(`SELECT filter(value) FROM mytable`)
      .pluck()
      .get();
    return JSON.parse(results);
  }

Is there any solution to this with the use of aggregate?

[eslachance]

@JoshuaWise If you could help with the above that would be awesome. This is a bit out of the depths of mostly everyone I know that uses sqlite ;)

What I feel I'm looking for would look like :

  filter2(predicate) {
    this.#db.aggregate('filter', {
      start: [],
      step: (accumulator, [value, key]) => {
        const parsed = JSON.parse(value);
        if (predicate(parsed, key)) {
          accumulator.push(parsed);
        }
        return accumulator;
      },
      result: (accumulator) => JSON.stringify(accumulator),
    });
    const results = this.#db
      .prepare(`SELECT filter(value, key) FROM mytable`)
      .pluck()
      .get();
    return JSON.parse(results);
  }

this gives me an error about the wrong number of arguments. Arrays and objects don't work either. I Really hope there's a way :(

[JoshuaWise]

@eslachance, You almost got it, just get rid of the array brackets:

db.aggregate('filter', {
  start: [],
  step: (accumulator, value, key) => {
    const parsed = JSON.parse(value);
    if (predicate(parsed, key)) {
      accumulator.push(parsed);
    }
    return accumulator;
  },
  result: (accumulator) => JSON.stringify(accumulator),
});
    
const results = db
  .prepare(`SELECT filter(value, key) FROM mytable`)
  .pluck()
  .get();

return JSON.parse(results);

[eslachance]

@JoshuaWise This is awesome! I guess it really was a question of matching parameter counts between the functions. Might I suggest this as an example in the docs somewhere? I even went and looked at your tests which all look at a single row, so I was wondering about the possibility, even. But now I know, and Enmap will be better for it, so thank you!

[KaustubhMogha11]

How can I find where SQLite stores data when using an in-memory database (:memory:)?

The memory consumption of a Node.js server process using an SQLite in-memory database decreased from 1.5 GB to 50 MB in Task Manager, while the SQLite database still contains 1 GB of data. The database size was found using this query: SELECT (page_count - freelist_count) * page_size AS size FROM pragma_page_count(), pragma_freelist_count(), pragma_page_size().