sqlite-mc

2.1.0-2.2.3-0indexed

SQLDelight driver leverages SQLite3MultipleCiphers for encrypted database management, enabling seamless configuration, key management, and migration of encryption settings across software releases.

AndroidJVMNative·toxicity-io/sqlite-mc

21

Stars

—

Used by

dependents

—

Health

/ 100

sqlite-mc

badge-platform-android badge-platform-jvm badge-platform-ios badge-platform-linux

An SQLDelight driver that uses SQLite3MultipleCiphers for database encryption.

Usage

Jvm Supported Operating Systems

NOTE: macOS and Windows binaries are code signed.

Library Versioning

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

SQLite3MultipleCiphers Flags

SQLite3MultipleCiphers is compiled with the following flags

Jvm & Native:

Jvm

SQLITE_THREADSAFE=1

Native:

SQLITE_THREADSAFE=2
SQLITE_OMIT_LOAD_EXTENSION

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  used 
 a network filesystem. This  important  macOS because 
some users think it  a good idea to put their home 
directory  a network filesystem.

I'm guessing    really a factor  iOS."

Get Started

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)
}

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

WXSQLITE3_HAVE_CIPHER_AEGIS=0

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.

Apply the sqlite-mc gradle plugin.

plugins {
    // Provides the SQLDelight gradle plugin automatically and applies it
    id("io.toxicity.sqlite-mc") version("2.1.0-2.2.3-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
    }
}

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.1.0-2.2.3-0")
}