Using SQLite with Sequelize and NodeJS

This project is only a POC to test how to use sqlite with encryption.

Prerequisites

...
    "@journeyapps/sqlcipher": "^5.3.1",
    "sequelize": "^6.15.0",
    "sqlite3": "^5.0.2"
...

POC

Here is how I tested the connection, database creation and the encryption:

I’m using the default SQLCipher, The end goal is to use the same solution with electron. But as for now I’m not sure if it works.

sequelize.js

const { Sequelize, DataTypes } = require("sequelize");

const sequelize = new Sequelize({
  dialect: "sqlite",
  dialectModulePath: "@journeyapps/sqlcipher",
  storage: "./sequelize.sqlite",
  logging: console.log,
});

(async () => {
  // SQLCipher config
  await sequelize.query("PRAGMA cipher_compatibility = 4");
  await sequelize.query("PRAGMA key = 'your-encryption-key'");

  const User = await sequelize.define("User", {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
    firstname: { type: DataTypes.STRING, allowNull: false },
    lastname: { type: DataTypes.STRING, allowNull: false },
  });
  await User.sync({ force: true });

  await sequelize.close();
})();

Simply launch node sequelize.js

You should have a new file in your current working directory: sequelize.sqlite

The content will look like :

I use this tool to access the data easily: DB Browser for SQLite

Type your passphrasse : your-encryption-key

Conclusion

I didn’t go further as for now, my goal is to find a unique solution to work with IOS and Electron.

I need to test the @capacitor-community/sqlite approach as well, but according to the documentation the encryption seems not supported with Electron.

Webux Lab

SQLite, Sequelize and NodeJS

Using SQLite with Sequelize and NodeJS

Prerequisites

POC

Conclusion

Sources