Hybrid Authentication with MySQL (Drizzle)

MySQL implementation of hybrid authentication using Drizzle ORM for users and Redis for session tracking and refresh token rotation.

Installation Guide

Run the following command:

npx servercn-cli@latest add bp hybrid-auth

Add the following scripts to your package.json:

package.json

  "scripts": {
    "dev": "cross-env NODE_ENV=development npx tsx watch --clearScreen=false src/server.ts",
    "build": "rm -rf dist && tsc && tsc-alias",
    "start": "cross-env NODE_ENV=production node dist/server.js",
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio",
    "typecheck": "tsc --noEmit",
    "docs": "npx tsx swagger.config.ts",
    "prepare": "husky",
    "lint:check": "eslint .",
    "lint:fix": "eslint . --fix",
    "format:check": "npx prettier . --check",
    "format:fix": "npx prettier . --write"
  },

Key Features

MySQL + Drizzle ORM for type-safe database operations
JWT access/refresh token rotation with Redis-backed sessions
OAuth support (GitHub, Google) via Passport.js
OTP-based email verification and password reset
Account lockout after failed login attempts
Soft/hard account deletion with reactivation support
Cloudinary file upload integration for avatars
Rate limiting and security headers

API Endpoints

All endpoints are the same for both MVC and Feature architectures.

Authentication Routes

Base URL: `http://localhost:9000/api/v1/auth`

Method	Endpoint	Description
POST	/signup	Register new user and send verification OTP
POST	/verify-user	Verify email with OTP
POST	/signin	Login user and set auth cookies
GET	/profile	Get current user profile
PATCH	/profile	Update profile and avatar
GET	/sessions	List active sessions
DELETE	/sessions	Revoke all sessions except current
DELETE	/sessions/:sessionId	Revoke a specific session
POST	/refresh-token	Rotate access and refresh tokens
POST	/logout	Logout current session
POST	/forgot-password	Request password reset OTP
POST	/verify-reset-otp	Verify password reset OTP
POST	/reset-password	Reset password after OTP verification
POST	/change-password	Change password and revoke sessions
POST	/account/request-delete	Request account deletion token
DELETE	/account/delete	Delete or deactivate account by token
PUT	/account/reactivate	Reactivate soft-deleted account

OAuth Routes

Base URL: `http://localhost:9000/api/auth`

Method	Endpoint	Description
GET	/github	Initiate GitHub OAuth
GET	/github/callback	GitHub OAuth callback
GET	/google	Initiate Google OAuth
GET	/google/callback	Google OAuth callback

Health Check

Base URL: `http://localhost:9000/api/v1/health`

Method	Endpoint	Description
GET	/	Basic health check
GET	/detailed	Detailed health status

Environment Variables

Create a .env file with the following:

PORT='9000'
NODE_ENV='development'
LOG_LEVEL='info'
CORS_ORIGIN=''
CLIENT_URL=''
 
DATABASE_URL='mySQL://user:password@localhost:5432/mydb'
REDIS_URL=''
 
JWT_ACCESS_SECRET=''
JWT_REFRESH_SECRET=''
CRYPTO_SECRET=''
 
RESEND_API_KEY=''
EMAIL_FROM=''
 
CLOUDINARY_CLOUD_NAME=''
CLOUDINARY_API_KEY=''
CLOUDINARY_API_SECRET=''
 
GOOGLE_CLIENT_ID=''
GOOGLE_CLIENT_SECRET=''
GOOGLE_REDIRECT_URI=''
 
GITHUB_CLIENT_ID=''
GITHUB_CLIENT_SECRET=''
GITHUB_REDIRECT_URI=''