Chi Hao
Chi Hao
Explore posts from servers
HHonoDTDrizzle Team
PostsComments
DTDrizzle Team
Created by Chi Hao on 4/11/2024 in #help
update multiple rows is not working. PostgresError: column excluded.name does not exist
Thanks for the help!
6 replies
DTDrizzle Team
Created by Chi Hao on 4/11/2024 in #help
update multiple rows is not working. PostgresError: column excluded.name does not exist
i have decided to use this instead 
          set: {
            name: sql`excluded."Name"`,
            from: sql`excluded."From"`,
            to: sql`excluded."To"`,
            delivery_time: sql`excluded."Delivery_Time"`,
          },
          set: {
            name: sql`excluded."Name"`,
            from: sql`excluded."From"`,
            to: sql`excluded."To"`,
            delivery_time: sql`excluded."Delivery_Time"`,
          },
6 replies
DTDrizzle Team
Created by Chi Hao on 4/11/2024 in #help
update multiple rows is not working. PostgresError: column excluded.name does not exist
I have a requirement whereby i will be inserting/updating a list of values at once and each item will be a row in the table, I wonder how am I suppose to delete multiple rows for those values that are no longer in the list when send to updateRestaurantSettings function ? Is it by checking the orderPhaseValues in updateRestaurantSettings and the order_phases table, then if the value exist in the table but doesnt exist in orderPhaseValues, that value needs to be deleted? I wonder if there's a more efficient way to perform this deletion.
6 replies
DTDrizzle Team
Created by Chi Hao on 4/1/2024 in #help
How do I reuse the results from the where clause within the findMany?
ok fixed, i forgot to export the relations in drizzle()
7 replies
DTDrizzle Team
Created by Chi Hao on 4/1/2024 in #help
How do I reuse the results from the where clause within the findMany?
hi @Aaroned , I wonder why I'm getting this error TypeError: Cannot read properties of undefined (reading 'referencedTable')? this is the code 
const result = await db.query.restaurants.findMany({
  with: {
    menus: true,
  },
});
const result = await db.query.restaurants.findMany({
  with: {
    menus: true,
  },
});
7 replies
DTDrizzle Team
Created by Chi Hao on 4/1/2024 in #help
How do I reuse the results from the where clause within the findMany?
I wonder if I should use join instead? but I thought findMany is similar to join?
7 replies
DTDrizzle Team
Created by Chi Hao on 4/1/2024 in #help
How do I reuse the results from the where clause within the findMany?
The query that might not be working? 
const result = await db.query.restaurants.findMany({
  where: eq(restaurants.vendor_id, vendor_id),
  columns: {
    id: true,
  },
  with: {
    menus: {
      where: eq(menus.restaurant_id, id), // how do i make sure this id matches the restaurant id of that vendor?
    },
  },
});
const result = await db.query.restaurants.findMany({
  where: eq(restaurants.vendor_id, vendor_id),
  columns: {
    id: true,
  },
  with: {
    menus: {
      where: eq(menus.restaurant_id, id), // how do i make sure this id matches the restaurant id of that vendor?
    },
  },
});
7 replies
DTDrizzle Team
Created by Chi Hao on 4/1/2024 in #help
How do I reuse the results from the where clause within the findMany?
my schema 
export const vendors = pgTable("Vendors", {
  id: serial("ID").notNull().primaryKey(),
  user_id: text("User_ID")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  is_approved: boolean("Is_Approved").default(false),
  subscription_type: SubscriptionEnum("Subscription_Type")
    .default("Free Trial")
    .notNull(),
});

export const restaurants = pgTable("Restaurants", {
  id: serial("ID").notNull().primaryKey(),
  vendor_id: integer("Vendor_ID")
    .notNull()
    .references(() => vendors.id, { onDelete: "cascade" }),
  restaurant_name: text("Restaurant_Name").notNull(),
  slug: text("Restaurant_Name_Slug").notNull().unique(),
  order_phases: jsonb("Order_Phases").notNull(),
  delivery_location: jsonb("Delivery_Location").notNull(),
});

export const menus = pgTable("Menus", {
  id: serial("ID").notNull().primaryKey(),
  restaurant_id: integer("Restaurant_ID")
    .notNull()
    .references(() => restaurants.id, { onDelete: "cascade" }),
  name: text("Name").notNull(),
  description: text("Description"),
  price: decimal("Price").notNull(),
  stock: integer("Stock").notNull(),
  customizations: jsonb("Customizations").notNull(),
  order_phases_type: text("Order_Phases_Type").array().notNull(),
});
export const vendors = pgTable("Vendors", {
  id: serial("ID").notNull().primaryKey(),
  user_id: text("User_ID")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  is_approved: boolean("Is_Approved").default(false),
  subscription_type: SubscriptionEnum("Subscription_Type")
    .default("Free Trial")
    .notNull(),
});

export const restaurants = pgTable("Restaurants", {
  id: serial("ID").notNull().primaryKey(),
  vendor_id: integer("Vendor_ID")
    .notNull()
    .references(() => vendors.id, { onDelete: "cascade" }),
  restaurant_name: text("Restaurant_Name").notNull(),
  slug: text("Restaurant_Name_Slug").notNull().unique(),
  order_phases: jsonb("Order_Phases").notNull(),
  delivery_location: jsonb("Delivery_Location").notNull(),
});

