better-SQLite3 and JS/Next.js - updating data using "transaction"- shorter / better code?

Question

I am pretty new to SQL databases and in particular to the better-SQLite3 database. I have managed to insert, update, and delete data in a way I desire. All of it running in the background of a self-hosted website.

I find that my update function looks a bit "overdone", especially when looking at the array when executing updateDb([[ , ]]) at the end. However, I have not found another way to get this to work. Information on the internet is not that dense, that's why I am asking.

DATABASE id | query | age ------------------------------ 1 | some string | some string 2 | some string | some string 3 | some string | some string 

As said, all is working as expected. I am just curious whether there is a more efficient way for the updateDb function. Ideally, I would supply an array of arrays to updateDb if I want to update more than one database entry.

const createDb = () => { db.prepare(` CREATE TABLE IF NOT EXISTS wolfram ( id INTEGER PRIMARY KEY AUTOINCREMENT, query TEXT NOT NULL, age TEXT NOT NULL ) `).run(); } async function updateData() { 'use server' const update = db.prepare(`UPDATE database SET query = ? WHERE age = ?`); const updateDb = db.transaction((database) => { for (const [query, age] of database) { update.run([query, age]) } }); updateDb([['nameJK','age1']]); } 

Answer 1

LGTM, ship it!

The OP code seems a natural way to express what is desired. It is clear, correct, and maintainable. I don't notice any big improvements we could make to it.

---

indexing

The posted DDL mentions no index on age. If your table has many rows, you might want to add one, so the UPDATE can choose a query plan better than "table scan".

Answer 2

I am just curious whether there is a more efficient way for the updateDb function. Ideally, I would supply an array of arrays to updateDb if I want to update more than one database entry.

There is a way to update multiple rows in one statement- using a CASE expression. For example - a raw UPDATE query could be run like this:

UPDATE wolfram SET query = CASE age WHEN 'age1' THEN 'nameA1E2' WHEN 'age2' THEN 'nameB1F2' END WHERE age in ('age1', 'age2'); 

Note: I'm presuming that the table name is wolfram given CREATE TABLE IF NOT EXISTS wolfram (....

As a user of the Laravel PHP framework I wanted to have a shorthand method to achieve this and was somewhat surprised the framework did not offer such a method, though I did find the issue logged on the Laravel GH issues list. User barryvdh (who has created many popular packages) supplied this response which offers a technique to implement such a method. Converting this to Javascript would be something like below. Beware I haven't tested it with NextJS so it may need some slight modifications.

const updateDb = db.transaction((values) => { const ages = []; const cases = []; const params = []; for (const [query, age] of values) { ages.push(age); cases.push('WHEN ? then ?'); params.push(age, query); } const whereAges = Array(ages.length).fill('?'); const sql = `UPDATE database SET query = CASE age ${cases.join(' ')} END WHERE age in (${whereAges.join(',')})`; db.prepare(sql).run([...params, ...ages]); }); 

Notice instead of the variable name database the name values was used instead. This can help avoid confusion for anyone reading the code (including your future self).