Post cover image

February 6, 2025

Create your encrypted database with SQLCipher and sqlx in Rust (for Windows)

Encrypting your database is quick and uncomplicated with SQLCipher.

By Luis Lema

8 min read

SQLCipher is a fork of SQLite that enables you to encrypt your database painlessly using the 256-bit AES encryption.

AES stands for Advanced Encryption Standard, the algorithm that protects data from unauthorized access.

Let's protect your database!

None

A tiny story about the origin of this project

(You can jump to the next section to get your hands dirty).

I use Firebird for an application whose database I want to protect.

However, Firebird does not support encryption at rest.

Encryption-at-rest means that the physical database file is encrypted and protected by a secret key.

You have to purchase a plugin to add encryption to your Firebird database.

Keep in mind that Firebird encryption plugin costs four figures! 😱

Here is where SQLite makes sense because it supports encryption with products like:

I chose SQLCipher because it has a free community version and it works!

SQLCipher also has a paid version, which includes features such as improved performance and customer support.

Setting up the prerequisites

1. Install the OpenSSL library for Windows

To compile SQLCipher, you need the OpenSSL library for Windows.

You can get the binaries from:

https://www.firedaemon.com/get-openssl

Follow the installer instructions, and you're done.

There are also other sources to download OpenSSL for Windows.

2. Create an environment variable for OpenSSL

Set up an environment variable named OPENSSL_DIR that points to the directory where you installed the OpenSSL binaries.

You can configure this by going to the "Edit user environment variables" dialog:

None

My variable is configured like this:

OPENSSL_DIR = C:\Program Files\FireDaemon OpenSSL 3

Here is the content of my OpenSSL directory:

None

You should restart your editor to load the newly create the OPENSSL_DIR environment variable.

Setting up the project

1. Create the Rust project

Ensure that the Rust toolchain is installed on your machine.

Then, run these commands to create your Rust Project:

cargo new create_encrypted_database_sqlcipher --lib
cd create_encrypted_database_sqlcipher

I will create a library named create_encrypted_database_sql_cipher (feel free to choose another name if you prefer).

Open the project using your preferred text editor (e.g., Vim, VS Code, or another of your choice).

I'll use RustRover.

Below is the file structure of the project:

create_encrypted_database_sqlcipher
├── src
│   └── lib.rs
├── .gitignore
└── Cargo.toml

2. Add the tokio and anyhow crates

Open your Cargo.toml file and add the following:

# Cargo.toml

# ...

[dependencies]
tokio = { version = "1.43.0", features = ["macros", "rt-multi-thread"] }
anyhow = "1.0.95"
  • Tokio provides support for asynchronous code, which is essential for sqlx.
  • Anyhow guarantees that a valuable backtrace is available when errors occur.

3. Add the sqlx crate

Open your Cargo.toml file and insert the [dependencies.sqlx] section.

# Cargo.toml

# ...

[dependencies.sqlx]
version = "0.8.3"
default-features = false
features = [
    "runtime-tokio-rustls",
    "macros",
    "sqlite",
    "migrate"
]

4. Add the crate for SQLCipher

In your Cargo.toml file, include the crate that integrates the SQLCipher database engine:

# Cargo.toml

# ...

[dependencies]
# ...
libsqlite3-sys = { version = "0.30.1", features = [
    "bundled-sqlcipher"
] }

# ...

5. Build the project

Run the following command to compile your project:

cargo build

Creating an SQLCipher database

  1. Create the file creation.rs in the src folder. The file structure so far is as follows:
create_encrypted_database_sqlcipher
├── src
│   ├── lib.rs
│   └── creation.rs
├── .gitignore
└── Cargo.toml
  1. In the creation.rs file add the following code:
// creation.rs

use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use sqlx::ConnectOptions;
use std::str::FromStr;

pub async fn create_encrypted_database(database_path: &str, password: &str) -> anyhow::Result<()> {
    let _ = SqliteConnectOptions::from_str(&database_path)?
        .pragma("key", password.to_owned())
        .create_if_missing(true)
        .connect()
        .await?;

    Ok(())
}

