Blume Posts - Answer Overflow

Blume

Explore posts from servers

DIAdiscord.js - Imagine an app TtRPC DTDrizzle Team

•Created by Blume on 2/4/2025 in #help

What's the hash in the __drizzle_migrations table used for?

I was wondering what the hash in the migration table is used for? I was playing around a bit to see what happens when I did a drizzle-kit push and manually update the migration table.. It seems to work by just updating the created_at timestamps to the when timestamp in the journal. Is it safe to do this? What is the hash used for and when could this create problems? (I know my use case shouldn't be done, but I want to understand and know what to do when things get corrupted etc.)

1 replies

DTDrizzle Team

•Created by Blume on 1/30/2025 in #help

Strategy to handle wrong migrations locally

How would I go about when I did a mistake in my schema locally when developing and migrated my local database. Do I need to always drop everything and run migrations from the start?.. I would love something where I can just push to the local database after I fixed my latest migration. e.g. drizzle-kit regenerate -> Generates the last migration file again containing my fixed schema and then drizzle-kit push but in a manner that it also correctly updates the migration table so I can proceed with additional migrations later on.. The way I see it I have to drop my database schema as well as the drizzle schema in postgres locally to be able to run the migrations in order again. Right?

1 replies

DTDrizzle Team

•Created by Blume on 1/22/2025 in #help

drizzle-kit studio reading .env file

Heya, I just wondered if it's drizzle-kit that automatically reads the .env file? Is it possible to disable that? I'm reading my env files in via nextjs's @next/env library so I can make use of .local etc. But when drizzle-kit reads in the normal .env file next-env can't override the already read env vars with the .env.local since it thinks the already read vars are system wide env variables which always have the highest priority.. Any Idea how to solve this? Just a flag or something that disables automatically reading .env files would be perfect..

5 replies

DTDrizzle Team

•Created by Blume on 10/15/2023 in #help

Delete from join

Is it possible to do something like this:

DELETE P
  FROM Product P
  LEFT JOIN OrderItem I ON P.Id = I.ProductId
 WHERE I.Id IS NULL

DELETE P
  FROM Product P
  LEFT JOIN OrderItem I ON P.Id = I.ProductId
 WHERE I.Id IS NULL

With the ORM? e.g.

DB.delete(Product).leftJoin(OrderItem, eq(Product.id, OrderItem.productId).where(...);

DB.delete(Product).leftJoin(OrderItem, eq(Product.id, OrderItem.productId).where(...);

Or do I need to write the full SQL statement in raw sql?

4 replies

DTDrizzle Team

•Created by Blume on 10/15/2023 in #help

Use column name in like operator

I'm trying to create a query with a join where I get all entries whose value starts with the value of the root table. e.g. Tags = { ..., tag: "some", ... } tagsWithNoteIds = [{ ..., tag: "some", ... }, { ..., tag: "some/string", ... }]

DB.with(tagsWithNoteIds)
  .select({
    tagId: Tags.id,
    tag: Tags.tag,
    noteIds: sql`json_agg(${tagsWithNoteIds.noteIds})`,
  })
  .from(Tags)
  .leftJoin(tagsWithNoteIds, or(eq(tagsWithNoteIds.tag, Tags.tag), like(tagsWithNoteIds.tag, `${Tags.tag}/%`)))
  .where(eq(Tags.authorId, query.authorId))
  .groupBy(Tags.id);

DB.with(tagsWithNoteIds)
  .select({
    tagId: Tags.id,
    tag: Tags.tag,
    noteIds: sql`json_agg(${tagsWithNoteIds.noteIds})`,
  })
  .from(Tags)
  .leftJoin(tagsWithNoteIds, or(eq(tagsWithNoteIds.tag, Tags.tag), like(tagsWithNoteIds.tag, `${Tags.tag}/%`)))
  .where(eq(Tags.authorId, query.authorId))
  .groupBy(Tags.id);

I'm not sure how the syntax should be to do this in the like operator. Is this even possible? When I debug the SQL that is created by this it uses params: [ "[object Object]/%", "d792d767-6662-4a56-8b93-bb6dac1a350d" ] for the params.. In the end it would still need a way to know, that the column name shouldn't be used as a raw string so maybe this is just not possible?

2 replies

DTDrizzle Team

•Created by Blume on 10/14/2023 in #help

many-to-many query with where filter on second level

Hi, I'm trying to create a many-to-many query where I can filter on the second "with" level.. My schema looks like this:

/**
 * NOTES
 */

export const Notes = pgTable("notes", {
  id: uuid("id").defaultRandom().primaryKey(),
  authorId: uuid("author_id")
    .references(() => Users.id)
    .notNull(),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
});

export const NotesRelations = relations(Notes, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * TAGS
 */

export const Tags = pgTable(
  "tags",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    authorId: uuid("author_id")
      .references(() => Users.id)
      .notNull(),
    tag: text("tag").notNull(),
    createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({ unq: unique().on(t.authorId, t.tag) }),
);

export const TagsRelations = relations(Tags, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * NOTES_TAGS
 */

export const NotesToTags = pgTable(
  "notes_to_tags",
  {
    noteId: uuid("note_id")
      .notNull()
      .references(() => Notes.id),
    tagId: uuid("tag_id")
      .notNull()
      .references(() => Tags.id),
  },
  (t) => ({ pk: primaryKey(t.noteId, t.tagId) }),
);

export const NotesToTagsRelations = relations(NotesToTags, ({ one }) => ({
  note: one(Notes, { fields: [NotesToTags.noteId], references: [Notes.id] }),
  tag: one(Tags, { fields: [NotesToTags.tagId], references: [Tags.id] }),
}));

/**
 * NOTES
 */

export const Notes = pgTable("notes", {
  id: uuid("id").defaultRandom().primaryKey(),
  authorId: uuid("author_id")
    .references(() => Users.id)
    .notNull(),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
});

export const NotesRelations = relations(Notes, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * TAGS
 */

export const Tags = pgTable(
  "tags",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    authorId: uuid("author_id")
      .references(() => Users.id)
      .notNull(),
    tag: text("tag").notNull(),
    createdAt: timestamp("created_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { mode: "date", withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({ unq: unique().on(t.authorId, t.tag) }),
);

export const TagsRelations = relations(Tags, ({ many }) => ({
  notesToTags: many(NotesToTags),
}));

/**
 * NOTES_TAGS
 */

export const NotesToTags = pgTable(
  "notes_to_tags",
  {
    noteId: uuid("note_id")
      .notNull()
      .references(() => Notes.id),
    tagId: uuid("tag_id")
      .notNull()
      .references(() => Tags.id),
  },
  (t) => ({ pk: primaryKey(t.noteId, t.tagId) }),
);

export const NotesToTagsRelations = relations(NotesToTags, ({ one }) => ({
  note: one(Notes, { fields: [NotesToTags.noteId], references: [Notes.id] }),
  tag: one(Tags, { fields: [NotesToTags.tagId], references: [Tags.id] }),
}));

And I tried to query it with

return DB.query.Notes.findMany({
  where: (Notes, { eq }) => eq(Notes.authorId, query.authorId),
  with: {
    notesToTags: {
      with: {
        tag: {
          where: (Tags, { like }) => {
            if (query.tag) return like(Tags.tag, `${query.tag}%`);
          },
        },
      },
    },
  },
});

return DB.query.Notes.findMany({
  where: (Notes, { eq }) => eq(Notes.authorId, query.authorId),
  with: {
    notesToTags: {
      with: {
        tag: {
          where: (Tags, { like }) => {
            if (query.tag) return like(Tags.tag, `${query.tag}%`);
          },
        },
      },
    },
  },
});

But it won't allow the where inside the second with.. Am I doing something wrong? Is this simply something that is not possible with drizzle? What I'm aiming for is to get all Notes where the tag starts with query.tag. I'm not that used to SQL so I might be missing some fundamentals to querying many-to-many relations. In pure SQL I think it could be done with 2 left joins and some aggregation, right?

2 replies

Gaming

Programming