v1.0
75%
Benchmarks
Extension
Studio
Studio Package
Drizzle Run
Our goodies!
EdgeDB
Upstash
Turso
🚀 Drizzle is giving you 10% off Turso Scaler and Pro for 1 Year 🚀
Payload
Xata
Neon
Nuxt
Hydra
Deco.cx
Tembo
SQLite Cloud
SingleStore
PrAha
Lokalise
Replit
Mooncake
Product by Drizzle Team
One Dollar Stats $1 per mo web analytics
christmas
deal
Drizzle | Include or Exclude Columns in Query
PostgreSQL
MySQL
SQLite
This guide assumes familiarity with:

Drizzle has flexible API for including or excluding columns in queries. To include all columns you can use .select() method like this:

index.ts
schema.ts
import { posts } from './schema';

const db = drizzle(...);

await db.select().from(posts);
// result type
type Result = {
  id: number;
  title: string;
  content: string;
  views: number;
}[];

To include specific columns you can use .select() method like this:

await db.select({ title: posts.title }).from(posts);
// result type
type Result = {
  title: string;
}[];

To include all columns with extra columns you can use getTableColumns() utility function like this:

import { getTableColumns, sql } from 'drizzle-orm';

await db
  .select({
    ...getTableColumns(posts),
    titleLength: sql<number>`length(${posts.title})`,
  })
  .from(posts);
// result type
type Result = {
  id: number;
  title: string;
  content: string;
  views: number;
  titleLength: number;
}[];

To exclude columns you can use getTableColumns() utility function like this:

import { getTableColumns } from 'drizzle-orm';

const { content, ...rest } = getTableColumns(posts); // exclude "content" column

await db.select({ ...rest }).from(posts); // select all other columns
// result type
type Result = {
  id: number;
  title: string;
  views: number;
}[];

This is how you can include or exclude columns with joins:

index.ts
schema.ts
import { eq, getTableColumns } from 'drizzle-orm';
import { comments, posts, users } from './db/schema';

// exclude "userId" and "postId" columns from "comments"
const { userId, postId, ...rest } = getTableColumns(comments);

await db
  .select({
    postId: posts.id, // include "id" column from "posts"
    comment: { ...rest }, // include all other columns
    user: users, // equivalent to getTableColumns(users)
  })
  .from(posts)
  .leftJoin(comments, eq(posts.id, comments.postId))
  .leftJoin(users, eq(users.id, posts.userId));
// result type
type Result = {
  postId: number;
  comment: {
    id: number;
    content: string;
    createdAt: Date;
  } | null;
  user: {
    id: number;
    name: string;
    email: string;
  } | null;
}[];

Drizzle has useful relational queries API, that lets you easily include or exclude columns in queries. This is how you can include all columns:

index.ts
schema.ts
import * as schema from './schema';

const db = drizzle(..., { schema });

await db.query.posts.findMany();
// result type
type Result = {
  id: number;
  title: string;
  content: string;
  views: number;
}[]

This is how you can include specific columns using relational queries:

await db.query.posts.findMany({
  columns: {
    title: true,
  },
});
// result type
type Result = {
  title: string;
}[]

This is how you can include all columns with extra columns using relational queries:

import { sql } from 'drizzle-orm';

await db.query.posts.findMany({
  extras: {
    titleLength: sql<number>`length(${posts.title})`.as('title_length'),
  },
});
// result type
type Result = {
  id: number;
  title: string;
  content: string;
  views: number;
  titleLength: number;
}[];

This is how you can exclude columns using relational queries:

await db.query.posts.findMany({
  columns: {
    content: false,
  },
});
// result type
type Result = {
  id: number;
  title: string;
  views: number;
}[]

This is how you can include or exclude columns with relations using relational queries:

index.ts
schema.ts
import * as schema from './schema';

const db = drizzle(..., { schema });

await db.query.posts.findMany({
  columns: {
    id: true, // include "id" column
  },
  with: {
    comments: {
      columns: {
        userId: false, // exclude "userId" column
        postId: false, // exclude "postId" column
      },
    },
    user: true, // include all columns from "users" table
  },
});
// result type
type Result = {
  id: number;
  user: {
    id: number;
    name: string;
    email: string;
  };
  comments: {
    id: number;
    content: string;
    createdAt: Date;
  }[];
}[]

This is how you can create custom solution for conditional select:

index.ts
schema.ts
import { posts } from './schema';

const searchPosts = async (withTitle = false) => {
  await db
    .select({
      id: posts.id,
      ...(withTitle && { title: posts.title }),
    })
    .from(posts);
};

await searchPosts();
await searchPosts(true);
// result type
type Result = {
  id: number;
  title?: string | undefined;
}[];