DT
Drizzle Teammr_pablo

db.query error with relation

I have created a schema.ts, with two tables, with a one-to-one relationship. I have also create the "relation". In my index file, I get an error with the following code 
const singleMatch = await db.query.matches.findMany({
        where: eq(matches.matchID, 'g123245'),
        with: {
          matchDetails: true,
        },
      });
const singleMatch = await db.query.matches.findMany({
        where: eq(matches.matchID, 'g123245'),
        with: {
          matchDetails: true,
        },
      });
saying "Property 'matches' does not exist on type '{}'"
AS
Andrii Sherman•357d ago
If you can show whole schema file and file where you creating drizzle() I can help
M
mr_pablo•357d ago
M
mr_pablo•357d ago
M
mr_pablo•357d ago
As far as i can tell, im following what is in the docs. I tried using "import * as schema from './schema';" in my index.ts but it broke more of the code :/
AS
Andrii Sherman•357d ago
ok, so I see, that you didn't specify all data for one relation
AS
Andrii Sherman•357d ago
AS
Andrii Sherman•357d ago
AS
Andrii Sherman•357d ago
as you can see you need to specify fields that will be connected for one relation
M
mr_pablo•357d ago
OK, so there is no info on what "fields" or "references" should be :/
AS
Andrii Sherman•357d ago
what fields from tableA should be connected to field in tableB in the above example invitedBy is connected to id
M
mr_pablo•357d ago
how do i include all fields? 
export const matchesRelations = relations(matches, ({ one, many }) => ({
  matchDetails: one(matchesDetails, {
    fields: [matchesDetails.matchID],
    references: [matchesDetails.matchID],
  }),
}));
export const matchesRelations = relations(matches, ({ one, many }) => ({
  matchDetails: one(matchesDetails, {
    fields: [matchesDetails.matchID],
    references: [matchesDetails.matchID],
  }),
}));
gives me an error now Type 'MySqlVarChar<{ tableName: "_matchDetails"; enumValues: [string, ...string[]]; name: "matchID"; data: string; driverParam: string | number; hasDefault: false; notNull: true; }>' is not assignable to type 'AnyColumn<{ tableName: "matches"; }>'. The types of '.config.tableName' are incompatible between these types. Type '"_matchDetails"' is not assignable to type '"_matches"'.ts(2322) this simply doesn't work, no matter what i put in the "fields" and"references" array If i do 
export const matchesRelations = relations(matches, ({ one, many }) => ({
  matchDetails: one(matchesDetails, {
    fields: [matchesDetails.matchID],
    references: [matches.matchID],
  }),
}));
export const matchesRelations = relations(matches, ({ one, many }) => ({
  matchDetails: one(matchesDetails, {
    fields: [matchesDetails.matchID],
    references: [matches.matchID],
  }),
}));
I get another error for "references"
Type 'MySqlVarChar<{ tableName: "_matches"; enumValues: [string, ...string[]]; name: "matchID"; data: string; driverParam: string | number; hasDefault: false; notNull: true; }>' is not assignable to type 'AnyColumn<{ tableName: "matchDetails"; }>'. The types of '.config.tableName' are incompatible between these types. Type '"_matches"' is not assignable to type '"_matchDetails"'.ts(2322)
I even just tried the exact code from the docs, and i get the same error
M
mr_pablo•357d ago
M
mr_pablo•357d ago
(apologies for the questions, but i really need this to work, as it's imperative to our project, as all other ORMs have failed me so far) OK, I think i had it the wrong way around, this now just gives the one error still though on the "fields" 
export const matchesRelations = relations(matches, ({ one, many }) => ({
  matchesDetails: one(matches, {
    fields: [matchesDetails.matchID],
    references: [matches.matchID],
  }),
}));
export const matchesRelations = relations(matches, ({ one, many }) => ({
  matchesDetails: one(matches, {
    fields: [matchesDetails.matchID],
    references: [matches.matchID],
  }),
}));
So, i can get rid of the error in the relation, but using "fields: [matches.matchID]," but no idea if thats correct at all? However, I still have the original error in my index.ts, "Property 'matches' does not exist on type '{}'"
AS
Andrii Sherman•357d ago
make sure you provide relations object to drizzle() would suggest to do just import all, so you won't forget anything 
import * as schema from "./db/schema"
const db = drizzle(connection, { schema });
import * as schema from "./db/schema"
const db = drizzle(connection, { schema });
and then if you want to get any table from that import 
const { matches, matchesDetails } = schema
const { matches, matchesDetails } = schema
and use it in queries
M
mr_pablo•357d ago
ok, but i still have that error "Property 'matches' does not exist on type '{}'" and no idea if my relations schema thing is correct 😦
AS
Andrii Sherman•357d ago
even when you provided relations to drizzle()?
M
mr_pablo•357d ago
yea see my index.ts file
AS
Andrii Sherman•357d ago
in your index ts you didn't do it that's why I'm asking
M
mr_pablo•357d ago
i have "const db = drizzle(connection, { schema: { ...matches, ...matchesDetails } });" :/
AS
Andrii Sherman•357d ago
and you need to provide matchesRelations as well
M
mr_pablo•357d ago
😩
AS
Andrii Sherman•357d ago
I would suggest to use this
AS
Andrii Sherman•357d ago
as it states in docs you need to provide both tables and relations
AS
Andrii Sherman•357d ago
basically whole schema you have
M
mr_pablo•357d ago
const db = drizzle(connection, { schema });
const { matches, matchesDetails } = schema;

await migrate(db, { migrationsFolder: './migrations-folder' });
const db = drizzle(connection, { schema });
const { matches, matchesDetails } = schema;

await migrate(db, { migrationsFolder: './migrations-folder' });
but now i have an error on my migrate command
Argument of type 'MySql2Database<typeof import("/Users/paulcanning/Programming/Work/rds-test/db/schema")>' is not assignable to parameter of type 'MySql2Database'. The types of '_.schema' are incompatible between these types. Type 'ExtractTablesWithRelations<typeof import("/Users/paulcanning/Programming/Work/rds-test/db/schema")> | undefined' is not assignable to type 'ExtractTablesWithRelations<Record<string, never>> | undefined'. Type 'ExtractTablesWithRelations<typeof import("/Users/paulcanning/Programming/Work/rds-test/db/schema")>' is not assignable to type 'ExtractTablesWithRelations<Record<string, never>>'.ts(2345)
AS
Andrii Sherman•357d ago
this one is known issue. Already have a PR for that: https://github.com/drizzle-team/drizzle-orm/pull/601 Let me check this PR now and push to beta, so it will unblock you
GitHub
Make migrators accept databases drizzle'd with schemas by mastondzn...
Fixes case where you specify your schemas when you use drizzle(), and migrate() wont accept the database type. Screenshot of current behaviour:
M
mr_pablo•357d ago
thank you
AS
Andrii Sherman•357d ago
Should be there soon but without migrate, query should work now
M
mr_pablo•357d ago
appreciate your help on this. I was screwed if i couldnt get this working. Just hope I've done this relation schema correct! does this look right for a one-to-one? 
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matches, {
    fields: [matches.matchID],
    references: [matches.matchID],
  }),
}));
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matches, {
    fields: [matches.matchID],
    references: [matches.matchID],
  }),
}));
"matches" is the main table, "matchesDetails" is the 2nd table, which has a foreign key on it
AS
Andrii Sherman•357d ago
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matches, {
    fields: [matches.matchID],
    references: [matchesDetails.matchID],
  }),
}));
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matches, {
    fields: [matches.matchID],
    references: [matchesDetails.matchID],
  }),
}));
should be like this you need to connect 1 table to another just changed to matchesDetails in references
M
mr_pablo•357d ago
that errors for me
Type 'MySqlVarChar<{ tableName: "_matchDetails"; name: "matchID"; data: string; driverParam: string | number; enumValues: [string, ...string[]]; notNull: false; hasDefault: false; }>' is not assignable to type 'AnyColumn<{ tableName: "matches"; }>'. The types of '.config.tableName' are incompatible between these types. Type '"_matchDetails"' is not assignable to type '"_matches"'.ts(2322)
AS
Andrii Sherman•357d ago
I guess you need to make your schema design in a bit other way, let me send you it
M
mr_pablo•357d ago
👍 wouldn't surprise me if i got it wrong hmm, i tried the code from the docs again 
import { pgTable, serial, text, integer, jsonb } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name'),
});
 
