Drizzle Team•2y ago

Are relational queries supported on mysql?

I'm new to this ORM and I'm having trouble getting relational queries to work. Normal db.select().from(exTable) works fine, but when I try db.query.exTable.findMany() I get a warning on VSCode: Property 'exTable' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?">'.ts(2339)

19 Replies

xamarot•2y ago

For relational queries to work, you need to supply your client with your schema like this

import * as schema from './schema';
import { drizzle } from 'drizzle-orm/...';
 
const db = drizzle(client, { schema });

import * as schema from './schema';
import { drizzle } from 'drizzle-orm/...';
 
const db = drizzle(client, { schema });

SebastiánOP•2y ago

Thanks, now findFirst and findMany are working, but for some reason when I include a with: { exRelation: true} , I get "You have an error in your SQL syntax;"

xamarot•2y ago

how does the full query look?

SebastiánOP•2y ago

tables are costume and gender, relation one to one for simplicity and testing const costumeId = 1

const result = await db.query.costume.findMany({
        with: {
            myGender: true
        },
        where: eq(costume.id, costumeId)
    })

xamarot•2y ago

and "myGender" is a column on costume table? i am not entirely sure but i think "with" is only used for related data such as a post having comments, or such things

SebastiánOP•2y ago

nope, just what I called the field inside the relationship, but also tested is by giving the name "gender", same error ok thanks, I'll try making it work on a fresh project tomorrrow following examples on docs step by step

xamarot•2y ago

from docs

const posts = await db.query.posts.findMany({
    with: {
        comments: true,
    },
});

const posts = await db.query.posts.findMany({
    with: {
        comments: true,
    },
});

how does your schema structure look? what is it you want to retrieve? because it looks to me like "myGender" is basically another variable? if thats the case you could do

const result = await db.query.costume.findMany({
        where: and(eq(costume.id, costumeId), eq(costume.gender, myGender))
    })

const result = await db.query.costume.findMany({
        where: and(eq(costume.id, costumeId), eq(costume.gender, myGender))
    })

SebastiánOP•2y ago

it's actually pretty simple, surprised it's not working

export const costume = mysqlTable("costume", {
  id: int("id").primaryKey().autoincrement(),
  imageSrc: varchar("image_src", {length: 255}),
  leased: boolean("leased").default(false),
  name: varchar("name", {length: 255}).notNull(),
  sizeId: int("size_id").references(() => size.id).notNull(),
  genderId: int("gender_id").references(() => gender.id).notNull(),
  seriesId: int("series_id").references(() => series.id).notNull(),
  studioId: int("studio_id").references(() => studio.id).notNull()
})
export const costumeRelations = relations(costume, ({one}) => ({
  gender: one(gender, {
    fields: [costume.genderId],
    references: [gender.id]
  })
}))

export const gender = mysqlTable("gender", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", {length: 255}).notNull()
})

and then on the API

async function getCostume(query: any) {

    const costumeId = query.id
    const result = await db.query.costume.findMany({
        with: {
            gender: true
        }
        ,
        where: eq(costume.id, costumeId)
    })
    return result
    
}

renamed "myGender" to just "gender"

xamarot•2y ago

hm i don't think you want to use "with" here. Since "gender" can not be null, "with" does not make sense since it will always be true, and I'm not sure but I don't think it works for "one"-relations. what data is it you want to retrieve? all costumes for your gender?

SebastiánOP•2y ago

I just want the JSON response to not just retrieve, gender_id: 3, but gender : { ...properties } as a nested object but I'll make it work tomorrow, thanks for your time maybe I'm doing something dumb cause it's too late

xamarot•2y ago

aight, just ask if theres anything else. good luck!

SebastiánOP•2y ago

Hey I'm back, just added 2 simple tables: Country and City, country has many cities. I thought I'd also mention the environment I'm working on: OS: Windows 10 Framework: Nuxt3 Database: phpMyAdmin 5.2.0 on local via Xampp 8.1.12 Mariadb Schema

export const country = mysqlTable("country", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }).notNull(),
  code: varchar("code", { length: 255 }).notNull()
})

export const countryRelations = relations(country, ({many}) => ({
  city: many(city)
}))

export const city = mysqlTable("city", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }).notNull(),
  countryId: int("country_id").references(() => country.id).notNull()
})

