Drizzle Team•2mo ago

db.insert doesn't immediately insert the values into the DB (Neon)

I am trying to insert some users and some organizations and then define membership relation between both of these. I have set a foreign key relation in the memberships table that references the id of orgs. Now, when I try to insert values in all three tables one after other I am not able to do it as it says the org id doesn't exist. When I do db.select statement just after db.insert, it results in an empty array. Is this the expected behavior? Why isn't db.insert promise resolving only after the data has been inserted? Thanks.

27 Replies

xorcyanOP•2mo ago

I am trying to use the org ids after putting a .returning() on the db.insert(orgs) statement. This is ridiculous. What am I doing wrong?

    const insertedUsers = await db.select({id: users.id}).from(users);
    console.log(insertedUsers);

    const insertedUsers = await db.select({id: users.id}).from(users);
    console.log(insertedUsers);

results in an empty array even though I can see the data exists in neon db through the web client.

Luc•2mo ago

send snippets of your schema and the code thats isnt working as expected, otherwise its hard to say

Kuba•2mo ago

You can also put it together on https://drizzle.run/ and see if it works there. This way we can also test is. I haven't worked with neon, but it seems to be just host postgres, so you should be able to put your code into the playground without any issues.

Drizzle Run

xorcyanOP•2mo ago

import {
  pgTable,
  serial,
  text,
  timestamp,
  integer,
  primaryKey,
  varchar,
  index,
  uniqueIndex,
  pgEnum,
} from "drizzle-orm/pg-core";

export const userEmails = pgTable("user_emails", {
  email: text("email").primaryKey(),
  userId: integer("user_id").references(() => users.id),
});

export const users = pgTable("users", {
  id: serial("id").primaryKey(),

  nameId: text("name_id").notNull().unique(),
  name: text("name").notNull(),

  hashedPassword: text("hashed_password").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),

  about: text("about"),
  avatar: text("avatar"),
});

export const orgs = pgTable("orgs", {
  id: serial("id").primaryKey(),

  nameId: text("name_id").notNull().unique(),
  name: text("name").notNull(),

  createdAt: timestamp("created_at").defaultNow().notNull(),

  about: text("about"),
  avatar: text("avatar"),
});

export const memberships = pgTable(
  "memberships",
  {
    orgId: integer("org_id")
      .notNull()
      .references(() => orgs.id, { onDelete: "cascade" }),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),

    role: text("role", { enum: ["owner", "organizer", "member"] }).notNull(),
    joinedAt: timestamp("joined_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.orgId, table.userId] }),

      orgIdIdx: index("org_id_idx").on(table.orgId),
      userIdIdx: index("user_id_idx").on(table.userId),
    };
  },
);

import {
  pgTable,
  serial,
  text,
  timestamp,
  integer,
  primaryKey,
  varchar,
  index,
  uniqueIndex,
  pgEnum,
} from "drizzle-orm/pg-core";

export const userEmails = pgTable("user_emails", {
  email: text("email").primaryKey(),
  userId: integer("user_id").references(() => users.id),
});

export const users = pgTable("users", {
  id: serial("id").primaryKey(),

  nameId: text("name_id").notNull().unique(),
  name: text("name").notNull(),

  hashedPassword: text("hashed_password").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),

  about: text("about"),
  avatar: text("avatar"),
});

export const orgs = pgTable("orgs", {
  id: serial("id").primaryKey(),

  nameId: text("name_id").notNull().unique(),
  name: text("name").notNull(),

  createdAt: timestamp("created_at").defaultNow().notNull(),

  about: text("about"),
  avatar: text("avatar"),
});

export const memberships = pgTable(
  "memberships",
  {
    orgId: integer("org_id")
      .notNull()
      .references(() => orgs.id, { onDelete: "cascade" }),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),

    role: text("role", { enum: ["owner", "organizer", "member"] }).notNull(),
    joinedAt: timestamp("joined_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.orgId, table.userId] }),

      orgIdIdx: index("org_id_idx").on(table.orgId),
      userIdIdx: index("user_id_idx").on(table.userId),
    };
  },
);

