Learn
•
Guides
•
Queries
Fetching all columns without explicitly listing them

This guide demonstrates 2 approaches how to retrieve all columns from a table without the need to specify each one in the select statement.

Schema

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  body: text('body').notNull(),
  postId: integer('post_id')
    .notNull()
    .references(() => posts.id),
});

Let’s retrieve only postId and all columns of comments table.

First approach with getTableColumns function

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

const response = await db
  .select({
    postId: posts.id,
    comment: getTableColumns(comments),
  })
  .from(posts)
  .leftJoin(comments, eq(posts.id, comments.postId));

// response type
{
  postId: number;
  comment: {
      id: number;
      body: string;
      postId: number;
  } | null;
}[]

Second approach with table

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

const response = await db
  .select({
    postId: posts.id,
    comment: comments,
  })
  .from(posts)
  .leftJoin(comments, eq(posts.id, comments.postId));

// response type
{
    postId: number;
    comment: {
        id: number;
        body: string;
        postId: number;
    } | null;
}[]

SQL

SQL query will be the same for both approaches.

'select "posts"."id", "comments"."id", "comments"."body", "comments"."post_id" from "posts" left join "comments" on "posts"."id" = "comments"."post_id"'
Queries
Conditional filters in query
Counting Related Entities
Incrementing a value
Decrementing a Value
Filtering records by a list of values
Fetching all columns without explicitly listing them
Toggling a boolean field