The code above does the following:

  • It creates a SQLCipher database at the path specified by database_path.
  • It encrypts the database using the password provided in the password parameter.
  • It creates the physical file if the database does not yet exist.
  1. Open the lib.rs file, replace the content with the following function:
// lib.rs

use sqlx::sqlite::SqliteQueryResult;
use sqlx::{query, Connection, SqliteConnection};

async fn fill_db(conn: &mut SqliteConnection) -> anyhow::Result<SqliteQueryResult> {
    conn.transaction(|tx| {
        Box::pin(async move {
            query(
                "
            CREATE TABLE Tool(
                Id INT PRIMARY KEY     NOT NULL,
                Name           TEXT    NOT NULL,
                Description    TEXT    NOT NULL,
                Price         REAL
             );
             ",
            )
            .execute(&mut **tx)
            .await?;

            query(
                r#"
            INSERT INTO Tool(Id, Name, Description, Price)
            VALUES
                (1, "Hammer", "🔨", 0.1),
                (2, "Screwdriver","🪛", 2.6),
                (3, "Wrench","🔧", 1.4)
            "#,
            )
            .execute(&mut **tx)
            .await
        })
    })
    .await
    .map_err(|e| e.into())
}

This function creates a sample table called Tool in the SQLCipher database.

  1. Now, let's create the actual database with this test function in the lib.rs file:
// lib.rs

use sqlx::sqlite::{SqliteConnectOptions, SqliteQueryResult};
use sqlx::{query, ConnectOptions, Connection, SqliteConnection};

use std::str::FromStr;

mod creation;
use crate::creation::*;

// [...]

#[tokio::test]
async fn test_create_encrypted_database() {
    let database_path = "C:\\store\\myencryptedatabase.sqlite3";
    let password = "my_top_secret_passworD";
    let _ = create_encrypted_database(&database_path, password).await;

    let mut conn = SqliteConnectOptions::from_str(&database_path)
        .unwrap()
        .pragma("key", password)
        .create_if_missing(true)
        .connect()
        .await
        .unwrap();

    let _ = fill_db(&mut conn).await;

    // Create another connection without a password, the query should fail
    let mut conn = SqliteConnectOptions::from_str(&database_path)
        .unwrap()
        .connect()
        .await
        .unwrap();

    assert!(conn
        .transaction(|tx| {
            Box::pin(async move { query("SELECT * FROM Tool;").fetch_all(&mut **tx).await })
        })
        .await
        .is_err());
}

The test performs the following steps:

  1. It creates an encrypted database.
  2. It connects to the database using the password.
  3. It creates a table and adds three rows to it.
  4. It connects to the encrypted database without a password.
  5. The connection fails, which is the expected result.

Run the test with:

cargo t test_create_encrypted_database -- --show-output

Output:

    Finished `test` profile [unoptimized + debuginfo] target(s) in 0.30s
     Running unittests src\lib.rs (target\debug\deps\create_encrypted_database_sqlcipher-6b9abab26548b50f.exe)

running 1 test
test tests::test_create_encrypted_database ... ok

successes:

successes:
    tests::test_create_encrypted_database

test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 1 filtered out; finished in 0.01s

The Tool table in the myencryptedatabase.sqlite3 database, as seen in SQLiteStudio:

None

Why use sqlx?

The sqlx crate provides features such as:

  1. Asynchronous runtime (with tokio)
  2. Prepared statements (with the query function)
  3. Migrations of database objects

We already tested the first two features.

Now, let's explore migrations for databases.

What are migrations in sqlx? 🤔

