Convert the User model in your Prisma/Next.js app to CipherStash

In this article, we'll demonstrate how to convert a Prisma User model in a Next.js application to a searchable-encrypted data vault using CipherStash. We're going to use @cipherstash/stashjs-adapter to stage the migration while making minimal changes to the rest of our app.

We'll go through the following steps:

1. Review the User model
2. Define a collection schema
3. Create a collection
4. Define an Adapter
5. Import users
6. Run some queries!
7. Update your code
8. Perform final cut-over

Let's get into it!

User Model

We have a Next.JS application that uses Prisma to manage users. The schema looks like the following:

ts
model User {
  id             Int     @id @default(autoincrement())
  email          String
  phone          String
  name           String
  signedUp       DateTime
  emailVerified  Boolean
}

As you can see, it isn't rocket surgery, but our user model could certainly contain some sensitive information, particularly in the email, phone and name fields. Data stored in the signedUp field isn't necessarily sensitive directly (though an attacker could use that information in a correlation attack). Because all data in a CipherStash collection is encrypted, this won't be a problem.

Define a Collection Schema

Before creating a collection, make sure you've created a free account and done the initial setup in the Getting Started Guide.

Collections are defined via a JSON schema that contains a type section and an indexes section. The type for our collection is going to look very similar to the schema used by our existing database (note that types in CipherStash are all lowercase and the signedUp column has a type of date which is functionally equivalent to DateTime in Prisma).

We've included one more field called originalId which will help us map CipherStash records back to the existing database (we'll get to that shortly).

ts
{
  "type": {
    "email": "string",
    "phone": "string",
    "name": "string",
    "signedUp": "date",
    "emailVerified": "boolean",
    "originalId": "float64"
  },
  // -- snip --
}

Note that all fields in our type will be encrypted by the CipherStash SDK before being sent across the network.

To perform searches, we need to define some indexes. Unlike traditional databases, where indexes are optional, searches in CipherStash can only be performed if an index is present. This is because the underlying data is fully encrypted using standard AES and so a sequential scan of the data won't work.

To define indexes on our collection, we specify them in the indexes section of the schema. For example, let's create an index on email so that we can look users up by their email address:

ts
{
  "type": {
     // -- excluded for brevity
  },
  "indexes": {
    "email": {
      "kind": "exact",
      "field": "email"
    }
  }
}

Let's break down what we've done here. Under the indexes section of our schema, we have an index called email. Index names can be any string but it often makes sense for the index to be named after the field (we'll cover an example shortly for when you might like to use a different name).

Our email index is on the email field and has a kind of exact. That means that we can perform lookups on users where the email address exactly matches our query. We might as well go ahead and add exact indexes for phone, name and emailVerified as well.

ts
{
  "type": {
     // -- excluded for brevity
  },
  "indexes": {
    "phone": { "kind": "exact", "field": "phone" },
    "name": { "kind": "exact", "field": "name" },
    "emailVerified": { "kind": "exact", "field": "emailVerified" }
  }
}

Exact matches in CipherStash behave similarly to keyword types in Elasticsearch or hash indexes in PostgreSQL

Next, let's add an index to our schema for the signedUp field. Because it contains date/time data, it would be useful to be able to do range queries. So this time, we'll set the index kind to be range!

ts
{
  "type": {
     // -- excluded for brevity
  },
  "indexes": {
    // -- snip
    "signedUp": { "kind": "range", "field": "signedUp" }
  }
}

Range indexes, as you might guess, allow us to query for values before, after or between a range of numbers or dates.

Before we create our collection, let's add one more index to our schema. This index will let us do full-text or fuzzy string matches over text data stored in our collection. Pretty useful if you want to search your users by partial name or implement a type-ahead box in your app's UI.

And to go even further, let's create this index on the email and name fields together. This way, searches will look for matches in either or both fields. And this time, we'll call the index fuzzy!

ts
{
  "type": {
     // -- excluded for brevity
  },
  "indexes": {
    // -- snip
    "fuzzy": {
      "kind": "match",
      "fields": ["name", "email"],
      "tokenFilters": [
        { "kind": "downcase" },
        { "kind": "ngram", "tokenLength": 3 }
      ],
      "tokenizer": { "kind": "standard" }
    }
  }
}

There is a bit more going on here than in the indexes we created earlier. If you've used Elasticsearch or Lucene before, some of these attributes may look familiar. However, for now, it is only really important to know that the kind is set to match and that the fields attribute is now an array containing both name and email. If you want to skip ahead and learn about all the options for fuzzy matching in CipherStash, you can check out the reference docs.

Our completed collection schema, now looks as follows:

ts
// schema.json
{
  "type": {
    "email": "string",
    "phone": "string",
    "name": "string",
    "signedUp": "date",
    "emailVerified": "boolean",
    "originalId": "float64"
  },
  "indexes": {
    "phone": { "kind": "exact", "field": "phone" },
    "name": { "kind": "exact", "field": "name" },
    "emailVerified": { "kind": "exact", "field": "emailVerified" }
    "signedUp": { "kind": "range", "field": "signedUp" },
    "fuzzy": {
      "kind": "match",
      "fields": ["name", "email"],
      "tokenFilters": [
        { "kind": "downcase" },
        { "kind": "ngram", "tokenLength": 3 }
      ],
      "tokenizer": { "kind": "standard" }
    }
  }
}

Creating the Collection

Make sure to save your schema to a file in the current directory (say schema.json). Now, we can use the stash command-line tool (CLI) to actually create the collection.

stash create-collection users --schema schema.json

This uses the create-collection subcommand to create a collection called users with the schema we just created.

To verify that our collection looks correct, we'll use the describe-collection subcommand:

stash describe-collection users

You should get some output that looks like the following:

┌───────────────┬────────────┬────────────────────┬─────────────────┐
│ Index Name    │ Index Type │ Field(s)           │ Query Operators │
├───────────────┼────────────┼────────────────────┼─────────────────┤
│ signedUp      │ range      │ signedUp           │ <, <=, =, >= >  │
├───────────────┼────────────┼────────────────────┼─────────────────┤
│ emailVerified │ exact      │ emailVerified      │ =               │
├───────────────┼────────────┼────────────────────┼─────────────────┤
│ exact         │ exact      │ email              │ =               │
├───────────────┼────────────┼────────────────────┼─────────────────┤
│ name          │ exact      │ name               │ =               │
├───────────────┼────────────┼────────────────────┼─────────────────┤
│ exact         │ exact      │ phone              │ =               │
├───────────────┼────────────┼────────────────────┼─────────────────┤
│ fuzzy         │ match      │ name, email        │ =~              │
└───────────────┴────────────┴────────────────────┴─────────────────┘

As you can see, the collection has been created with 6 indexes and for each index we can see the fields that are indexed and the types of operations that can be performed.

Define an Adapter

CipherStash collections use UUIDs instead of integer primary keys so we're going to add a column to our existing User model to map CipherStash IDs to the existing integer IDs. (Note that if your model already has UUID primary keys, this step isn't really necessary and you can probably just use @cipherstash/stashjs directly!)

Let's use Prisma's schema to add the column to our user:

ts
model User {
  id             Int     @id @default(autoincrement())
  email          String
  phone          String
  name           String
  signedUp       DateTime
  emailVerified  Boolean
  // -- Add this line
  stashId        String? @db.Uuid
}

Then apply the migration by running:

npx prisma migrate dev

We've made stashId optional so that we stage our migration to CipherStash (records that have been migrated will have it set, others won't).

Next, we're going to define a UserVault that wraps our newly created CipherStash users collection and helps us manage the relationship between it and the existing Prisma User model.

To do that, we'll need to install stashjs-adapter @cipherstash/stashjs-adapter.

npm install --save @cipherstash/stashjs-adapter

Let's create a file inside the lib directory of our application called user-vault.ts. We'll start by importing the PrismaClient and User model as well as RecordMapper and CollectionAPI from stashjs-adapter (we'll use these in a moment).

lib/user-vault.ts
ts
import { User, PrismaClient } from '@prisma/client'
import {
  RecordMapper,
  CollectionAPI
} from '@cipherstash/stashjs-adapter'

RecordMapper defines an interface that we can use to map CipherStash records to the records in the rest of the system. To do that, we need to define 3 functions.

The first is setStashId. This function defines how stashId's from CipherStash should be set on the existing user records.

ts
const prisma = new PrismaClient()
class UserMapper implements RecordMapper {
  async setStashId(record: {id: number}, stashId: string | null) {
    await prisma.user.update({
      where: { id: record.id },
      data: { stashId: stashId }
    })
  }
}

In our case, this is quite simple. We just use Prisma to update the User record with the given numeric ID by setting the newly created stashId column to the provided stashId value.

Next, we'll define findStashIdsFor which is essentially the reverse of the above (i.e. we find all the stashId values for the existing records with the given numeric IDs). In this case we handle an array of values instead of just one.

ts
class UserMapper implements RecordMapper {
  // -- snip
  async findStashIdsFor(ids: Array<number>): Promise<Array<string>> {
    let result = await prisma.user.findMany({
      where: {
        id: {
          in: ids
        }
      },
      select: { stashId: true}
    })
    return result.flatMap(({ stashId }) => stashId ? [stashId] : [])
  }
}

Once again, we use Prisma to do the work for us.

Finally, we need to define the newIdFor function. This is used to create new numeric IDs to use for newly created records in CipherStash. This probably looks a bit odd but just add it for now and we'll cover it in more detail shortly.

ts
class UserMapper implements RecordMapper {
  // -- snip
  async newIdFor(stashId: string): Promise<number> {
    let dummyUser = await prisma.user.create({
      data: {
        email: "000",
        phone: "000",
        name: "000",
        signedUp: new Date(1970),
        emailVerified: false,
        stashId: stashId
      }
    })
    return dummyUser.id
  }
}

Now we can use our UserMapper and the CollectionAPI to create our super, duper secure encrypted User Vault!

ts
const userMapper = new UserMapper()
export const UserVault = new CollectionAPI<User>("users", userMapper)

This creates an API to our CipherStash users collection and maps it to the existing User model.

The final code (lib/user-vault.ts) looks like this:

lib/user-vault.ts
ts
import { User, PrismaClient } from '@prisma/client'
import {
  RecordMapper,
  CollectionAPI
} from '@cipherstash/stashjs-adapter'
const prisma = new PrismaClient()
class UserMapper implements RecordMapper {
  async setStashId(record: {id: number}, stashId: string | null) {
    await prisma.user.update({
      where: { id: record.id },
      data: { stashId: stashId }
    })
  }
  async findStashIdsFor(ids: Array<number>): Promise<Array<string>> {
    let result = await prisma.user.findMany({
      where: {
        id: {
          in: ids
        }
      },
      select: { stashId: true}
    })
    return result.flatMap(({ stashId }) => stashId ? [stashId] : [])
  }
  async newIdFor(stashId: string): Promise<number> {
    let dummyUser = await prisma.user.create({
      data: {
        email: "000",
        phone: "000",
        name: "000",
        signedUp: new Date(1970),
        emailVerified: false,
        stashId: stashId
      }
    })
    return dummyUser.id
  }
}
const userMapper = new UserMapper()
export const UserVault = new CollectionAPI<User>("users", userMapper)

Import Users into CipherStash

There are two main ways to get data into CipherStash: the stash CLI or in code. Since we are migrating users into CipherStash, we're going to use our shiny new UserVault.

Create a file in your application root called migrate.ts.

migrate.ts
ts
import { PrismaClient } from '@prisma/client'
import { UserVault } from "lib/user-vault"
(async function() {
  const prisma = new PrismaClient()
  let prismaUsers = await prisma.user.findMany()
  prismaUsers.forEach(async (user) => {
    console.log(`Migrating '${user.name}'...`)
    await UserVault.put(user)
  })
  await prisma.$disconnect()
})()

This code loads all of the users in the existing database and for each one, calls the UserVault's put function to insert the user into CipherStash.

As we can see, stashId has now been set in the database.

cs-demo-next=# select id,"stashId" from "User";
 id |               stashId
----+--------------------------------------
  1 | aa4dc961-9aee-4c82-ba50-cdf75475ef19
  2 | 3b14a8d3-b74a-46a0-903d-034099ec7dfe
  3 | c8891e78-2b69-4c11-b97b-29184463cfd1
  4 | 7b5b8585-1f70-49ed-9046-1da76f35f15d

The put function in CipherStash is actually an upsert. That means, subsequent calls to put will update a record with the same ID rather than return an error.

Because CipherStash uses upserts, we can run the migrateUsers function as many times as we want.

Add to Next.js Build

As we're using Next.js we can have this migrate function built as a separate artefact by adding it to the next.config.js file:

ts
const nextConfig = {
  // -- snip
  webpack: (config, { isServer }) => {
    if (isServer) {
      return {
        ...config,
        entry() {
          return config.entry().then((entry) => {
            return Object.assign({}, entry, {
              // Our migrate tool
              migrate: "migrate.ts",
            })
          })
        }
      }
    }
    return config;
  }
}
module.exports = nextConfig

Run the Migration

To perform our migration just call call the migrate.js script:

npm run build
node .next/server/migrate.js

Queries!

Now we have some data in our collection, it's time to run some queries! CollectionAPI wraps Collection#query so we can use it in exactly the same way (the only difference here is that records are returned as the User type).

Let's start by looking up a user by an email address:

ts
// Fetch a user by exact email address
let result = await UserVault.query(user => (
  user.email.eq('ada@example.net')
))

The query method takes a function which allows us to specify the field we want to constrain and the comparison we want to use (in this case equality via the eq function).

Let's use a range query to see all users who signed up in March.

ts
// Sign-ups in March
let result = await User.query(user => (
  user.signedUp.between(
    new Date(2022, 2, 1),
    new Date(2022, 2, 31)
  )
))

We can do a free-text search across both the name and email fields by using our fuzzy index!

ts
// Sign-ups in March
let result = await User.query(user => (
  user.fuzzy.match('Grace Hop')
))

See our reference docs for the full details of our query API.

Update code across the app

Our app has a users API which is used to create, list and search users. Let's update it to use CipherStash.

For now though, we aren't going to move away from our Prisma User model entirely. Instead, we're going to keep both data stores in parallel until we're ready to fully cut-over to CipherStash.

The API currently looks like this:

pages/api/users.ts
ts
import { PrismaClient, User } from "@prisma/client"
import type { NextApiRequest, NextApiResponse } from "next"
const prisma = new PrismaClient()
export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse<any>
) {
  if (req.method == "POST") {
    const user = await createUser(req)
    res.status(201).json(user)
  } else {
    let users = await getUsers(req)
    res.status(200).json(users)
  }
}
async function createUser(req: NextApiRequest): Promise<User> {
  const { email, phone, name } = req.body
  return await prisma.user.create({
    data: {
      email: email,
      phone: phone,
      name: name,
      signedUp: new Date(),
      emailVerified: false
    }
  })
}
async function getUsers(req: NextApiRequest): Promise<Array<User>> {
  if (req.query && req.query.q) {
    let query = req.query.q
    return await prisma.user.findMany({
      where: {
        name: {
          contains: query[0]
        }
      }
    })
  } else {
    return await prisma.user.findMany({})
  } 
}

