Migrate from Prisma to Drizzle

import 'dotenv/config'; // make sure to install dotenv package
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  out: './src/drizzle',
  schema: './src/drizzle/schema.ts',
  dbCredentials: {
    host: process.env.DB_HOST!,
    port: Number(process.env.DB_PORT!),
    user: process.env.DB_USERNAME!,
    password: process.env.DB_PASSWORD!,
    database: process.env.DB_NAME!,
  },
  // Print all statements
  verbose: true,
  // Always ask for confirmation
  strict: true,
});

// ...other imports
import { relations } from 'drizzle-orm';

// ...other tables
export const suppliersRelations = relations(suppliers, ({ many }) => ({
  products: many(products),
}));

export const productsRelations = relations(products, ({ one, many }) => ({
  supplier: one(suppliers, { fields: [products.supplierId], references: [suppliers.id] }),
  orderDetails: many(orderDetails),
}));

export const ordersRelations = relations(orders, ({ many }) => ({
  orderDetails: many(orderDetails),
}));

export const orderDetailsRelations = relations(orderDetails, ({ one }) => ({
  order: one(orders, { fields: [orderDetails.orderId], references: [orders.id] }),
  product: one(products, { fields: [orderDetails.productId], references: [products.id] }),
}));

import { drizzle } from 'drizzle-orm/node-postgres';
import { Client } from 'pg';
import * as schema from './schema';

export const client = new Client({
  host: process.env.DB_HOST!,
  port: Number(process.env.DB_PORT!),
  user: process.env.DB_USERNAME!,
  password: process.env.DB_PASSWORD!,
  database: process.env.DB_NAME!,
});

// { schema } is used for relational queries
export const db = drizzle({ client, schema });

import 'dotenv/config';
import { client, db } from './drizzle/db';
import { resolve } from 'node:path';
import { migrate } from 'drizzle-orm/node-postgres/migrator';


(async () => {
  await client.connect();

  // This command run all migrations from the migrations folder and apply changes to the database
  await migrate(db, { migrationsFolder: resolve(__dirname, './drizzle') });

  // ... start your application
})();

import { prisma } from '../db/db';

await prisma.supplier.createMany({
  data: [
    { companyName: 'TestCompanyName1', city: 'TestCity1', country: 'TestCountry1' },
    { companyName: 'TestCompanyName2', city: 'TestCity2', country: 'TestCountry2' },
  ],
});

import { db } from '../drizzle/db';
import { suppliers } from '../drizzle/schema';

await db.insert(suppliers).values([
  {
    companyName: 'TestCompanyName1',
    city: 'TestCity1',
    country: 'TestCountry1',
  },
  {
    companyName: 'TestCompanyName2',
    city: 'TestCity2',
    country: 'TestCountry2',
  },
]);

import { prisma } from '../db/db';

await prisma.product.createMany({
  data: [
    { 
      name: 'TestProductName1',
      supplierId: 1,
      unitPrice: 10,
      unitsInStock: 20,
    },
    {
      name: 'TestProductName2',
      supplierId: 1,
      unitPrice: 25,
      unitsInStock: 7,
    },
    {
      name: 'TestProductName3',
      supplierId: 2,
      unitPrice: 50,
      unitsInStock: 17,
    },
    {
      name: 'TestProductName4',
      supplierId: 2,
      unitPrice: 100,
      unitsInStock: 2,
    },
  ],
});

await db.insert(products).values([
  {
    name: 'TestProductName1',
    supplierId: 1,
    unitPrice: '10',
    unitsInStock: 20,
  },
  {
    name: 'TestProductName2',
    supplierId: 1,
    unitPrice: '25',
    unitsInStock: 7,
  },
  {
    name: 'TestProductName3',
    supplierId: 2,
    unitPrice: '50',
    unitsInStock: 17,
  },
  {
    name: 'TestProductName4',
    supplierId: 2,
    unitPrice: '100',
    unitsInStock: 2,
  },
]);

import { prisma } from '../db/db';

const { id } = req.params;

