db-service.ts•4.39 kB
import { Pool, QueryResult, QueryResultRow } from "pg";
import { User, Product, Order } from "../types/db.types.js";
import { USE_PGLITE } from "../config/db-config.js";
import { PGlite } from "@electric-sql/pglite";
interface PGliteResult {
rows: any[];
}
class DatabaseService {
private pool: Pool | PGlite;
private isPGlite: boolean;
constructor() {
this.isPGlite = USE_PGLITE;
if (this.isPGlite) {
this.pool = new PGlite();
} else {
this.pool = new Pool({
user: process.env.DB_USER || "postgres",
host: process.env.DB_HOST || "localhost",
database: process.env.DB_NAME || "json_demo",
password: process.env.DB_PASSWORD || "postgres",
port: parseInt(process.env.DB_PORT || "5432", 10),
});
}
}
async initialize(): Promise<void> {
try {
if (this.isPGlite) {
await (this.pool as PGlite).query("SELECT 1");
console.log("PGlite initialized");
} else {
await (this.pool as Pool).connect();
console.log("Successfully connected to PostgreSQL");
}
} catch (error) {
console.error("Error initializing database:", error);
throw error;
}
}
async query<T extends QueryResultRow = any>(
sql: string,
params?: any[]
): Promise<QueryResult<T>> {
try {
if (this.isPGlite) {
const result = (await (this.pool as PGlite).query(
sql,
params
)) as PGliteResult;
return {
rows: result.rows as T[],
command: "",
rowCount: result.rows.length,
oid: 0,
fields: [],
};
} else {
return await (this.pool as Pool).query<T>(sql, params);
}
} catch (error) {
console.error("Error executing query:", error);
throw error;
}
}
async createTables(): Promise<void> {
const createTablesSQL = `
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
metadata JSONB DEFAULT '{}',
preferences JSONB DEFAULT '{"theme": "light", "notifications": true, "language": "en"}'
);
CREATE TABLE IF NOT EXISTS products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
attributes JSONB DEFAULT '{}',
tags JSONB DEFAULT '[]'
);
CREATE TABLE IF NOT EXISTS orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
items JSONB NOT NULL,
status VARCHAR(50) NOT NULL,
shipping_address JSONB NOT NULL
);
`;
try {
await this.query(createTablesSQL);
console.log("Tables created successfully");
} catch (error) {
console.error("Error creating tables:", error);
throw error;
}
}
async insertUser(user: Omit<User, "id">): Promise<User> {
const sql = `
INSERT INTO users (name, email, metadata, preferences)
VALUES ($1, $2, $3, $4)
RETURNING *
`;
const result = await this.query<User>(sql, [
user.name,
user.email,
user.metadata,
user.preferences,
]);
return result.rows[0];
}
async insertProduct(product: Omit<Product, "id">): Promise<Product> {
const sql = `
INSERT INTO products (name, price, attributes, tags)
VALUES ($1, $2, $3, $4)
RETURNING *
`;
const result = await this.query<Product>(sql, [
product.name,
product.price,
product.attributes,
product.tags,
]);
return result.rows[0];
}
async insertOrder(order: Omit<Order, "id">): Promise<Order> {
const sql = `
INSERT INTO orders (user_id, items, status, shipping_address)
VALUES ($1, $2, $3, $4)
RETURNING *
`;
const result = await this.query<Order>(sql, [
order.user_id,
order.items,
order.status,
order.shipping_address,
]);
return result.rows[0];
}
async close(): Promise<void> {
try {
if (this.isPGlite) {
await (this.pool as PGlite).close();
} else {
await (this.pool as Pool).end();
}
console.log("Database connection closed");
} catch (error) {
console.error("Error closing database connection:", error);
throw error;
}
}
}
export default new DatabaseService();