export const menus = pgTable("Menus", {
  id: serial("ID").notNull().primaryKey(),
  restaurant_id: integer("Restaurant_ID")
    .notNull()
    .references(() => restaurants.id, { onDelete: "cascade" }),
  name: text("Name").notNull(),
  description: text("Description"),
  price: decimal("Price").notNull(),
  stock: integer("Stock").notNull(),
  customizations: jsonb("Customizations").notNull(),
  order_phases_type: text("Order_Phases_Type").array().notNull(),
});
the relations 
export const vendorsRelations = relations(vendors, ({ one, many }) => ({
  // a vendor can have multiple restaurants
  restaurants: many(restaurants),
}));

export const restaurantsRelations = relations(restaurants, ({ one, many }) => ({
  // a restaurant is owned by a vendor
  vendor: one(vendors, {
    fields: [restaurants.vendor_id],
    references: [vendors.id],
  }),

  // a restaurant can have multiple menu items
  menus: many(menus),
}));

export const menusRelations = relations(menus, ({ one, many }) => ({
  // a menu item is given by a restaurant
  restaurant: one(restaurants, {
    fields: [menus.restaurant_id],
    references: [restaurants.id],
  }),
}));
export const vendorsRelations = relations(vendors, ({ one, many }) => ({
  // a vendor can have multiple restaurants
  restaurants: many(restaurants),
}));

export const restaurantsRelations = relations(restaurants, ({ one, many }) => ({
  // a restaurant is owned by a vendor
  vendor: one(vendors, {
    fields: [restaurants.vendor_id],
    references: [vendors.id],
  }),

  // a restaurant can have multiple menu items
  menus: many(menus),
}));

export const menusRelations = relations(menus, ({ one, many }) => ({
  // a menu item is given by a restaurant
  restaurant: one(restaurants, {
    fields: [menus.restaurant_id],
    references: [restaurants.id],
  }),
}));
7 replies
DTDrizzle Team
Created by Chi Hao on 3/28/2024 in #help
How to use findMany?
thanks @Sillvva ! I also need to make sure my types in the schema are matching the casing of the pgtable name 
export type MyDatabase = NodePgDatabase<{
  users: typeof users;
  vendors: typeof vendors;
  restaurants: typeof restaurants;
}>;
export type MyDatabase = NodePgDatabase<{
  users: typeof users;
  vendors: typeof vendors;
  restaurants: typeof restaurants;
}>;
7 replies
DTDrizzle Team
Created by Chi Hao on 3/28/2024 in #help
How to use findMany?
The line of code is where i use findMany
7 replies
DTDrizzle Team
Created by Chi Hao on 3/28/2024 in #help
How to use findMany?
7 replies
DTDrizzle Team
Created by Chi Hao on 3/9/2024 in #help
Connection timeout when connecting supabase to astro project while using Drizzle kit
solved: I was supposed to use the Connection string URI not the Project URL
4 replies
DTDrizzle Team
Created by Chi Hao on 3/9/2024 in #help
Connection timeout when connecting supabase to astro project while using Drizzle kit
i also encountered an issue whereby im not allowed to use 
import.meta.env.SECRET_SUPABASE_URL
import.meta.env.SECRET_SUPABASE_URL
in the drizzle.config.ts file. The error was : 
"import.meta" is not available in the configured target environment ("es2015") and will be empty
"import.meta" is not available in the configured target environment ("es2015") and will be empty
I have also tried hard coding the URL and connectionString in the drizzle.config.ts and src/lib/db.ts respectively and I'm still facing the connection timed out issue.
4 replies
DTDrizzle Team
Created by Chi Hao on 3/9/2024 in #help
Connection timeout when connecting supabase to astro project while using Drizzle kit
This is my drizzle.config.ts 
import type { Config } from "drizzle-kit";
import 'dotenv/config';

const URL = process.env.SECRET_SUPABASE_URL;

if (!URL) {
    throw new Error("DATABASE_URL is missing");
}

export default {
        schema: "./src/lib/schema.ts",
        out: "./drizzle",
        dbCredentials: {
            connectionString: URL,
        },
        driver: "pg",
        verbose: true,
        strict: true,  
} satisfies Config;
import type { Config } from "drizzle-kit";
import 'dotenv/config';

const URL = process.env.SECRET_SUPABASE_URL;

if (!URL) {
    throw new Error("DATABASE_URL is missing");
}

export default {
        schema: "./src/lib/schema.ts",
        out: "./drizzle",
        dbCredentials: {
            connectionString: URL,
        },
        driver: "pg",
        verbose: true,
        strict: true,  
} satisfies Config;
this is my src/lib/db.ts 
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import type { MyDatabase } from "@/lib/schema";

export const connectionString = import.meta.env.SECRET_SUPABASE_URL;

declare global {
    var db: MyDatabase | undefined;
}

let db: MyDatabase;

if (!global.db) {
    const client = postgres(connectionString, { prepare: false })

    global.db = drizzle(client, { logger: true });
}

db = global.db;

export default db;
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import type { MyDatabase } from "@/lib/schema";

export const connectionString = import.meta.env.SECRET_SUPABASE_URL;

declare global {
    var db: MyDatabase | undefined;
}

let db: MyDatabase;

if (!global.db) {
    const client = postgres(connectionString, { prepare: false })

    global.db = drizzle(client, { logger: true });
}

db = global.db;

export default db;
this is the script at package.json 
    "db:push": "drizzle-kit push:pg",
    "db:push": "drizzle-kit push:pg",
my .env variables' names looks like this 
SECRET_SUPABASE_URL=
SECRET_SUPABASE_ANON_KEY=
SECRET_SUPABASE_URL=
SECRET_SUPABASE_ANON_KEY=
4 replies