db.query error with relation

Mmr_pablo5/24/2023
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,
        },
      });

saying "Property 'matches' does not exist on type '{}'"
ASAndrii Sherman5/24/2023
If you can show whole schema file and file where you creating drizzle() I can help
Mmr_pablo5/24/2023
Mmr_pablo5/24/2023
Mmr_pablo5/24/2023
As far as i can tell, im following what is in the docs.
Mmr_pablo5/24/2023
I tried using "import * as schema from './schema';" in my index.ts but it broke more of the code :/
ASAndrii Sherman5/24/2023
ok, so I see, that you didn't specify all data for one relation
ASAndrii Sherman5/24/2023
ASAndrii Sherman5/24/2023
as you can see you need to specify fields that will be connected for one relation
Mmr_pablo5/24/2023
OK, so there is no info on what "fields" or "references" should be :/
ASAndrii Sherman5/24/2023
what fields from tableA should be connected to field in tableB
in the above example invitedBy is connected to id
Mmr_pablo5/24/2023
how do i include all fields?
Mmr_pablo5/24/2023
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)
Mmr_pablo5/24/2023
this simply doesn't work, no matter what i put in the "fields" and"references" array
Mmr_pablo5/24/2023
If i do

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)
Mmr_pablo5/24/2023
I even just tried the exact code from the docs, and i get the same error
Mmr_pablo5/24/2023
Mmr_pablo5/24/2023
(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)
Mmr_pablo5/24/2023
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],
  }),
}));
Mmr_pablo5/24/2023
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 '{}'"
ASAndrii Sherman5/24/2023
make sure you provide relations object to drizzle()
ASAndrii Sherman5/24/2023
would suggest to do just import all, so you won't forget anything

import * as schema from "./db/schema"
const db = drizzle(connection, { schema });
ASAndrii Sherman5/24/2023
and then if you want to get any table from that import
const { matches, matchesDetails } = schema
ASAndrii Sherman5/24/2023
and use it in queries
Mmr_pablo5/24/2023
ok, but i still have that error "Property 'matches' does not exist on type '{}'"
Mmr_pablo5/24/2023
and no idea if my relations schema thing is correct 😦
ASAndrii Sherman5/24/2023
even when you provided relations to drizzle()?
Mmr_pablo5/24/2023
yea
Mmr_pablo5/24/2023
see my index.ts file
ASAndrii Sherman5/24/2023
in your index ts
ASAndrii Sherman5/24/2023
you didn't do it
ASAndrii Sherman5/24/2023
that's why I'm asking
Mmr_pablo5/24/2023
i have "const db = drizzle(connection, { schema: { ...matches, ...matchesDetails } });"
Mmr_pablo5/24/2023
:/
ASAndrii Sherman5/24/2023
and you need to provide matchesRelations as well
Mmr_pablo5/24/2023
😩
ASAndrii Sherman5/24/2023
I would suggest to use this
ASAndrii Sherman5/24/2023
as it states in docs you need to provide both tables and relations
ASAndrii Sherman5/24/2023
basically whole schema you have
Mmr_pablo5/24/2023
const db = drizzle(connection, { schema });
const { matches, matchesDetails } = schema;

await migrate(db, { migrationsFolder: './migrations-folder' });
Mmr_pablo5/24/2023
but now i have an error on my migrate command
Mmr_pablo5/24/2023
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)
ASAndrii Sherman5/24/2023
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
Mmr_pablo5/24/2023
thank you
ASAndrii Sherman5/24/2023
Should be there soon
ASAndrii Sherman5/24/2023
but without migrate, query should work now
Mmr_pablo5/24/2023
appreciate your help on this. I was screwed if i couldnt get this working. Just hope I've done this relation schema correct!
Mmr_pablo5/24/2023
does this look right for a one-to-one?

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
ASAndrii Sherman5/24/2023
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matches, {
    fields: [matches.matchID],
    references: [matchesDetails.matchID],
  }),
}));
ASAndrii Sherman5/24/2023
should be like this
ASAndrii Sherman5/24/2023
you need to connect 1 table to another
ASAndrii Sherman5/24/2023
just changed to matchesDetails in references
Mmr_pablo5/24/2023
that errors for me
Mmr_pablo5/24/2023
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)
ASAndrii Sherman5/24/2023
I guess you need to make your schema design in a bit other way, let me send you it
Mmr_pablo5/24/2023
👍
Mmr_pablo5/24/2023
wouldn't surprise me if i got it wrong
Mmr_pablo5/24/2023
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"),
});


and this gives me that same kind of error on the relation :/
ASAndrii Sherman5/24/2023
oh I see now
ASAndrii Sherman5/24/2023
docs are wrong for exact this example
ASAndrii Sherman5/24/2023
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],
    }),
}));
ASAndrii Sherman5/24/2023
try this one
ASAndrii Sherman5/24/2023
we will fix the docs and explain this part more
ASAndrii Sherman5/24/2023
thanks a lot for your feedback and the issue you've found
ASAndrii Sherman5/24/2023
same for you example
ASAndrii Sherman5/24/2023
you just need to do
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matchesDetails, {
    fields: [matches.matchID],
    references: [matchesDetails.matchID],
  }),
}));
ASAndrii Sherman5/24/2023
this one won't give any errors
ASAndrii Sherman5/24/2023
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"),
});
ASAndrii Sherman5/24/2023
and relations
export const matchesRelations = relations(matches, ({ one }) => ({
  matchesDetails: one(matchesDetails, {
    fields: [matches.id],
    references: [matchesDetails.matchID],
  }),
}));
Mmr_pablo5/24/2023
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
Mmr_pablo5/24/2023
the matchID's are known IDs, as they come from another system, hence no auto increment either and being varchars 🙂
Mmr_pablo5/24/2023
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
Mmr_pablo5/24/2023
BTW that relation now works great and brings back the data successfully, thank you!!
ASAndrii Sherman5/24/2023
Great to hear!
ASAndrii Sherman5/24/2023
I also merged a PR to drizzle-orm@beta with migrator fixes
ASAndrii Sherman5/24/2023
you can install this tag and try it out
Mmr_pablo5/24/2023
🙏