Skip to content

README_PURE_SQL.md

Latest commit

 

History

History
339 lines (267 loc) · 7.31 KB

README_PURE_SQL.md

File metadata and controls

339 lines (267 loc) · 7.31 KB

Task API - Pure SQL Implementation

A NestJS-based Task API using pure SQL queries with PostgreSQL instead of TypeORM.

✨ What Changed

This project has been successfully migrated from TypeORM to pure SQL. All database operations now use direct PostgreSQL queries with parameterized statements for security.

📁 Project Structure

src/
├── auth/                    # Authentication module (unchanged)
│   ├── auth.module.ts
│   ├── auth.service.ts
│   ├── auth.resolver.ts
│   ├── dto/
│   │   └── auth.dto.ts
│   ├── guards/
│   │   └── jwt.guard.ts
│   └── strategies/
│       └── jwt.strategy.ts
├── database/               # NEW: Pure SQL database service
│   ├── database.module.ts
│   └── database.service.ts
├── users/                  # Users module (updated for pure SQL)
│   ├── users.module.ts
│   ├── users.service.ts
│   ├── users.resolver.ts
│   └── entities/
│       └── user.entity.ts (simplified)
├── config/
│   └── database.config.ts  # Updated for pg pool config
├── app.module.ts           # Updated (removed TypeORM)
├── app.controller.ts
├── app.service.ts
└── main.ts

🗄️ Database Service

The new DatabaseService (src/database/database.service.ts) provides:

  • Connection pooling: Manages multiple connections efficiently
  • Query execution: query(text, params) method for running SQL queries
  • Transactions: transaction(callback) method for ACID transactions
  • Auto-initialization: Creates tables on module initialization
  • Clean lifecycle: Properly closes connections on module destruction

Core Methods

// Execute a query
const result = await databaseService.query(
  'SELECT * FROM users WHERE email = $1',
  ['user@example.com']
);

// Get raw client for complex operations
const client = await databaseService.getClient();

// Execute transaction
await databaseService.transaction(async (client) => {
  await client.query('INSERT INTO users ...');
  await client.query('INSERT INTO tasks ...');
});

🔍 Pure SQL Queries

All database operations use parameterized SQL queries to prevent SQL injection:

Users Operations

Create User:

INSERT INTO users (email, password, "firstName", "lastName")
VALUES ($1, $2, $3, $4)
RETURNING id, email, password, "firstName", "lastName", "isVerified", "createdAt", "updatedAt"

Find by Email:

SELECT id, email, password, "firstName", "lastName", "isVerified", "createdAt", "updatedAt"
FROM users
WHERE email = $1

Find by ID:

SELECT id, email, password, "firstName", "lastName", "isVerified", "createdAt", "updatedAt"
FROM users
WHERE id = $1

See SQL_QUERIES.md for detailed query documentation.

🚀 Getting Started

Prerequisites

  • Node.js 18+
  • PostgreSQL 12+
  • npm or yarn

Installation

  1. Clone and install:
npm install
  1. Create .env file:
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
  1. Initialize database (creates schema):
npm run db:init
  1. Start development server:
npm run start:dev

The GraphQL API will be available at http://localhost:3000/graphql

📚 Available Scripts

# Build the application
npm run build

# Start development server with watch mode
npm run start:dev

# Start debug mode
npm run start:debug

# Production build
npm run build && npm run start:prod

# Initialize database schema
npm run db:init

# Format code
npm run format

# Lint code
npm run lint

# Run tests
npm run test

# Run tests with coverage
npm run test:cov

# Run e2e tests
npm run test:e2e

🔐 Authentication

  • JWT-based authentication
  • Password hashing with bcryptjs
  • Passport.js strategy integration
  • Protected GraphQL mutations and queries

📊 Database Schema

Users Table

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

🔄 GraphQL API

Mutations

Sign Up:

mutation {
  signUp(input: {
    email: "user@example.com"
    password: "password123"
    firstName: "John"
    lastName: "Doe"
  }) {
    accessToken
    id
    email
    firstName
    lastName
  }
}

Sign In:

mutation {
  signIn(input: {
    email: "user@example.com"
    password: "password123"
  }) {
    accessToken
    id
    email
    firstName
    lastName
  }
}

Queries

Get Current User (requires authentication):

query {
  getUser {
    id
    email
    firstName
    lastName
    isVerified
  }
}

Get User by ID:

query {
  getUserById(id: "uuid-here") {
    id
    email
    firstName
    lastName
  }
}

🛡️ Security Features

  • ✅ SQL injection prevention (parameterized queries)
  • ✅ Password hashing (bcryptjs)
  • ✅ JWT authentication
  • ✅ Type-safe database operations
  • ✅ CORS protected endpoints
  • ✅ Class validation with class-validator

📝 Migration Notes

See MIGRATION.md for detailed information about the migration from TypeORM to pure SQL.

Key Changes:

  1. Removed @nestjs/typeorm and typeorm dependencies
  2. Created DatabaseService for connection pooling and query execution
  3. Replaced repository pattern with direct SQL queries
  4. Simplified entity definitions (GraphQL types only)
  5. Updated all service methods to use DatabaseService

🧪 Testing

The application includes:

  • Unit tests for services
  • E2E tests for API endpoints
  • Jest configuration for test running
# Run unit tests
npm run test

# Run tests in watch mode
npm run test:watch

# Run tests with coverage report
npm run test:cov

# Run E2E tests
npm run test:e2e

🔧 Configuration

All configuration is managed through environment variables:

Variable Default Description
DATABASE_HOST 127.0.0.1 PostgreSQL host
DATABASE_PORT 5432 PostgreSQL port
DATABASE_USER postgres Database user
DATABASE_PASSWORD admin Database password
DATABASE_NAME task_api_db Database name
JWT_SECRET secret JWT signing secret
NODE_ENV development Environment mode

📄 Documentation Files

  • MIGRATION.md - Detailed migration guide
  • SQL_QUERIES.md - Reference for all SQL queries used
  • SETUP.md - Setup instructions
  • PSQL_HINTS.md - PostgreSQL tips and hints

🤝 Contributing

When adding new features:

  1. Write SQL queries with parameterized statements
  2. Add methods to DatabaseService if needed
  3. Use the query method from services
  4. Update documentation with new queries
  5. Ensure type safety with TypeScript interfaces

📄 License

UNLICENSED

🆘 Support

For issues or questions about the pure SQL implementation, refer to:

  • SQL_QUERIES.md for query examples
  • MIGRATION.md for migration details
  • DatabaseService comments for API usage