francis Posts - Answer Overflow

francis

Explore posts from servers

•Created by francis on 9/20/2024 in #help

How do you define two tables that have foreign keys to each other? drizzle-kit introspect fails

Drizzle-kit now generates a schema file with type errors - it was previously working prior to 0.24.0. In addition, the generated foreign key entry for multicolumn foreign keys has the columns in the incorrect order. (code below due to length limit)

5 replies

DTDrizzle Team

•Created by francis on 8/8/2024 in #help

What's the recommended way to do a basic filter by a boolean column's value?

I can't figure out how to use a non-filter expression for filtering. e.g., I would expect db.select().from(appointment).where(appointment.canceled) to generate the equivalent sql select * from appointment where appointment.canceled, which works exactly how I expect. .where(() => appointment.canceled) or .where((a) => a.canceled) don't work either. I can use

.where(sql`${appointment.canceled}`)

.where(sql`${appointment.canceled}`)

but surely there must be a simpler way? I can provide this column as an argument to a filter, so why can't I use this column itself as a filter?

3 replies

DTDrizzle Team

•Created by francis on 3/7/2024 in #help

New string mode date handling for postgres in 0.30.0 is potentially inconsistent

When selecting a value from a column of type timestamp with time zone, I receive the following string format: 2024-01-19 16:54:36.74526+00 When selecting a value from a column of type date, I receive the following string format: 1990-01-01T00:00:00.000Z It appears the serialization changes for the postgres driver may not be working - the ones here https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/postgres-js/driver.ts#L27 If I define my own custom type as:

  types: {
    date_temp: {
      to: 1082,
      from: [1082],
      serialize: (val: any) => val,
      parse: (val: any) => val,
    }
  }

  types: {
    date_temp: {
      to: 1082,
      from: [1082],
      serialize: (val: any) => val,
      parse: (val: any) => val,
    }
  }

in the postgres-js options, then suddenly I receive the raw sql values I expect: 1990-01-01

2 replies

DTDrizzle Team

•Created by francis on 1/28/2024 in #help

Issuing multiple sql queries in order, but without waiting for round trips?

I need to run several commands to set up transaction variables for my use case. They need to be run in order, but I don't need the result of any of them. Is there a way to issue e.g. 3 sequential commands in a single query to the database so I don't have to wait for the round trip for each one, like happens now with await tx.execute(.....) 3 times in a row?

21 replies

DTDrizzle Team

•Created by francis on 1/16/2024 in #help

How do you create a scalar subquery in drizzle?

I want to create the drizzle analog of select * from table_a where id = (select a_id from table_b where id = <....>) This works in raw SQL, but I'm not sure how to construct the subquery to get only the a_id field to supply in the where condition.

drizzle.select().from(tableA).where(eq(tableA.id, drizzle.select(<WHAT GOES HERE>).from(tableB).where(eq(tableB.id, userInputBId))))

drizzle.select().from(tableA).where(eq(tableA.id, drizzle.select(<WHAT GOES HERE>).from(tableB).where(eq(tableB.id, userInputBId))))

11 replies

DTDrizzle Team

•Created by francis on 1/14/2024 in #help

Is there a way to get the update count resulting from a query without returning?

I would like to assert that a statement in a transaction updates only the number of rows I expect, and fail if that number is different. I can do it with result.length and returning() but that includes a ton of data I don't need.

8 replies

DTDrizzle Team

•Created by francis on 12/28/2023 in #help

Does calling `drizzle()` multiple times on the same db client instance consume more resources?

related to a question from: https://github.com/drizzle-team/drizzle-orm/issues/594 Would using the same postgres(...) client instance and creating multiple Drizzle instances from it, e.g. one for each request in a middleware, consume additional database connection resources compared to creating one drizzle instance for the whole server process? @Angelelz this is related to the question you asked in the github issue. I believe my current implementation doesn't have this problem since it uses proxies, but I was about to refactor to call drizzle() once per request and want to make sure I'm not about to blow up my database!

7 replies

DTDrizzle Team

•Created by francis on 12/28/2023 in #help

Is there a better way to extract Typescript types for use in function args than what I'm doing here?

export const client = drizzle(adminPgClient, { schema });
export type DrizzleClient = typeof client;
export type DrizzleTransaction = Parameters<Parameters<DrizzleClient["transaction"]>[0]>[0];

