How to Secure Drizzle ORM

January 2026How-To Guides
Share
How-To Guide

How to Secure Drizzle ORM

Type-safe database queries with security best practices

TL;DR

TL;DR (20 minutes): Drizzle's query builder is SQL-injection safe. For raw SQL, always use the sql template tag. Validate inputs with Zod, implement access control by adding user filters to queries, and use Drizzle's select() to control which fields are returned.

Prerequisites:

  • Drizzle ORM installed in your project
  • Basic understanding of TypeScript
  • PostgreSQL, MySQL, or SQLite database

Why This Matters

Drizzle is designed to be type-safe and SQL-injection resistant, but security still requires proper implementation. This guide covers how to use Drizzle's features safely and avoid common pitfalls.

Step-by-Step Guide

1

Understand Drizzle's built-in safety

Drizzle's query builder automatically parameterizes values:

import { eq, and, like, sql } from 'drizzle-orm';
import { db } from './db';
import { users, posts } from './schema';

// SAFE - Values are automatically parameterized
const user = await db.select()
  .from(users)
  .where(eq(users.email, userInput));

// SAFE - Multiple conditions
const results = await db.select()
  .from(posts)
  .where(and(
    eq(posts.userId, userId),
    like(posts.title, `%${searchTerm}%`)
  ));

// SAFE - Joins and complex queries
const userPosts = await db.select({
    postId: posts.id,
    title: posts.title,
    userName: users.name
  })
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .where(eq(posts.status, 'published'));
2

Use the sql template tag for raw queries

import { sql } from 'drizzle-orm';

// DANGEROUS - String interpolation in raw SQL
const bad = await db.execute(
  `SELECT * FROM users WHERE email = '${userInput}'`  // SQL INJECTION!
);

// SAFE - Using sql template tag
const good = await db.execute(
  sql`SELECT * FROM users WHERE email = ${userInput}`
);

// SAFE - Building dynamic queries with sql
const searchUsers = async (searchTerm: string, sortBy: string) => {
  // Validate sortBy against allowlist
  const allowedSorts = ['created_at', 'name', 'email'] as const;
  if (!allowedSorts.includes(sortBy as any)) {
    throw new Error('Invalid sort column');
  }

  // sql.raw() for trusted values only (like validated column names)
  return db.execute(sql`
    SELECT id, name, email
    FROM users
    WHERE name ILIKE ${`%${searchTerm}%`}
    ORDER BY ${sql.raw(sortBy)} DESC
    LIMIT 100
  `);
};

// SAFE - Conditional query building
const buildQuery = (filters: { email?: string; status?: string }) => {
  let query = sql`SELECT * FROM users WHERE 1=1`;

  if (filters.email) {
    query = sql`${query} AND email = ${filters.email}`;
  }
  if (filters.status) {
    query = sql`${query} AND status = ${filters.status}`;
  }

  return db.execute(query);
};
3

Validate inputs with Zod

import { z } from 'zod';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';
import { users } from './schema';

// Generate Zod schema from Drizzle schema
const insertUserSchema = createInsertSchema(users, {
  email: z.string().email(),
  name: z.string().min(1).max(100),
  // Override or add custom validations
});

const selectUserSchema = createSelectSchema(users);

// Use in your API handlers
async function createUser(input: unknown) {
  // Validate input
  const data = insertUserSchema.parse(input);

  // Safe to use with Drizzle
  return db.insert(users).values(data).returning();
}

// Custom schemas for specific operations
const updateUserSchema = z.object({
  name: z.string().min(1).max(100).optional(),
  bio: z.string().max(500).optional(),
}).refine(data => Object.keys(data).length > 0, {
  message: 'At least one field must be provided'
});

async function updateUser(id: string, input: unknown) {
  const userId = z.string().uuid().parse(id);
  const data = updateUserSchema.parse(input);

  return db.update(users)
    .set(data)
    .where(eq(users.id, userId))
    .returning();
}
4

Implement access control

import { and, eq } from 'drizzle-orm';

// Create a wrapper that enforces user access
function createUserScopedDb(userId: string) {
  return {
    // Orders - user can only see their own
    orders: {
      findMany: () =>
        db.select()
          .from(orders)
          .where(eq(orders.userId, userId)),

      findOne: (orderId: string) =>
        db.select()
          .from(orders)
          .where(and(
            eq(orders.id, orderId),
            eq(orders.userId, userId)  // Always include user filter
          ))
          .limit(1),

      create: (data: NewOrder) =>
        db.insert(orders)
          .values({ ...data, userId })  // Always set userId
          .returning(),

      update: (orderId: string, data: Partial) =>
        db.update(orders)
          .set(data)
          .where(and(
            eq(orders.id, orderId),
            eq(orders.userId, userId)  // User can only update their own
          ))
          .returning()
    }
  };
}

