Jonathan
Explore posts from serversDTDrizzle Team
•Created by Jonathan on 5/10/2024 in #help
Incorrect relations
This is the query I wrote in typescript that produced the SQL query:
I filtered some columns, as you can see, to make debugging a bit easier. Hopefully someone can help me out, because I do not understand why the problem I described in my post is happening.
const projects = await ctx.db.query.syndicationProject.findMany({
columns: { id: true },
with: {
entity: {
columns: { id: true },
with: {
members: {
columns: {
id: true,
memberId: true,
isControlPerson: true,
},
with: {
member: { columns: { id: true } },
},
},
},
},
},
});
const projects = await ctx.db.query.syndicationProject.findMany({
columns: { id: true },
with: {
entity: {
columns: { id: true },
with: {
members: {
columns: {
id: true,
memberId: true,
isControlPerson: true,
},
with: {
member: { columns: { id: true } },
},
},
},
},
},
});
4 replies
DTDrizzle Team
•Created by Jonathan on 5/10/2024 in #help
Incorrect relations
For debugging purposes, this is the SQL query that ws executed by Drizzle:
select "syndicationProject"."id", "syndicationProject_entity"."data" as "entity" from "syndication_project" "syndicationProject" left join lateral (select json_build_array("syndicationProject_entity"."id", "syndicationProject_entity_members"."data") as "data" from (select * from "person" "syndicationProject_entity" where "syndicationProject_entity"."id" = "syndicationProject"."entity_id" limit $1) "syndicationProject_entity" left join lateral (select coalesce(json_agg(json_build_array("syndicationProject_entity_members"."id", "syndicationProject_entity_members"."member_id", "syndicationProject_entity_members"."is_control_person", "syndicationProject_entity_members_member"."data")), '[]'::json) as "data" from "membership" "syndicationProject_entity_members" left join lateral (select json_build_array("syndicationProject_entity_members_member"."id") as "data" from (select * from "person" "syndicationProject_entity_members_member" where "syndicationProject_entity_members_member"."id" = "syndicationProject_entity_members"."member_id" limit $2) "syndicationProject_entity_members_member") "syndicationProject_entity_members_member" on true where "syndicationProject_entity_members"."member_id" = "syndicationProject_entity"."id") "syndicationProject_entity_members" on true) "syndicationProject_entity" on true
select "syndicationProject"."id", "syndicationProject_entity"."data" as "entity" from "syndication_project" "syndicationProject" left join lateral (select json_build_array("syndicationProject_entity"."id", "syndicationProject_entity_members"."data") as "data" from (select * from "person" "syndicationProject_entity" where "syndicationProject_entity"."id" = "syndicationProject"."entity_id" limit $1) "syndicationProject_entity" left join lateral (select coalesce(json_agg(json_build_array("syndicationProject_entity_members"."id", "syndicationProject_entity_members"."member_id", "syndicationProject_entity_members"."is_control_person", "syndicationProject_entity_members_member"."data")), '[]'::json) as "data" from "membership" "syndicationProject_entity_members" left join lateral (select json_build_array("syndicationProject_entity_members_member"."id") as "data" from (select * from "person" "syndicationProject_entity_members_member" where "syndicationProject_entity_members_member"."id" = "syndicationProject_entity_members"."member_id" limit $2) "syndicationProject_entity_members_member") "syndicationProject_entity_members_member" on true where "syndicationProject_entity_members"."member_id" = "syndicationProject_entity"."id") "syndicationProject_entity_members" on true) "syndicationProject_entity" on true
4 replies
DTDrizzle Team
•Created by Jonathan on 5/10/2024 in #help
Incorrect relations
Here are the schemas and relations in code:
export const person = pgTable(
"person",
{
id: uuid("id").defaultRandom().notNull().primaryKey(),
type: text("type", {
enum: ["natural-person", "entity"],
}).notNull(),
emailAddress: text("email_address"),
phoneNumber: text("phone_number"),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
}
);
export const personRelations = relations(person, ({ one, many }) => ({
project: one(syndicationProject),
members: many(membership, { relationName: "member" }),
}));
export const membership = pgTable("membership", {
id: uuid("id").defaultRandom().notNull().primaryKey(),
entityId: uuid("entity_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
memberId: uuid("member_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
});
export const membershipRelations = relations(membership, ({ one }) => ({
entity: one(person, {
relationName: "entity",
fields: [membership.entityId],
references: [person.id],
}),
member: one(person, {
relationName: "member",
fields: [membership.memberId],
references: [person.id],
}),
}));
export const person = pgTable(
"person",
{
id: uuid("id").defaultRandom().notNull().primaryKey(),
type: text("type", {
enum: ["natural-person", "entity"],
}).notNull(),
emailAddress: text("email_address"),
phoneNumber: text("phone_number"),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
}
);
export const personRelations = relations(person, ({ one, many }) => ({
project: one(syndicationProject),
members: many(membership, { relationName: "member" }),
}));
export const membership = pgTable("membership", {
id: uuid("id").defaultRandom().notNull().primaryKey(),
entityId: uuid("entity_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
memberId: uuid("member_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
});
export const membershipRelations = relations(membership, ({ one }) => ({
entity: one(person, {
relationName: "entity",
fields: [membership.entityId],
references: [person.id],
}),
member: one(person, {
relationName: "member",
fields: [membership.memberId],
references: [person.id],
}),
}));
4 replies
DTDrizzle Team
•Created by Jonathan on 8/1/2023 in #help
Drizzle kit SQL error
Okay I figured out the issue, but I still dont have a solution. If I use the connection string from the original project inside of my forked project, then drizzle-kit push works perfectly fine.
12 replies
DTDrizzle Team
•Created by Jonathan on 8/1/2023 in #help
Drizzle kit SQL error
A side note:
The code base I am working in is a fork of another project I am working on. I want to give this fork its own database.
12 replies
DTDrizzle Team
•Created by Jonathan on 8/1/2023 in #help
Drizzle kit SQL error
And my db.ts file:
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { connect } from "@planetscale/database";
import * as schema from "./schema";
const client = connect({
url: process.env.DATABASE_URL as string,
});
const db = drizzle(client, { schema });
export default db;
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { connect } from "@planetscale/database";
import * as schema from "./schema";
const client = connect({
url: process.env.DATABASE_URL as string,
});
const db = drizzle(client, { schema });
export default db;
12 replies
DTDrizzle Team
•Created by Jonathan on 8/1/2023 in #help
Drizzle kit SQL error
This is my schema file:
import { relations, sql } from "drizzle-orm";
import {
mysqlTable,
timestamp,
text,
varchar,
json,
} from "drizzle-orm/mysql-core";
export const users = mysqlTable("users", {
id: varchar("id", { length: 255 }).primaryKey(),
externalId: text("externalId"),
firstName: text("firstName").notNull(),
lastName: text("lastName").notNull(),
createdAt: timestamp("createdAt")
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: timestamp("updatedAt")
.default(sql`CURRENT_TIMESTAMP`)
.onUpdateNow()
.notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
documents: many(documents),
}));
export const documents = mysqlTable("documents", {
id: varchar("id", { length: 255 }).primaryKey(),
type: text("type").notNull(),
name: text("name").notNull(),
answers: json("answers"),
userId: text("userId"),
createdAt: timestamp("createdAt")
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: timestamp("updatedAt")
.default(sql`CURRENT_TIMESTAMP`)
.onUpdateNow()
.notNull(),
});
export const documentsRelations = relations(documents, ({ one }) => ({
user: one(users, { fields: [documents.userId], references: [users.id] }),
}));
import { relations, sql } from "drizzle-orm";
import {
mysqlTable,
timestamp,
text,
varchar,
json,
} from "drizzle-orm/mysql-core";
export const users = mysqlTable("users", {
id: varchar("id", { length: 255 }).primaryKey(),
externalId: text("externalId"),
firstName: text("firstName").notNull(),
lastName: text("lastName").notNull(),
createdAt: timestamp("createdAt")
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: timestamp("updatedAt")
.default(sql`CURRENT_TIMESTAMP`)
.onUpdateNow()
.notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
documents: many(documents),
}));
export const documents = mysqlTable("documents", {
id: varchar("id", { length: 255 }).primaryKey(),
type: text("type").notNull(),
name: text("name").notNull(),
answers: json("answers"),
userId: text("userId"),
createdAt: timestamp("createdAt")
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: timestamp("updatedAt")
.default(sql`CURRENT_TIMESTAMP`)
.onUpdateNow()
.notNull(),
});
export const documentsRelations = relations(documents, ({ one }) => ({
user: one(users, { fields: [documents.userId], references: [users.id] }),
}));
12 replies