my schema

    await db
      .insert(users)
      .values([
        {
          nameId: 'john-doe',
          name: 'John Doe',
          hashedPassword: await saltAndHashPassword('john-doe1'),
          about: 'Software engineer with 5 years of experience',
          avatar: 'avatar1.jpg'
        },
        {
          nameId: 'jane-smith',
          name: 'Jane Smith',
          hashedPassword: await saltAndHashPassword('jane-smith1'),
          about: 'Product manager passionate about user experience',
          avatar: 'avatar2.jpg'
        },
        {
          nameId: 'mike-johnson',
          name: 'Mike Johnson',
          hashedPassword: await saltAndHashPassword('mike-johnson1'),
          about: 'Data scientist specializing in machine learning',
          avatar: 'avatar3.jpg'
        },
        {
          nameId: 'emily-brown',
          name: 'Emily Brown',
          hashedPassword: await saltAndHashPassword('emily-brown'),
          about: 'UX designer with a keen eye for detail',
          avatar: 'avatar4.jpg'
        }
      ]).onConflictDoNothing();

    const insertedUsers = await db.select().from(users);
    console.log('insertedUsers', insertedUsers);

    await db
      .insert(users)
      .values([
        {
          nameId: 'john-doe',
          name: 'John Doe',
          hashedPassword: await saltAndHashPassword('john-doe1'),
          about: 'Software engineer with 5 years of experience',
          avatar: 'avatar1.jpg'
        },
        {
          nameId: 'jane-smith',
          name: 'Jane Smith',
          hashedPassword: await saltAndHashPassword('jane-smith1'),
          about: 'Product manager passionate about user experience',
          avatar: 'avatar2.jpg'
        },
        {
          nameId: 'mike-johnson',
          name: 'Mike Johnson',
          hashedPassword: await saltAndHashPassword('mike-johnson1'),
          about: 'Data scientist specializing in machine learning',
          avatar: 'avatar3.jpg'
        },
        {
          nameId: 'emily-brown',
          name: 'Emily Brown',
          hashedPassword: await saltAndHashPassword('emily-brown'),
          about: 'UX designer with a keen eye for detail',
          avatar: 'avatar4.jpg'
        }
      ]).onConflictDoNothing();

    const insertedUsers = await db.select().from(users);
    console.log('insertedUsers', insertedUsers);

my seeding script this prints empty array I cannot get the rows after I have inserted them this was causing foreign key conflict cause the orgId I was referencing in memberships table wasn't even present in the orgs table

xorcyanOP•2mo ago

the leftover part of the seeding script

message.txt

rphlmr ⚡•2mo ago

Oh you await in the array you pass to vslues

xorcyanOP•2mo ago

pardon?

rphlmr ⚡•2mo ago

You have async code for your hashed password

xorcyanOP•2mo ago

would that be problematic?

rphlmr ⚡•2mo ago

So you have to await all in a Promise.all()

xorcyanOP•2mo ago

