kratious
kratious
PostsComments
DTDrizzle Team
Created by sakura on 10/6/2023 in #help
[Error] Using Drizzle with Neon DB
Sorry didn't see that you were using @Neondatabase/serverless. Drizzle doesn't use prepared statements for all requests internally Just wondering, what are the names of the prepared statements that don't exist?
12 replies
DTDrizzle Team
Created by sakura on 10/6/2023 in #help
[Error] Using Drizzle with Neon DB
postgresjs auto generates prepared statements by default if you're using that https://github.com/porsager/postgres#prepared-statements
12 replies
DTDrizzle Team
Created by saM69420 on 10/5/2023 in #help
Creating a Case-Insensitive Unique Index
Ah OK, then that's a bug with drizzle-kit I think the indexing approach is perfectly reasonable as long as you follow this
The database can use a function-based index if the exact expression of the index definition appears in an SQL statement
See: https://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search
8 replies
DTDrizzle Team
Created by saM69420 on 10/5/2023 in #help
Creating a Case-Insensitive Unique Index
You're adding a unique constraint which doesn't have the flexibility of lowercasing Could try it with this https://orm.drizzle.team/docs/indexes-constraints#indexes
8 replies
DTDrizzle Team
Created by zerokelvin on 9/18/2023 in #help
Why is my query so much slower when filter by all four latitude/longitude bounds than just three?
What you could try is finding the facility you need first and then doing the joins in a separate query 
const filteredFacility = await db.query.facility.findFirst({ 
    orderBy: desc(facility.internalRating),
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });

const joined = await db.query.facility.findFirst({
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: eq(facility.id, filteredFacility[0].id)
  });
const filteredFacility = await db.query.facility.findFirst({ 
    orderBy: desc(facility.internalRating),
    where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
  });

const joined = await db.query.facility.findFirst({
    with: {
      city: true,
      apartments: true,
      images: {
        limit: 1,
      },
      amenityToFacility: {
        limit: 0,
        with: {
          amenity: {
            columns: {
              name: true,
            },
          },
        },
      },
    },
    where: eq(facility.id, filteredFacility[0].id)
  });
11 replies
DTDrizzle Team
Created by zerokelvin on 9/18/2023 in #help
Why is my query so much slower when filter by all four latitude/longitude bounds than just three?
In prisma the include/in queries are split up into separate queries. See here: https://www.prisma.io/docs/guides/performance-and-optimization/query-optimization-performance#solving-n1-with-include As you've seen in the logs, drizzle keeps these queries in one huge query, this leaves the responsibility on the DB planner on how the queries are ran, sometimes the planner is not smart enough to optimize such cases. Looking and thinking about the query, my intuition on how it would run fast would follow something like the following steps: 1. Filter out all facility rows based on the clauses in the section 
where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
where: and(
      whereLevelOfCare(levelOfCare),
      eq(facility.showOnListPage, true),
      gte(facility.addressLat, currentCity.lat - LAT_RADIUS),
      lte(facility.addressLat, currentCity.lat + LAT_RADIUS),
      /*
      gte(facility.addressLng, currentCity.lng - LNG_RADIUS),
      */
      lte(facility.addressLng, currentCity.lng + LNG_RADIUS)
    ),
2. Work on left joining the other tables from filtered facility rows In your case the planner is probably optimizing incorrectly and does it in this way: 1. Work on left joining all the other tables from the unfiltered facility rows 2. Filter out rows from previous step based on the where clauses
11 replies
DTDrizzle Team
Created by msu on 10/2/2023 in #help
Does Drizzle autogenerate prepared statements under the hood?
pg doesn't. You can turn off the autogenerating with prepare: false for postgres.js too
9 replies
DTDrizzle Team
Created by msu on 10/2/2023 in #help
Does Drizzle autogenerate prepared statements under the hood?
PostgresJS does autogenerate, if you're using that https://github.com/porsager/postgres#prepared-statements
9 replies
DTDrizzle Team
Created by delight on 10/2/2023 in #help
Attempting to get a Joined Subquery query into drizzle crud api
I think the issue is with converting the Select into a Subquery. Append an .as at the end of sq, this mirrors exactly what you have in the native SQL 
    const sq = db
        .select({
            id: loginlog.userId,
            numberOfLogins: sql<number>`count(${loginlog.createdAt})`,
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`,
        })
        .from(loginlog)
        .groupBy(loginlog.userId)
        .as("ll")
    const sq = db
        .select({
            id: loginlog.userId,
            numberOfLogins: sql<number>`count(${loginlog.createdAt})`,
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`,
        })
        .from(loginlog)
        .groupBy(loginlog.userId)
        .as("ll")
36 replies