Migrate from TypeORM 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 dataSource from '../db/typeorm.config';
import { Supplier } from '../entities/supplier.entity';

const repository = dataSource.getRepository(Supplier);

const suppliers = repository.create([
  {
    companyName: 'TestCompanyName1',
    city: 'TestCity1',
    country: 'TestCountry1',
  },
  {
    companyName: 'TestCompanyName2',
    city: 'TestCity2',
    country: 'TestCountry2',
  },
]);

await repository.save(suppliers);

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 dataSource from '../db/typeorm.config';
import { Product } from '../entities/product.entity';

const repository = dataSource.getRepository(Product);

const products = repository.create([
  {
    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 repository.save(products);

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 dataSource from '../db/typeorm.config';
import { Product } from '../entities/product.entity';

const { id } = req.params;

const repository = dataSource.getRepository(Product);

const response = await repostitory.findOne({
  where: { id },
  relations: ['supplier'],
});

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 { ILike } from 'typeorm';
import dataSource from '../db/typeorm.config';
import { Product } from '../entities/product.entity';

const repository = dataSource.getRepository(Product);

const response = await repostitory.findAndCount({
  skip: 0,
  take: 10,
  where: {
    name: ILike(`%test%`),
  },
  select: ['id', 'name', 'unitPrice', 'unitsInStock'],
});

import dataSource from '../db/typeorm.config';
import { Order } from '../entities/order.entity';

const { id } = req.params;

const orderRepository = dataSource.getRepository(Order);

const orderQueryBuilder = orderRepository.createQueryBuilder('order');

const response = await orderQueryBuilder
  .select([
    'order.id as id',
    'order.orderDate as "orderDate"',
    'order.shipCountry as "shipCountry"',
    'SUM(product.unitPrice * detail.quantity)::float as "totalPrice"',
    'SUM(detail.quantity)::int as "totalQuantity"',
    'COUNT(detail.productId)::int as "totalProducts"',
  ])
  .leftJoin('order.orderDetails', 'detail')
  .leftJoin('detail.product', 'product')
  .groupBy('order.id')
  .where('order.id = :id', { id })
  .getRawOne();

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 dataSource from '../db/typeorm.config';
import { Supplier } from '../entities/supplier.entity';

const { id } = req.params;

const repository = dataSource.getRepository(Supplier);

const supplier = await repository.findOneBy({ id });
if (!supplier) {
  throw new Error('Supplier not found');
}

supplier.city = 'TestCity1Updated';
supplier.country = 'TestCountry1Updated';

await repository.save(supplier);

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 dataSource from '../db/typeorm.config';
import { OrderDetail } from '../entities/order-detail.entity';
import { Order } from '../entities/order.entity';

const { id } = req.params;

const queryRunner = dataSource.createQueryRunner();

await queryRunner.connect();

await queryRunner.startTransaction();

try {
  await queryRunner.manager.delete(OrderDetail, { orderId: id });

  await queryRunner.manager.delete(Order, { id });

  await queryRunner.commitTransaction();
} catch (e) {
  await queryRunner.rollbackTransaction();

  console.error(e);
} finally {
  await queryRunner.release();
}

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);
}