How to Implement Data Encryption in Retool

1// For self-hosted Retool (docker-compose.yml):
2// ENCRYPTION_KEY=your-random-32-byte-hex-string
3// Generate with: openssl rand -hex 32
4
5// Environment variable in Retool UI:
6// Settings → Configuration Variables → Add
7// Name: ENCRYPTION_KEY (mark as secret)
8// Value: your-key-here
9
10// In a resource query, Retool automatically decrypts connection
11// credentials when making database connections — you don't see the key.

1// Add in Settings → Configuration Variables:
2// Name: APP_ENCRYPTION_KEY (Secret: YES)
3// Value: generate with: crypto.getRandomValues(new Uint8Array(32))
4//        then convert to base64
5
6// Generate a key in browser console for initial setup:
7const key = await window.crypto.subtle.generateKey(
8  { name: 'AES-GCM', length: 256 },
9  true, // extractable
10  ['encrypt', 'decrypt']
11);
12const exported = await window.crypto.subtle.exportKey('raw', key);
13const base64Key = btoa(String.fromCharCode(...new Uint8Array(exported)));
14console.log('Encryption key (store in config var):', base64Key);
15
16// A key retrieved from a server-side query:
17// SELECT decrypted_key FROM encryption_keys WHERE active = true
18// (key stored server-side, not client-accessible)

1// JS Query: encryptField
2// Input: encryptInput.value (the plaintext to encrypt)
3// Requires: getEncryptionKey query that returns the base64 key
4
5async function encryptData(plaintext, base64Key) {
6  // Decode the base64 key
7  const keyBytes = Uint8Array.from(atob(base64Key), c => c.charCodeAt(0));
8
9  // Import the key for use with Web Crypto
10  const cryptoKey = await window.crypto.subtle.importKey(
11    'raw',
12    keyBytes,
13    { name: 'AES-GCM' },
14    false,
15    ['encrypt']
16  );
17
18  // Generate a random 12-byte IV (required for AES-GCM)
19  const iv = window.crypto.getRandomValues(new Uint8Array(12));
20
21  // Encode plaintext
22  const encodedText = new TextEncoder().encode(plaintext);
23
24  // Encrypt
25  const ciphertext = await window.crypto.subtle.encrypt(
26    { name: 'AES-GCM', iv },
27    cryptoKey,
28    encodedText
29  );
30
31  // Combine IV + ciphertext and encode as base64 for storage
32  const combined = new Uint8Array(iv.length + ciphertext.byteLength);
33  combined.set(iv, 0);
34  combined.set(new Uint8Array(ciphertext), iv.length);
35
36  return btoa(String.fromCharCode(...combined));
37}
38
39// Get the encryption key from a server-side query
40const keyResult = await getEncryptionKey.trigger();
41const base64Key = keyResult.data[0].key_value;
42
43// Encrypt the input value
44const encrypted = await encryptData(encryptInput.value, base64Key);
45return { encrypted };

1// JS Query: decryptField
2// Input: the encrypted base64 string from database
3// Usage: await decryptField.trigger({ additionalScope: { encryptedValue: row.ssn } })
4
5async function decryptData(base64Combined, base64Key) {
6  // Decode the key
7  const keyBytes = Uint8Array.from(atob(base64Key), c => c.charCodeAt(0));
8
9  const cryptoKey = await window.crypto.subtle.importKey(
10    'raw',
11    keyBytes,
12    { name: 'AES-GCM' },
13    false,
14    ['decrypt']
15  );
16
17  // Decode the combined IV + ciphertext
18  const combined = Uint8Array.from(atob(base64Combined), c => c.charCodeAt(0));
19
20  // Extract IV (first 12 bytes) and ciphertext (rest)
21  const iv = combined.slice(0, 12);
22  const ciphertext = combined.slice(12);
23
24  // Decrypt
25  const decrypted = await window.crypto.subtle.decrypt(
26    { name: 'AES-GCM', iv },
27    cryptoKey,
28    ciphertext
29  );
30
31  return new TextDecoder().decode(decrypted);
32}
33
34// Get key (server-side)
35const keyResult = await getEncryptionKey.trigger();
36const base64Key = keyResult.data[0].key_value;
37
38// encryptedValue is passed via additionalScope or from a component
39const plaintext = await decryptData(encryptedValue, base64Key);
40return { plaintext };

1// JS Query: saveUserWithEncryption
2// Triggered by 'Save' button
3
4// Step 1: Encrypt the SSN
5const encryptResult = await encryptField.trigger({
6  additionalScope: { plaintext: ssnInput.value }
7});
8const encryptedSSN = encryptResult.encrypted;
9
10// Step 2: Run the INSERT with encrypted SSN
11await saveUserQuery.trigger({
12  additionalScope: {
13    firstName: firstNameInput.value,
14    lastName: lastNameInput.value,
15    email: emailInput.value,
16    encryptedSSN: encryptedSSN  // stored as encrypted base64
17  }
18});
19
20// Step 3: Show success notification
21utils.showNotification({
22  title: 'User saved',
23  description: 'Sensitive data encrypted and stored securely.',
24  notificationType: 'success',
25  duration: 3
26});
27
28// SQL Query: saveUserQuery
29// INSERT INTO users (first_name, last_name, email, ssn_encrypted)
30// VALUES ({{ firstName }}, {{ lastName }}, {{ email }}, {{ encryptedSSN }})

1// JS Query: rotateEncryptionKeys (admin operation only)
2// Run manually when rotating keys
3
4const records = await getEncryptedRecords.trigger();
5const oldKey = retoolContext.configVars.OLD_ENCRYPTION_KEY; // before rotation
6const newKey = retoolContext.configVars.APP_ENCRYPTION_KEY; // new key
7
8const batchSize = 100;
9let updated = 0;
10
11for (let i = 0; i < records.data.length; i += batchSize) {
12  const batch = records.data.slice(i, i + batchSize);
13
14  for (const record of batch) {
15    // Decrypt with old key
16    const plaintext = await decryptWithKey(record.ssn_encrypted, oldKey);
17    // Re-encrypt with new key
18    const newEncrypted = await encryptWithKey(plaintext, newKey);
19    // Update record
20    await updateEncryptedField.trigger({
21      additionalScope: { id: record.id, newEncrypted }
22    });
23    updated++;
24  }
25}
26
27return { message: `Rotated ${updated} records` };