How to set options when using better-sqlite3 with sqlcipher?

[yolopunk]

Issue type:

• question
• bug report
• feature request
• documentation issue

Database system/driver:

• cordova
• mongodb
• mssql
• mysql / mariadb
• oracle
• postgres
• sqlite / better-sqlite3-multiple-ciphers
• sqljs
• react-native
• expo

TypeORM version:

[x] 0.2.41
[ ] @next
[ ] 0.x.x (or put your version here)

better-sqlite3-multiple-ciphers version: 7.4.5

• The following method is in effect （using better-sqlite3-multiple-ciphers)

// encrypt db
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', { verbose: console.log })
db.pragma("cipher='sqlcipher'")
db.pragma(`rekey='secret-key'`)
db.prepare(`CREATE TABLE "post" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar NOT NULL, "text" varchar NOT NULL)`).run()
const stmt = db.prepare('INSERT INTO post (title, text) VALUES (?, ?)')
const info = stmt.run('Joey', 'my homie')
db.close()

// decrypt db
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', { verbose: console.log });

db.pragma(`cipher='sqlcipher'`)
db.pragma("key='secret-key'");
const stmt = db.prepare("SELECT * FROM post")
console.log(stmt.get()); // { id: 1, title: 'Joey', text: 'my homie' }

• The following method is not valid （using typeorm)

import { createConnection } from 'typeorm'
import { BetterSqlite3ConnectionOptions } from 'typeorm/driver/better-sqlite3/BetterSqlite3ConnectionOptions'
import { Post } from './entity/post'

const config: BetterSqlite3ConnectionOptions = {
  type: 'better-sqlite3',
  key: 'secret-key',
  database: 'foobar.db',
  driver: require('better-sqlite3-multiple-ciphers'),
  entities: ['entity/*.ts'],
  logging: true,
  verbose: console.log,
  prepareDatabase: db => {
    db.pragma(`cipher='sqlcipher'`)
  }
}

const start = async () => {
  const conn = await createConnection(config)
  const posts = await conn.manager.find(Post)
  console.log(posts)
}

start()  // SqliteError: file is not a database

I don’t know what’s wrong with my options for typeorm.

Related to issue: m4heshd/better-sqlite3-multiple-ciphers#4

The reproducible repo: https://github.com/yolopunk/typeorm-better-sqlite-sqlcipher

[m4heshd]

The issue is typeorm tries to run PRAGMA foreign_keys = ON before executing prepareDatabase() which leads to file not being recognized as a DB since it's already encrypted.

In my opinion this execution order is not effective or helpful at all when coupled with custom implementations of the library which in this case is an encryption extension. Current method of initialization also goes against the practices of official SQLite Encryption Extension which has identical usage and behavior to better-sqlite3-multiple-ciphers.

You must invoke this pragma before trying to do any other interaction with the database. - SEE documentation

There are two possible solutions for this.

1. Bring the execution of prepareDatabase() to the top
2. Introduce a new functional config that runs right after initializing the DB

Please feel free to point out anything I have missed here because I've never used typeorm and just looked through the source solely for the purpose of diagnosing this issue.

[yolopunk]

@m4heshd

typeorm/src/driver/better-sqlite3/BetterSqlite3Driver.ts

Lines 102 to 120 in cefddd9

	const databaseConnection = this.sqlite(database, { readonly, fileMustExist, timeout, verbose });
	// we need to enable foreign keys in sqlite to make sure all foreign key related features
	// working properly. this also makes onDelete to work with sqlite.
	databaseConnection.exec(`PRAGMA foreign_keys = ON`);
	// turn on WAL mode to enhance performance
	databaseConnection.exec(`PRAGMA journal_mode = WAL`);
	// in the options, if encryption key for SQLCipher is setted.
	if (this.options.key) {
	databaseConnection.exec(`PRAGMA key = ${JSON.stringify(this.options.key)}`);
	}
	if (typeof prepareDatabase === "function") {
	prepareDatabase(databaseConnection);
	}
	return databaseConnection;

Here is the related code. I don't see anything wrong with the logical order.

Please review it, maybe you can focus on some problems.

[m4heshd]

@yolopunk That's the same block of code which I linked above.

typeorm/src/driver/better-sqlite3/BetterSqlite3Driver.ts

Line 106 in cefddd9

databaseConnection.exec(`PRAGMA foreign_keys = ON`);

This line should not execute before prepareDatabase() does.

[yolopunk]

@m4heshd Awesome! That is worked by verification. I could commit a pull request for fixed the bug!

Thank you very much!

[m4heshd]

@yolopunk I wouldn't jump into that because that could be a breaking change for the existing users. Introducing something new would be the best option here.

[pleerock]

Thank you guys!