4
\$\begingroup\$

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']]);
}
\$\endgroup\$
0

2 Answers 2

Reset to default
4
\$\begingroup\$

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

\$\endgroup\$
3
\$\begingroup\$

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

\$\endgroup\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.