Skip to content

MIGRATION.md

Latest commit

 

History

History
137 lines (108 loc) · 4.05 KB

MIGRATION.md

File metadata and controls

137 lines (108 loc) · 4.05 KB

TypeORM to Pure SQL Migration Summary

This document outlines the changes made to remove TypeORM and rewrite the API with pure SQL queries.

Changes Made

1. Dependencies

  • Removed: @nestjs/typeorm and typeorm
  • Kept: pg (Node.js PostgreSQL client)

2. Database Service

  • Created: src/database/database.service.ts

    • Manages PostgreSQL connection pool
    • Provides query execution methods
    • Handles transactions
    • Automatically initializes database schema on module load

  • Created: src/database/database.module.ts

    • Exports DatabaseService for dependency injection

3. App Module

  • Updated: src/app.module.ts
    • Removed TypeOrmModule.forRoot()
    • Added DatabaseModule import
    • Configuration is now handled by DatabaseService

4. Users Service

  • Updated: src/users/users.service.ts
    • Removed @InjectRepository decorator
    • Injected DatabaseService instead
    • Replaced TypeORM methods with raw SQL queries:
      • create(): INSERT query with UUID generation
      • findByEmail(): SELECT query with email parameter
      • findById(): SELECT query with ID parameter
    • Added User interface definition
    • Added mapRowToUser() helper method for type mapping

5. Users Entity

  • Updated: src/users/entities/user.entity.ts
    • Removed all TypeORM decorators (@Entity, @Column, @PrimaryGeneratedColumn, etc.)
    • Kept only GraphQL decorators (@ObjectType, @Field)
    • Removed password field from GraphQL type (security best practice)

6. Users Module

  • Updated: src/users/users.module.ts
    • Removed TypeOrmModule.forFeature([User])
    • Added DatabaseModule import
    • Now uses DatabaseService via UsersService

7. Database Configuration

  • Updated: src/config/database.config.ts
    • Changed from TypeOrmModuleOptions to simple pg pool config
    • Returns connection pool configuration (host, port, user, password, database, pool options)

8. Database Initialization

  • Created: scripts/init-db.ts

    • Standalone script to initialize database schema
    • Creates users table with proper structure
    • Creates indexes for performance
    • Run with: npm run db:init

  • Updated: package.json

    • Added db:init script

Database Schema

The users table is automatically created with the following structure:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  "firstName" VARCHAR(255) NOT NULL,
  "lastName" VARCHAR(255) NOT NULL,
  "isVerified" BOOLEAN DEFAULT false,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

API Methods Now Using Pure SQL

Users Service

  1. create() - INSERT with UUID and password hashing
  2. findByEmail() - SELECT by email parameter
  3. findById() - SELECT by UUID parameter
  4. validatePassword() - Bcrypt password comparison

Setup Instructions

  1. Install dependencies:

    npm install

  2. Initialize database (creates schema):

    npm run db:init

  3. Start development server:

    npm run start:dev

Environment Variables

Make sure your .env file contains:

DATABASE_HOST=127.0.0.1
DATABASE_PORT=5432
DATABASE_USER=postgres
DATABASE_PASSWORD=admin
DATABASE_NAME=task_api_db
JWT_SECRET=your_secret_key
NODE_ENV=development

Benefits of This Approach

  1. No ORM overhead: Direct SQL queries are faster and more efficient
  2. Better control: Full control over query execution and optimization
  3. Reduced dependencies: Fewer dependencies to maintain
  4. Type safety: Interface-based types with TypeScript
  5. Simplified migrations: SQL scripts are easier to understand and maintain
  6. Connection pooling: pg library handles efficient connection management

GraphQL API Unchanged

  • All GraphQL resolvers remain unchanged
  • Auth mutations (signUp, signIn) work the same
  • User queries remain the same
  • JWT authentication is still in place