CODEWITHSUNDEEP
react-nativesqliteexpo
moonyWolf
moonyWolf

Reputation: 79

Expo SQLite doesn't work for tables with Foreign Key

I have a React Native project managed by expo and I am trying to work on an offline database so ended up going with the expo-sqlite package since everything else that is better (watermelondb, pouchdb, realm etc.) doesn't work with expo managed workflow. So very basic table creation is working fine, but whenever I am trying to add a foreign key it just says that the parent table can't be resolved. So in the following chunk of code, the error is Unable to resolve table 'Profile'.

enter image description here

Below is a good picture to illustrate what I am seeing on IntelliJ. So far I haven't found any way of selecting a Datasource since if you are developing with expo the very basic way (not specifically for android or iOS) then there's no way to choose the Datasource.

I know that you would also have to turn on PRAGMA Foreign Keys and I have done so as well first with tx.executeSql and finally what you see below (that's how it currently is).

enter image description here

I have also gone to Project Structure and tried out SQLite, GeneralSQL, and MySQL for SQL Dialects under Langauge and Framework.

I am launching the expo app on an iOS device if that makes any difference at all.

Upvotes: 1

Views: 710

Answers (1)

moonyWolf
moonyWolf

Reputation: 79

The problem wasn't anything to do with this specific table but another one similar to this one later down the file. Also disregard the red on table_name and column_id, since that is something not solvable with how IntelliJ IDEA is setup (at least with what I have come to figure out!)

So initially I had the code like the following. Notice how the foreign key is declared on line 4

enter image description here

After a whole lot of commenting and uncommenting chunks of code, I found out that this was the one that was causing the problem. So I found out in this github issue that you can write transaction in the following way:

tx.executeSql("...", [], null, null)
//or
tx.executeSql("...", [], null, (tx, error) => {
  return true //rollback
})

And changing my code similary led me to the realization that, the

FOREIN KEY (column) REFRENCES PARENT_TABLE ( PARENT_TABLE_COLUMN )

has to be at the very last line otherwise, you will get errors such as syntax error at created_at (that was the error for me since created_at was right after the foreign key line)

Finally, I changed it to:

enter image description here

Bottom line is, instead of tx.executeSql("...") if you write out all the arguments and even pass a console log in the error_callback your life will become much better debugging what went wrong!

Upvotes: 1

Related Questions