Drizzle ORM - Include or Exclude Columns in Query

Drizzle | Include or Exclude Columns in Query

PostgreSQL

MySQL

SQLite

This guide assumes familiarity with:

Get started with PostgreSQL, MySQL and SQLite
Select statement
Get typed table columns
Joins
Relational queries
Partial select with relational queries

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

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  views: integer('views').notNull().default(0),
});

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

import { integer, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  views: integer('views').notNull().default(0),
  userId: integer('user_id').notNull().references(() => users.id),
});

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  postId: integer('post_id').notNull().references(() => posts.id),
  userId: integer('user_id').notNull().references(() => users.id),
  content: text('content').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

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

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  views: integer('views').notNull().default(0),
});

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

import { relations } from 'drizzle-orm';
import { integer, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  views: integer('views').notNull().default(0),
  userId: integer('user_id').notNull().references(() => users.id),
});

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  postId: integer('post_id').notNull().references(() => posts.id),
  userId: integer('user_id').notNull().references(() => users.id),
  content: text('content').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  comments: many(comments),
}));

export const postsRelations = relations(posts, ({ many, one }) => ({
  comments: many(comments),
  user: one(users, { fields: [posts.userId], references: [users.id] }),
}));

export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, { fields: [comments.postId], references: [posts.id] }),
  user: one(users, { fields: [comments.userId], references: [users.id] }),
}));

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

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  views: integer('views').notNull().default(0),
});