sqlite-mc

An SQLDelight driver that uses SQLite3MultipleCiphers for database encryption.

Usage

• Define DatabasesDir

  Common (available for all targets):

  // Use system default location for the given platform
  val databasesDir = DatabasesDir()
  val databasesDir = DatabasesDir(null) // null
  val databasesDir = DatabasesDir("     ") // blank
  
  // Specify a path string
  val databasesDir = DatabasesDir("/path/to/databases")

  Android:

  val databasesDir = context.databasesDir()

  Jvm or Android:

  val databasesDir = DatabasesDir(File("/path/to/databases"))
  val databasesDir = DatabasesDir(Path.of("/path/to/databases"))

• Define your SQLiteMCDriver.Factory configuration in commonMain

  NOTE: Realistically, your favorite singleton pattern or dependency injection should be utilized here.

  // TLDR; 1 factory for each database file (will become evident later)
  val factory = SQLiteMCDriver.Factory(dbName = "test.db", schema = TestDatabase.Schema) {
      logger = { log -> println(log) }
      // Will redact key/rekey values, disable for debugging or playing (default: true)
      redactLogs = true
  
      // SqlDelight AfterVersion migration hooks
      afterVersions.add(AfterVersion(afterVersion = 2) { driver ->
          // do something
      })
      afterVersion(of = 2) { driver ->
          // do something
      }
  
      // Optional: Add PRAGMA statements to be executed
      // upon each connection opening.
      //
      // See >> https://www.sqlite.org/pragma.html
      pragmas {
          // both ephemeral and filesystem connections
          put("busy_timeout", 3_000.toString())
  
          // ephemeral connections only
          ephemeral.put("secure_delete", false.toString())
  
          // filesystem connections only
          filesystem.put("secure_delete", "fast")
      }
  
      // Can omit to simply go with the default DatabasesDir and
      // EncryptionConfig (ChaCha20)
      filesystem(databasesDir) {        
          encryption {
              // e.g. coming from SQLCipher library
              sqlCipher {
                  // v1()
                  // v2()
                  // v3()
                  v4()
                  // default()
              }
          }
      }
  }
  
  // NOTE: Suspension function "create" alternative available
  val driver1: SQLiteMcDriver = factory.createBlocking(Key.passphrase("password"))
  driver1.close()

• Easily spin up an ephemeral database for your configuration (no encryption)

  // NOTE: Suspension function "create" alternative available
  val inMemoryDriver = factory.createBlocking(opt = EphemeralOpt.IN_MEMORY)
  val namedDriver = factory.createBlocking(opt = EphemeralOpt.NAMED)
  val tempDriver = factory.createBlocking(opt = EphemeralOpt.TEMPORARY)
  inMemoryDriver.close()
  namedDriver.close()
  tempDriver.close()

• Easily change Keys

  // NOTE: Suspension function "create" alternative available
  val driver2 = factory.createBlocking(key = Key.passphrase("password"), rekey = Key.passphrase("new password"))
  driver2.close()
  
  // Remove encryption entirely by passing an empty key (i.e. Key.passphrase(""))
  val driver3 = factory.createBlocking(key = key.passphrase("new password"), rekey = Key.Empty)
  driver3.close()
  
  // Also supports use of RAW (already derived) keys and/or salt storage for SQLCipher & ChaCha20
  val salt = getOrCreate16ByteSalt("user abc")
  val derivedKey = derive32ByteKey(salt, "user secret password input")
  val rawKey = Key.raw(key = derivedKey, salt = salt, fillKey = true)
  
  val driver4 = factory.createBlocking(key = Key.Empty, rekey = rawKey)
  driver4.close()