Migration is a mechanism for implementing and tracking changes to database objects from the application (Rust in this case), such as:

  1. Altering tables
  2. Inserting or deleting rows
  3. Creating stored procedures (Note: SQLCipher doesn't support this, but other databases do)

In the next section, we will add a migration for SQLCipher.

Creating a Migration for SQLCipher

Let's add a column to the Tool table to store the register date.

  1. Install the sqlx-cli tool with this command:
cargo install sqlx-cli --no-default-features --features sqlite
  1. Run the following command to create our first migration file:
sqlx migrate add add_register_column

The above command creates the file {timestamp}_add_register_column.sql inside the migrations folder.

The migrations folder contains the SQL scripts to be executed in the SQLCipher database.

Mine looks like this:

create_encrypted_database_sqlcipher
├── migrations
│   └── 20250201042949_add_register_column.sql
├── src
│   ├── lib.rs
│   └── creation.rs
├── .gitignore
└── Cargo.toml

The initial numbers in the .sql file represent the date and time the file was created. These numbers may vary on your machine.

  1. Open the {timestamp}_add_register_column.sql file and add this code:
-- migrations\{timestamp}_add_register_column.sql

ALTER TABLE Tool ADD COLUMN Register_Date TEXT;
  1. In the creation.rs file add this function:
// creation.rs

// [...]
use std::path::Path;
use sqlx::migrate::Migrator;

// [...]

pub async fn run_migration_encrypted_database(database_path:&str, password:&str) -> anyhow::Result<()> {
    let db_path = Path::new(database_path);

    let m = Migrator::new(Path::new("./migrations")).await?;
    let pool = SqlitePoolOptions::new()
        .connect_with(
            SqliteConnectOptions::from_str(&db_path.to_str().unwrap())
                .unwrap()
                .pragma("key",password.to_owned())
                .create_if_missing(true),
        )
        .await?;

    m.run(&pool).await?;

    Ok(())
}
  1. Open the lib.rs file to add this test function:
// lib.rs

// [...]

#[tokio::test]
async fn test_run_migration_encrypted_sqlcipher() {
    let database_path = "C:\\store\\myprotectedatabase.sqlite3";
    let password = "my_top_secret_passworD";

    let _ = create_encrypted_database(&database_path, password).await;

    let mut conn = SqliteConnectOptions::from_str(&database_path)
        .unwrap()
        .pragma("key", password)
        .create_if_missing(true)
        .connect()
        .await
        .unwrap();

    let _ = fill_db(&mut conn).await;

    let result = run_migration_encrypted_database(&database_path, password).await;

    // Uncomment the line below to check any error message if the test fails
    //dbg!(&result);

    assert!(result.is_ok())
}
  1. Run the test function with:
cargo t test_run_migration_encrypted_sqlcipher

Output:

Finished `test` profile [unoptimized + debuginfo] target(s) in 2.47s
     Running unittests src\lib.rs (target\debug\deps\create_encrypted_database_sqlcipher-6b9abab26548b50f.exe)

running 1 test
test test_run_migration_encrypted_sqlcipher ... ok

test result: ok. 1 passed; 0 failed; 0 ignored; 0 measured; 3 filtered out; finished in 0.74s

Here is the brand new Register_Date :

None

Notice that there is also a new table called _sqlx_migrations .

The _sqlx_migrations table stores information about all the migrations applied to the database. It looks like this:

None

⚠️Do not delete that table unless you want to reset the migration history.

sqlx ensures that each migration is applied only once, thanks to the _sql_migrations table.

You can now implement any ongoing changes to your SQLCipher database from Rust. 🙂

What if I need to run a migration at application deployment?

Use embedded migrations, and run them on application startup.

An embedded migration lets you run SQL code against the database when triggered by your Rust application.

Rust stores the migration files( .sql ) inside the binary application (the .exe file for Windows).

To test this feature of sqlx, let's build the entire Tool table from the ground up.

  1. Create the folder encrypted_db and, within it, the embedded_migrations folder into it. The folder structure should be like this:
create_encrypted_database_sqlcipher
├── encrypted_db
│   └── embedded_migrations
├── migrations
│   └── 20250201042949_add_register_column.sql
├── src
│   ├── lib.rs
│   └── creation.rs
├── .gitignore
└── Cargo.toml
  1. Run the following commands to create the SQL Scripts for the migrations:
sqlx migrate add create_tool_table
sqlx migrate add insert_rows_tool_table
sqlx migrate add add_register_column_tool_table
  1. Move the three created .sql files to the folder encrypted_db\embedded_migrations .

You should end up with this file structure:

create_encrypted_database_sqlcipher
├── encrypted_db
│   └── embedded_migrations
│       ├── 20250206043217_create_tool_table.sql
│       ├── 20250206043218_insert_rows_tool_table.sql
│       └── 20250206043219_add_register_column_tool_table.sql
├── migrations
│   └── 20250201042949_add_register_column.sql
├── src
│   ├── lib.rs
│   └── creation.rs
├── .gitignore
└── Cargo.toml

ℹ️️IMPORTANT: You shouldn't perform this step if you are using the default migrations folder.

I relocate the .sql files manually since sqlx does not allow modifying the default directory where it creates the SQL scripts for SQLCipher.

You can change the folder that sqlx uses for migrations by running this command:

sqlx migrate info --source path_to_your_folder

Sadly, this isn't possible since sqlx requires the DATABASE_URL environment variable before the command can run.

The DATABASE_URL variable contains the connection string for your database.

But DATABASE_URL does not work with SQLCipher databases ☹️.

  1. Open each .sql file created and add the SQL code below:
-- encrypted_db\embedded_migrations\{timestamp}_create_tool_table.sql

CREATE TABLE Tool
(
    Id          INT PRIMARY KEY NOT NULL,
    Name        TEXT            NOT NULL,
    Description TEXT            NOT NULL,
    Price       REAL
);
-- encrypted_db\embedded_migrations\{timestamp}_insert_rows_tool_table.sql

INSERT INTO Tool(Id, Name, Description, Price)
VALUES (1, "Hammer", "🔨", 0.1),
       (2, "Screwdriver", "🪛", 2.6),
       (3, "Wrench", "🔧", 1.4)
-- encrypted_db\embedded_migrations\{timestamp}_add_register_column_tool_table.sql

ALTER TABLE Tool ADD COLUMN Register_Date TEXT;
  1. In the creation.rs file add this function:
// creations.rs

// [...]

pub async fn run_embedded_migration_encrypted_database(database_path:&str, password:&str) -> anyhow::Result<()> {
    let db_path = Path::new(database_path);

    let pool = SqlitePoolOptions::new()
        .connect_with(
            SqliteConnectOptions::from_str(&db_path.to_str().unwrap())
                .unwrap()
                .pragma("key",password.to_owned())
                .create_if_missing(true),
        )
        .await?;

    // By default, the `migrate!` macro will search for sql files
    // in the migrations folder.
    // We need to change this behavior by setting the parameter
    // to the `encrypted_db/embedded_migrations` folder.
    sqlx::migrate!("encrypted_db/embedded_migrations")
        .run(&pool)
        .await?;

    Ok(())
}

The sqlx:migrate! macro will use the SQL scripts in the encrypted_db/embedded_migrations folder.

  1. Add the test function in the lib.rs file:
// lib.rs

// [...]
use std::path::Path;
use std::fs;

// [...]

#[tokio::test]
async fn test_run_embedded_migration_encrypted_sqlcipher() {
    let database_path = "C:\\store\\myotherprotectedatabase.sqlite3";
    let password = "my_top_secret_passworD";

    if Path::new(database_path).exists(){
        fs::remove_file(database_path).unwrap();
    }

    let _ = create_encrypted_database(&database_path, password).await;

    let result=run_embedded_migration_encrypted_database(&database_path, password).await;

    //dbg!(&result);

    assert!(result.is_ok())
}
  1. Run the test with:
cargo t test_run_embedded_migration_encrypted_sqlcipher

Checking out the myotherprotectedatabase.sqlite3 we have the following:

This is the Tool table and its rows:

None

This is the _sqlx_migrations table with the three migrations we just applied:

None

Some benefits of embedded migrations are:

  • You can run them whenever you want.
  • The .sql files are embedded within the binary application, so there's no need to use separate files during deployment.

Summary

Why didn't I use SQL Server instead of SQLCipher?

SQL Server cannot run as a serverless database on my knockoff phone, whereas SQLCipher can — and it's dirt cheap 🤘.

Find this Rust project in GitHub.