Drizzle Team•2y ago

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 '{}'"

39 Replies

Andrii Sherman•2y ago

If you can show whole schema file and file where you creating drizzle() I can help

mr_pabloOP•2y ago

schema.ts

mr_pabloOP•2y ago

index.ts

mr_pabloOP•2y 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 :/

Andrii Sherman•2y ago

ok, so I see, that you didn't specify all data for one relation

Andrii Sherman•2y ago

https://orm.drizzle.team/docs/rqb#one-to-one

Relational queries – DrizzleORM

Drizzle ORM | %s

Andrii Sherman•2y ago

Andrii Sherman•2y ago

as you can see you need to specify fields that will be connected for one relation

mr_pabloOP•2y ago

OK, so there is no info on what "fields" or "references" should be :/

Andrii Sherman•2y ago

what fields from tableA should be connected to field in tableB in the above example invitedBy is connected to id

mr_pabloOP•2y 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

mr_pabloOP•2y ago

mr_pabloOP•2y 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 '{}'"

Andrii Sherman•2y 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

mr_pabloOP•2y 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 😦

Andrii Sherman•2y ago

even when you provided relations to drizzle()?

mr_pabloOP•2y ago

yea see my index.ts file

Andrii Sherman•2y ago

in your index ts you didn't do it that's why I'm asking

mr_pabloOP•2y ago

i have "const db = drizzle(connection, { schema: { ...matches, ...matchesDetails } });" :/

Andrii Sherman•2y ago

and you need to provide matchesRelations as well

mr_pabloOP•2y ago

😩

Andrii Sherman•2y ago

I would suggest to use this

Andrii Sherman•2y ago

as it states in docs you need to provide both tables and relations

Andrii Sherman•2y ago

basically whole schema you have

mr_pabloOP•2y 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)

Andrii Sherman•2y 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:

mr_pabloOP•2y ago

thank you

Andrii Sherman•2y ago

Should be there soon but without migrate, query should work now

mr_pabloOP•2y 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

Andrii Sherman•2y 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

mr_pabloOP•2y 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)

Andrii Sherman•2y ago

I guess you need to make your schema design in a bit other way, let me send you it

mr_pabloOP•2y 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 :/

Andrii Sherman•2y 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],
  }),
}));

mr_pabloOP•2y 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 🙂

mr_pabloOP•2y 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

mr_pabloOP•2y ago

BTW that relation now works great and brings back the data successfully, thank you!!

Andrii Sherman•2y 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

mr_pabloOP•2y ago

🙏

Gaming

Programming

db.query error with relation