Skip to content

SQL_QUERIES.md

Latest commit

 

History

History
115 lines (85 loc) · 2.53 KB

SQL_QUERIES.md

File metadata and controls

115 lines (85 loc) · 2.53 KB

Pure SQL Queries Reference

This document lists all the SQL queries used in the application after removing TypeORM.

Users Service Queries

1. Create User

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

Parameters:

  • $1: email (string)
  • $2: hashed password (string)
  • $3: firstName (string)
  • $4: lastName (string)

Returns: Full user object

Usage: UsersService.create(email, password, firstName, lastName)

2. Find User by Email

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

Parameters:

  • $1: email (string)

Returns: User object or throws NotFoundException

Usage: UsersService.findByEmail(email)

3. Find User by ID

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

Parameters:

  • $1: id (UUID string)

Returns: User object or throws NotFoundException

Usage: UsersService.findById(id)

Database Indexes

Users Email Index

CREATE INDEX idx_users_email ON users(email)

Purpose: Optimizes findByEmail() queries for faster lookups

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
)

Parameter Safety

All queries use parameterized statements ($1, $2, etc.) which:

  • Prevent SQL injection attacks
  • Are handled by the pg library
  • Automatically escape special characters

Query Execution

All queries are executed through DatabaseService.query(text, params):

const result = await this.databaseService.query(queryText, [param1, param2, ...]);
// result.rows contains the returned rows
// result.rowCount contains the number of affected rows

Transaction Support

For operations requiring transactions:

await this.databaseService.transaction(async (client) => {
  // Execute multiple queries within the transaction
  await client.query(queryText1, params1);
  await client.query(queryText2, params2);
  // Automatically commits on success or rolls back on error
});