I have created, published and maintained a couple of Ionic Angular Capacitor applications having SQLite relational database with an ORM, working on web, android, and iOS using the following strategy.
Here’s the repo, I’ve created as a template
Config
- Angular 20 ( Standalone/Modular, works for both )
- Ionic 8
- Capacitor 7
- Capacitor Community Sqlite 7.0.2
- Typeorm 0.3.27
There are a few challenges in working with typeorm that must be kept in mind
- Typeorm manages several db drivers, so, some behaviors can be unexpected,
- for eg :- on inserting 10 new rows using insert function, a response will be returned containing 10 rows with primary key, but it will be primary key of the first row repeated 10 times.
- for eg :- below sqlite version 3.25.0, there was no function to rename column, if you used rename column in typeorm and you’re using sqlite, typeorm will drop your entire table, create a new table with the column name changed, so, all your data in the table is lost.
Some nuance
- Capacitor community sqlite installs sql.js and localforage along with it, which manage sqlite on web. Sql.js is a web assembly port of sqlite, and is used to create sqlite in the memory (RAM), and all operations are run on the in-memory database. This database is not stored, to store it, each time db interaction is made, the entire db is emitted as a unit8 binary array, which is stored in indexed db using localforage. Therefore, when in web, typeorm must use sqljs as the driver, and when in mobile capacitor-sqlite as the driver, otherwise, typeorm will lose the db after a few reloads, and create a new db.
- I am storing the db only when a set request is made, which I am differentiation by creating separate service files, for getter, and setter functions, and passing the entire class through a class wrapper, that sets a subject true only in case of set functions, indicating db needs to be stored.
- Typeorm or any ORM will not do a batch insert when you want to save a large number of rows, with children, here you should be smart to create batches, to insert 500 rows at a time, then iterate the children to put the parentId on them, and saving the children as a batch. Which will result in difference of time taken to save let’s say 1000 rows with 2-3 children each, from 4-12 seconds to 0.1-0.4 s.
- Read documentations