• Easily migrate encryption configurations between software releases by defining a migrations block

  val factory = SQLiteMCDriver.Factory(dbName = "test.db", schema = TestDatabase.Schema) {
      logger = { log -> println(log) }
      redactLogs = false
  
      filesystem(databasesDir) {
          // NOTE: Never modify migrations, just leave them
          // for users who haven't opened your app in 5 years.
          encryptionMigrations {
  
              // Simply move your old encryption config up to a migration.
              //
              // If you _are_ migrating from SQLCipher library, note the
              // version of SQLCipher used the first time your app was
              // published with it. You will also need to define migrations
              // all the way back for each possible version (v1, v2, v3),
              // so that users who have not opened your app in a long time
              // can migrate from those versions as well.
              migrationFrom {
                  note = "Migration from SQLCipher library to sqlite-mc"
                  sqlCipher { v4() }
              }
          }
        
          encryption {
              sqlCipher { default() }
          }
      }
  }
  
  // Will try to open SQLCipher Default (legacy: 0).
  // 
  // On failure, Will try to open using migrations in reverse order of
  // what is expressed (i.e. SQLCipher-v4 (legacy: 4)).
  //
  // Once opened, will automatically migrate to SQLCipher Default (legacy: 0)
  // using the same Key that it opened with.
  val driverMigrate = factory.createBlocking(Key.passphrase("password"))
  driverMigrate.close()

• Share configurations between multiple factories

  val customChaCha20 = EncryptionConfig.new(other = null) {
      chaCha20 {
          default {
              // Define some non-default parameters if you wish
              kdfIter(250_000)
          }
      }
    
      // Other cipher choices
      //
      // ascon128 { default() }
      //
      // The following should not be utilized for new databases,
      // but are there for migration purposes.
      //
      // rc4 { default() }
      // wxAES128 { default() }
      // wxAES256 { default() }
    
  }
  
  val migrationConfig = EncryptionMigrationConfig.new(other = null) {
      migrationFrom {
          note = "Migration from SQLCipher library to sqlite-mc"
          sqlCipher { v4() }
      }
  
      migrationFrom {
          note = "Migration from SQLCipher:default to ChaCha20"
          sqlCipher { default() }
      }
  }
  
  val sharedPragmas = PragmaConfig.new(other = null) {
      put("busy_timeout", 5_000.toString())
  }
  
  val sharedFilesystem = FilesystemConfig.new(databasesDir) {
      encryptionMigrations(migrationConfig)
      encryption(customChaCha20)
  }
  
  val factory1 = SQLiteMCDriver.Factory("first.db", DatabaseFirst.Schema) {
      pragmas(sharedPragmas)
      filesystem(sharedFilesystem)
  }
  val factory2 = SQLiteMCDriver.Factory("second.db", DatabaseSecond.Schema) {
      pragmas(sharedPragmas)
      filesystem(sharedFilesystem)
  }

Jvm Supported Operating Systems

NOTE: macOS and Windows binaries are code signed.

	x86	x86_64	armv5	armv6	armv7	arm64	ppc64
Windows	✔	✔
macOS		✔				✔
Linux (libc)	✔	✔	✔	✔	✔	✔	✔
Linux (musl)	✔	✔				✔
FreeBSD

Library Versioning

Versioning follows the following pattern of SQLDelight - SQLite3MultipleCiphers - sqlite-mc sub version

• a.b.c - x.y.z - s
• The s is a single digit to allow for bug fixes and the like
• An s of 0 indicates a "first release" for that pairing of SQLDelight and SQLite3MultipleCiphers
• Examples:
  • 2.0.0 - 1.6.4 - 0
  • 2.0.0 - 1.6.4 - 1 (an update with sqlite-mc for 2.0.0-1.6.4)
  • 2.0.1 - 1.6.4 - 0 (a minor version update with SQLDelight)
  • 2.0.1 - 1.6.5 - 0 (a minor version update with SQLite3MultipleCiphers)
  • 2.0.1 - 1.6.5 - 1 (an update with sqlite-mc for 2.0.1-1.6.5)

SQLite3MultipleCiphers Flags

SQLite3MultipleCiphers is compiled with the following flags

