Sillvva
Sillvva
PostsComments
DTDrizzle Team
Created by Deen24ID on 5/18/2025 in #help
Issues from a WITH clause (CTE).
Updated the above query
8 replies
DTDrizzle Team
Created by Deen24ID on 5/18/2025 in #help
Issues from a WITH clause (CTE).
Oh, add .as() to the max function. max(schema.price.lastUpdatedAt).as("lastUpdatedAt")
To select arbitrary SQL values as fields in a CTE and reference them in other CTEs or in the main query, you need to add aliases to them: 
const sq = db.$with('sq').as(db.select({ 
  name: sql<string>`upper(${users.name})`.as('name'),
})
.from(users));

const result = await db.with(sq).select({ name: sq.name }).from(sq);
const sq = db.$with('sq').as(db.select({ 
  name: sql<string>`upper(${users.name})`.as('name'),
})
.from(users));

const result = await db.with(sq).select({ name: sq.name }).from(sq);
If you don’t provide an alias, the field type will become DrizzleTypeError and you won’t be able to reference it in other queries. If you ignore the type error and still try to use the field, you will get a runtime error, since there’s no way to reference that field without an alias.
8 replies
DTDrizzle Team
Created by Deen24ID on 5/18/2025 in #help
Issues from a WITH clause (CTE).
@Deen24ID pinging for notification
8 replies
DTDrizzle Team
Created by Deen24ID on 5/18/2025 in #help
Issues from a WITH clause (CTE).
https://orm.drizzle.team/docs/select#with-clause 
import { max } from "drizzle-orm";

const cte = db
    .select({
        name: price.name,
        lastUpdatedAt: max(price.lastUpdatedAt).as('lastUpdatedAt')
    })
    .from(price)
    .groupBy(price.name);
const priceLast = db.$with("priceLast").as(cte);
const res = await db
    .with(priceLast)
    .select({
        id: price.id,
        name: priceLast.name,
        lastUpdatedAt: priceLast.lastUpdatedAt,
        buy: price.buy,
        sell: price.sell
    })
    .from(priceLast)
    .innerJoin(price, and(eq(priceLast.name, price.name), eq(priceLast.lastUpdatedAt, price.lastUpdatedAt)));
import { max } from "drizzle-orm";

const cte = db
    .select({
        name: price.name,
        lastUpdatedAt: max(price.lastUpdatedAt).as('lastUpdatedAt')
    })
    .from(price)
    .groupBy(price.name);
const priceLast = db.$with("priceLast").as(cte);
const res = await db
    .with(priceLast)
    .select({
        id: price.id,
        name: priceLast.name,
        lastUpdatedAt: priceLast.lastUpdatedAt,
        buy: price.buy,
        sell: price.sell
    })
    .from(priceLast)
    .innerJoin(price, and(eq(priceLast.name, price.name), eq(priceLast.lastUpdatedAt, price.lastUpdatedAt)));
8 replies
DTDrizzle Team
Created by salzar on 5/13/2025 in #help
Seems like the schema generic is missing - did you forget to add it to your DB type?
I think the config file is primarily for migrations. You still need to pass it to the drizzle() init for runtime code.
8 replies
DTDrizzle Team
Created by Little Cutie Penguin 🐧 on 5/12/2025 in #help
How to infer connection type in TypeScript?
If you have the RQBv2 beta installed, it would look like this: 
import * as schema from "$server/db/schema";
import { relations } from "$server/db/relations";
import { type MySqlTransaction } from "drizzle-orm/mysql-core";
import { drizzle, MySql2Database, type MySql2PreparedQueryHKT, type MySql2QueryResultHKT } from "drizzle-orm/mysql2";

export const db = drizzle(connection, { schema, relations });

export type Databse = MySql2Database<typeof schema, typeof relations>;
export type Transaction = MySqlTransaction<MySql2QueryResultHKT, MySql2PreparedQueryHKT, typeof schema, typeof relations>;
import * as schema from "$server/db/schema";
import { relations } from "$server/db/relations";
import { type MySqlTransaction } from "drizzle-orm/mysql-core";
import { drizzle, MySql2Database, type MySql2PreparedQueryHKT, type MySql2QueryResultHKT } from "drizzle-orm/mysql2";

export const db = drizzle(connection, { schema, relations });

export type Databse = MySql2Database<typeof schema, typeof relations>;
export type Transaction = MySqlTransaction<MySql2QueryResultHKT, MySql2PreparedQueryHKT, typeof schema, typeof relations>;
3 replies
DTDrizzle Team
Created by Little Cutie Penguin 🐧 on 5/12/2025 in #help
How to infer connection type in TypeScript?
I think this is what you're looking for. I use postgres, so I'm not 100% on this. 
import * as schema from "$server/db/schema";
import { type MySqlTransaction } from "drizzle-orm/mysql-core";
import { drizzle, MySql2Database, type MySql2PreparedQueryHKT, type MySql2QueryResultHKT } from "drizzle-orm/mysql2";

