Ravi Posts - Answer Overflow

Ravi

Explore posts from servers

•Created by Ravi on 7/13/2023 in #help

How can I add a Prefix Index?

I migrated from Prisma, and I have a column with varchar(2000) which I indexed with @@index([columnName(length: 191)]) but the introspection schema didn't seem to recognize the index length:

columnNameIdx: index("TableName_columnName_idx").on(table.columnName)

columnNameIdx: index("TableName_columnName_idx").on(table.columnName)

is there a workaround to set the index length? maybe with raw sql? Thank you in advance.

6 replies

DTDrizzle Team

•Created by Ravi on 7/12/2023 in #help

Custom column type with default not working?

I tried to add a new column with a custom type and a default value. but for some reason when I tried to db push, it warned me that it didn't have a default value and that it would truncate the tables. Am I doing something wrong?

const unsignedBigInt = customType<{
  data: number;
  // I also tried this ↓ https://orm.drizzle.team/docs/custom-types#examples
  // notNull: true;
  // default: true;
}>({
  dataType() {
    return "bigint UNSIGNED";
  },
});

export const myTable = mysqlTable("MyTable", {
  id: varchar("id", { length: 191 }).primaryKey().notNull(),
  //...
  // ↓ this was added ↓
  usage: unsignedBigInt("usage").default(0).notNull(),
});

const unsignedBigInt = customType<{
  data: number;
  // I also tried this ↓ https://orm.drizzle.team/docs/custom-types#examples
  // notNull: true;
  // default: true;
}>({
  dataType() {
    return "bigint UNSIGNED";
  },
});

export const myTable = mysqlTable("MyTable", {
  id: varchar("id", { length: 191 }).primaryKey().notNull(),
  //...
  // ↓ this was added ↓
  usage: unsignedBigInt("usage").default(0).notNull(),
});

· You're about to add not-null usage column without default value, which contains 3 items

· You're about to add not-null usage column without default value, which contains 3 items

4 replies

DTDrizzle Team

•Created by Ravi on 7/11/2023 in #help

good way to get the "count" for paginated queries

I'm doing the following to get the total count and the values in a paginated query:

const fileCountRes = await ctx.db
  .select({
    count: sql<number>`count(*)`.mapWith(Number),
  })
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName));

const filesRes = await ctx.db
  .select()
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName))
  .limit(pageSize)
  .offset((currentPage - 1) * pageSize)
  .orderBy(desc(file.updatedAt));

const fileCountRes = await ctx.db
  .select({
    count: sql<number>`count(*)`.mapWith(Number),
  })
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName));

const filesRes = await ctx.db
  .select()
  .from(file)
  .innerJoin(bucket, eq(file.bucketId, bucket.id))
  .where(eq(bucket.name, bucketName))
  .limit(pageSize)
  .offset((currentPage - 1) * pageSize)
  .orderBy(desc(file.updatedAt));

I wonder if there is a better way to do it. I'll add more things in the where to make a filter functionality and It would be great if I didn't have to add it in both places separately. Is there a way to do it (and still keep the type safety)? thank you in advance!

3 replies

Gaming

Programming