export const usersRelations = relations(users, ({ one, many }) => ({
    profileInfo: one(users, {
        fields: [profileInfo.userId],
        references: [users.id],
    }),
}));
 
export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});
import { pgTable, serial, text, integer, jsonb } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name'),
});
 
export const usersRelations = relations(users, ({ one, many }) => ({
    profileInfo: one(users, {
        fields: [profileInfo.userId],
        references: [users.id],
    }),
}));
 
export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});
and this gives me that same kind of error on the relation :/
AS
Andrii Sherman•357d ago
oh I see now docs are wrong for exact this example 
export const usersRelations = relations(users, ({ one, many }) => ({
    // You need to do one not to user, but to another table you want to connect
    profileInfo: one(profileInfo, {
        fields: [users.id],
        references: [profileInfo.u],
    }),
}));
export const usersRelations = relations(users, ({ one, many }) => ({
    // You need to do one not to user, but to another table you want to connect
    profileInfo: one(profileInfo, {
        fields: [users.id],
        references: [profileInfo.u],
    }),
}));
try this one we will fix the docs and explain this part more thanks a lot for your feedback and the issue you've found same for you example you just need to do 
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matchesDetails, {
    fields: [matches.matchID],
    references: [matchesDetails.matchID],
  }),
}));
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matchesDetails, {
    fields: [matches.matchID],
    references: [matchesDetails.matchID],
  }),
}));
this one won't give any errors But I still would suggest to make your schema something like this(by adding ids fields) 
export const matches = mysqlTable("_matches", {
  id: bigint("matchID", { mode: "number" }).primaryKey().autoincrement(),
  competitionID: int("competitionID"),
  seasonID: int("sessionID"),
  kickOffUTC: datetime("kickOffUTC"),
  groupName: varchar("groupName", { length: 20 }),
  matchDay: varchar("matchDay", { length: 20 }),
  matchType: varchar("matchType", { length: 20 }),
  leg: varchar("leg", { length: 20 }),
  matchWinnerID: varchar("matchWinnerID", { length: 20 }),
  gameWinnerID: varchar("gameWinnerID", { length: 20 }),
  gameWinnerType: varchar("gameWinnerType", { length: 20 }),
  legWinnerTypeID: varchar("legWinnerTypeID", { length: 20 }),
  period: varchar("period", { length: 20 }),
  roundNumber: varchar("roundNumber", { length: 20 }),
  roundType: varchar("roundType", { length: 20 }),
  venue: varchar("venue", { length: 20 }),
  venueCity: varchar("venueCity", { length: 20 }),
  refereeName: varchar("refereeName", { length: 20 }),
  homeTeamID: varchar("homeTeamID", { length: 20 }),
  awayTeamID: varchar("awayTeamID", { length: 20 }),
  createAt: timestamp("createdAt"),
  updatedAt: timestamp("updatedAt"),
});