export const db = drizzle(connection, { schema });

export type Databse = MySql2Database<typeof schema>;
export type Transaction = MySqlTransaction<MySql2QueryResultHKT, MySql2PreparedQueryHKT, typeof schema>;
import * as schema from "$server/db/schema";
import { type MySqlTransaction } from "drizzle-orm/mysql-core";
import { drizzle, MySql2Database, type MySql2PreparedQueryHKT, type MySql2QueryResultHKT } from "drizzle-orm/mysql2";

export const db = drizzle(connection, { schema });

export type Databse = MySql2Database<typeof schema>;
export type Transaction = MySqlTransaction<MySql2QueryResultHKT, MySql2PreparedQueryHKT, typeof schema>;
With this, the type you're looking for is Database | Transaction
3 replies
DTDrizzle Team
Created by Mike on 5/8/2025 in #help
How do I make Drizzle infer the correct return type when dynamically adding a custom select field
In Cursor, I right clicked on the select method and opened its reference. SelectedFields is the select method's parameter type.
6 replies
DTDrizzle Team
Created by Aditya Kirad on 5/8/2025 in #help
getting the returning data after inserting record as string | undefined instead of string
I believe it also rolls back if an error is thrown. That's all the rollback function does internally. 
if(!userId) throw new Error("Failed to insert user");
if(!userId) throw new Error("Failed to insert user");
19 replies
DTDrizzle Team
Created by Aditya Kirad on 5/8/2025 in #help
getting the returning data after inserting record as string | undefined instead of string
On this line, you should throw. tx.rollback() throws internally, but I guess TS doesn't realize that. Adding throw adds the type narrowing you're looking for. Otherwise, TS assumes the code is allowed to continue. And since userId hasn't been defined in the undefined case, it can still be undefined. 
if(!userId) throw tx.rollback()
if(!userId) throw tx.rollback()
19 replies
DTDrizzle Team
Created by Aditya Kirad on 5/8/2025 in #help
getting the returning data after inserting record as string | undefined instead of string
I prefer having that property on in all my projects, because it catches potential runtime errors that TS wouldn't otherwise catch. Example with it off: https://www.typescriptlang.org/play/#code/MYewdgzgLgBGCuBbARgUwE4QFxyW9A2gLowC8MxA3AFDWiSxhm4oYQEAMRNA9DzAJgA9APxA Example with it on: https://www.typescriptlang.org/play/?noUncheckedIndexedAccess=true#code/MYewdgzgLgBGCuBbARgUwE4QFxyW9A2gLowC8MxA3AFDWiSxhm4oYQEAMRNA9DzAJgA9APxA
19 replies
DTDrizzle Team
Created by Aditya Kirad on 5/8/2025 in #help
getting the returning data after inserting record as string | undefined instead of string
I see. In your postgres app, do you have "noUncheckedIndexedAccess": true, in your tsconfig? That causes the behavior I described.
19 replies
DTDrizzle Team
Created by Aditya Kirad on 5/8/2025 in #help
getting the returning data after inserting record as string | undefined instead of string
@Aditya Kirad ping for notification
19 replies
DTDrizzle Team
Created by Mike on 5/8/2025 in #help
How do I make Drizzle infer the correct return type when dynamically adding a custom select field
Ping for notification
6 replies
DTDrizzle Team
Created by Mike on 5/8/2025 in #help
How do I make Drizzle infer the correct return type when dynamically adding a custom select field
I think the following changes fix the return type. See the 4 comments: 
import { type SelectedFields } from "drizzle-orm/pg-core";

// changed Record<...> to SelectedFields
export function createBaseVideoQuery<T extends SelectedFields = {}>(
  // Change the optional param to a default value instead
  customFields: T = {} as T
) {
  const baseFields = getTableColumns(videosTable);

  const query = db
    .select({ ...baseFields, ...customFields })
    .from(videosTable)
    .leftJoin(channelsTable, eq(videosTable.channel_id, channelsTable.id))
    .$dynamic();

  return query;
}

export function withQuestionCount() {
  return {
    // Add explicit number type
    question_count: sql<number>`COALESCE((
      SELECT COUNT(*) 
      FROM questions q
      WHERE ...
    ), 0)`.as("question_count"),
  } satisfies SelectedFields; // adds type safety here too
}
import { type SelectedFields } from "drizzle-orm/pg-core";

// changed Record<...> to SelectedFields
export function createBaseVideoQuery<T extends SelectedFields = {}>(
  // Change the optional param to a default value instead
  customFields: T = {} as T
) {
  const baseFields = getTableColumns(videosTable);

  const query = db
    .select({ ...baseFields, ...customFields })
    .from(videosTable)
    .leftJoin(channelsTable, eq(videosTable.channel_id, channelsTable.id))
    .$dynamic();

  return query;
}