// Usage in your API
async function getOrders(req: Request) {
  const userId = req.user.id;
  const userDb = createUserScopedDb(userId);
  return userDb.orders.findMany();
}

// Admin bypass for privileged operations
function createAdminDb() {
  return {
    orders: {
      findMany: (filters?: { userId?: string; status?: string }) =>
        db.select()
          .from(orders)
          .where(filters?.userId ? eq(orders.userId, filters.userId) : undefined)
    }
  };
}
5

Control field exposure

import { users } from './schema';

// Define what fields to expose publicly
const publicUserFields = {
  id: users.id,
  name: users.name,
  avatar: users.avatar,
  createdAt: users.createdAt
};

// Exclude sensitive fields
async function getPublicProfile(userId: string) {
  const result = await db
    .select(publicUserFields)  // Only select public fields
    .from(users)
    .where(eq(users.id, userId))
    .limit(1);

  return result[0];
}

// For the user viewing their own profile
const privateUserFields = {
  ...publicUserFields,
  email: users.email,
  settings: users.settings
};

async function getMyProfile(userId: string) {
  const result = await db
    .select(privateUserFields)
    .from(users)
    .where(eq(users.id, userId))
    .limit(1);

  return result[0];
}

// Never expose these fields
// - passwordHash
// - resetToken
// - verificationToken
// - internalNotes
6

Secure connection management

// db.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

// Validate environment variables
if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL is required');
}

// Create connection pool with security settings
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: true }
    : false,
  max: 10,  // Limit connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000
});

export const db = drizzle(pool, { schema });

// For serverless, use connection pooling
// Neon example
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });
7

Implement query limits and pagination

// Always limit query results
const MAX_LIMIT = 100;
const DEFAULT_LIMIT = 20;

interface PaginationParams {
  page?: number;
  limit?: number;
}

function getPagination(params: PaginationParams) {
  const page = Math.max(1, params.page || 1);
  const limit = Math.min(MAX_LIMIT, Math.max(1, params.limit || DEFAULT_LIMIT));
  const offset = (page - 1) * limit;

  return { limit, offset };
}

async function getUsers(params: PaginationParams) {
  const { limit, offset } = getPagination(params);

  const results = await db.select()
    .from(users)
    .limit(limit)
    .offset(offset)
    .orderBy(users.createdAt);

  // Get total count for pagination
  const [{ count }] = await db
    .select({ count: sql`count(*)` })
    .from(users);

  return {
    data: results,
    pagination: {
      page: Math.floor(offset / limit) + 1,
      limit,
      total: Number(count),
      pages: Math.ceil(Number(count) / limit)
    }
  };
}

Drizzle Security Checklist:

  • Always use sql template tag for raw SQL
  • Never use sql.raw() with user input
  • Validate all inputs before using in queries
  • Add user filters for multi-tenant data
  • Use select() to control returned fields
  • Always limit query results
  • Store DATABASE_URL in environment variables

How to Verify It Worked

  1. Test SQL injection payloads:
const maliciousInputs = [
  "'; DROP TABLE users; --",
  "1 OR 1=1",
  "admin'--"
];

for (const input of maliciousInputs) {
  const result = await db.select()
    .from(users)
    .where(eq(users.email, input));
  // Should return empty array, not error or all users
}
  1. Test access control: Try accessing another user's data
  2. Check field exposure: Review API responses for sensitive fields

Common Errors & Troubleshooting

"column does not exist" in raw queries

Column names in sql template are case-sensitive. Use quotes for mixed-case columns: sql.raw('"userId"')

Type errors with sql template

Use type parameter: sql<number>count(*)`` for correct TypeScript types.

Connection pool exhaustion

Ensure you're reusing the db instance, not creating new connections per request.

Is Drizzle as safe as Prisma?

Both are safe when used correctly. Drizzle gives you more control over raw SQL, which means more opportunity for mistakes. Use the sql template tag consistently.

Can I use dynamic column names safely?

Yes, but only with an allowlist. Validate the column name against a list of allowed values, then use sql.raw() for the validated value.

How do I handle soft deletes securely?

Add a default filter for deletedAt IS NULL to all queries. Consider using a wrapper function that always includes this condition.

Related guides:Prisma Security · Parameterized Queries · Zod Validation

How-To Guides

How to Secure Drizzle ORM