query with one to many (postgres + foreign keys)

I have the following schema:

export const accounts = pgTable("accounts", {
  id: serial("id").primaryKey(),
  address: text("address").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow(),
  points: integer("points").default(0).notNull(),
});

export const nfts = pgTable("nfts", {
    id: serial("id").primaryKey(),
    contractAddress: text("contract_address").notNull(),
    tokenId: integer("token_id").notNull(),
    accountId: integer("account_id").references(() => accounts.id)
});

export const accounts = pgTable("accounts", {
  id: serial("id").primaryKey(),
  address: text("address").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow(),
  points: integer("points").default(0).notNull(),
});

export const nfts = pgTable("nfts", {
    id: serial("id").primaryKey(),
    contractAddress: text("contract_address").notNull(),
    tokenId: integer("token_id").notNull(),
    accountId: integer("account_id").references(() => accounts.id)
});

I am trying to query for a specific accounts and get all associated nfts with:

db.query.accounts.findFirst({
  where: eq(accounts.address, "0xDEAD...BEEF"),
  with: { nfts: true },
});

db.query.accounts.findFirst({
  where: eq(accounts.address, "0xDEAD...BEEF"),
  with: { nfts: true },
});

This results in the error: Invalid drizzle query. Do I have to use drizzles relations to associate nfts to accounts instead of just foreign keys in order to use db.query?

1 Reply

shot•3mo ago

I have tried to use relations as well but still can't seem to query with nfts:

import { serial, text, timestamp, integer, pgTable, numeric } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm"

export const accounts = pgTable("accounts", {
  id: serial("id").primaryKey(),
  address: text("address").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow(),
  points: integer("points").default(0).notNull(),
});

export const accountsRelations = relations(accounts, ({ many }) => ({
    nfts: many(nfts),
}));

export const nfts = pgTable("nfts", {
    id: serial("id").primaryKey(),
    contractAddress: text("contract_address").notNull(),
    tokenId: integer("token_id").notNull(),
    accountId: integer("account_id"),
});

export const nftsRelations = relations(nfts, ({ one }) => ({
    account: one(accounts, {
        fields: [nfts.accountId],
        references: [accounts.id],
    } ),
}));

import { serial, text, timestamp, integer, pgTable, numeric } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm"

export const accounts = pgTable("accounts", {
  id: serial("id").primaryKey(),
  address: text("address").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow(),
  points: integer("points").default(0).notNull(),
});

export const accountsRelations = relations(accounts, ({ many }) => ({
    nfts: many(nfts),
}));

export const nfts = pgTable("nfts", {
    id: serial("id").primaryKey(),
    contractAddress: text("contract_address").notNull(),
    tokenId: integer("token_id").notNull(),
    accountId: integer("account_id"),
});

export const nftsRelations = relations(nfts, ({ one }) => ({
    account: one(accounts, {
        fields: [nfts.accountId],
        references: [accounts.id],
    } ),
}));

db.query.accounts.findMany({
  where: eq(accounts.address, "0x00...00"),
  with: {
    nfts: true,
  },
});

db.query.accounts.findMany({
  where: eq(accounts.address, "0x00...00"),
  with: {
    nfts: true,
  },
});

This query errors on nft:

Type '{ nfts: true; }' is not assignable to type '{ nft?: true | { columns?: { id?: boolean | undefined; contractAddress?: boolean | undefined; tokenId?: boolean | undefined; accountId?: boolean | undefined; } | undefined; with?: { accounts?: true | ... 1 more ... | undefined; account?: true | ... 1 more ... | undefined; } | undefined; extras?: Record<...> | ... 1 ...'.
  Object literal may only specify known properties, but 'nfts' does not exist in type '{ nft?: true | { columns?: { id?: boolean | undefined; contractAddress?: boolean | undefined; tokenId?: boolean | undefined; accountId?: boolean | undefined; } | undefined; with?: { accounts?: true | ... 1 more ... | undefined; account?: true | ... 1 more ... | undefined; } | undefined; extras?: Record<...> | ... 1 ...'. Did you mean to write 'nft'?

Type '{ nfts: true; }' is not assignable to type '{ nft?: true | { columns?: { id?: boolean | undefined; contractAddress?: boolean | undefined; tokenId?: boolean | undefined; accountId?: boolean | undefined; } | undefined; with?: { accounts?: true | ... 1 more ... | undefined; account?: true | ... 1 more ... | undefined; } | undefined; extras?: Record<...> | ... 1 ...'.
  Object literal may only specify known properties, but 'nfts' does not exist in type '{ nft?: true | { columns?: { id?: boolean | undefined; contractAddress?: boolean | undefined; tokenId?: boolean | undefined; accountId?: boolean | undefined; } | undefined; with?: { accounts?: true | ... 1 more ... | undefined; account?: true | ... 1 more ... | undefined; } | undefined; extras?: Record<...> | ... 1 ...'. Did you mean to write 'nft'?

If I change it to nft (singular) it will return one single nft belonging to the account... This seems to be working in my product code but not in drizzle studio.

Gaming

Programming

query with one to many (postgres + foreign keys)