export function withQuestionCount() {
  return {
    // Add explicit number type
    question_count: sql<number>`COALESCE((
      SELECT COUNT(*) 
      FROM questions q
      WHERE ...
    ), 0)`.as("question_count"),
  } satisfies SelectedFields; // adds type safety here too
}
6 replies
DTDrizzle Team
Created by Aditya Kirad on 5/8/2025 in #help
getting the returning data after inserting record as string | undefined instead of string
TypeScript can't assume an array has a first value. 
const test: number[] = [1,2,3];
const value = test[0];
//    ^? const value: number | undefined
const test: number[] = [1,2,3];
const value = test[0];
//    ^? const value: number | undefined
The same is true regardless of how you try to access it. 
const [value] = [1,2,3] as number[];
//     ^? const value: number | undefined
const [value] = [1,2,3] as number[];
//     ^? const value: number | undefined
This is different from a tuple type. 
const [value1, value2] = [1,2,3] as [number, ...number[]];
//     ^? const value1: number
//        const value2: number | undefined
const [value1, value2] = [1,2,3] as [number, ...number[]];
//     ^? const value1: number
//        const value2: number | undefined
If the second insert query relies on the first, then you should wrap it in an if statement. 
const userId = // first insert query
if (userId) {
  // second insert query
} else {
  tx.rollback();
}
const userId = // first insert query
if (userId) {
  // second insert query
} else {
  tx.rollback();
}
19 replies
DTDrizzle Team
Created by T. on 5/5/2025 in #help
Referencing Column at runtime
Here's how I'd do it: 
// If using RQBv2
type TRSchema = ExtractTablesWithRelations<typeof relations>;
// If using RQBv1
type TRSchema = ExtractTablesWithRelations<typeof schema>;

// The function ensures table and column names are string literal types and provides autocomplete
function createSearch<TableName extends keyof TRSchema, ColumnName extends keyof TRSchema[TableName]["columns"]>(table: TableName, column: ColumnName, value: string) {
    return {
        table,
        column,
        value
    }
}

const search = [
  createSearch("users", "name", "%John%"),
  createSearch("admins", "address", "%SmallTown%")  
];

const conditions = new Set<SQL>();
const dynamicQuery = db.select().from(usersTable).$dynamic();
dynamicQuery.leftJoin(adminsTable, eq(adminsTable.userId, usersTable.id));

for (const filter of search) {
  let table: TRSchema[typeof filter.table]["table"];
  switch (filter.table) {
    case "users":
      table = usersTable;
      break;
    case "admins":
      table = adminsTable;
      break;
    default:
      throw Error("Invalid table");
  }
  const column = table[filter.column];

  if (column) {
    conditions.add(like(column, filter.value))
  }
}
dynamicQuery.where(and(...conditions));
console.log(dynamicQuery.toSQL());
// If using RQBv2
type TRSchema = ExtractTablesWithRelations<typeof relations>;
// If using RQBv1
type TRSchema = ExtractTablesWithRelations<typeof schema>;

// The function ensures table and column names are string literal types and provides autocomplete
function createSearch<TableName extends keyof TRSchema, ColumnName extends keyof TRSchema[TableName]["columns"]>(table: TableName, column: ColumnName, value: string) {
    return {
        table,
        column,
        value
    }
}

const search = [
  createSearch("users", "name", "%John%"),
  createSearch("admins", "address", "%SmallTown%")  
];

const conditions = new Set<SQL>();
const dynamicQuery = db.select().from(usersTable).$dynamic();
dynamicQuery.leftJoin(adminsTable, eq(adminsTable.userId, usersTable.id));

for (const filter of search) {
  let table: TRSchema[typeof filter.table]["table"];
  switch (filter.table) {
    case "users":
      table = usersTable;
      break;
    case "admins":
      table = adminsTable;
      break;
    default:
      throw Error("Invalid table");
  }
  const column = table[filter.column];

  if (column) {
    conditions.add(like(column, filter.value))
  }
}
dynamicQuery.where(and(...conditions));
console.log(dynamicQuery.toSQL());
2 replies
DTDrizzle Team
Created by ArChak on 7/5/2024 in #help
How to check user input is a valid column before appending condition?
No description
4 replies
DTDrizzle Team
Created by ArChak on 7/5/2024 in #help
How to check user input is a valid column before appending condition?
Can you show the how you defined the columnFilters variable?
4 replies
DTDrizzle Team
Created by magicspon on 7/4/2024 in #help
How to query many to many? Are the docs out of date?
Alternatively 
db.query.events.findMany({
  with: {
    drinks: { // you could also change this key
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});
db.query.events.findMany({
  with: {
    drinks: { // you could also change this key
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});
7 replies