stephen
stephen
Explore posts from servers
DTDrizzle Team
Created by stephen on 4/15/2024 in #help
Query `with` returning no results?
Hi! Wondering if anyone can see what I'm doing wrong here... I've got a recipes table that had a relation with recipeTags which, in turn, is related to tags. I've got a query that's returning the correct recipes, but the recipeTags is empty: I'm looking at: https://orm.drizzle.team/docs/rqb#find-many
const foundRecipes = await db.query.recipes.findMany({
...rest,
where: eq(recipes.submittedBy, userId),
columns: {
id: true,
...
},
with: { recipeTags: { with: { tags: true } } },
orderBy: (recipes) => [desc(recipes.createdDate)],
});
const foundRecipes = await db.query.recipes.findMany({
...rest,
where: eq(recipes.submittedBy, userId),
columns: {
id: true,
...
},
with: { recipeTags: { with: { tags: true } } },
orderBy: (recipes) => [desc(recipes.createdDate)],
});
// schema.ts

export const recipes = sqliteTable('recipes', {
id: text('id').$default(() => createId()).primaryKey(),
...
});

export const recipesRelations = relations(recipes, ({ one, many }) => ({
recipeTags: many(recipeTags),
...
}));

export const recipeTags = sqliteTable('recipe_tags', {
recipeId: text('recipe_id'),
tagId: text('tag_id'),
...
}, (table) => ({
pk: primaryKey({ columns: [table.recipeId, table.tagId] }),
}));

export const recipeTagsRelations = relations(recipeTags, ({ one }) => ({
recipes: one(recipes, { fields: [recipeTags.recipeId], references: [recipes.id] }),
tags: one(tags, { fields: [recipeTags.tagId], references: [tags.id] }),
}));

export const tags = sqliteTable('tags', {
id: text('id').$default(() => createId()).primaryKey(),
name: text('name').notNull(),
isPrivate: integer('is_private', { mode: 'boolean' }).$default(() => false),
userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
createdDate: text('created_date').$default(() => toISO8601(new Date())),
updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
});

export const tagsRelations = relations(tags, ({ one, many }) => ({
recipeTags: many(recipeTags),
menuTags: many(menuTags),
menuCourseTags: many(menuCourseTags),
user: one(users, { fields: [tags.userId], references: [users.id] }),
}));
// schema.ts

export const recipes = sqliteTable('recipes', {
id: text('id').$default(() => createId()).primaryKey(),
...
});

export const recipesRelations = relations(recipes, ({ one, many }) => ({
recipeTags: many(recipeTags),
...
}));

export const recipeTags = sqliteTable('recipe_tags', {
recipeId: text('recipe_id'),
tagId: text('tag_id'),
...
}, (table) => ({
pk: primaryKey({ columns: [table.recipeId, table.tagId] }),
}));

export const recipeTagsRelations = relations(recipeTags, ({ one }) => ({
recipes: one(recipes, { fields: [recipeTags.recipeId], references: [recipes.id] }),
tags: one(tags, { fields: [recipeTags.tagId], references: [tags.id] }),
}));

export const tags = sqliteTable('tags', {
id: text('id').$default(() => createId()).primaryKey(),
name: text('name').notNull(),
isPrivate: integer('is_private', { mode: 'boolean' }).$default(() => false),
userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
createdDate: text('created_date').$default(() => toISO8601(new Date())),
updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
});

export const tagsRelations = relations(tags, ({ one, many }) => ({
recipeTags: many(recipeTags),
menuTags: many(menuTags),
menuCourseTags: many(menuCourseTags),
user: one(users, { fields: [tags.userId], references: [users.id] }),
}));
5 replies
DTDrizzle Team
Created by stephen on 3/21/2024 in #help
Schema is "wrong" resulting in "foreign key mismatch" error
I generated my sqlite schema using drizzle but now that I'm trying to use it, I'm getting a foreign key error. Here's where it seems to error: I added
export const menuRecipes = sqliteTable('menu_recipes', {
id: text('id').$default(() => createId()).primaryKey(),
menuId: text('menu_id').references(() => menus.id, { onDelete: "cascade" }).notNull(),
recipeId: text('recipe_id').references(() => recipes.id, { onDelete: "cascade" }).notNull(),
addedBy: text('added_by').references(() => users.id),
createdDate: text('created_date').$default(() => toISO8601(new Date())),
updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onUpdate: "cascade" }),
});

export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
menu: one(menus, { fields: [menuRecipes.menuId], references: [menus.id] }),
recipe: one(recipes, { fields: [menuRecipes.recipeId], references: [recipes.id] }),
user: one(users, { fields: [menuRecipes.addedBy], references: [users.id] }),
menuCourse: one(menuCourses, { fields: [menuRecipes.menuCourseId], references: [menuCourses.id] }),
}));


export const menuCourseRecipes = sqliteTable('menu_course_recipes', {
menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onDelete: "cascade" }).notNull(),
menuRecipeId: text('recipe_id').references(() => menuRecipes.id, { onDelete: "cascade" }).notNull(),
}, (t) => ({
pk: primaryKey({ columns: [t.menuCourseId, t.menuRecipeId] }),
}));

