adrtivv Posts - Answer Overflow

adrtivv

•Created by adrtivv on 11/27/2024 in #help

What are the possible ways to prevent race conditions in this postgres insert operation?

Here are typescript types for instructions and sections database tables:

type Instruction = {
  body: string;
  id: string;
  // Foreign key reference to `id` column of Section
  sectionId: string | null;
};

type Section = {
  id: string;
  name: string;
  // Foreign key reference to `id` column of Section 
  parentSectionId: string | null;
};

type Instruction = {
  body: string;
  id: string;
  // Foreign key reference to `id` column of Section
  sectionId: string | null;
};

type Section = {
  id: string;
  name: string;
  // Foreign key reference to `id` column of Section 
  parentSectionId: string | null;
};

I want it so that any time a section can only contain many instructions or many sections but not both. How do I enforce this while preventing race conditions that could create inconsistencies? For example take a look at the following code:

// START
const parentSection = await drizzle.sections.findFirst({ 
  with: {
    instructions: {
      limit: 1,
    }
  }
  where: (fields, operators) => operators.eq(fields.id, sectionId)
 });


if (parentSection.instructions[0] !== undefined) {
  throw new Error("Section already contains instructions.");
};

const childSection = await drizzle.insert(sections).values({ name, parentSectionId: sectionId }).returning();
// END

// START
const parentSection = await drizzle.sections.findFirst({ 
  with: {
    instructions: {
      limit: 1,
    }
  }
  where: (fields, operators) => operators.eq(fields.id, sectionId)
 });


if (parentSection.instructions[0] !== undefined) {
  throw new Error("Section already contains instructions.");
};

const childSection = await drizzle.insert(sections).values({ name, parentSectionId: sectionId }).returning();
// END

There is a race condition here during the time between START and END where the parent section could be made to contain instructions before this operation completes. What can be done to prevent any row in sections table to be referenced with sectionId as the value for parentSectionId column while this operation is being carried out?

1 replies

DTDrizzle Team

•Created by adrtivv on 11/6/2024 in #help

column is of type date but expression is of type text

So, I was trying this and it resulted in an error:

column \"date_of_birth\" is of type date but expression is of type text

column \"date_of_birth\" is of type date but expression is of type text

It happened when I tried updating multiple columns of multiple rows of a table. It doesn't happen when multiple columns of a single row are being updated. What causes this? I asked another person and they said that it's not necessarily a postgres issue and probably has somethi to do with drizzle orm.

6 replies

DTDrizzle Team

•Created by adrtivv on 11/4/2024 in #help

How to resolve mutiple queries in a single request to postgres database?

I have more than one sql select statements that need to act independently and are not related to each other. Is there a clean, type-safe way to fetch results for all these select statements in one request to postgres instead of having to use Promise.all and making a seperate request for each select statement? Most probably what I'm asking for is a single select statement that contains the logic for all the three select statements below.

const usersQuery = db.select().from(users).where(eq(users.age, 24));
const accountsQuery = db.select().from(accounts).where(eq(accounts.verified, false));
const sessionsQuery = db.select().from(sessions).where(inArray(sessions.userId, userIds));
const [users, accounts, sessions] = await Promise.all([usersQuery, accountsQuery, sessionsQuery]);

const usersQuery = db.select().from(users).where(eq(users.age, 24));
const accountsQuery = db.select().from(accounts).where(eq(accounts.verified, false));
const sessionsQuery = db.select().from(sessions).where(inArray(sessions.userId, userIds));
const [users, accounts, sessions] = await Promise.all([usersQuery, accountsQuery, sessionsQuery]);

6 replies

DTDrizzle Team

•Created by adrtivv on 10/12/2024 in #help

how to sort rows lexographically by the string value of the postgres enum column

I've read that postgres by default sorts rows by the integer value of enum columns and not their string value. Using drizzle orm how to sort rows of a postgres table lexographically by the string value of one of its enum columns?

1 replies

DTDrizzle Team

•Created by adrtivv on 11/27/2023 in #help

drizzle studio blank screen

11 replies

Gaming

Programming