Database with encrypted values

Like it was mentioned in searchable and sortable you can still use your database to query data to the customer without the need to decrypt it first and then doing queries like searching for a value, getting the exact results or returning a sorted list of values.

Use searchable encryption to get either the exact data match or multiple data to a searched value and sortable encryption to do range queries like ORDER BY name.

Because sortable encryption won't encrypt your full data it should not be used for exact matches.

The best thing is that you don't need to modify your database or just different functions. Both technics can be used with the native database query functions like you would do with not encrypted data.

The example below is a relational database like mysql, sqlite or postgres but this will also work for nosql databases.

The tables

Use a normal table for your data like you would do without encryption.

Table: user:


In the real world the data is end-to-end encrypted in your group. The problem, you can't do anything with it, except storing them.


Create a 2nd table for the search hashes of each column you want to query and link it to your users table.

Table: user_hash:

123hash of Jon
123another hash of Jon
124hash of John
125hash of Johnny

Now create the hashes like this:

const hashes_jon = group.createSearchRaw("Jon");

const hashes_john = group.createSearchRaw("John");

const hashes_johnnny = group.createSearchRaw("Johnny");

You can also set a boolean flag in your hashes table for the last hash of a word. The last item in the hashes list is also the hash of the exact word.

This helps to do queries where you need the equal value and not all values like this.

123hash of Jonfalse
123another hash of Jontrue
124hash of Johnfalse
125hash of Johnnyfalse

If you want to only got the exact values test if the last is true.

Sort/Order able

If you also want to query the last name create a second table for the hashes of the last name or create a column with a flag that identifies to what column the hash is for, for the id.

To do range queries expand the users table by a column of a value that you want to do the range query. Like if you want to ORDER BY first_name, then create another column with the sortable first_name.


Now create the sortable column like this:

const sort_jon = group.encryptSortableRawString("Jon");
const sort_john = group.encryptSortableRawString("John");
const sort_johnny = group.encryptSortableRawString("Johnny");


To get now the data just use the normal database queries.

To get all users order by name:

SELECT id, first_name, last_name, age FROM users ORDER BY order_first_name

This data can then decrypt by the group key.

To get the exact data you need to create a hash in the client first and then search it:

const hash ="jo");
SELECT id, first_name, last_name, age 
FROM users u, user_hash uh 
WHERE = uh.item_id AND 
    hash = ?

The result would be all three users because everyone begins with jo. The is equal to sql LIKE queries.

To get exact values just check if it is the last hash (of the full word).

SELECT id, first_name, last_name, age 
FROM users u, user_hash uh 
WHERE = uh.item_id AND last_hash = TRUE AND
    hash = ?

For the hash of John only the data with id 124 will be returned but not johnny (id 125).

Now you still get the ability to do search queries and exact matches. With createSearch option full you can't do searching.

Last Updated: