Integration Guide: Database Field Encryption
This guide demonstrates how to encrypt sensitive database fields using the Grizzly SDK. You'll learn patterns for transparent field-level encryption, working with various database systems, and maintaining query performance.
Overview
Database field encryption with Grizzly provides:
- Transparent encryption - Encrypt/decrypt automatically in your application layer
- Selective field protection - Encrypt only sensitive columns (PII, PHI, financial data)
- Database-agnostic - Works with any database (SQL, NoSQL, key-value stores)
- Audit trail - Track all encryption/decryption operations in Activity feed
- Compliance support - Meet GDPR, HIPAA, PCI-DSS requirements
Basic Field Encryption Pattern
Simple Encrypt-Before-Save Pattern
import { KeyClient } from '@grizzlycbg/grizzly-sdk'
const client = await KeyClient.create({
host: 'https://your-tenant.grizzlycbg.io',
apikey: process.env.GRIZZLY_API_KEY
})
// Example: User model with sensitive fields
class User {
constructor(data) {
this.id = data.id
this.email = data.email
this.name = data.name
// Sensitive fields (encrypted)
this.ssn = data.ssn
this.creditCard = data.creditCard
this.dateOfBirth = data.dateOfBirth
}
async encryptSensitiveFields(keyringName) {
const ring = await client.ring.get(keyringName)
if (this.ssn) {
this.ssn = await ring.encrypt(this.ssn, {
asset: {
id: `user-${this.id}`,
name: `User ${this.id} - SSN`, // Display in Dashboard
type: 'pii',
field: 'ssn',
userId: this.id
}
})
}
if (this.creditCard) {
this.creditCard = await ring.encrypt(this.creditCard, {
asset: {
id: `user-${this.id}`,
name: `User ${this.id} - Credit Card`,
type: 'payment',
field: 'creditCard',
userId: this.id
}
})
}
if (this.dateOfBirth) {
this.dateOfBirth = await ring.encrypt(this.dateOfBirth, {
asset: {
id: `user-${this.id}`,
name: `User ${this.id} - DOB`,
type: 'pii',
field: 'dateOfBirth',
userId: this.id
}
})
}
return this
}
async decryptSensitiveFields() {
if (this.ssn) {
this.ssn = await client.decrypt(this.ssn)
}
if (this.creditCard) {
this.creditCard = await client.decrypt(this.creditCard)
}
if (this.dateOfBirth) {
this.dateOfBirth = await client.decrypt(this.dateOfBirth)
}
return this
}
}
// Usage
const user = new User({
id: '12345',
email: 'john.doe@example.com',
name: 'John Doe',
ssn: '123-45-6789',
creditCard: '4111-1111-1111-1111',
dateOfBirth: '1990-01-15'
})
// Encrypt before saving
await user.encryptSensitiveFields('UserData')
// Save to database (fields are now encrypted)
await db.users.insert(user)
// Decrypt after loading
const loadedUser = await db.users.findById('12345')
await loadedUser.decryptSensitiveFields()
console.log(loadedUser.ssn) // '123-45-6789' (decrypted)
PostgreSQL with Prisma
Transparent Field Encryption with Prisma
Schema:
// schema.prisma
model User {
id String @id @default(uuid())
email String @unique
name String
// Encrypted fields stored as TEXT
ssn String?
creditCard String?
medicalRecord String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Encryption Service:
import { PrismaClient } from '@prisma/client'
import { KeyClient } from '@grizzlycbg/grizzly-sdk'
class SecureUserService {
constructor(grizzlyClient, keyringName = 'UserPII') {
this.prisma = new PrismaClient()
this.client = grizzlyClient
this.keyringName = keyringName
}
async createUser(userData) {
const ring = await this.client.ring.get(this.keyringName)
// Encrypt sensitive fields before saving
const encryptedData = {
email: userData.email,
name: userData.name,
ssn: userData.ssn ? await ring.encrypt(userData.ssn, {
asset: {
id: `user-${userData.email}`,
name: `${userData.name} - SSN`,
type: 'pii',
field: 'ssn',
email: userData.email
}
}) : null,
creditCard: userData.creditCard ? await ring.encrypt(userData.creditCard, {
asset: {
id: `user-${userData.email}`,
name: `${userData.name} - Credit Card`,
type: 'payment',
field: 'creditCard',
email: userData.email
}
}) : null,
medicalRecord: userData.medicalRecord ? await ring.encrypt(userData.medicalRecord, {
asset: {
id: `user-${userData.email}`,
name: `${userData.name} - Medical Record`,
type: 'phi',
field: 'medicalRecord',
email: userData.email,
hipaaProtected: true
}
}) : null
}
const user = await this.prisma.user.create({
data: encryptedData
})
console.log(`User created: ${user.id}`)
return user
}
async getUser(userId, options = {}) {
const user = await this.prisma.user.findUnique({
where: { id: userId }
})
if (!user) {
return null
}
// Optionally decrypt fields
if (options.decrypt) {
return await this.decryptUser(user, options.fields)
}
return user
}
async decryptUser(user, fields = ['ssn', 'creditCard', 'medicalRecord']) {
const decrypted = { ...user }
for (const field of fields) {
if (decrypted[field]) {
try {
decrypted[field] = await this.client.decrypt(decrypted[field])
} catch (error) {
console.error(`Failed to decrypt ${field}:`, error.message)
decrypted[field] = null
}
}
}
return decrypted
}
async updateUser(userId, updates) {
const ring = await this.client.ring.get(this.keyringName)
// Encrypt any updated sensitive fields
const encryptedUpdates = {}
if (updates.email !== undefined) {
encryptedUpdates.email = updates.email
}
if (updates.name !== undefined) {
encryptedUpdates.name = updates.name
}
if (updates.ssn !== undefined) {
encryptedUpdates.ssn = updates.ssn ? await ring.encrypt(updates.ssn, {
asset: {
id: `user-${userId}`,
name: `User ${userId} - SSN (Updated)`,
type: 'pii',
field: 'ssn',
userId
}
}) : null
}
if (updates.creditCard !== undefined) {
encryptedUpdates.creditCard = updates.creditCard ? await ring.encrypt(updates.creditCard, {
asset: {
id: `user-${userId}`,
name: `User ${userId} - Credit Card (Updated)`,
type: 'payment',
field: 'creditCard',
userId
}
}) : null
}
const user = await this.prisma.user.update({
where: { id: userId },
data: encryptedUpdates
})
return user
}
async deleteUser(userId) {
// Decrypt fields for audit/logging before deletion if needed
const user = await this.getUser(userId, { decrypt: false })
await this.prisma.user.delete({
where: { id: userId }
})
console.log(`User deleted: ${userId}`)
}
}
// Usage
const grizzlyClient = await KeyClient.create({
host: 'https://your-tenant.grizzlycbg.io',
apikey: process.env.GRIZZLY_API_KEY
})
const userService = new SecureUserService(grizzlyClient, 'UserPII')
// Create user with encrypted fields
const user = await userService.createUser({
email: 'jane@example.com',
name: 'Jane Smith',
ssn: '987-65-4321',
creditCard: '5555-5555-5555-4444',
medicalRecord: 'Patient has history of...'
})
// Get user with encrypted fields (default)
const encryptedUser = await userService.getUser(user.id)
console.log(encryptedUser.ssn) // Encrypted string
// Get user with decrypted fields
const decryptedUser = await userService.getUser(user.id, {
decrypt: true,
fields: ['ssn', 'medicalRecord'] // Selective decryption
})
console.log(decryptedUser.ssn) // '987-65-4321' (decrypted)
// Update encrypted fields
await userService.updateUser(user.id, {
creditCard: '6011-0000-0000-0004'
})
MongoDB Integration
Field Encryption with MongoDB
import { MongoClient } from 'mongodb'
import { KeyClient } from '@grizzlycbg/grizzly-sdk'
class SecureMongoService {
constructor(grizzlyClient, mongoUri, dbName) {
this.client = grizzlyClient
this.mongoClient = new MongoClient(mongoUri)
this.dbName = dbName
this.keyringName = 'MongoDB-PII'
}
async connect() {
await this.mongoClient.connect()
this.db = this.mongoClient.db(this.dbName)
console.log('Connected to MongoDB')
}
async insertPatient(patientData) {
const ring = await this.client.ring.get(this.keyringName)
// Encrypt PHI (Protected Health Information)
const encryptedPatient = {
patientId: patientData.patientId,
firstName: patientData.firstName,
lastName: patientData.lastName,
// Encrypted fields
ssn: await ring.encrypt(patientData.ssn, {
asset: {
id: `patient-${patientData.patientId}`,
name: `${patientData.firstName} ${patientData.lastName} - SSN`,
type: 'phi',
field: 'ssn',
patientId: patientData.patientId,
hipaa: true
}
}),
diagnosis: await ring.encrypt(JSON.stringify(patientData.diagnosis), {
asset: {
id: `patient-${patientData.patientId}`,
name: `${patientData.firstName} ${patientData.lastName} - Diagnosis`,
type: 'phi',
field: 'diagnosis',
patientId: patientData.patientId,
hipaa: true
}
}),
medications: await ring.encrypt(JSON.stringify(patientData.medications), {
asset: {
id: `patient-${patientData.patientId}`,
name: `${patientData.firstName} ${patientData.lastName} - Medications`,
type: 'phi',
field: 'medications',
patientId: patientData.patientId,
hipaa: true
}
}),
// Non-encrypted metadata
department: patientData.department,
admissionDate: new Date(),
lastUpdated: new Date()
}
const result = await this.db.collection('patients').insertOne(encryptedPatient)
console.log(`Patient inserted: ${result.insertedId}`)
return result
}
async findPatient(patientId, decrypt = true) {
const patient = await this.db.collection('patients').findOne({ patientId })
if (!patient) {
return null
}
if (decrypt) {
// Decrypt PHI fields
patient.ssn = await this.client.decrypt(patient.ssn)
patient.diagnosis = JSON.parse(await this.client.decrypt(patient.diagnosis))
patient.medications = JSON.parse(await this.client.decrypt(patient.medications))
}
return patient
}
async updatePatient(patientId, updates) {
const ring = await this.client.ring.get(this.keyringName)
const encryptedUpdates = {
lastUpdated: new Date()
}
// Encrypt any updated PHI fields
if (updates.ssn) {
encryptedUpdates.ssn = await ring.encrypt(updates.ssn, {
asset: {
id: `patient-${patientId}`,
name: `Patient ${patientId} - SSN (Updated)`,
type: 'phi',
field: 'ssn',
patientId,
hipaa: true
}
})
}
if (updates.diagnosis) {
encryptedUpdates.diagnosis = await ring.encrypt(JSON.stringify(updates.diagnosis), {
asset: {
id: `patient-${patientId}`,
name: `Patient ${patientId} - Diagnosis (Updated)`,
type: 'phi',
field: 'diagnosis',
patientId,
hipaa: true
}
})
}
if (updates.medications) {
encryptedUpdates.medications = await ring.encrypt(JSON.stringify(updates.medications), {
asset: {
id: `patient-${patientId}`,
name: `Patient ${patientId} - Medications (Updated)`,
type: 'phi',
field: 'medications',
patientId,
hipaa: true
}
})
}
const result = await this.db.collection('patients').updateOne(
{ patientId },
{ $set: encryptedUpdates }
)
return result
}
async close() {
await this.mongoClient.close()
}
}
// Usage
const grizzlyClient = await KeyClient.create({
host: 'https://your-tenant.grizzlycbg.io',
apikey: process.env.GRIZZLY_API_KEY
})
const mongoService = new SecureMongoService(
grizzlyClient,
'mongodb://localhost:27017',
'healthcare'
)
await mongoService.connect()
// Insert patient with encrypted PHI
await mongoService.insertPatient({
patientId: 'P12345',
firstName: 'Alice',
lastName: 'Johnson',
ssn: '111-22-3333',
diagnosis: ['Type 2 Diabetes', 'Hypertension'],
medications: ['Metformin 500mg', 'Lisinopril 10mg'],
department: 'Cardiology'
})
// Retrieve with decryption
const patient = await mongoService.findPatient('P12345', true)
console.log(patient.diagnosis) // ['Type 2 Diabetes', 'Hypertension']
// Retrieve without decryption (for display/audit)
const encryptedPatient = await mongoService.findPatient('P12345', false)
console.log(encryptedPatient.diagnosis) // Encrypted string
await mongoService.close()
Field Encryption Helper Class
Reusable Encryption Helper
class FieldEncryptor {
constructor(grizzlyClient, keyringName) {
this.client = grizzlyClient
this.keyringName = keyringName
}
async encryptFields(record, fieldConfig, recordId) {
const ring = await this.client.ring.get(this.keyringName)
const encrypted = { ...record }
for (const [field, config] of Object.entries(fieldConfig)) {
const value = record[field]
if (value === undefined || value === null) {
continue
}
// Convert to string if needed
const stringValue = typeof value === 'string' ? value : JSON.stringify(value)
encrypted[field] = await ring.encrypt(stringValue, {
asset: {
id: recordId,
name: config.assetName || `${recordId} - ${field}`,
type: config.type || 'sensitive',
field,
...config.metadata
}
})
}
return encrypted
}
async decryptFields(record, fields) {
const decrypted = { ...record }
for (const field of fields) {
const value = record[field]
if (!value) {
continue
}
try {
const decryptedValue = await this.client.decrypt(value)
// Try to parse as JSON if it was originally an object
try {
decrypted[field] = JSON.parse(decryptedValue)
} catch {
decrypted[field] = decryptedValue
}
} catch (error) {
console.error(`Failed to decrypt field ${field}:`, error.message)
decrypted[field] = null
}
}
return decrypted
}
async batchEncrypt(records, fieldConfig, getRecordId) {
return Promise.all(
records.map(async (record) => {
const recordId = getRecordId(record)
return await this.encryptFields(record, fieldConfig, recordId)
})
)
}
async batchDecrypt(records, fields) {
return Promise.all(
records.map((record) => this.decryptFields(record, fields))
)
}
}
// Usage
const encryptor = new FieldEncryptor(grizzlyClient, 'UserData')
// Define which fields to encrypt and their config
const fieldConfig = {
ssn: {
type: 'pii',
assetName: 'User SSN',
metadata: { classification: 'highly-sensitive' }
},
creditCard: {
type: 'payment',
assetName: 'User Payment Info',
metadata: { pciCompliant: true }
},
medicalHistory: {
type: 'phi',
assetName: 'User Medical History',
metadata: { hipaa: true }
}
}
// Encrypt single record
const user = {
id: 'user-123',
name: 'John Doe',
ssn: '123-45-6789',
creditCard: '4111-1111-1111-1111',
medicalHistory: { conditions: ['diabetes'], allergies: ['penicillin'] }
}
const encrypted = await encryptor.encryptFields(user, fieldConfig, user.id)
// Save to database
await db.users.insert(encrypted)
// Decrypt when needed
const loaded = await db.users.findById('user-123')
const decrypted = await encryptor.decryptFields(loaded, ['ssn', 'creditCard', 'medicalHistory'])
console.log(decrypted.medicalHistory) // { conditions: ['diabetes'], allergies: ['penicillin'] }
// Batch operations
const users = [
{ id: '1', name: 'Alice', ssn: '111-11-1111', creditCard: '4111...' },
{ id: '2', name: 'Bob', ssn: '222-22-2222', creditCard: '5555...' }
]
const encryptedUsers = await encryptor.batchEncrypt(
users,
fieldConfig,
(user) => user.id
)
await db.users.insertMany(encryptedUsers)
Query Considerations
Working with Encrypted Fields
Important Limitations:
Encrypted fields cannot be directly queried or indexed. Here are strategies to work with this:
1. Use Hash Indexes for Lookups
import crypto from 'crypto'
class SearchableEncryptedField {
constructor(grizzlyClient, keyringName) {
this.client = grizzlyClient
this.keyringName = keyringName
}
// Create searchable hash of value
createSearchHash(value) {
return crypto
.createHash('sha256')
.update(value)
.digest('hex')
}
async encryptWithHash(value, recordId, fieldName) {
const ring = await this.client.ring.get(this.keyringName)
const encrypted = await ring.encrypt(value, {
asset: {
id: recordId,
name: `${recordId} - ${fieldName}`,
type: 'pii',
field: fieldName
}
})
const searchHash = this.createSearchHash(value)
return {
encrypted,
searchHash // Store this in a separate indexed column
}
}
async findByValue(db, collection, fieldName, searchValue) {
const searchHash = this.createSearchHash(searchValue)
// Query by hash (fast, indexed)
const records = await db.collection(collection).find({
[`${fieldName}_hash`]: searchHash
}).toArray()
// Decrypt and verify (in case of hash collision)
const decrypted = await Promise.all(
records.map(async (record) => {
const decryptedValue = await this.client.decrypt(record[fieldName])
return {
...record,
[fieldName]: decryptedValue,
_matches: decryptedValue === searchValue
}
})
)
return decrypted.filter(r => r._matches)
}
}
// Schema example
const userSchema = {
id: 'string',
name: 'string',
email: 'string',
ssn: 'string', // Encrypted value
ssn_hash: 'string', // SHA-256 hash for lookups (indexed)
created_at: 'timestamp'
}
// Usage
const searchable = new SearchableEncryptedField(grizzlyClient, 'UserData')
// Insert with searchable hash
const { encrypted, searchHash } = await searchable.encryptWithHash(
'123-45-6789',
'user-123',
'ssn'
)
await db.users.insert({
id: 'user-123',
name: 'John Doe',
ssn: encrypted,
ssn_hash: searchHash // Index this field for fast lookups
})
// Find by SSN (using hash)
const users = await searchable.findByValue(db, 'users', 'ssn', '123-45-6789')
2. Separate Metadata Table
// Keep encrypted data separate from searchable metadata
// users table (searchable, no PII)
{
id: 'user-123',
email: 'john@example.com',
name: 'John Doe',
created_at: '2024-01-15'
}
// user_encrypted_data table (encrypted PII)
{
user_id: 'user-123',
ssn_encrypted: '...',
credit_card_encrypted: '...',
medical_history_encrypted: '...'
}
// Query users normally, decrypt PII only when needed
const users = await db.users.find({ email: 'john@example.com' })
const encryptedData = await db.user_encrypted_data.findOne({ user_id: users[0].id })
const decrypted = await encryptor.decryptFields(encryptedData, ['ssn_encrypted'])
3. Client-Side Filtering
// For small datasets, decrypt and filter client-side
async function findUsersByAge(minAge, maxAge) {
// Load all users
const users = await db.users.find({})
// Decrypt date of birth
const decrypted = await Promise.all(
users.map(async (user) => ({
...user,
dateOfBirth: await client.decrypt(user.dateOfBirth)
}))
)
// Calculate age and filter
const now = new Date()
return decrypted.filter((user) => {
const dob = new Date(user.dateOfBirth)
const age = now.getFullYear() - dob.getFullYear()
return age >= minAge && age <= maxAge
})
}
Performance Optimization
Caching and Batch Processing
class OptimizedFieldEncryptor {
constructor(grizzlyClient, keyringName, options = {}) {
this.client = grizzlyClient
this.keyringName = keyringName
this.batchSize = options.batchSize || 100
this.decryptionCache = new Map()
this.cacheTimeout = options.cacheTimeout || 60000 // 1 minute
}
async encryptBatch(records, fieldConfig) {
const ring = await this.client.ring.get(this.keyringName)
const results = []
// Process in batches to avoid overwhelming the system
for (let i = 0; i < records.length; i += this.batchSize) {
const batch = records.slice(i, i + this.batchSize)
const batchResults = await Promise.all(
batch.map(async (record) => {
const encrypted = { ...record }
for (const [field, config] of Object.entries(fieldConfig)) {
if (record[field]) {
encrypted[field] = await ring.encrypt(
typeof record[field] === 'string'
? record[field]
: JSON.stringify(record[field]),
{
asset: {
id: record.id || record._id,
name: `${record.id || record._id} - ${field}`,
type: config.type,
field
}
}
)
}
}
return encrypted
})
)
results.push(...batchResults)
console.log(`Processed batch ${Math.floor(i / this.batchSize) + 1}`)
}
return results
}
async decryptWithCache(encryptedValue, cacheKey) {
// Check cache
const cached = this.decryptionCache.get(cacheKey)
if (cached && Date.now() - cached.timestamp < this.cacheTimeout) {
return cached.value
}
// Decrypt
const decrypted = await this.client.decrypt(encryptedValue)
// Cache result
this.decryptionCache.set(cacheKey, {
value: decrypted,
timestamp: Date.now()
})
return decrypted
}
clearCache() {
this.decryptionCache.clear()
}
}
// Usage
const optimizer = new OptimizedFieldEncryptor(grizzlyClient, 'UserData', {
batchSize: 50,
cacheTimeout: 300000 // 5 minutes
})
// Encrypt large dataset
const users = await db.users.find({}).limit(10000).toArray()
const encryptedUsers = await optimizer.encryptBatch(users, {
ssn: { type: 'pii' },
creditCard: { type: 'payment' }
})
// Decrypt with caching (useful for repeated access)
const user = await db.users.findOne({ id: 'user-123' })
const ssn = await optimizer.decryptWithCache(user.ssn, `user-123-ssn`)
Error Handling
Graceful Degradation
class ResilientFieldEncryptor {
constructor(grizzlyClient, keyringName) {
this.client = grizzlyClient
this.keyringName = keyringName
}
async safeEncrypt(value, recordId, fieldName, options = {}) {
try {
const ring = await this.client.ring.get(this.keyringName)
return await ring.encrypt(value, {
asset: {
id: recordId,
name: `${recordId} - ${fieldName}`,
type: options.type || 'sensitive',
field: fieldName
}
})
} catch (error) {
console.error(`Encryption failed for ${fieldName}:`, error.message)
if (options.fallbackToPlaintext) {
console.warn(`Storing ${fieldName} as plaintext (fallback mode)`)
return value
}
if (options.defaultValue) {
return options.defaultValue
}
throw error
}
}
async safeDecrypt(encryptedValue, options = {}) {
try {
return await this.client.decrypt(encryptedValue)
} catch (error) {
console.error('Decryption failed:', error.message)
if (options.returnEncrypted) {
console.warn('Returning encrypted value due to decryption failure')
return encryptedValue
}
if (options.defaultValue !== undefined) {
return options.defaultValue
}
return null
}
}
async encryptWithRetry(value, recordId, fieldName, maxRetries = 3) {
for (let attempt = 1; attempt <= maxRetries; attempt++) {
try {
return await this.safeEncrypt(value, recordId, fieldName)
} catch (error) {
if (attempt === maxRetries) {
throw error
}
const delay = Math.pow(2, attempt - 1) * 1000
console.log(`Retry ${attempt}/${maxRetries} in ${delay}ms...`)
await new Promise(resolve => setTimeout(resolve, delay))
}
}
}
}
// Usage
const resilient = new ResilientFieldEncryptor(grizzlyClient, 'UserData')
// Encrypt with fallback
const encrypted = await resilient.safeEncrypt(
'sensitive-data',
'record-123',
'ssn',
{ fallbackToPlaintext: false, defaultValue: null }
)
// Decrypt with graceful degradation
const decrypted = await resilient.safeDecrypt(encrypted, {
returnEncrypted: false,
defaultValue: '[REDACTED]'
})
// Retry on failure
const encryptedWithRetry = await resilient.encryptWithRetry(
'important-data',
'record-456',
'creditCard'
)
Next Steps
- File Encryption - Encrypt files and documents
- Third-Party Integration - Integrate with external services
- Security Best Practices - Production security guidelines
- API Reference - Complete SDK documentation