Become a Gold Sponsor
Drizzle | Include or Exclude Columns in Query
PostgreSQL
MySQL
SQLite

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;
}[];