DT
Drizzle Team•4mo ago
Rorsch

How to Normalize Database using Drizzle?

I previously have made my Order Table to only support one service/line item back then (based on the business logic back then). Now, I need to normalize my database, so that it supports multiple services/line items in a single order. I have tried to write the migration file but it's not running.
Solution:
Node can’t execute ts file. You can try to run your script with tsx. npx tsx path/to/your/file.ts...
Jump to solution
5 Replies
Rorsch
Rorsch•4mo ago
Here is my table 
export const orders = pgTable("order", {
  id: varchar("id", { length: 25 }).primaryKey(),
  customerId: varchar("customer_id", { length: 26 }).notNull(),
  serviceId: varchar("service_id", { length: 25 }).notNull(),
  qty: smallint("qty").notNull(),
  status: statusEnum("status").notNull(),
  note: varchar("note", { length: 256 }),
  price: bigint("price", { mode: "number" }).notNull(),
  totalPrice: bigint("total_price", { mode: "number" }).notNull(),
  paymentMethodId: varchar("payment_method_id", { length: 27 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const newOrders = pgTable("new_order", {
  id: varchar("id", { length: 25 }).primaryKey(),
  customerId: varchar("customer_id", { length: 26 }).notNull(),
  status: statusEnum("status").notNull(),
  note: varchar("note", { length: 256 }),
  totalPrice: bigint("total_price", { mode: "number" }).notNull(),
  paymentMethodId: varchar("payment_method_id", { length: 27 }).notNull(),
  creditId: varchar("credit_id", { length: 24 }),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

export const orderLineItems = pgTable("order_line_item", {
  id: varchar("id", { length: 27 }).primaryKey(), //ordli_
  orderId: varchar("order_id", { length: 25 })
    .references(() => newOrders.id)
    .notNull(),
  serviceId: varchar("service_id", { length: 26 })
    .references(() => services.id)
    .notNull(),
  qty: smallint("qty").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});
export const orders = pgTable("order", {
  id: varchar("id", { length: 25 }).primaryKey(),
  customerId: varchar("customer_id", { length: 26 }).notNull(),
  serviceId: varchar("service_id", { length: 25 }).notNull(),
  qty: smallint("qty").notNull(),
  status: statusEnum("status").notNull(),
  note: varchar("note", { length: 256 }),
  price: bigint("price", { mode: "number" }).notNull(),
  totalPrice: bigint("total_price", { mode: "number" }).notNull(),
  paymentMethodId: varchar("payment_method_id", { length: 27 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const newOrders = pgTable("new_order", {
  id: varchar("id", { length: 25 }).primaryKey(),
  customerId: varchar("customer_id", { length: 26 }).notNull(),
  status: statusEnum("status").notNull(),
  note: varchar("note", { length: 256 }),
  totalPrice: bigint("total_price", { mode: "number" }).notNull(),
  paymentMethodId: varchar("payment_method_id", { length: 27 }).notNull(),
  creditId: varchar("credit_id", { length: 24 }),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

export const orderLineItems = pgTable("order_line_item", {
  id: varchar("id", { length: 27 }).primaryKey(), //ordli_
  orderId: varchar("order_id", { length: 25 })
    .references(() => newOrders.id)
    .notNull(),
  serviceId: varchar("service_id", { length: 26 })
    .references(() => services.id)
    .notNull(),
  qty: smallint("qty").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});
and this is what I wrote for the migration 
import { db } from "../config";
import { newOrders, orderLineItems, orders } from "../schema";

async function migrateOrders() {
  const oldOrders = await db.select().from(orders);

  await db.transaction(async (tx) => {
    for (const oldOrder of oldOrders) {
      // Insert into newOrders table
      const newOrder = {
        id: oldOrder.id,
        customerId: oldOrder.customerId,
        status: oldOrder.status,
        note: oldOrder.note,
        totalPrice: oldOrder.totalPrice,
        paymentMethodId: oldOrder.paymentMethodId,
        creditId: oldOrder.creditId,
        createdAt: oldOrder.createdAt,
        updatedAt: oldOrder.updatedAt,
      };
      await tx.insert(newOrders).values(newOrder);

      // Insert into orderLineItems table
      const lineItem = {
        id: "ordli_" + nanoid(),
        orderId: oldOrder.id,
        serviceId: oldOrder.serviceId,
        qty: oldOrder.qty,
        createdAt: oldOrder.createdAt,
        updatedAt: oldOrder.updatedAt,
      };
      await tx.insert(orderLineItems).values(lineItem);
    }
  });
}

// Run the migration
migrateOrders()
  .then(() => {
    console.log("Migration complete!");
  })
  .catch((err) => {
    console.error("Migration failed:", err);
  });
import { db } from "../config";
import { newOrders, orderLineItems, orders } from "../schema";

async function migrateOrders() {
  const oldOrders = await db.select().from(orders);

  await db.transaction(async (tx) => {
    for (const oldOrder of oldOrders) {
      // Insert into newOrders table
      const newOrder = {
        id: oldOrder.id,
        customerId: oldOrder.customerId,
        status: oldOrder.status,
        note: oldOrder.note,
        totalPrice: oldOrder.totalPrice,
        paymentMethodId: oldOrder.paymentMethodId,
        creditId: oldOrder.creditId,
        createdAt: oldOrder.createdAt,
        updatedAt: oldOrder.updatedAt,
      };
      await tx.insert(newOrders).values(newOrder);

      // Insert into orderLineItems table
      const lineItem = {
        id: "ordli_" + nanoid(),
        orderId: oldOrder.id,
        serviceId: oldOrder.serviceId,
        qty: oldOrder.qty,
        createdAt: oldOrder.createdAt,
        updatedAt: oldOrder.updatedAt,
      };
      await tx.insert(orderLineItems).values(lineItem);
    }
  });
}

// Run the migration
migrateOrders()
  .then(() => {
    console.log("Migration complete!");
  })
  .catch((err) => {
    console.error("Migration failed:", err);
  });
This is the error that I got. I tried to use require instead but it's not working as well. 
➜  admin-dashboard git:(order-migration) ✗ node app/db/migration/20240712_154500_migrate_orders_to_new_schema.ts
(node:5815) Warning: To load an ES module, set "type": "module" in the package.json or use the .mjs extension.
(Use `node --trace-warnings ...` to show where the warning was created)
/Users/user/Developer/Projects/laundry-admin-dashboard/app/db/migration/20240712_154500_migrate_orders_to_new_schema.ts:1
import { db } from "../config"; // Import your DB connection
^^^^^^

SyntaxError: Cannot use import statement outside a module
    at internalCompileFunction (node:internal/vm:77:18)
    at wrapSafe (node:internal/modules/cjs/loader:1288:20)
    at Module._compile (node:internal/modules/cjs/loader:1340:27)
    at Module._extensions..js (node:internal/modules/cjs/loader:1435:10)
    at Module.load (node:internal/modules/cjs/loader:1207:32)
    at Module._load (node:internal/modules/cjs/loader:1023:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:135:12)
    at node:internal/main/run_main_module:28:49

Node.js v20.11.0
➜  admin-dashboard git:(order-migration) ✗ node app/db/migration/20240712_154500_migrate_orders_to_new_schema.ts
(node:5815) Warning: To load an ES module, set "type": "module" in the package.json or use the .mjs extension.
(Use `node --trace-warnings ...` to show where the warning was created)
/Users/user/Developer/Projects/laundry-admin-dashboard/app/db/migration/20240712_154500_migrate_orders_to_new_schema.ts:1
import { db } from "../config"; // Import your DB connection
^^^^^^

SyntaxError: Cannot use import statement outside a module
    at internalCompileFunction (node:internal/vm:77:18)
    at wrapSafe (node:internal/modules/cjs/loader:1288:20)
    at Module._compile (node:internal/modules/cjs/loader:1340:27)
    at Module._extensions..js (node:internal/modules/cjs/loader:1435:10)
    at Module.load (node:internal/modules/cjs/loader:1207:32)
    at Module._load (node:internal/modules/cjs/loader:1023:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:135:12)
    at node:internal/main/run_main_module:28:49

Node.js v20.11.0
Solution
rphlmr ⚡
rphlmr ⚡•4mo ago
Node can’t execute ts file. You can try to run your script with tsx. npx tsx path/to/your/file.ts
Rorsch
Rorsch•4mo ago
@Raphaël M (@rphlmr) ⚡ thank you! it's working now, but I need to use dotenv on my DB config to read the env file. I have another question just for my improvements. Am I doing the database normalization the right way as it should be? Are people in the industry doing it this way as well? Or there should be a better way to tackle this kind of problem? I'm currently saving the migration script as /db/migration/YYYYMMDD_migrate_orders_to_new_schema.ts and I will be commiting the script to git.
rphlmr ⚡
rphlmr ⚡•4mo ago
Oh you can also do something like that: dotenv -e .env -- npx xxx (with "dotenv-cli" in dev dep) It always depends, but from experience, I do the same thing as new_order and order_line_item. Most of the time, I use arrays instead of single values too for things that can evolve to ‘oh, now we can have more than one thing here’ (so, db normalization)) it is more code at first, but less pain in the future. I am far from being a database expert, and it took me so much time to decide on a design 😂
I'm currently saving the migration script as /db/migration/YYYYMMDD_migrate_orders_to_new_schema.ts and I will be commiting the script to git.
This is the way, a project I work on for years now has many of these scripts
Rorsch
Rorsch•4mo ago
thank you for your insights! at least i think I'm on the right track 😄
Want results from more Discord servers?
Add your server