ivan Posts - Answer Overflow

ivan

•Created by ivan on 8/5/2024 in #help

sqliteTable infer optional vs required fields

I am trying to create optional fields on my model but no matter what I do the drizzle is inferring all the properties as required. See the example below:

const example = sqliteTable("example", {
  id: text("id").primaryKey(),
  required: text("required").notNull(),
  optional: text("optional"),
});

type NewExample = typeof example.$inferInsert;
// resolves in:
// type NewExample = {
//   id: string;
//   required: string;
// }

type Example = typeof example.$inferSelect;
// resolves in:
// type Example = {
//   id: string;
//   required: string;
//   optional: string;
// }

// my expectation:
// type Example = {
//   id: string;
//   required: string;
//   optional?: string;
// }
//
// or
//
// type Example = {
//   id: string;
//   required: string;
//   optional: string | undefined;
// }

const example = sqliteTable("example", {
  id: text("id").primaryKey(),
  required: text("required").notNull(),
  optional: text("optional"),
});

type NewExample = typeof example.$inferInsert;
// resolves in:
// type NewExample = {
//   id: string;
//   required: string;
// }

type Example = typeof example.$inferSelect;
// resolves in:
// type Example = {
//   id: string;
//   required: string;
//   optional: string;
// }

// my expectation:
// type Example = {
//   id: string;
//   required: string;
//   optional?: string;
// }
//
// or
//
// type Example = {
//   id: string;
//   required: string;
//   optional: string | undefined;
// }

Is there any way to achieve expected behaviour with the drizzle orm?

8 replies

DTDrizzle Team

•Created by ivan on 8/4/2024 in #help

How to get paginated result with the total count?

I want to show pagination in my frontend and calculate the total pages number. I am able to do the paginated query but I also want to select total of the filtered query without pagination. Is this possible within one query? Or do I need to do 2 queries? I haven't found the way of doing this, I was trying to somehow do this using "with" clause (https://orm.drizzle.team/docs/select#with-clause) but I am not sure if I get it right. The idea was to do this:

const filters = []; // some filters here

const total = db.$with("total").as(
      db
        .select({ count: count(tab.id) })
        .from(tab)
        .where(and(...filters))
    );

const result = await withPagination(db
        .with(total)
        .select({
          tab: getTableColumns(tab),
          tab1: getTableColumns(tab1),
          tab2: getTableColumns(tab2),
          total: total, // not entirely sure what to do here or if even I understand it correctly
        })
        .from(tab)
        .leftJoin(tab1, eq(tab.id, tab1.tabId))
        .leftJoin(tab2, eq(tab.id, tab2.tabId))
        .where(and(...filters))
        .$dynamic()
   );

function withPagination<T extends SQLiteSelect>(
  props: {
    query: T;
    orderBy: SQL[];
  } & Page
) {
  return props.query
    .orderBy(...props.orderBy)
    .limit(props.pageSize)
    .offset((props.page - 1) * props.pageSize);
}

const filters = []; // some filters here

const total = db.$with("total").as(
      db
        .select({ count: count(tab.id) })
        .from(tab)
        .where(and(...filters))
    );

const result = await withPagination(db
        .with(total)
        .select({
          tab: getTableColumns(tab),
          tab1: getTableColumns(tab1),
          tab2: getTableColumns(tab2),
          total: total, // not entirely sure what to do here or if even I understand it correctly
        })
        .from(tab)
        .leftJoin(tab1, eq(tab.id, tab1.tabId))
        .leftJoin(tab2, eq(tab.id, tab2.tabId))
        .where(and(...filters))
        .$dynamic()
   );

function withPagination<T extends SQLiteSelect>(
  props: {
    query: T;
    orderBy: SQL[];
  } & Page
) {
  return props.query
    .orderBy(...props.orderBy)
    .limit(props.pageSize)
    .offset((props.page - 1) * props.pageSize);
}

Is this approach even possible? Or do I need to do 2 separate queries? Thanks in advance for the help - if there is more information needed for this, please let me know.

6 replies

Gaming

Programming