xamarot Posts - Answer Overflow

xamarot

•Created by xamarot on 11/13/2023 in #help

Omit fields on "$inferSelect"?

Let's say I have a table with 5 columns, I want to do type TableType = typeof theTable.$inferSelect; but omit some fields. How do I do that?

3 replies

DTDrizzle Team

•Created by xamarot on 8/21/2023 in #help

How to use "onConflictDoUpdate" with composite key?

If a user sends a friend request while one exists it should update to pending. But the "friends" table is using a composite key. Table looks like this

export const friends = pgTable("friends", {
    senderUsername: varchar("sender_username").notNull(),
    recipientUsername: varchar("recipient_username").notNull(),
    sentAt: date("sent_at").defaultNow().notNull(),
    repliedAt: date("replied_at"),

    requestStatus: requestStatusEnum("request_status").notNull().default("PENDING")
},
    (t) => ({
        pk: primaryKey(t.senderUsername, t.recipientUsername) <- composite key
    }));

export const friends = pgTable("friends", {
    senderUsername: varchar("sender_username").notNull(),
    recipientUsername: varchar("recipient_username").notNull(),
    sentAt: date("sent_at").defaultNow().notNull(),
    repliedAt: date("replied_at"),

    requestStatus: requestStatusEnum("request_status").notNull().default("PENDING")
},
    (t) => ({
        pk: primaryKey(t.senderUsername, t.recipientUsername) <- composite key
    }));

query looks like this

    await db.insert(friends).values({
        senderUsername,
        recipientUsername
    }).onConflictDoUpdate({ target: friends.senderUsername, // <- how to do this?
        set: { requestStatus: REQUEST_STATUS.PENDING } });

    return {
        sendFriendRequestForm
    };

    await db.insert(friends).values({
        senderUsername,
        recipientUsername
    }).onConflictDoUpdate({ target: friends.senderUsername, // <- how to do this?
        set: { requestStatus: REQUEST_STATUS.PENDING } });

    return {
        sendFriendRequestForm
    };

Do I need to use a primary (non-composite) key in the friends table?

10 replies

DTDrizzle Team

•Created by xamarot on 8/17/2023 in #help

Invert a boolean easily?

In SQL you can do

UPDATE my_table SET is_active = NOT is_active;

UPDATE my_table SET is_active = NOT is_active;

Is there a similiar syntax for Drizzle?

4 replies

DTDrizzle Team

•Created by xamarot on 8/9/2023 in #help

Get the latest message from each user in a table

Say my messages table looks something like this:

content|sender_id|recipient_id|sent_at_timestamp
hello|1|2|yyyy-mm-dd # message 1
greetings|2|1|yyyy-mm-dd # message 2
bonjour|1|3|yyyy-mm-dd # message 3

content|sender_id|recipient_id|sent_at_timestamp
hello|1|2|yyyy-mm-dd # message 1
greetings|2|1|yyyy-mm-dd # message 2
bonjour|1|3|yyyy-mm-dd # message 3

This means user 1 has sent a greeting to user 2, and user 2 has responded. User 1 has written to user 3, who has not responded. This means that the latest messages in all conversations for user 1 should be messages 2 and 3. This can then grow exponentially. But what I want is to get the LATEST message in any conversation, regardless of if you are the sender or recipient. How would I write a query for this? Using plain SQL it's been suggested to use a CTE but how would I do it in Drizzle?

16 replies

DTDrizzle Team

•Created by xamarot on 7/23/2023 in #help

Schema issues with split files, relational queries

I'm having issues getting db queries to work with split schema files. My config (drizzle.config.ts) looks like:

export default {
    out: './drizzle',
    schema: './src/lib/db/schema/*'
} satisfies Config;

export default {
    out: './drizzle',
    schema: './src/lib/db/schema/*'
} satisfies Config;

and my folder structure:

lib/db/schema:
  - users.ts
  - games.ts

lib/db/schema:
  - users.ts
  - games.ts

and when I do stuff like (auth user is in users.ts)

await db.query.auth_user.findFirst

await db.query.auth_user.findFirst

I get the error

Property 'auth_user' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?">'

Property 'auth_user' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?">'

So it seems like it can't load my table structure. When I had my schema in a single file, it was easy just do do

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

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

But I can't figure out how to do that when I have separate schema files?

14 replies

DTDrizzle Team

•Created by xamarot on 7/10/2023 in #help

Extract interface for table from schema

Can I extract an interface for a table from my schema without using it from the client? Something like "typeof [nameOfTable]" or something? Or do I need to define my own interfaces?

3 replies

DTDrizzle Team

•Created by xamarot on 6/26/2023 in #help

Deleted migrations, still can't create new

I wanted to reset my entire database so I deleted my entire migrations folder and ran generate again. It says "no schema changes" and does not create a migration. Any way to force it?

17 replies

Gaming

Programming