How to make encrypted sqlite3 database with nodejs in windows platform

Question

I want to make a small electron app that uses sqlite3 as database. I have installed sqlite3. npm install sqlite3 and rebuild it using node-gyp for using it with electron so far so good.

Now I want to encrypt the database how to do that particularly in windows platform

Answer 1

Yes, it's bit old question, though if somebody still wanted an answer, this might help them.

You can use @journeyapps/sqlcipher to enable encryption on your sqlite3 DB with electron app.

If you use any javascript ORM sequelize, you can configure that to use @journeyapps/sqlcipher as sqlite3 database engine as shown below

const sequelize = new Sequelize(null, null, 'your-encryption-key', { dialect: 'sqlite', dialectModulePath: '@journeyapps/sqlcipher', storage: 'path/to/db.sqlite' })

This works for me :)

Here is the example app which uses sqlite3 with sqlchiper wrapper.

Answer 2

Sqlite does not provide ways to encrypt the database file by default. It poses a lot of issues to attempt to do so. You'd have to encrypt the file with something like AES256, and then you'd have to decrypt the file when you wanted to access it, which implies that you'd have to keep a decrypted version somewhere. You could keep it in memory, but larger databases may not fit in memory, you'd also need to implement this in the SQLite library. You could create a temporary file, but this would mean that the decrypted version would be accessible any time the database was in use, and if your app crashes unexpectedly that file could fail to be cleaned up leaving your data exposed.

There are things like sqlitecrypt or sqlitecipher which encrypt your database, however they are replacements for sqlite. They implement the same API, however they are usually forks of sqlite. The node-sqlite3 module supports building for sqlitecipher, as illustrated here.

It's possible to encrypt data within the database. You'd need to generate a key with a passphrase, and then encrypt the data in each column. Set the passphrase on the key to the passphrase you'd like the user to use to unlock the data. This doesn't hide the structure of your database, and I'd only think this would be a pratical solution when you have a few columns of data that you'd like to encrypt, as you still need index fields to be able to query the data. This would be usefull for something like a password manager, where the username and password are encrypted fields, and there is a name associate to the user/pass pair which describes what its for. You'd be able to query by the credentials name, but the username and password would only be accessible by a password.