SQLite is the best solution to store data locally, but when using it? Maybe it's better to understand when does't use it:
IF data are detached from the app OR there are lots of concurrent writers OR Big Data
THEN t's better build a server side services (client-server)
- transactional: Atomic,Consistent,Isolated, Durable (ACID)
SQLite han't all feature that usually a server side db (as MySQL) have:
- Right and full outer join.
- ALTER TABLE isn't completely supported.
- There isn't Date and Time DataType but it's possible to store data as a REAL (Julian day number), INTEGER (Unix time) or TEXT (ISO8601) and extract it by specific function. I prefer store date as text so for example the today's date is strftime("%s",'now') or use in a query as "strftime('%Y-%m-%d',DATA_RILIEVO) DESC" where DATA_RILIEVO is the columns name.
- If an INTEGER column is a primary key then it's an alias of the ROWID column. ROWID set is value to first available integer, otherwise it's possible to use AUTOINCREMENT.
- I have noticed that multiple primary key isn't always supported.