export const cityRelations = relations(city, ({one}) => ({
  country: one(country, {
    fields: [city.countryId],
    references: [country.id]
  })
}))


export type Country = InferModel<typeof country>
export type City = InferModel<typeof city>

API country.get.ts

import { eq } from "drizzle-orm"
import { country } from "../schema/schema"

export default defineEventHandler(async (e) => {
    const result = await db.query.country.findFirst({
        with: {
            city: true
        },
        where: eq(country.id, 3) // Country is Albania, with 74 cities on the Database
    })
    return result
})

Same error from last time:

// http://localhost:3000/api/country

{
  "url": "/api/country",
  "statusCode": 500,
  "statusMessage": "",
  "message": "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json) from (select

country.id, country.name, country.code

, if(cou...' at line 1",
  "stack": "<pre><span class=\"stack internal\">at PromiseConnection.query (C:\\Users\\username\\Documents\\GitHub\\projectname\\node_modules\\.pnpm\\[email protected]\\node_modules\\mysql2\\promise.js:94:22)</span>\n<span class=\"stack internal\">at MySql2PreparedQuery.execute (/C:/Users/username/Documents/GitHub/projectname/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/mysql2/index.mjs:51:37)</span>\n<span class=\"stack internal\">at QueryPromise.execute (/C:/Users/username/Documents/GitHub/projectname/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/session-9628aea0.mjs:1329:31)</span>\n<span class=\"stack internal\">at QueryPromise.then (/C:/Users/username/Documents/GitHub/projectname/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/alias-3e926a50.mjs:636:21)</span>\n<span class=\"stack internal\">at process.processTicksAndRejections (node:internal/process/task_queues:95:5)</span></pre>"
}

Works without "with":

export default defineEventHandler(async (e) => {
  const result = await db.query.country.findFirst({
      with: {
          
      },
      where: eq(country.id, 3) // Albania populated with 74 cities
  })
  return result
})

// http://localhost:3000/api/country

{
  "id": 3,
  "name": "Albania",
  "code": "AL"
}

Also notices drizzle-kit push:mysql failed if I used serialas the type for id on any table So my guess is the database running on xampp is not compatible with some functinality? got the logger running and theese are the queries generated: using "with" "city" doesn't work

select id, name, code, cast(city as json) from (select country.id, country.name, country.code, if(count(country_city.country_id) = 0, '[]', json_arrayagg(json_array(country_city.id, country_city.name, country_city.country_id))) as city from country left join city country_city on country.id = country_city.country_id group by country.id) country where country.id = ? -- params: [3]

not using "with" works select id, name, code from country where country.id = ? -- params: [3] this works

db.select({
        country,
        city
    }).from(country)
    .leftJoin(city, eq(city.countryId, country.id))
    .where(eq(country.id, 3))

and generates

select country.id, country.name, country.code, city.id, city.name, city.country_id from country left join city on city.country_id = country.id where country.id = ? -- params: [3]

iukea•2y ago

No joke what is above looks like a spaghetti dish on my phone Interesting

SebastiánOP•2y ago

I'll test the same code on Planetscale later, if it works it means xampp is partially not supported by default well, planetscale works flawlessly, I guess you shouldn't use xampp/phpMyAdmin for local development

Asseater Peter•2y ago

Hi Sebastian, I've actually dealt with this exact thing not too long ago, feel free to DM me if you need any more help

SebastiánOP•2y ago

Hi, I'm just gonna work directly on planetscale for the time being. But I'm curious about what mysql tool should I use on Windows for local development

Asseater Peter•2y ago

I personally just have a dev branch on PlanetScale so I'm not sure ngl However, I did solve most of the relational querying problems using the query syntax, you might wanna give that a look (I didn't read like 90% of this thread, maybe you talked about that already idk)

SebastiánOP•2y ago

TLDR: Xampp/phpmyAdmin didn't recognize some SQL generated code (Serial column declaration, some JSON inside select). But I already tested all code on PlanetScale and everything works. I guess I'll do the same and develop on a dev branch as well

Asseater Peter•17mo ago

Yeah that'd be my suggestion. I also have a MySQL server running on my NAS that works fine with this, so it could just be an issue with phpmyAdmin btw, don't forget to mark the thread as done for my OCD's sake

Gaming

Programming

Are relational queries supported on mysql?