export const client = drizzle(adminPgClient, { schema });
export type DrizzleClient = typeof client;
export type DrizzleTransaction = Parameters<Parameters<DrizzleClient["transaction"]>[0]>[0];

It works well enough, but this seems like something which should be exposed on a library level, maybe? I need it in order to e.g. write a function which takes in a transaction and performs operations on it, such that multiple functions can be called on a single transaction and then the transaction either committed or rolled back as one unit (so the client cannot be passed in instead).

12 replies

DTDrizzle Team

•Created by francis on 12/28/2023 in #help

Has JSON aggregation been added yet (analogous to Kysely?)

I added some jsonAgg... functions to generate raw SQL to helper utilities a while back, in order to generate arrays of nested json objects in aggregation. There was discussion of adding these as first-class drizzle functions, did that ever happen? Helpers source: https://gist.github.com/rphlmr/0d1722a794ed5a16da0fdf6652902b15

4 replies

DTDrizzle Team

•Created by francis on 12/12/2023 in #help

Is there a way to explicitly type certain drizzle results as `Array<T | undefined>`?

For example, I am making a query which I know will sometimes return undefined, since I am accessing a row that may or may not be present. I'm not sure how to type this such that I have type safety through the rest of the code to ensure that I never access properties without undefined checks. I'm aware of Typescript noUncheckedIndexedAccess but that will change the behavior of all arrays everywhere in my codebase, which I do not want. I just want the ability to indicate that a certain Drizzle select result will sometimes have no items returned. e.g. const [maybeExistingEntity] = await drizzle.select(.....).limit(1) now strongly types maybeExistingEntity where I would like the type to be T | undefined.

64 replies

DTDrizzle Team

•Created by francis on 12/12/2023 in #help

Is there a way to configure pg introspect to generate date rather than string inference columns?

My workflow involves writing migrations by hand outside of drizzle and using schema introspection to pull in changes. This works well except for date columns, which are always generated as mode: 'string' by default.

16 replies

DTDrizzle Team

•Created by francis on 9/15/2023 in #help

Creating join table that is simultaneously many-to-many and one-to-many

got a data modeling / sql question for you all: I have an entity A with either a many-to-many relationship to B, or a one-to-many relationship to C. There must be a unique ordering of all relationships from A to B or C, such that we can define an ordering such as B1, B2, C1, B3, C2... etc B and C are different entities, with wildly different table structures, and therefore cannot be combined into a single table. I am solving this with: A, B, and C all have id primary key fields. I create a table a_joins as:

create table a_joins (
    id uuid primary key default uuid_generate_v4(),
    a_id uuid not null references a (id) on delete cascade,
    b_id uuid references b (id) on delete restrict,
    c_id uuid unique references c (id) on delete restrict,
    index integer not null,
    check ((b_id is not null) != (c_id is not null)),
    unique (a_id, b_id),
    unique (a_id, index)
);

create table a_joins (
    id uuid primary key default uuid_generate_v4(),
    a_id uuid not null references a (id) on delete cascade,
    b_id uuid references b (id) on delete restrict,
    c_id uuid unique references c (id) on delete restrict,
    index integer not null,
    check ((b_id is not null) != (c_id is not null)),
    unique (a_id, b_id),
    unique (a_id, index)
);

This allows me to ensure an ordering for all a_id entries, such that each entry in the ordering points to either a b (shared by many a) or a c (for this a only). The problem I have is that I want cascade delete behavior, such that on deletion of a, the a_joins entries are deleted (which works due to cascade), and any c associated with a is deleted as well. How would you recommend I accomplish this? I attempted to add a foreign key relationship to c to perform the cascade:

alter table c add constraint c_a_fkey
foreign key (id, a_id)
references a_joins (c_id, id) on delete cascade;

alter table c add constraint c_a_fkey
foreign key (id, a_id)
references a_joins (c_id, id) on delete cascade;

This should ensure both that a c entry only ever points to the single unique a_joins entry that references it, and that the c entry is deleted when the corresponding a_joins entry is removed. However, this fails due to the lack of a unique index on a_joins (c_id, id), even though both of those columns are individually marked as either unique or primary key. How is this possible?

32 replies

DTDrizzle Team

•Created by francis on 9/13/2023 in #help