I don't have any such calls in the array when inserting in orgs though still that fails

    const passwords = await Promise.all([
      saltAndHashPassword('john-doe1'),
      saltAndHashPassword('jane-smith1'),
      saltAndHashPassword('mike-johnson1'),
      saltAndHashPassword('emily-brown'),
    ]);

    // Insert users
    await db
      .insert(users)
      .values([
        {
          nameId: 'john-doe',
          name: 'John Doe',
          hashedPassword: passwords[0],
          about: 'Software engineer with 5 years of experience',
          avatar: 'avatar1.jpg'
        },
        {
          nameId: 'jane-smith',
          name: 'Jane Smith',
          hashedPassword: passwords[1],
          about: 'Product manager passionate about user experience',
          avatar: 'avatar2.jpg'
        },
        {
          nameId: 'mike-johnson',
          name: 'Mike Johnson',
          hashedPassword: passwords[2],
          about: 'Data scientist specializing in machine learning',
          avatar: 'avatar3.jpg'
        },
        {
          nameId: 'emily-brown',
          name: 'Emily Brown',
          hashedPassword: passwords[3],
          about: 'UX designer with a keen eye for detail',
          avatar: 'avatar4.jpg'
        }
      ]).onConflictDoNothing();

    const insertedUsers = await db.select().from(users);
    console.log('insertedUsers', insertedUsers);

    const passwords = await Promise.all([
      saltAndHashPassword('john-doe1'),
      saltAndHashPassword('jane-smith1'),
      saltAndHashPassword('mike-johnson1'),
      saltAndHashPassword('emily-brown'),
    ]);

    // Insert users
    await db
      .insert(users)
      .values([
        {
          nameId: 'john-doe',
          name: 'John Doe',
          hashedPassword: passwords[0],
          about: 'Software engineer with 5 years of experience',
          avatar: 'avatar1.jpg'
        },
        {
          nameId: 'jane-smith',
          name: 'Jane Smith',
          hashedPassword: passwords[1],
          about: 'Product manager passionate about user experience',
          avatar: 'avatar2.jpg'
        },
        {
          nameId: 'mike-johnson',
          name: 'Mike Johnson',
          hashedPassword: passwords[2],
          about: 'Data scientist specializing in machine learning',
          avatar: 'avatar3.jpg'
        },
        {
          nameId: 'emily-brown',
          name: 'Emily Brown',
          hashedPassword: passwords[3],
          about: 'UX designer with a keen eye for detail',
          avatar: 'avatar4.jpg'
        }
      ]).onConflictDoNothing();

    const insertedUsers = await db.select().from(users);
    console.log('insertedUsers', insertedUsers);

prints insertedUsers [] wait same output even after removing awaits from the array inside Promise.all

xorcyanOP•2mo ago

I can see it in the neon table explorer

xorcyanOP•2mo ago

and for some reason the id keeps starting from the incremented value instead of 0 even tho I delete those records

rphlmr ⚡•2mo ago

That’s expected , serial will always increment and sometimes could skip some numbers Would you mind trying again but within a db.transaction?

xorcyanOP•2mo ago

I tried but the neon client doesn't support transactions

rphlmr ⚡•2mo ago

Ah 😌

xorcyanOP•2mo ago

I got an error along those lines got the issue? still, I would expect each query statement to be auto committed

rphlmr ⚡•2mo ago

It should be but insert like that (across multiple tables) should be in a transaction (insert all or nothing). I’m checking about neon and transactions. Maybe I missed something

xorcyanOP•2mo ago

huh

rphlmr ⚡•2mo ago

What driver do you use? Neon serverless or Postgres?

xorcyanOP•2mo ago

neon serverless ("@neondatabase/serverless": "^0.9.5") sorry for the ping 😐

rphlmr ⚡•2mo ago

Transaction should work (it’s in their doc) 🧐

xorcyanOP•2mo ago

Error: No transactions support in neon-http driver I get this error

xorcyanOP•2mo ago

nvm, got it I think https://orm.drizzle.team/docs/get-started-postgresql#neon-postgres

Drizzle ORM - PostgreSQL

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

xorcyanOP•2mo ago

websocket 👍 I missed that somehow

rphlmr ⚡•2mo ago

Ok that’s over http for serverless env. that can explain why you see empty data This is why I don’t use serverless envs 😅 looks complicated

xorcyanOP•2mo ago

yay, fixed too late to grasp the issue properly though I have a few questions though:

but insert like that (across multiple tables) should be in a transaction (insert all or nothing)

Are DB commits not guaranteed to be as expected across tables? wdym by http for serverless env? why does it differ from websocket? good night 😴

Gaming

Programming

db.insert doesn't immediately insert the values into the DB (Neon)