How to actually set PRAGMA FOREIGN_KEYS = ON; in expo-sqlite?-CodePudding

I would really love to simplify a lot of my logic by using a foreign key constraint with ON DELETE CASCADE in one of my table definitions using Android native SQLite and the expo-sqlite module.

Through researching I've found that you have to set PRAGMA FOREIGN_KEYS = ON before every DB transaction. The tools that are available in many other SQLite libraries are not available for expo-sqlite. As far as I can tell, I can only execute one query per transaction using this library (though I'd love to be proven wrong).

So currently a simple query might look like this:

export function insertEntity(entity) {
    return new Promise((resolve, reject) => {
        database.transaction(tx => {
            tx.executeSql(
                `INSERT INTO ${tableName} 
                (firstName, lastName)
                VALUES (?, ?)`,
                [entity.fn, entity.ln],
                (_, result) => { resolve(result)},
                (_, err) => { reject(err); return true; }
            )
        })
    });
};

But when I try to set the PRAGMA FOREIGN_KEYS = ON; statement as part of that transaction like this:

export function insertEntity(entity) {
    return new Promise((resolve, reject) => {
        database.transaction(tx => {
            tx.executeSql(
                `PRAGMA FOREIGN_KEYS =  ON;
                INSERT INTO ${tableName} 
                (firstName, lastName)
                VALUES (?, ?)`,
                [entity.fn, entity.ln],
                (_, result) => { resolve(result)},
                (_, err) => { reject(err); return true; }
            )
        })
    });
};

I start to get errors about my parameters not being able to map...

I also don't see a way to configure any settings on opening the DB either, which would be ideally what I want. Because I can't imagine why I would want to create a foreign key constraint and then not use it by default, but that is how this API is designed apparently...

There is this, but without context around where and when to use it I can't figure it out. (Do I just run it once when opening the DB? Do I run it before every transaction? Does my transaction then have to be a callback passed to that method?) https://docs.expo.dev/versions/latest/sdk/sqlite/#executing-statements-outside-of-a-transaction

CodePudding user response：

From SQLite documentation https://www.sqlite.org/foreignkeys.html

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled. The following command-line session demonstrates this

So you can do check any time you create a new connection to DB.

const db = SQLite.openDatabase('dbName', version);  

// once per connection
db.exec(
  [{ sql: 'PRAGMA foreign_keys = ON;', args: [] }], 
  false, 
  () =>   console.log('Foreign keys turned on') 
);