Configuring SQLite Beyond Room

Room covers a lot of what you will need when interacting with SQLite from your app. Room might not cover everything of what you would like to use with SQLite, though.

Some things — particularly anything involving table definitions — pretty much requires Room itself to be upgraded in order to work. For example, you cannot readily add full-text searching yourself, as that requires particular options in the CREATE TABLE statement.

Anything that lies outside of Room, though, is fair game, though you have to resort to classic SQLite approaches to make it work.

When To Make Changes

You have two main events for when to make changes outside of Room to the database: when it is created and when it is opened. Which you use depends on the nature of your changes.

Changes that are persistent would be applied when the database is created, or (eventually) via a Migration when the database schema is modified. For example, using CREATE TRIGGER to create a trigger results in a persistent change to the database, so you only need to do this when the database schema is created or modified.

However, some PRAGMA statements are transient, living for the life of our connection to the database. Once the connection is closed, the effects of those PRAGMA statements go away. As a result, we have to apply these every time that the database is opened.