How to directly update note records in SQLite database?

Support
1

Say, we need to directly update note records in SQLite: what is the correct way to ensure data integrity with the Joplin desktop application and not cause any harm?

In my particular case, I need to update a part of the body column in notes conditionally (either via DB SQLite browser or a custom script).

But there seem to be several more or less relevant tables for notes: notes, notes_normalized and notes_fts*, where * stands for the suffix of a couple of tables being part of SQL full text search.

If I understand correctly, SQLite updates the full text search index (FTS) tables automatically, if sources are updated, so I would exclude them. According to schema, the source is notes_normalized.

So is notes_normalized the correct table to make changes? If yes, what is the purpose of notes?

2

To go a bit more into detail:
I am storing some meta fields inside the note content and need to create a batch job to programmatically change these fields in certain notes.

Are SQL queries run on notes_normalized or notes? Something other to be aware of? Appreciate any details.

3

I don’t know hot to correctly update note records in SQLite but did you already try to achieve your goal with the Rest API or the Terminal App? I think it is not foreseen that users make changes directly in the DB.

1 Like
4

@johnny I did not know about the REST API, this is a great tip and certainly helps! I will read through it, thank you.