I am trying to create a schema for my application, for context my application is a sports match manager, so an admin can create many matches, users can register for those matches and at the day of the match the system will automatically assign random teams to every registered user, users can then click on each match and find out the teams, and who plays for which team.

here is my schema design

import { integer, pgTable, primaryKey, text, timestamp, uuid, varchar } from "drizzle-orm/pg-core";

export const players = pgTable('players', {
  id: uuid('id').primaryKey().defaultRandom().notNull(),
  name: text('name').notNull(),
  playerImg: varchar('player_img', { length: 256 }),
  goals: integer('goals').notNull().default(0),
  assists: integer('assists').notNull().default(0),
});

export const matches = pgTable('matches', {
  id: uuid('id').primaryKey().defaultRandom().notNull(),
  location: text('location').notNull(),
  matchDate: timestamp('match_date', { withTimezone: true }).notNull(),
  team1Id: integer('team1_id').references(() => teams.id).notNull(),
  team2Id: integer('team2_id').references(() => teams.id).notNull(),
});

export const teams = pgTable('teams', {
  id: uuid('id').primaryKey().defaultRandom().notNull(),
});

export const playerRegistrations = pgTable('player_registrations', {
  playerId: uuid('player_id').notNull().references(() => players.id),
  teamId: uuid('team_id').notNull().references(() => teams.id),
  matchId: uuid('match_id').notNull().references(() => matches.id),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.playerId, table.teamId, table.matchId] }),
  };
});

I am not sure if i have the correct schema for my application, however i am getting this error when i try migrate the database.

No config path provided, using default path Reading config file 'D:EducationComputerScienceWebDevPortfolioProjectsfootball-managerdrizzle.config.ts' error: column "player_id" cannot be cast automatically to type uuid at D:EducationComputerScienceWebDevPortfolioProjectsfootball-managernode_modulesdrizzle-kitbin.cjs:43518:21 at process.processTicksAndRejections (node:internal/process/task_queues:95:5) at async PgPostgres.query (D:EducationComputerScienceWebDevPortfolioProjectsfootball-managernode_modulesdrizzle-kitbin.cjs:62584:21) at async Command.<anonymous> (D:EducationComputerScienceWebDevPortfolioProjectsfootball-managernode_modulesdrizzle-kitbin.cjs:66267:9) { length: 183, severity: 'ERROR', code: '42804', detail: undefined, hint: 'You might need to specify "USING player_id::uuid".', position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'tablecmds.c', line: '12336', routine: 'ATPrepAlterColumnType' }

I tried changing UUID into serial but serial had the same error as UUID.

I tried using integer and then use autoincrement() but apparently autoincrement() does not exist as i got type error.