As you can see, when a POST request is made to the API, a User is created. If the request uses GET, a list of users is returned. Additionally, if a query parameter is passed, then it is used to filter the records (in this case just by the name field).

We're going to make one very small change to the createUser function:

ts
import { UserVault } from "../../lib/user-vault"
async function createUser(req: NextApiRequest): Promise<User> {
  const { email, phone, name } = req.body
  const user = await prisma.user.create({
    data: {
      email: email,
      // -- snip
    }
  })
  // ** Add this line **
  return await UserVault.put(user)
}

With this change, new users are created in our Prisma User and added to the CipherStash collection.

Adding a user via the API:

curl -H "Content-Type: application/json" -v -d \
 '{"email":"foo@bar.com","name":"Hope Cheer","phone":"555-1234"}' \
 http://localhost:3000/api/users

Shows that the newly created user has a stashId!

cs-demo-next=# select id,name,"stashId" from "User";
 id |     name     |               stashId
----+--------------+--------------------------------------
 ...
 15 | Hope Cheer   | 573f8950-de6b-4dd6-ad02-55134c38029a

We can also change the getUsers function to rely soley on UserVault.

ts
async function getUsers(req: NextApiRequest): Promise<Array<User>> {
  if (req.query && req.query.q) {
    return await UserVault.query(user => (
      user.fuzzy.match(req.query.q)
    ))
  } else {
    return await UserVault.list({})
  }
}

