93 lines
3.4 KiB
TypeScript
93 lines
3.4 KiB
TypeScript
import { drizzle } from "drizzle-orm/node-postgres";
|
|
import { eq } from "drizzle-orm";
|
|
import { users, User, NewUser } from "../shared/schema";
|
|
import { Pool } from "pg";
|
|
|
|
// PostgreSQL connection (local on production droplet)
|
|
const pool = new Pool({
|
|
host: process.env.DB_HOST || "127.0.0.1",
|
|
port: parseInt(process.env.DB_PORT || "5432"),
|
|
user: process.env.DB_USER || "gn_admin",
|
|
password: process.env.DB_PASSWORD || "",
|
|
database: process.env.DB_NAME || "gammanexus",
|
|
ssl: (process.env.DB_SSL === "true" && process.env.NODE_ENV === "production") ? { rejectUnauthorized: false } : false,
|
|
max: 20,
|
|
idleTimeoutMillis: 30000,
|
|
connectionTimeoutMillis: 5000,
|
|
});
|
|
|
|
export const db = drizzle(pool);
|
|
|
|
// Initialize users table if it doesn't exist (called at startup)
|
|
export async function initDb(): Promise<void> {
|
|
await db.execute(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
verification_token TEXT,
|
|
verification_token_expiry TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
|
|
)
|
|
`);
|
|
|
|
// Add columns if they don't exist (for existing tables)
|
|
await addColumnIfExists('email_verified', 'BOOLEAN NOT NULL DEFAULT FALSE');
|
|
await addColumnIfExists('verification_token', 'TEXT');
|
|
await addColumnIfExists('verification_token_expiry', 'TIMESTAMP WITH TIME ZONE');
|
|
}
|
|
|
|
async function addColumnIfExists(column: string, typeDef: string): Promise<void> {
|
|
try {
|
|
await db.execute(`ALTER TABLE users ADD COLUMN ${column} ${typeDef}`);
|
|
} catch {
|
|
// Column already exists or table doesn't exist — ignore
|
|
}
|
|
}
|
|
|
|
export async function createUser(data: NewUser): Promise<User> {
|
|
const [user] = await db.insert(users).values(data).returning();
|
|
return user;
|
|
}
|
|
|
|
export async function getUserByEmail(email: string): Promise<User | undefined> {
|
|
const result = await db.select().from(users).where(eq(users.email, email.toLowerCase()));
|
|
return result[0];
|
|
}
|
|
|
|
export async function getUserById(id: number): Promise<User | undefined> {
|
|
const result = await db.select().from(users).where(eq(users.id, id));
|
|
return result[0];
|
|
}
|
|
|
|
export async function updateUser(id: number, data: Partial<Pick<User, "name" | "email">>): Promise<User> {
|
|
const result = await db.update(users).set(data).where(eq(users.id, id)).returning();
|
|
return result[0];
|
|
}
|
|
|
|
export async function updateUserPassword(id: number, passwordHash: string): Promise<User> {
|
|
const result = await db.update(users).set({ passwordHash }).where(eq(users.id, id)).returning();
|
|
return result[0];
|
|
}
|
|
|
|
export async function updateUserVerification(id: number, token: string, expiry: Date): Promise<void> {
|
|
await db.update(users).set({ verificationToken: token, verificationTokenExpiry: expiry }).where(eq(users.id, id));
|
|
}
|
|
|
|
export async function getUserByVerificationToken(token: string): Promise<User | undefined> {
|
|
const { and, eq, gt } = await import("drizzle-orm");
|
|
const result = await db.select().from(users).where(
|
|
and(
|
|
eq(users.verificationToken, token),
|
|
gt(users.verificationTokenExpiry, new Date())
|
|
)
|
|
);
|
|
return result[0];
|
|
}
|
|
|
|
export async function markEmailVerified(id: number): Promise<void> {
|
|
await db.update(users).set({ emailVerified: true, verificationToken: null, verificationTokenExpiry: null }).where(eq(users.id, id));
|
|
}
|