Jvm & Native:

SQLITE_HAVE_ISNAN=1
HAVE_USLEEP=1
SQLITE_ENABLE_COLUMN_METADATA=1
SQLITE_CORE=1
SQLITE_ENABLE_FTS3=1
SQLITE_ENABLE_FTS3_PARENTHESIS=1
SQLITE_ENABLE_FTS5=1
SQLITE_ENABLE_RTREE=1
SQLITE_ENABLE_STAT4=1
SQLITE_ENABLE_DBSTAT_VTAB=1
SQLITE_ENABLE_MATH_FUNCTIONS=1
SQLITE_DEFAULT_MEMSTATUS=0
SQLITE_DEFAULT_FILE_PERMISSIONS=0666
SQLITE_MAX_VARIABLE_NUMBER=250000
SQLITE_MAX_MMAP_SIZE=0
SQLITE_MAX_LENGTH=2147483647
SQLITE_MAX_COLUMN=32767
SQLITE_MAX_SQL_LENGTH=1073741824
SQLITE_MAX_FUNCTION_ARG=127
SQLITE_MAX_ATTACHED=125
SQLITE_MAX_PAGE_COUNT=4294967294
SQLITE_DISABLE_PAGECACHE_OVERFLOW_STATS
SQLITE_DQS=0
CODEC_TYPE=CODEC_TYPE_CHACHA20
SQLITE_ENABLE_EXTFUNC=1
SQLITE_ENABLE_REGEXP=1
SQLITE_TEMP_STORE=2
SQLITE_USE_URI=1

Jvm

SQLITE_THREADSAFE=1

Native:

SQLITE_THREADSAFE=2
SQLITE_OMIT_LOAD_EXTENSION

Reason

2 (Multi-Threaded) is the default for Darwin
targets, but on JVM it is using 1 (Serialized).

SQLDelight's NativeSqliteDriver utilizes thread pools
and nerfs any benefit that Serialized would offer, so.

This *might* change in the future if migrating away from
SQLDelight's NativeSqliteDriver and SQLiter.

Omission of the load extension code is only able to be
set for Native, as Jvm requires the code to remain in
order to link with the JNI interface. Extension loading
is disabled by default for Jvm, but the C code must stay
in order to mitigate modifying the Java codebase.

Darwin:

SQLITE_ENABLE_API_ARMOR
SQLITE_OMIT_AUTORESET

Reason

Options that SQLite is compiled with on
Darwin devices. macOS 10.11.6+, iOS 9.3.5+

iOS, tvOS, watchOS:

SQLITE_ENABLE_LOCKING_STYLE=0

Reason

D.Richard Hipp (SQLite architect) suggests for non-macOS: 

"The SQLITE_ENABLE_LOCKING_STYLE thing is an apple-only 
extension that boosts performance when SQLite is used 
on a network filesystem. This is important on macOS because 
some users think it is a good idea to put their home 
directory on a network filesystem.

I'm guessing this is not really a factor on iOS."

Get Started

1. Remove SQLDelight gradle plugin and driver dependencies from your project
2. Apply the sqlite-mc gradle plugin.

   plugins {
       // Provides the SQLDelight gradle plugin automatically and applies it
       id("io.toxicity.sqlite-mc") version("2.0.2-1.8.7-0")
   }
   
   // Will automatically:
   //  - Configure the latest SQLite dialect
   //  - Add the sqlite-mc driver dependency
   //  - Link native targets for provided SQLite3MultipleCiphers binaries
   sqliteMC {
       databases {
           // Configure just like you would the SQLDelight plugin
       }
   }

3. If you have Android unit tests

   dependencies {
       // For android unit tests (NOT instrumented)
       //
       // This is simply the desktop binary resources needed for
       // JDBC to operate locally on the machine.
       testImplementation("io.toxicity.sqlite-mc:android-unit-test:2.0.2-1.8.7-0")
   }