export const matchesDetails = mysqlTable("_matchDetails", {
  id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
  matchID: bigint("matchID", { mode: "number" }).references(() => matches.id),
  kickOffUTC: datetime("kickOffUTC"),
  period: varchar("period", { length: 20 }),
  weather: varchar("weather", { length: 50 }),
  attendance: int("attendance"),
  awayAttendance: int("awayAttendance"),
  winner: varchar("winner", { length: 20 }),
  resultType: varchar("resultType", { length: 50 }),
  refereeName: varchar("refereeName", { length: 50 }),
  matchTime: int("matchTime"),
  createdAt: timestamp("createdAt"),
  updatedAt: timestamp("updatedAt"),
});
export const matches = mysqlTable("_matches", {
  id: bigint("matchID", { mode: "number" }).primaryKey().autoincrement(),
  competitionID: int("competitionID"),
  seasonID: int("sessionID"),
  kickOffUTC: datetime("kickOffUTC"),
  groupName: varchar("groupName", { length: 20 }),
  matchDay: varchar("matchDay", { length: 20 }),
  matchType: varchar("matchType", { length: 20 }),
  leg: varchar("leg", { length: 20 }),
  matchWinnerID: varchar("matchWinnerID", { length: 20 }),
  gameWinnerID: varchar("gameWinnerID", { length: 20 }),
  gameWinnerType: varchar("gameWinnerType", { length: 20 }),
  legWinnerTypeID: varchar("legWinnerTypeID", { length: 20 }),
  period: varchar("period", { length: 20 }),
  roundNumber: varchar("roundNumber", { length: 20 }),
  roundType: varchar("roundType", { length: 20 }),
  venue: varchar("venue", { length: 20 }),
  venueCity: varchar("venueCity", { length: 20 }),
  refereeName: varchar("refereeName", { length: 20 }),
  homeTeamID: varchar("homeTeamID", { length: 20 }),
  awayTeamID: varchar("awayTeamID", { length: 20 }),
  createAt: timestamp("createdAt"),
  updatedAt: timestamp("updatedAt"),
});

