Commit with pool

[frankred]

I have a script that is inserting tons of rows inside a mysql table. At the end of the script I do:

1. At the beginning of the script: Create Pool this.pool = await mysql.createPool({ host: "xxxx.de", user: "xxx", password: "xxx", database: "xxx" });

2. Add all my entries: Insert each

   const params = [entry.portal, entry.portalId || "", entry.title, entry.company || "", entry.region || "", entry.start || "", entry.duration || "", entry.jobType || "", entry.link, entry.description, entry.crawlTaskId, 'ACTIVE', entry.link];

    const sql = 'INSERT INTO jobs SET portal=?, portalId=?, title=?, company=?, region=?, start=?, duration=?, jobType=?, link=?, description=?, crawlTaskId=?, status=?, linkHash=SHA2(?, 256)';

    try {
        await this.pool.execute(sql, params);
        return true;
    } catch (error) {
        console.log(error);
        return false;
    }

3. At the end of the script

Database.prototype.close = async function(){
    await this.pool.commit();
    await this.pool.close();
}

I get the error

(node:23600) UnhandledPromiseRejectionWarning: TypeError: this.pool.commit is not a function

Can I just remove the commit statement? And does a pool.close() flush all insertions to the database? I'm a bit scared that if I do no commit some entries will be lost.

Best Regards

[sidorares]

pool.execute() is a short for "get connection" + execute + "release connection". If you need to perform a transaction / multiple queries on the same connection that depend on each other and on connection state / commit/rollback transaction it's much better to do all the steps explicitly. There is no .commit() helper on the pool and connection.commit() is just a shortcut for connection.query('COMMIT')

[sidorares]

Having said that, I'm open to suggestions for a good api to perform exception safe / pool friendly / good DX for "make group of queries, commit on success and roll back on any error (client side or sql )"

[flipperlite]

@sidorares You can do a multiline statement using transactions through a single connection.execute or pool.query call. You need to enable namedPlaceholders and multipleStatements in your connection config. On error, it automatically rolls back. The MySQL table must be InnoDB though (won't work on MyISAM).

const sql = `SET autocommit = 0;
    START TRANSACTION;
    SET @taskid = :taskid;
    SET @now = NOW();
    SET @uuid = UUID();
    INSERT INTO ...;
    INSERT INTO ...;
    UPDATE ...;
    COMMIT;`