GabrielC Posts - Answer Overflow

GabrielC

•Created by GabrielC on 1/4/2024 in #help

left join make the types never

Hi guys, im now sure if this is the expected behavior or not but i have this query

    const reminders = await db.select({
      reminderId: reminderTable.id,
    })
      .from(reminderTable);

    const reminders = await db.select({
      reminderId: reminderTable.id,
    })
      .from(reminderTable);

where the type of reminders is

const reminders: {
    reminderId: number;
}[]

const reminders: {
    reminderId: number;
}[]

so that is ok but when i add a left join all the types are never

    const reminders = await db.select({
      reminderId: reminderTable.id,
    })
      .from(reminderTable)
      .leftJoin(sentReminderTable, eq(reminderTable.id, sentReminderTable.reminderId));

    const reminders = await db.select({
      reminderId: reminderTable.id,
    })
      .from(reminderTable)
      .leftJoin(sentReminderTable, eq(reminderTable.id, sentReminderTable.reminderId));

this is the left join and now the types are

{
    reminderId: never;
}[]

{
    reminderId: never;
}[]

I'm not sure why is never i was expecting something like

  {
    reminderId: number | null;
}[]

  {
    reminderId: number | null;
}[]

Im doing something wrong or it is correct this way? Im using "drizzle-orm": "^0.29.2" Thanks so much in advance for the time

11 replies

DTDrizzle Team

•Created by GabrielC on 11/26/2023 in #help

Mysql JSON_ARRAYAGG as a string instead of a json

Hi guys, how are you ? I'm trying to use a sub query to get and array of objects but in the result i'm not sure how to get the array and dont' get a string My query is like this

    const data: PaymentResponse[] = await db
      .select({
        id: parent.id,
        date: parent.date,
        status: parent.status,
        method: parent.method,
        totalAmount: parent.totalAmount,
        contacts: sql<SimpleContactResponse[]>`(
            SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'id', c.id,
                'name', c.name,
                'lastName', c.last_name
            ))
            FROM contact c
            INNER JOIN invoice i ON c.id = i.contact_id
            INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
            WHERE pi.payment_id = p.id
            GROUP BY pi.payment_id
        )`,
        settlements: sql<{
          id: number;
          name: string;
        }[]>`(
            SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'id', s.id,
                'name', s.name
            ))
            FROM settlement s
            INNER JOIN invoice i ON s.id = i.settlement_id
            INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
            WHERE pi.payment_id = p.id
            GROUP BY pi.payment_id
        )`
      })
      .from(parent)
      .where(condition)
      .limit(dto.limit)
      .offset(offset)
      .orderBy(desc(parent.date), desc(parent.id));

    const data: PaymentResponse[] = await db
      .select({
        id: parent.id,
        date: parent.date,
        status: parent.status,
        method: parent.method,
        totalAmount: parent.totalAmount,
        contacts: sql<SimpleContactResponse[]>`(
            SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'id', c.id,
                'name', c.name,
                'lastName', c.last_name
            ))
            FROM contact c
            INNER JOIN invoice i ON c.id = i.contact_id
            INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
            WHERE pi.payment_id = p.id
            GROUP BY pi.payment_id
        )`,
        settlements: sql<{
          id: number;
          name: string;
        }[]>`(
            SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'id', s.id,
                'name', s.name
            ))
            FROM settlement s
            INNER JOIN invoice i ON s.id = i.settlement_id
            INNER JOIN payment_invoice pi ON i.id = pi.invoice_id
            WHERE pi.payment_id = p.id
            GROUP BY pi.payment_id
        )`
      })
      .from(parent)
      .where(condition)
      .limit(dto.limit)
      .offset(offset)
      .orderBy(desc(parent.date), desc(parent.id));

and the result that im getting is

        {
            "id": 2901,
            "date": "2023-10-17T07:00:00.000Z",
            "status": "PAID",
            "method": "TRANSFER",
            "totalAmount": "56460.00",
            "contacts": "[{\"id\": 311, \"name\": \"Maximiliano\", \"lastName\": \"Figueroa\"}]",
            "settlements": "[{\"id\": 64, \"name\": \"Octubre 2023\"}]"
        },

        {
            "id": 2901,
            "date": "2023-10-17T07:00:00.000Z",
            "status": "PAID",
            "method": "TRANSFER",
            "totalAmount": "56460.00",
            "contacts": "[{\"id\": 311, \"name\": \"Maximiliano\", \"lastName\": \"Figueroa\"}]",
            "settlements": "[{\"id\": 64, \"name\": \"Octubre 2023\"}]"
        },

one solution is for loop every result and do a JSON.parse but dosen't feel right I'm not really sure what to do here thanks for help

3 replies

DTDrizzle Team

•Created by GabrielC on 9/25/2023 in #help

How can I get a type from SelectedFields<MySqlColumn, Table>?

Hi guys sorry to bother buy I'm trying to build a Repository Pattern with Drizzle and I stuck with a type. The interface with I came up is

interface BaseRepository<T extends MySqlTable,
    S extends SelectedFields<MySqlColumn, T>> {
    findAll(): Promise<S[]>;
    findById(id: number): Promise<S | null>;
    create(data: InferInsertModel<T>): Promise<number>;
    delete(id: number): Promise<void>;
    findAllPaginated(page: number, limit: number, condition?: SQL<unknown>): Promise<Pagination<S>>;
}