How to include array literals in raw sql?

I have an input array used for an order by using the array_position function - rather than passing the array as a single param, the call to:

.orderBy(sql`array_position(${ingredientNames}, ${entity.name})`);

.orderBy(sql`array_position(${ingredientNames}, ${entity.name})`);

generates a param list for each item in the input array. This generates a record rather than an array literal in Postgres. Trying raw sql with:

`{${array.map((item) => `'${item}'`).join(",")}}`

`{${array.map((item) => `'${item}'`).join(",")}}`

doesn't work either.

3 replies

DTDrizzle Team

•Created by francis on 9/4/2023 in #help

Drizzle join in a subquery results in ambiguous columns

This fails with id ambiguous error, since the orderedQuestions.survey_question.id reference in the field selection of the second query generates the sql as 'id' rather than 'survey_question'.'id' as it should.

      const orderedQuestions = tx
        .select()
        .from(surveyQuestion)
        .leftJoin(surveySection, eq(surveySection.id, surveyQuestion.surveySectionId))
        .where(eq(surveySection.surveyId, LIFESTYLE_AUDIT_SURVEY_ID))
        .orderBy(surveySection.index, surveyQuestion.index)
        .as("orderedQuestions");

      console.log(
        await tx
          .select({ id: orderedQuestions.survey_question.id })
          .from(orderedQuestions)
          .toSQL()
      );

      const orderedQuestions = tx
        .select()
        .from(surveyQuestion)
        .leftJoin(surveySection, eq(surveySection.id, surveyQuestion.surveySectionId))
        .where(eq(surveySection.surveyId, LIFESTYLE_AUDIT_SURVEY_ID))
        .orderBy(surveySection.index, surveyQuestion.index)
        .as("orderedQuestions");

      console.log(
        await tx
          .select({ id: orderedQuestions.survey_question.id })
          .from(orderedQuestions)
          .toSQL()
      );

Generates the following ambiguous sql:

'select "id" from (select "survey_question"."id", "survey_question"."survey_section_id", "survey_question"."index", "survey_question"."title", "survey_question"."description", "survey_question"."short_name", "survey_question"."type", "survey_question"."survey_product_category_id", "survey_question"."survey_exposure_id", "survey_question"."survey_question_text_option_id", "survey_question"."survey_question_answer_set_id", "survey_question"."created_by_id", "survey_question"."created_at", "survey_question"."updated_at", "survey_section"."id", "survey_section"."survey_id", "survey_section"."index", "survey_section"."name", "survey_section"."created_by_id", "survey_section"."created_at", "survey_section"."updated_at" from "survey_question" left join "survey_section" on "survey_section"."id" = "survey_question"."survey_section_id" where "survey_section"."survey_id" = $1 order by "survey_section"."index", "survey_question"."index") "orderedQuestions"'

'select "id" from (select "survey_question"."id", "survey_question"."survey_section_id", "survey_question"."index", "survey_question"."title", "survey_question"."description", "survey_question"."short_name", "survey_question"."type", "survey_question"."survey_product_category_id", "survey_question"."survey_exposure_id", "survey_question"."survey_question_text_option_id", "survey_question"."survey_question_answer_set_id", "survey_question"."created_by_id", "survey_question"."created_at", "survey_question"."updated_at", "survey_section"."id", "survey_section"."survey_id", "survey_section"."index", "survey_section"."name", "survey_section"."created_by_id", "survey_section"."created_at", "survey_section"."updated_at" from "survey_question" left join "survey_section" on "survey_section"."id" = "survey_question"."survey_section_id" where "survey_section"."survey_id" = $1 order by "survey_section"."index", "survey_question"."index") "orderedQuestions"'

26 replies

DTDrizzle Team

•Created by francis on 9/3/2023 in #help

Is there a recommended way to find or create a record? (postgres)

I don't want to do an insert with ON CONFLICT RETURNING since I have triggers which fire on row update and can't have them be called without an actual row change.

12 replies

DTDrizzle Team

•Created by francis on 8/18/2023 in #help

Is there a way to have a query- or transaction-level hook?

I want to: - for every transaction, before statements are run, issue a query against the database to set settings - for every query, if not in a transaction, open a transaction and set the setting, run the query, then close the transaction Is there any recommended way to do this with drizzle?

2 replies

Gaming

Programming