oxinto-backend / src /config /database.js
Ahsan
Deploy Oxinto backend 2026-04-23 00:01
f4656a0
Raw History Blame Contribute Delete
7.32 kB
'use strict';
const path = require('path');
const fs = require('fs');
const logger = require('../utils/logger');
let db = null;
let dbType = null;
/**
* Initialize database connection based on DB_TYPE env variable.
* Uses SQLite for development and PostgreSQL for production.
*/
async function initDatabase() {
// Idempotent — safe to call multiple times
if (db) return db;
dbType = process.env.DB_TYPE || 'sqlite';
if (dbType === 'sqlite') {
return initSQLite();
} else {
return initPostgres();
}
}
function initSQLite() {
const Database = require('better-sqlite3');
const dbPath = process.env.DB_PATH || './data/database.db';
const resolvedPath = path.resolve(dbPath);
// Ensure data directory exists
const dir = path.dirname(resolvedPath);
if (!fs.existsSync(dir)) {
fs.mkdirSync(dir, { recursive: true });
}
db = new Database(resolvedPath);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');
logger.info(`SQLite database initialized at: ${resolvedPath}`);
createTables();
return db;
}
async function initPostgres() {
const { Pool } = require('pg');
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
// Test connection
const client = await pool.connect();
await client.query('SELECT NOW()');
client.release();
db = pool;
logger.info('PostgreSQL database connected');
await createTablesPg();
return db;
}
/**
* Create all tables for SQLite.
*/
function createTables() {
db.exec(`
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
price REAL NOT NULL,
category TEXT NOT NULL,
tags TEXT DEFAULT '[]',
features TEXT DEFAULT '[]',
images TEXT DEFAULT '[]',
stock INTEGER DEFAULT 100,
active INTEGER DEFAULT 1,
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
channel TEXT NOT NULL,
user_id TEXT NOT NULL,
current_question_index INTEGER DEFAULT 0,
responses TEXT DEFAULT '[]',
completed INTEGER DEFAULT 0,
recommendations TEXT,
metadata TEXT DEFAULT '{}',
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now')),
expires_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_channel ON sessions(user_id, channel);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_products_active ON products(active);
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now')),
last_seen_at TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE TABLE IF NOT EXISTS user_activity (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
session_id TEXT,
action TEXT NOT NULL,
category TEXT,
data TEXT DEFAULT '{}',
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_activity_user ON user_activity(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_action ON user_activity(action);
CREATE TABLE IF NOT EXISTS temp_users (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
otp TEXT NOT NULL,
otp_expires_at TEXT NOT NULL,
otp_attempts INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_temp_users_email ON temp_users(email);
CREATE INDEX IF NOT EXISTS idx_temp_users_expires ON temp_users(otp_expires_at);
CREATE TABLE IF NOT EXISTS password_resets (
id TEXT PRIMARY KEY,
email TEXT NOT NULL,
otp TEXT NOT NULL,
otp_expires_at TEXT NOT NULL,
otp_attempts INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_pw_resets_email ON password_resets(email);
CREATE TABLE IF NOT EXISTS google_users (
id TEXT PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
google_uid TEXT UNIQUE NOT NULL,
name TEXT,
user_id INTEGER,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_google_users_email ON google_users(email);
CREATE INDEX IF NOT EXISTS idx_google_users_uid ON google_users(google_uid);
`);
logger.info('SQLite tables created/verified');
}
async function createTablesPg() {
await db.query(`
CREATE TABLE IF NOT EXISTS products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
category VARCHAR(100) NOT NULL,
tags JSONB DEFAULT '[]',
features JSONB DEFAULT '[]',
images JSONB DEFAULT '[]',
stock INTEGER DEFAULT 100,
active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS sessions (
id UUID PRIMARY KEY,
channel VARCHAR(50) NOT NULL,
user_id VARCHAR(255) NOT NULL,
current_question_index INTEGER DEFAULT 0,
responses JSONB DEFAULT '[]',
completed BOOLEAN DEFAULT false,
recommendations JSONB,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
expires_at TIMESTAMP NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_channel ON sessions(user_id, channel);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_products_active ON products(active);
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
last_seen_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS user_activity (
id SERIAL PRIMARY KEY,
user_id VARCHAR(255) NOT NULL,
session_id VARCHAR(255),
action VARCHAR(100) NOT NULL,
category VARCHAR(100),
data JSONB DEFAULT '{}',
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_activity_user ON user_activity(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_action ON user_activity(action);
`);
logger.info('PostgreSQL tables created/verified');
}
function getDb() {
if (!db) throw new Error('Database not initialized. Call initDatabase() first.');
return db;
}
function getDbType() {
return dbType || process.env.DB_TYPE || 'sqlite';
}
module.exports = { initDatabase, getDb, getDbType };