And this time because we're using the fuzzy index we created earlier, not only are we getting secure, encrypted search, but we're also able to search across multiple fields in fewer lines of code. Woo!

Final Cut-over

We're ready to cut over to our CipherStash users collection. To do that, we are actually going to keep the users table in our existing database but drop all columns except for id and stashId.

Note: that we could also remove the users table entirely but that would require changing the type of any foreign key relationships to UUID. A totally reasonable approach but more work!

Before we make any changes to the database, let's run the migration one more time to be absolutely sure we have everything in CipherStash:

node .next/server/migrate.js

Now, we can modify the Prisma schema:

ts
model User {
  id             Int     @id @default(autoincrement())
  stashId        String? @db.Uuid
  // Remove all other fields
}

And run the migration:

npx prisma migrate dev
npx prisma generate

The columns containing sensitive data should now be gone!

cs-demo-next=# select * from "User";
 id |               stashId
----+--------------------------------------
  1 | aa4dc961-9aee-4c82-ba50-cdf75475ef19
  2 | 3b14a8d3-b74a-46a0-903d-034099ec7dfe
  3 | c8891e78-2b69-4c11-b97b-29184463cfd1
  4 | 7b5b8585-1f70-49ed-9046-1da76f35f15d
 15 | 573f8950-de6b-4dd6-ad02-55134c38029a

But we're not quite done yet!

The UserVault.put function is great for syncing an existing record into CipherStash, but there is also a UserVault.create function for creating new users directly.

We can use it to replace the createUser function in the users API:

pages/api/users.ts
ts
// -- snip
async function createUser(req: NextApiRequest): Promise<User> {
  const { email, phone, name } = req.body
  return await UserVault.create({
    email,
    phone,
    name,
    signedUp: new Date(),
    emailVerified: false
  })
}

This won't work yet, though. Remember that funny looking newIdFor function in the UserMapper we glossed over earlier? This is where that function comes in!

Right now it looks like this:

lib/user-vault.ts
ts
// -- snip
async newIdFor(stashId: string): Promise<number> {
  let dummyUser = await prisma.user.create({
    data: {
      email: "000",
      phone: "000",
      name: "000",
      signedUp: new Date(1970),
      emailVerified: false,
      stashId
    }
  })
  return dummyUser.id
}

But since we just deleted virtually all of the fields in the User database, we can simplify this function to:

ts
async newIdFor(stashId: string): Promise<number> {
  let user = await prisma.user.create({
    data: { stashId }
  })
  return user.id
}

The CollectionAPI uses newIdFor internally to map CipherStash IDs to numerical IDs that are compatible with the rest of the system. In our case, we are doing that by creating an entry in the users table - it has become a sort of join table!

Also, we can't use the Prisma User type anymore (its missing most of the fields) so we will define our own:

lib/user-vault.ts
ts
export type User = {
  id: number
  email: string
  phone: string
  name: string
  signedUp: Date
  emailVerified: boolean
  // no longer optional!
  stashId: string
}

And use it instead of the Prisma one.

pages/api/users.ts
ts
import { User, UserVault } from "../../lib/user-vault"

You can also now remove the migrate.ts file and the entry in next.config.js.

Congratulations!

You have now fully migrated your users to a CipherStash collection.

If you want to have a tinker and see how the examples in this article work, we've made the full codebase available on Github.

As you can see, getting going with CipherStash is pretty straightforward. With just a little bit of work you can get a dramatic improvement in security while retaining the kind of query functionality you're used to.

...and just in case you haven't already...

Try CipherStash for free!