Cumbersome error messages with SQLite and database integrity constraints turned on

[bdeboer]

When executing queries we receive errors like:

[cds] - SQLITE_CONSTRAINT: FOREIGN KEY constraint failed in:
COMMIT
    at /home/node/app/node_modules/@sap/cds/libx/_runtime/sqlite/execute.js:43:15
    at new Promise (<anonymous>)
    at _executeSimpleSQL (/home/node/app/node_modules/@sap/cds/libx/_runtime/sqlite/execute.js:42:10)
    at executePlainSQL (/home/node/app/node_modules/@sap/cds/libx/_runtime/sqlite/execute.js:199:10)
    at SQLiteDatabase._run (/home/node/app/node_modules/@sap/cds/libx/_runtime/db/query/run.js:5:12)
    at SQLiteDatabase.<anonymous> (/home/node/app/node_modules/@sap/cds/libx/_runtime/sqlite/Service.js:57:19)
    at next (/home/node/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:70:36)
    at SQLiteDatabase.handle (/home/node/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:74:6)
    at async SQLiteDatabase.commit (/home/node/app/node_modules/@sap/cds/libx/_runtime/db/Service.js:69:5)
    at async SQLiteDatabase.commit (/home/node/app/node_modules/@sap/cds/lib/serve/Transaction.js:70:34) {
  errno: 19,
  code: 'SQLITE_CONSTRAINT',
  query: 'COMMIT',
  id: '1071256',
  level: 'ERROR',
  timestamp: 1660546526449
}

In our case we're running multiple queries. We have enabled query debugging which is nicely displaying our queries. But it seems the error occurs during the final commit. It doesn't show which query was the one that failed.

Is there some way we can have CDS/SQLite provide more detailed debugging information?

[gregorw]

Hi patricetao,

did the @cap-js/sqlite bring any improvement here?

[patricebender]

gregorw sqlite does not give any indication of which statement caused the referential integrity violation. You can try this out with a simple sqite console. Just `BEGIN` a statement, `INSERT` some entries with correct fk references and mixin one statement (or multiple) which refers to a non exisiting foreign key. `COMMIT` the transaction and observe the poor error message which comes back from sqlite.

[patricebender]

Hi,

unfortunately, the error messages for integrity violations are passed to you as they come from the database.

This is also documented in capire.

Best Regards,

Patrice

[bdeboer]

That was my understanding. Is there maybe a way to make those database messages more verbose?

sqlite3 has something like:

sqlite3.verbose();

That could perhaps be enabled in the cds sqlite driver somehow?

[david_kunz2]

Hi Bart,

Thank you for this suggestion! Unfortunately, it seems that setting `sqlite3.verbose()` doesn't improve the error message.
Did it work in your case?
Thanks and best regards,
David

[bdeboer]

Hi David,

No it didn't have any effect for me. I presume because the CDS SQLite driver creates its own sqlite3 instance. I would need to call verbose() on that instance. I don't know if it's possible?

[bdeboer]

Just tried to directly hack it into:

./node_modules/@sap/cds/libx/_runtime/sqlite/Service.js

that doesn't seem to have any effect either. Not sure if that's the only place where the sqlite3 instance is created. Although require('sqlite3') should be pointing to the same instance.