SQlite constraints

Ask for Help
1

Hi! I have been using Grist since several months and I love it, as it makes it very easy to give users simple apps to manage their data.

I have a technical question. I am also using Python scripts to manage the users data in Sqlite behind the hood.

I would need to use Sqlite constraints (like unique constraint to be able to use the Sqlite upsert feature to manage records properly). Is it possible to do so, or does it interfere with Grist internal workings?

I am asking as I see that Grist is storing a lot of metadata (for example, not using Sqlite foreign keys but managing references otherwise), and I want to make sure first that dealing with Sqlite from outside of Grist does not create havoc.

Thanks in advance for any guidance on the subject, and happy new year to all!!
Michel

2 Likes
2

There are of course ways to interfere with Grist’s handling of the SQLite databases used to store metadata. But as long as Grist can read and write normally, you should be okay.

Which uniqueness constraints do you want to use? The login table already has a uniqueness constraint on the email column, for example. You might be able to add other constraints, depending on what you want to do. I do caution you, however, to first test this on a copy of your real data in case there is trouble I have not anticipated.

3

Hi JordiGH,

Thanks for the answer and happy new year!

Got it, I’ll test on a copy, and see if this creates problems. At this writing the main issue I want to deal with is using SQLite UPSERT for updating some tables, hence the need of having the needed constraints for the UPSERTs to be able to operate properly. So far, I worked around the issue by having my scripts deal with the problem, but this is extra programming each time, which I’d prefer to avoid.

Thanks again!

Michel