DT
Drizzle Team•4mo ago
xorcyan

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.
28 Replies
xorcyan
xorcyanOP•4mo 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
Luc•4mo ago
send snippets of your schema and the code thats isnt working as expected, otherwise its hard to say
Kuba
Kuba•4mo 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
Drizzle Run
xorcyan
xorcyanOP•4mo 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
xorcyan
xorcyanOP•4mo ago
the leftover part of the seeding script

message.txt

rphlmr ⚡
rphlmr ⚡•4mo ago
Oh you await in the array you pass to vslues
xorcyan
xorcyanOP•4mo ago
pardon?
rphlmr ⚡
rphlmr ⚡•4mo ago
You have async code for your hashed password
xorcyan
xorcyanOP•4mo ago
would that be problematic?
rphlmr ⚡
rphlmr ⚡•4mo ago
So you have to await all in a Promise.all()
xorcyan
xorcyanOP•4mo 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
xorcyan
xorcyanOP•4mo ago
I can see it in the neon table explorer
No description
xorcyan
xorcyanOP•4mo ago
and for some reason the id keeps starting from the incremented value instead of 0 even tho I delete those records
rphlmr ⚡
rphlmr ⚡•4mo ago
That’s expected , serial will always increment and sometimes could skip some numbers Would you mind trying again but within a db.transaction?
xorcyan
xorcyanOP•4mo ago
I tried but the neon client doesn't support transactions
rphlmr ⚡
rphlmr ⚡•4mo ago
Ah 😌
xorcyan
xorcyanOP•4mo ago
I got an error along those lines got the issue? still, I would expect each query statement to be auto committed
rphlmr ⚡
rphlmr ⚡•4mo 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
xorcyan
xorcyanOP•4mo ago
huh
rphlmr ⚡
rphlmr ⚡•4mo ago
What driver do you use? Neon serverless or Postgres?
xorcyan
xorcyanOP•4mo ago
neon serverless ("@neondatabase/serverless": "^0.9.5") sorry for the ping 😐
rphlmr ⚡
rphlmr ⚡•4mo ago
Transaction should work (it’s in their doc) 🧐
xorcyan
xorcyanOP•4mo ago
Error: No transactions support in neon-http driver I get this error
xorcyan
xorcyanOP•4mo 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.
xorcyan
xorcyanOP•4mo ago
websocket 👍 I missed that somehow
rphlmr ⚡
rphlmr ⚡•4mo 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
xorcyan
xorcyanOP•4mo 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 😴
MarvinKR
MarvinKR•2mo ago
so you just switched: 
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "@/db/schema";

export const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "@/db/schema";

export const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });
for? 
import { Pool } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool })
import { Pool } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool })

Did you find this page helpful?