Fabian B.
Fabian B.
Explore posts from servers
NNuxtNNovuDTDrizzle Team
PostsComments
DTDrizzle Team
Created by PapaFinn on 7/10/2024 in #help
Inner joining and left joining the same table (aliased) causes the return type to be never
Having the exact same error! Did you find a solution for it? It seems that it does work in runtime, so the sql is generated correctly, but the types are never[]. I fixed it by just leftJoining both aliases.
16 replies
DTDrizzle Team
Created by Fabian B. on 2/14/2024 in #help
How do I write a subquery inside select?
Nevermind, I think the most simplest solution works for this case. 
const parent = aliasedTable(jobTypes, "parent");

const jobTypesData = await db
  .select({
    jobType: jobTypes,
    parent: parent,
  })
  .from(jobTypes)
  .innerJoin(parent, eq(jobTypes.parentId, parent.id));
  .where(isNotNull(jobTypes.parentId))
const parent = aliasedTable(jobTypes, "parent");

const jobTypesData = await db
  .select({
    jobType: jobTypes,
    parent: parent,
  })
  .from(jobTypes)
  .innerJoin(parent, eq(jobTypes.parentId, parent.id));
  .where(isNotNull(jobTypes.parentId))
2 replies
DTDrizzle Team
Created by Fabian B. on 1/15/2024 in #help
type "GEOMETRY(POINT)" does not exist
Thanks! This worked 🙂 Not sure why they are there, this is my customType 
import { sql } from 'drizzle-orm'
import { customType } from 'drizzle-orm/pg-core'
// @ts-ignore
import type { Geometry, Point } from 'geojson'
import wkx from 'wkx'

// --- Utilities to work with PostGIS ---

type GeometryOptions = { type?: string; srid?: never } | { type: string; srid: number }

const dataType = (options?: GeometryOptions) => {
    let result = 'GEOMETRY'
    if (options?.type) {
        result += `(${options.type.toUpperCase()}`
        if (options?.srid) {
            result += `,${options.srid}`
        }

        result += ')'
    }

    return result
}

const toDriver = (value: Geometry) => {
    return sql`ST_GeomFromGeoJSON(${JSON.stringify(value)})`
}

const fromDriver = (value: string) => {
    const b = Buffer.from(value, 'hex')
    return wkx.Geometry.parse(b).toGeoJSON({ shortCrs: true }) as Geometry
}

const geometry = (name: string, options?: GeometryOptions) =>
    customType<{
        data: Geometry
        config: GeometryOptions
        driverData: string
    }>({
        dataType,
        toDriver,
        fromDriver,
    })(name, options)

type PointOptions = Omit<GeometryOptions, 'type'>
type LatLng = { lat: number; lng: number }

const point = (name: string, options?: PointOptions) =>
    customType<{
        data: LatLng
        config: PointOptions
        driverData: string
    }>({
        dataType: options => dataType({ type: 'POINT', ...options }),
        toDriver: ({ lat, lng }: LatLng) =>
            toDriver({
                type: 'Point',
                coordinates: [lng, lat],
            }),
        fromDriver: value => {
            const [lng, lat] = (fromDriver(value) as Point).coordinates
            return { lat, lng }
        },
    })(name, options)

export const postgis = {
    geometry,
    point,
}
import { sql } from 'drizzle-orm'
import { customType } from 'drizzle-orm/pg-core'
// @ts-ignore
import type { Geometry, Point } from 'geojson'
import wkx from 'wkx'

// --- Utilities to work with PostGIS ---

type GeometryOptions = { type?: string; srid?: never } | { type: string; srid: number }

const dataType = (options?: GeometryOptions) => {
    let result = 'GEOMETRY'
    if (options?.type) {
        result += `(${options.type.toUpperCase()}`
        if (options?.srid) {
            result += `,${options.srid}`
        }

        result += ')'
    }

    return result
}

const toDriver = (value: Geometry) => {
    return sql`ST_GeomFromGeoJSON(${JSON.stringify(value)})`
}

const fromDriver = (value: string) => {
    const b = Buffer.from(value, 'hex')
    return wkx.Geometry.parse(b).toGeoJSON({ shortCrs: true }) as Geometry
}

const geometry = (name: string, options?: GeometryOptions) =>
    customType<{
        data: Geometry
        config: GeometryOptions
        driverData: string
    }>({
        dataType,
        toDriver,
        fromDriver,
    })(name, options)

type PointOptions = Omit<GeometryOptions, 'type'>
type LatLng = { lat: number; lng: number }

const point = (name: string, options?: PointOptions) =>
    customType<{
        data: LatLng
        config: PointOptions
        driverData: string
    }>({
        dataType: options => dataType({ type: 'POINT', ...options }),
        toDriver: ({ lat, lng }: LatLng) =>
            toDriver({
                type: 'Point',
                coordinates: [lng, lat],
            }),
        fromDriver: value => {
            const [lng, lat] = (fromDriver(value) as Point).coordinates
            return { lat, lng }
        },
    })(name, options)

export const postgis = {
    geometry,
    point,
}
12 replies
DTDrizzle Team
Created by Fabian B. on 8/27/2023 in #help
on every `migrate()`: schema "drizzle" already exists, skipping
Not a fix but at least silences it
11 replies
DTDrizzle Team
Created by Fabian B. on 8/27/2023 in #help
on every `migrate()`: schema "drizzle" already exists, skipping
@productdevbook hi, check the answer from @angelelz
11 replies
DTDrizzle Team
Created by Fabian B. on 8/27/2023 in #help
on every `migrate()`: schema "drizzle" already exists, skipping
@angelelz Thanks! Haven't seen that one.
11 replies
DTDrizzle Team
Created by Fabian B. on 8/27/2023 in #help
on every `migrate()`: schema "drizzle" already exists, skipping
Ah and I am connected to a supabase postgres instance, using the pg bouncer connection string
11 replies
DTDrizzle Team
Created by Fabian B. on 5/22/2023 in #help
`where` inside relational queries `with` does not work
@bloberenober I see, alright. Yeah will query it in reverse then. Thanks!
9 replies
DTDrizzle Team
Created by Fabian B. on 5/22/2023 in #help
`where` inside relational queries `with` does not work
If I run the sql directly, it works fine. 
db.execute(sql`
  SELECT p.*
  FROM projects p
  JOIN customers c ON p.customer_id = c.id
  WHERE c.space_id = ${spaceId};
`),
db.execute(sql`
  SELECT p.*
  FROM projects p
  JOIN customers c ON p.customer_id = c.id
  WHERE c.space_id = ${spaceId};
`),
9 replies
DTDrizzle Team
Created by Fabian B. on 5/22/2023 in #help
`where` inside relational queries `with` does not work
Another way I guess would be to db.query.customers.findMany , apply the where spaceId to the root and then join into the projects.
9 replies
DTDrizzle Team
Created by Fabian B. on 5/22/2023 in #help
`where` inside relational queries `with` does not work
@bloberenober Thanks for your quick answer! So my goal is "select all projects where projects.customer.space.id is equal to..." So logially the where must apply to the projects, so ideally like this (invalid syntax): 
db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      }
    }
  },
  where: (projects, { eq }) => eq(projects.customer.spaceId, spaceId),
}),
db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      }
    }
  },
  where: (projects, { eq }) => eq(projects.customer.spaceId, spaceId),
}),
hope this better explains what my goal is.
9 replies