export const matchesDetails = mysqlTable("_matchDetails", {
  id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
  matchID: bigint("matchID", { mode: "number" }).references(() => matches.id),
  kickOffUTC: datetime("kickOffUTC"),
  period: varchar("period", { length: 20 }),
  weather: varchar("weather", { length: 50 }),
  attendance: int("attendance"),
  awayAttendance: int("awayAttendance"),
  winner: varchar("winner", { length: 20 }),
  resultType: varchar("resultType", { length: 50 }),
  refereeName: varchar("refereeName", { length: 50 }),
  matchTime: int("matchTime"),
  createdAt: timestamp("createdAt"),
  updatedAt: timestamp("updatedAt"),
});
and relations 
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matchesDetails, {
    fields: [matches.id],
    references: [matchesDetails.matchID],
  }),
}));
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matchesDetails, {
    fields: [matches.id],
    references: [matchesDetails.matchID],
  }),
}));
M
mr_pablo•357d ago
Yea, I originally wanted to use the matchesDetails primary key as the foreign key. I read that you can do that, but I might just use an extra field like you say the matchID's are known IDs, as they come from another system, hence no auto increment either and being varchars 🙂
M
mr_pablo•357d ago
quick one, do you know how i stop these TS errors when I run tsc? I have excluded node_modules in my tsconfig but it doesnt do anything, i also tried skipLibCheck
M
mr_pablo•357d ago
BTW that relation now works great and brings back the data successfully, thank you!!
AS
Andrii Sherman•357d ago
Great to hear! I also merged a PR to drizzle-orm@beta with migrator fixes you can install this tag and try it out
M
mr_pablo•357d ago
🙏
Want results from more Discord servers?
Add your server
More Posts
How to transform to camelCase with json_agg()?As topic. I can't find any example on how to go about this. The closest I can find is https://orm.drHow to use select?I have a SQL query ``` SELECT row_to_json(departments.*) as department, row_to_jsonCount in relational queriesHow do I count in relational queries? For example if i'm querying db.query.posts.findMany() and wantCreate a type of VARCHAR[]I want to create a type for my column of `VARCHAR[]` but when I used Drizzle-Kit to generate it, I gSelect with relationIs there a way to get relations when using `db.select().from(table)`I can't get the `db.query.table.Any plan to support ClickHouse db?Any plan to support ClickHouse db? https://clickhouse.com/`where` inside relational queries `with` does not workHi there! I'm new to drizzle and tried out the relational queries. I want to do a nested `where` rIssue with 'insert on conflict do update where'I am using db.insert(lastHeaterMetricsTable) .values(heaterEntityWithMaxTime) Duplicate relations when using `with`I'm running into a problem where some duplicate values are being returned through a relation with `wUsing BIN_TO_UUID / UUID_TO_BINI’m trying to understand the best way to use `BIN_TO_UUID` and `UUID_TO_BIN` (MySQL). The below is drizzle-kit drop config file does not existI have a `/foo/drizzle.config.ts` file as suggested in the docs (https://orm.drizzle.team/kit-docs/Related object is not typed correctlyI have the following schema ``` export const menus = pgTable('menus', { id: uuid('id').primaryKey(Custom vector type with pgvectorI'm trying to use the `pgvector` extension with a custom type but am running into an issue with the Missing 'with' clause additional operators (where, limit, offset, etc)Have been digging into the docs and the latest update. ❣️ In the docs (https://orm.drizzle.team/ddrizzle-zod type errors with latest versionsI updated all drizzle deps to latest and having type errors when using drizzle-zodI am confused on how the new relational queries worksIn the docs I see the following ``` import { pgTable, serial, text, integer, boolean } from 'drizzleis not assignable to type 'DrizzleD1Database'Hello This last release was amazing. Making joins simple was the missing piece for me. However, IDrizzle kit generate gives error after upgradeI have updated drizzle-orm to 0.26.0 and drizzle-kit to 018.0. I have defined relations according toIntrospection error with pgHello, I am trying to introspect my already created DB but when I run the cli command I get: ``` > dHow to declare PostgreSQL extensions/plugin?Reference: https://www.prisma.io/docs/concepts/components/prisma-schema/postgresql-extensions Is th