Select full rows
Read complete records from a table when all columns are needed.
Drizzle ORM · Module
db.select
Drizzle’s select API fetches rows with SQL-like, type-safe queries. Use an empty selection for all table columns or a selection object for a custom projection.
How it works
Call db.select() and finish the query with .from(table) to fetch rows. Passing an object to select limits the result to named columns or SQL expressions, while the inferred result type follows that selection.
Syntax
db.select(fields?).from(source)Parameters
| Name | Type | Description |
|---|---|---|
fields | object | undefined | Optional object mapping result property names to table columns or SQL expressions. |
source | table or query source | The table or query source supplied to .from(...). |
Returns
A promise resolving to an array of rows whose properties and types match the selected fields.
Key points
- The select API models SQL-style row retrieval while preserving composability and static type safety.
- With no selection object,
db.select().from(table)returns every row with all table columns. - Pass an object to select a precise projection and control the keys exposed in each returned row.
- Selection fields may be computed SQL expressions, not only direct column references.
- Conditional object spreading lets a query include optional fields based on runtime logic.
- selectDistinct provides duplicate elimination for result sets when uniqueness is required.
Common mistakes
- Do not assume an unqualified selection emits SQL
select *; Drizzle explicitly enumerates the table columns. - Use selectDistinct when duplicate result rows must be removed; ordinary select does not provide distinctness.
Example: Select specific columns
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';
const users = sqliteTable('users', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
});
const db = drizzle(new Database(':memory:'));
const rows = await db.select({ id: users.id, name: users.name }).from(users);Builds a type-safe query that returns only the selected user columns.
When to use it
Project selected columns
Return only the fields required by an API response or internal operation.
Select SQL expressions
Add database-computed values such as lowercase text to each result row.
Select distinct rows
Remove duplicate result rows from a dataset.
Interview questions
QHow do you select all columns versus a subset?
The no-argument form selects every column from the table, and the query is completed with .from(users). A selection object instead defines the output fields and their aliases.
Follow-up: How would you select a computed field? Use an SQL expression such as sql<string>\lower(${users.name})\ inside the selection object.
QHow does a partial select affect the result shape?
The selection object maps result keys to table columns or arbitrary SQL expressions. Drizzle infers the returned object shape from that mapping, so selecting field1 and field2 produces rows with those keys.
Follow-up: How can the selected shape vary at runtime? Spread a conditional object into the selection, such as ...(withName ? { name: users.name } : {}).
QWhen should selectDistinct be used?
Use selectDistinct() when only unique rows are needed. It supports the same general selection style, including selecting a subset such as { id: users.id }.
Follow-up: What ordering constraint appears in the documented example? The distinct query is followed by orderBy(users.id, users.name).
Related
selectDistinct()from()orderBy()sql``where()