export const menuCourseRecipesRelations = relations(menuCourseRecipes, ({ one }) => ({
menuCourses: one(menuCourses, {
fields: [menuCourseRecipes.menuCourseId],
references: [menuCourses.id],
}),
menuRecipes: one(menuRecipes, {
fields: [menuCourseRecipes.menuRecipeId],
references: [menuRecipes.id],
}),
}));
export const menuRecipes = sqliteTable('menu_recipes', {
id: text('id').$default(() => createId()).primaryKey(),
menuId: text('menu_id').references(() => menus.id, { onDelete: "cascade" }).notNull(),
recipeId: text('recipe_id').references(() => recipes.id, { onDelete: "cascade" }).notNull(),
addedBy: text('added_by').references(() => users.id),
createdDate: text('created_date').$default(() => toISO8601(new Date())),
updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onUpdate: "cascade" }),
});

export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
menu: one(menus, { fields: [menuRecipes.menuId], references: [menus.id] }),
recipe: one(recipes, { fields: [menuRecipes.recipeId], references: [recipes.id] }),
user: one(users, { fields: [menuRecipes.addedBy], references: [users.id] }),
menuCourse: one(menuCourses, { fields: [menuRecipes.menuCourseId], references: [menuCourses.id] }),
}));


export const menuCourseRecipes = sqliteTable('menu_course_recipes', {
menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onDelete: "cascade" }).notNull(),
menuRecipeId: text('recipe_id').references(() => menuRecipes.id, { onDelete: "cascade" }).notNull(),
}, (t) => ({
pk: primaryKey({ columns: [t.menuCourseId, t.menuRecipeId] }),
}));

export const menuCourseRecipesRelations = relations(menuCourseRecipes, ({ one }) => ({
menuCourses: one(menuCourses, {
fields: [menuCourseRecipes.menuCourseId],
references: [menuCourses.id],
}),
menuRecipes: one(menuRecipes, {
fields: [menuCourseRecipes.menuRecipeId],
references: [menuRecipes.id],
}),
}));
4 replies
DTDrizzle Team
Created by stephen on 3/15/2024 in #help
Studio: Not Enough Info to Infer Relation
https://www.answeroverflow.com/m/1180072254214766632 Running into this issue:
bun run drizzle-kit studio
Error: There is not enough information to infer relation "__public__.auditLogs.users"
bun run drizzle-kit studio
Error: There is not enough information to infer relation "__public__.auditLogs.users"
export const users = sqliteTable('users', {
id: text('id').$default(() => createId()).primaryKey(),
username: text('username').unique().notNull(),
email: text('email'),
//...
});

export const usersRelations = relations(users, ({ one, many }) => ({
auditLogs: many(auditLogs),
//...
}));

export const auditLogs = sqliteTable('audit_logs', {
userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
action: text('action'),
timeStamp: text('time_stamp').$default(() => toISO8601(new Date())),
}, (table) => ({
unq: unique().on(table.userId, table.action, table.timeStamp),
}));

export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
users: one(users),
}));
export const users = sqliteTable('users', {
id: text('id').$default(() => createId()).primaryKey(),
username: text('username').unique().notNull(),
email: text('email'),
//...
});

export const usersRelations = relations(users, ({ one, many }) => ({
auditLogs: many(auditLogs),
//...
}));

export const auditLogs = sqliteTable('audit_logs', {
userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
action: text('action'),
timeStamp: text('time_stamp').$default(() => toISO8601(new Date())),
}, (table) => ({
unq: unique().on(table.userId, table.action, table.timeStamp),
}));

export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
users: one(users),
}));
Any ideas? Based on the stack overflow, the suggestion is that the relationship isn't on both sides, but it seems like I do in this case, no?
11 replies
DTDrizzle Team
Created by stephen on 3/13/2024 in #help
Creating migrations with Enums present (postgres)
Hi! I'm new to drizzle but am trying to create migrations and running into troubles with the auto generation of migration files when an enum is present. For example:
const userStatusEnum = pgEnum('statusEnum', ["pending", "active", "archived"]);
export const users = pgTable('users', {
id: text('id').$default(() => createId()).primaryKey(),
username: text('username').unique().notNull(),
status: userStatusEnum('status').$default(() => "pending"),
});
const userStatusEnum = pgEnum('statusEnum', ["pending", "active", "archived"]);
export const users = pgTable('users', {
id: text('id').$default(() => createId()).primaryKey(),
username: text('username').unique().notNull(),
status: userStatusEnum('status').$default(() => "pending"),
});
When I generate a migration (e.g., npx drizzle-kit generate:pg), I get something like:
-- 0001_awesome_venus.sql
CREATE TABLE IF NOT EXISTS "users" (
"id" text PRIMARY KEY NOT NULL,
"username" text NOT NULL,
"status" "statusEnum",
);
-- 0001_awesome_venus.sql
CREATE TABLE IF NOT EXISTS "users" (
"id" text PRIMARY KEY NOT NULL,
"username" text NOT NULL,
"status" "statusEnum",
);
This fails however as "statusEnum" is not defined which means i need to make the following (manual) modifications to my migration file...
+ DROP TYPE IF EXISTS statusEnum;
+ CREATE TYPE statusEnum AS ENUM ('pending', 'active', 'archived');

CREATE TABLE IF NOT EXISTS "users" (
"id" text PRIMARY KEY NOT NULL,
"username" text NOT NULL,
- "status" "statusEnum",
+ "status" statusEnum DEFAULT 'pending',
);
+ DROP TYPE IF EXISTS statusEnum;
+ CREATE TYPE statusEnum AS ENUM ('pending', 'active', 'archived');

CREATE TABLE IF NOT EXISTS "users" (
"id" text PRIMARY KEY NOT NULL,
"username" text NOT NULL,
- "status" "statusEnum",
+ "status" statusEnum DEFAULT 'pending',
);
Is this anticipated? or is there a way to configure it so that these are done automatically?
3 replies