Select with relation

Is there a way to get relations when using db.select().from(table)I can't get the db.query.table.findManyto work, so if someone could help me get that setup that would be fine too

Andrii Sherman•358d ago

I can help you with setting up relational quries, what didn't work for you?

Eko•358d ago

Basically whenever I try following the docs I get an error like postgreserror: unrecognized configuration parameter "schema" This is what I had setup

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from 'postgres'
import * as schema from "@/db/migrations/schema"

const connectionString: string | undefined = process.env.DATABASE_URL

const client = postgres(connectionString!, { max: 1 })
const db = drizzle(client, {schema});

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from 'postgres'
import * as schema from "@/db/migrations/schema"

const connectionString: string | undefined = process.env.DATABASE_URL

const client = postgres(connectionString!, { max: 1 })
const db = drizzle(client, {schema});

And my schema for question looks like this

export const question = pgTable(
  "question",
  {
    id: serial("id").notNull(),
    title: varchar("title", { length: 100 }).notNull(),
    desc: varchar("desc", { length: 100 }),
    createdAt: timestamp("createdAt", {
      precision: 6,
      mode: "string",
    }).defaultNow(),
    option1: varchar("option1", { length: 30 }).notNull(),
    option2: varchar("option2", { length: 30 }).notNull(),
    ownerId: text("ownerId").notNull(),
    image1: text("image1"),
    image2: text("image2"),
    isDeleted: boolean("isDeleted").notNull(),
  },
  (table) => {
    return {
      questionPkey: primaryKey(table.id, table.id),
    };
  }
);

export const questionRelations = relations(question, ({ many })=>({
  votes: many(questionVotes),
  likes: many(questionLikes),
}))

export const question = pgTable(
  "question",
  {
    id: serial("id").notNull(),
    title: varchar("title", { length: 100 }).notNull(),
    desc: varchar("desc", { length: 100 }),
    createdAt: timestamp("createdAt", {
      precision: 6,
      mode: "string",
    }).defaultNow(),
    option1: varchar("option1", { length: 30 }).notNull(),
    option2: varchar("option2", { length: 30 }).notNull(),
    ownerId: text("ownerId").notNull(),
    image1: text("image1"),
    image2: text("image2"),
    isDeleted: boolean("isDeleted").notNull(),
  },
  (table) => {
    return {
      questionPkey: primaryKey(table.id, table.id),
    };
  }
);

export const questionRelations = relations(question, ({ many })=>({
  votes: many(questionVotes),
  likes: many(questionLikes),
}))

Andrii Sherman•358d ago

What version of drizzle orm are you using? 0.26.0?

Eko•358d ago

yes is it possible that I did not properly push my schemas?

Andrii Sherman•358d ago

do you have anything except of table and relations object in your schema file?

Eko•358d ago

I got multiple tables but nothing else

Andrii Sherman•358d ago

oh, it's a bit tough, can't repro locally will dig into it more

Eko•358d ago

I might have fixed it... Ill update in a bit @Andrew Sherman I seem to have had quite a few issues with an outdated config for drizzle now it's been cleared up but one question tho, what is the correct order of drizzle-kit commands and how can I make it automatically push schemas to the db?

Motomo•283d ago

Hey there, I just setup drizzle and got the same error can you point me to a direction on how you fixed it ? I even tried with a schema of a single table and got the same issue trying to run

import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";
import 'dotenv/config'
import path from "path";
import { readdirSync } from "fs";

const connectionString = process.env.DATABASE_URL as string;
const sql = postgres(connectionString, { max: 1 });
const db = drizzle(sql);

async function run() {
    const migrationsFolder = path.join(__dirname, '..', 'drizzle');
    console.log(readdirSync(migrationsFolder))
    await migrate(db, { migrationsFolder });
    process.exit(0);
}

run();

import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";
import 'dotenv/config'
import path from "path";
import { readdirSync } from "fs";

const connectionString = process.env.DATABASE_URL as string;
const sql = postgres(connectionString, { max: 1 });
const db = drizzle(sql);

async function run() {
    const migrationsFolder = path.join(__dirname, '..', 'drizzle');
    console.log(readdirSync(migrationsFolder))
    await migrate(db, { migrationsFolder });
    process.exit(0);
}

run();

So I pushed the schema (this worked correclty) but when trying to run a query in the code got the same issue

@Injectable()
export class PostgresService implements OnModuleInit {
    db: PostgresJsDatabase<typeof schema>;
    constructor(private readonly config: ConfigService) { }

    onModuleInit() {
        console.log(this.config.databaseUrl())
        const client = postgres(this.config.databaseUrl());
        this.db = drizzle(client, { schema });
    }
}

@Injectable()
export class PostgresService implements OnModuleInit {
    db: PostgresJsDatabase<typeof schema>;
    constructor(private readonly config: ConfigService) { }

    onModuleInit() {
        console.log(this.config.databaseUrl())
        const client = postgres(this.config.databaseUrl());
        this.db = drizzle(client, { schema });
    }
}

(I checked that everything was defined correcly (schema path, db url)) Ok stypid mistake on my part I just reused the url of the database from Prisma and they use a ?schema=public instead of ?search_path=public in the Url

West side ⁉•244d ago

so you're saying drizzle uses ?search_path=public? YES THATS WHAT YOU MEAN THANK YOU DUDE HOLY, I SPENT FUCKING HOURS TRYING TO FIX

Drizzle Team Join

The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!

7.3KMembers

View on Discord

Want results from more Discord servers?

Add your server

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` r Issue 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 `w Using 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/d drizzle-zod type errors with latest versionsI updated all drizzle deps to latest and having type errors when using drizzle-zod I am confused on how the new relational queries worksIn the docs I see the following ``` import { pgTable, serial, text, integer, boolean } from 'drizzle is not assignable to type 'DrizzleD1Database'Hello This last release was amazing. Making joins simple was the missing piece for me. However, I Drizzle 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 to Introspection error with pgHello, I am trying to introspect my already created DB but when I run the cli command I get: ``` > d How to declare PostgreSQL extensions/plugin?Reference: https://www.prisma.io/docs/concepts/components/prisma-schema/postgresql-extensions Is th How to consume existing supabase migration?I have a supabase migrations and would like to reuse them as a starter for drizzle. When trying to p Prisma Studio style db explorerHey there! Anyone aware of a prisma studio style db-playground / db-admin-dashboard that works with Incorrect return type findFristHi! I just upgraded to `26.0` and I'm refactoring queries to `findFirst` where needed. But the retu Relations module - or conditionHi there! I have a teams table, an users table, and a team_members table that connects these two. A Are foreign key polyfills for PlanetScale supported by Drizzle?Hi Prisma supports this (although slow). See: https://www.prisma.io/docs/guides/database/planetscal

Gaming

Programming

Select with relation