export abstract class CrudRepository<
    M extends MySqlTable & { id: SQLWrapper, deletedAt: SQLWrapper },
    S extends SelectedFields<MySqlColumn, M>
> implements BaseRepository<M, S> {
 // ....implementation
}

export const userTable = mysqlTable("user", {
    id: int('id').primaryKey().autoincrement().notNull(),
    email: varchar("email", { length: 255 }).notNull(),
    password: varchar("password", { length: 255 }).notNull(),
    name: varchar("name", { length: 255 }).notNull(),
    deletedAt: timestamp("deleted_at"),
});

const userSelect = {
    id: userTable.id,
    email: userTable.email,
    name: userTable.name,
};

export class UserRepository extends CrudRepository<typeof userTable, typeof userSelect> {
    constructor(db: DbConnection) {
        super(userTable, db, userSelect);
    }
}

const userRepository = new UserRepository(db);

const allUsers = userRepository.findAll();

interface BaseRepository<T extends MySqlTable,
    S extends SelectedFields<MySqlColumn, T>> {
    findAll(): Promise<S[]>;
    findById(id: number): Promise<S | null>;
    create(data: InferInsertModel<T>): Promise<number>;
    delete(id: number): Promise<void>;
    findAllPaginated(page: number, limit: number, condition?: SQL<unknown>): Promise<Pagination<S>>;
}

export abstract class CrudRepository<
    M extends MySqlTable & { id: SQLWrapper, deletedAt: SQLWrapper },
    S extends SelectedFields<MySqlColumn, M>
> implements BaseRepository<M, S> {
 // ....implementation
}

export const userTable = mysqlTable("user", {
    id: int('id').primaryKey().autoincrement().notNull(),
    email: varchar("email", { length: 255 }).notNull(),
    password: varchar("password", { length: 255 }).notNull(),
    name: varchar("name", { length: 255 }).notNull(),
    deletedAt: timestamp("deleted_at"),
});

const userSelect = {
    id: userTable.id,
    email: userTable.email,
    name: userTable.name,
};

export class UserRepository extends CrudRepository<typeof userTable, typeof userSelect> {
    constructor(db: DbConnection) {
        super(userTable, db, userSelect);
    }
}

const userRepository = new UserRepository(db);

const allUsers = userRepository.findAll();

This is working fine but my problem is that the type of allUsers is

const allUsers: Promise<{
    id: MySqlColumn<...>;
    email: MySqlColumn<...>;
    name: MySqlColumn<...>;
}[]>

const allUsers: Promise<{
    id: MySqlColumn<...>;
    email: MySqlColumn<...>;
    name: MySqlColumn<...>;
}[]>

and I'm trying to get something like

const allUsers: Promise<{
    id: number;
    name: string;
    email: string;
}>

const allUsers: Promise<{
    id: number;
    name: string;
    email: string;
}>

So i want to do something like InferSelectModel<S> but I can't and I'm not sure how to do it, any hint on how can i solve it o what documentation can i read to understand a bit more and solve it? Thanks

18 replies

DTDrizzle Team

•Created by GabrielC on 5/4/2023 in #help

Error types with custom schema

Hi, I'm trying to do a general function to paginate my querys, but I'm having some problems with the types. I'm trying to use the types of the library, but I'm not sure if I'm doing it right. All my tables have the same columns, so I created a function to create the base table, and I'm trying to use it to create the pagination function, and definitly i'm doing it wrong but I'm not shure how to do it, could you point me in the right direction? to know if it's possible to do it.

const baseMysqlTable = <TColumnsMap extends Record<string, AnyMySqlColumnBuilder>>(name: string, columns: TColumnsMap) => {
    return mysqlTable(name, {
        ...columns,
        id: int('id').primaryKey().autoincrement().notNull(),
        updatedAt: timestamp('updated_at').onUpdateNow(),
        createdAt: timestamp('created_at').defaultNow(),
        deletedAt: timestamp('deleted_at')
    });
};

type BaseMysqlTable = InferModel<typeof baseMysqlTable>;

const withPagination = async <S extends AnyMySqlSelect, T extends BaseMysqlTable>(qb: S, table: T, offset = 0, limit = 10) => {
    const data = await qb.offset(offset).limit(limit).orderBy(desc(table.id));
    // const count = await db.select(sql<number>`count(${table.id})`).from(table);
    return {
        data,
        offset,
        limit,
        // total: count[0]
    }
}

const baseMysqlTable = <TColumnsMap extends Record<string, AnyMySqlColumnBuilder>>(name: string, columns: TColumnsMap) => {
    return mysqlTable(name, {
        ...columns,
        id: int('id').primaryKey().autoincrement().notNull(),
        updatedAt: timestamp('updated_at').onUpdateNow(),
        createdAt: timestamp('created_at').defaultNow(),
        deletedAt: timestamp('deleted_at')
    });
};

type BaseMysqlTable = InferModel<typeof baseMysqlTable>;

const withPagination = async <S extends AnyMySqlSelect, T extends BaseMysqlTable>(qb: S, table: T, offset = 0, limit = 10) => {
    const data = await qb.offset(offset).limit(limit).orderBy(desc(table.id));
    // const count = await db.select(sql<number>`count(${table.id})`).from(table);
    return {
        data,
        offset,
        limit,
        // total: count[0]
    }
}

5 replies

DTDrizzle Team

•Created by GabrielC on 4/12/2023 in #help

Problem running a migration

4 replies

Gaming

Programming