query api - select

20 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Cast count() result to integer in PostgreSQL, MySQL and Cockroach

In PostgreSQL, MySQL, and Cockroach, the count() function returns bigint, which is interpreted as string by their drivers. Cast to integer using sql<number>`cast(count(...) as integer)`. In MySQL, cast to unsigned integer. Example: sql<number>`cast(count(${column}) as integer)` for a column or sql<number>`cast(count(*) as integer)` for all rows.

Count result type in SQLite and MSSQL

In SQLite and MSSQL, the count() result returns as integer directly without needing type casting. Use sql<number>`count(*)` or sql<number>`count(${column})` for these databases.

sql<T> generic does not perform runtime type casting

When specifying sql<number>, you are telling Drizzle the expected type of the field is number. Drizzle cannot perform type casts based on the provided type generic because that information is not available at runtime. If the runtime value doesn't match the expected type, a type mismatch will occur. To apply runtime transformations, use the .mapWith() method instead.

Count rows with WHERE condition

To count rows that match a condition, use the .where() method with Drizzle operators. Example: await db.select({ count: count() }).from(products).where(gt(products.price, 100)); generates SQL: select count(*) from products where price > 100

Count with GROUP BY and joins

To count rows grouped by a column using joins, combine count() with .groupBy() and join operations. Example: await db.select({ country: countries.name, citiesCount: count(cities.id) }).from(countries).leftJoin(cities, eq(countries.id, cities.countryId)).groupBy(countries.id).orderBy(countries.name); generates SQL: select countries.name, count("cities"."id") from countries left join cities on countries.id = cities.country_id group by countries.id order by countries.name;

Count rows with non-NULL values in a specific column

To count rows where a specified column contains non-NULL values, pass the column to the count() function. Example: await db.select({ count: count(products.discount) }).from(products); generates SQL: select count("discount") from products;

Count all rows with count() function

To count all rows in a table, use the count() function from drizzle-orm. The count() function casts its result to a number at runtime. Example: await db.select({ count: count() }).from(products); generates SQL: select count(*) from products;

Count all rows with sql operator

To count all rows using the sql operator, use sql`count(*)`.mapWith(Number) to cast the result to a number at runtime. Example: await db.select({ count: sql`count(*)`.mapWith(Number) }).from(products);

Relational query to select specific columns

To select specific columns with relational queries, pass a columns object with true values for desired columns. For example: await db.query.posts.findMany({ columns: { title: true } }).

Relational query to exclude specific columns

To exclude specific columns with relational queries, pass a columns object with false values for columns to exclude. For example: await db.query.posts.findMany({ columns: { content: false } }).

Add computed columns to relational query with extras

To add computed or extra columns to a relational query result, use the extras property with functions that return sql expressions. For example: await db.query.posts.findMany({ extras: { titleLength: (t) => sql<number>`length(${t.title})`.as("title_length") } }).

Conditional column selection with spread operator

Create conditional select by using the spread operator with conditional logic. For example: select({ id: posts.id, ...(withTitle && { title: posts.title }) }).from(posts) conditionally includes the title column.

Control column selection in relational query joins

When using relational queries with relations, use the with property to specify column inclusion/exclusion for related tables. Use columns with true/false values, or pass true to include all columns. For example: with: { comments: { columns: { userId: false, postId: false } }, user: true }.

Select all columns with .select().from()

To include all columns in a query, use the .select() method without arguments, then chain .from() with the table. For example: await db.select().from(posts).

Select specific columns with object syntax

To include specific columns in a query, pass an object to .select() with the desired columns. For example: await db.select({ title: posts.title }).from(posts).

Use getColumns() to get all table columns

The getColumns() utility function from 'drizzle-orm' retrieves all columns from a table. It can be used with the spread operator to include all columns or to exclude specific columns by destructuring.

Add extra computed columns with getColumns() and sql

To include all existing columns plus additional computed columns, spread getColumns(table) and add extra columns using sql expressions. For example: await db.select({ ...getColumns(posts), titleLength: sql<number>`length(${posts.title})` }).from(posts).

Exclude specific columns with getColumns()

To exclude columns from a query, destructure getColumns(table) to remove unwanted columns, then spread the remaining columns in select. For example: const { content, ...rest } = getColumns(posts); await db.select({ ...rest }).from(posts).

Include or exclude columns in joins

When joining tables, you can selectively include or exclude columns from each table by destructuring getColumns() results and organizing the select object with nested structure. Use table references directly (like users) as shorthand for getColumns(users).

Relational query to find all records with all columns

To retrieve all records with all columns using relational queries, use db.query.tableName.findMany() without arguments. For example: await db.query.posts.findMany().

Give your agent this brain