const response = await prisma.product.findUnique({
  where: { id },
  include: {
    supplier: true,
  },
});

import { eq } from 'drizzle-orm';
import { db } from '../drizzle/db';
import { products, suppliers } from '../drizzle/schema';

const { id } = req.params;

const response = await db
  .select({
    product: products,
    supplier: suppliers,
  })
  .from(products)
  .where(eq(products.id, id))
  .leftJoin(suppliers, eq(suppliers.id, products.supplierId));

// or you can use relational queries
const response = await db.query.products.findFirst({
  where: (products, { eq }) => eq(products.id, id),
  with: {
    supplier: true,
  },
});

import { Prisma } from '@prisma/client';
import { prisma } from '../db/db';

const whereOptions: Prisma.ProductWhereInput = {
  name: { contains: 'test', mode: 'insensitive' },
};

const [response, count] = await Promise.all([
  prisma.product.findMany({
    where: whereOptions,
    take: 10,
    skip: 0,
    select: {
      id: true,
      name: true,
      unitPrice: true,
      unitsInStock: true,
    },
  }),
  prisma.product.count({ where: whereOptions }),
]);

import { prisma } from '../db/db';

const { id } = req.params;

const order = await prisma.order.findFirst({
  where: { id },
  select: {
    id: true,
    orderDate: true,
    shipCountry: true,
  },
});
if (!order) {
  throw new Error('Order not found');
}

const { _count, _sum } = await prisma.orderDetail.aggregate({
  where: { orderId: id },
  _sum: {
    quantity: true,
  },
  _count: {
    orderId: true,
  },
});

const totalPrice: Array<{ totalPrice: number }> = await prisma.$queryRaw<number>`
  SELECT SUM(unitPrice * quantity) as "totalPrice"
  FROM order_details
  WHERE "orderId" = ${id}
`;

const response = {
  ...order,
  totalPrice: totalPrice[0].totalPrice,
  totalQuantity: _sum.quantity,
  totalProducts: _count.orderId,
};

import { eq, sql } from 'drizzle-orm';
import { db } from '../drizzle/db';
import { orders, orderDetails, products } from '../drizzle/schema';

const { id } = req.params;

const response = await db
      .select({
        id: orders.id,
        shipCountry: orders.shipCountry,
        orderDate: orders.orderDate,
        totalPrice: sql<number>`cast(sum(${orderDetails.quantity} * ${products.unitPrice}) as float)`,
        totalQuantity: sql<number>`cast(sum(${orderDetails.quantity}) as int)`,
        totalProducts: sql<number>`cast(count(${orderDetails.productId}) as int)`,
      })
      .from(orders)
      .where(eq(orders.id, id))
      .groupBy(orders.id)
      .leftJoin(orderDetails, eq(orderDetails.orderId, orders.id))
      .leftJoin(products, eq(products.id, orderDetails.productId));

import { prisma } from '../db/db';

const { id } = req.params;

const supplier = await prisma.supplier.update({
  where: { id },
  data: { city: 'TestCity1Updated', country: 'TestCountry1Updated' },
});

import { eq } from 'drizzle-orm';
import { db } from '../drizzle/db';
import { suppliers } from '../drizzle/schema';

const { id } = req.params;

await db
    .update(suppliers)
    .set({
      city: 'TestCity1Updated',
      country: 'TestCountry1Updated',
    })
    .where(eq(suppliers.id, id));

import { prisma } from '../db/db';

const { id } = req.params;

const orderDetailQuery = prisma.orderDetail.deleteMany({
  where: { orderId: id },
});

const orderQuery = prisma.order.deleteMany({
  where: { id },
});

await prisma.$transaction([orderDetailQuery, orderQuery]);

import { eq } from 'drizzle-orm';
import { db } from '../drizzle/db';
import { orderDetails, orders } from '../drizzle/schema';

const { id } = req.params;

try {
  await db.transaction(async (tx) => {
    await tx.delete(orderDetails).where(eq(orderDetails.orderId, id));

    await tx.delete(orders).where(eq(orders.id, id));
  });
} catch (e) {
  console.error(e);
}