Android SQLite best practices

SQLCipher
1

Please can someone confirm whether all the Android SQLite best practices also apply to SQLCipher on Android?

2

Hi @marcardar,

That recommendation link appears inline with usage for SQLCipher. In relation to SQLCipher specifically, we often recommend the following [1] for performance as there are some perf-related usage differences between SQLite and SQLCipher.

  1. SQLCipher Performance Optimization - Zetetic ↩︎

3

Thanks Nick. On that page, the only sqlcipher-specific optimizations seem to be:

  1. PRAGMA cipher_memory_security = OFF
  2. cipher_profile

The first one comes out of the box with 4.5.0+ so is not an issue if using the latest version (unless it’s an issue with sqlcipher dbs created with earlier versions?).

The second one seems like a query optimization approach.

Is there anything else regarding configuration parameters? For example, I noticed the android dev doc recommends using PRAGMA synchronous = NORMAL, whereas I remember Room defaulted to FULL.

4

Hi @marcardar,

In particular to SQLCipher is our recommendation to not repeatedly open/close connections. This is commonly the top issue identified for users migrating from SQLite to SQLCipher due to the key derivation process.

5

Thanks Nick. So regarding synchronous I can just defer to the Android dev docs?

6

Hey @marcardar - The appropriate PRAGMA synchronous setting depends on the journal mode. NORMAL is generally considered safe with WAL mode, but if you are not using WAL then we recommend FULL.

Also, somewhat related, it’s worth noting that apple file systems may not actually flush on fsync. For the highest integrity, you would use PRAGMA synchronous = FULL; and PRAGMA fullfsync = ON;, but that will likely affect performance.

1 Like