MiniORM Documentation

import { QueryBuilder, BaseEntityClass } from 'miniorm';

// Create a base query
const baseQuery = new QueryBuilder('main_db', 'users')
    .select(['id', 'name', 'email'])
    .where({ active: true });

// Clone for the same table
const adminQuery = baseQuery.clone()
    .where({ role: 'admin' });

// Clone for a different table
const archivedQuery = baseQuery.clone({ table: 'archived_users' });

// Clone for a different database
const analyticsQuery = baseQuery.clone({ db: 'analytics_db' });

// Clone with entity class
class User extends BaseEntityClass {
    id!: number;
    name!: string;
    email!: string;
}

class AdminUser extends BaseEntityClass {
    static $table = 'admin_users';
    id!: number;
    name!: string;
    permissions!: string[];
}

const userQuery = new QueryBuilder('main_db', User)
    .select(['id', 'name']);

// Clone with different entity
const adminUserQuery = userQuery.clone<AdminUser>({ table: AdminUser });

import { MiniOrm, QueryBuilder } from 'miniorm';

// Setup database connection
MiniOrm.connection('main_db', {
    type: 'mysql',
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'main'
});

// Disconnect from database
const query = new QueryBuilder('main_db', 'users');
await query.disconnect();

// Reconnect to database
await query.reconnect();

// Run query after reconnection
const users = await query
    .select()
    .where({ active: true })
    .run();

import { QueryBuilder } from 'miniorm';

// Build a query
const query = new QueryBuilder('main_db', 'users')
    .select(['id', 'name', 'email'])
    .where({ active: true })
    .order(['created_at'], 'DESC')
    .limit(10);

// Get SQL and parameters
const [sql, params] = query.toSQL();

console.log('SQL Query:', sql);
console.log('Parameters:', params);

// Useful for logging or custom execution
// Example output:
// SQL Query: SELECT id,name,email FROM users WHERE active = ? ORDER BY created_at DESC LIMIT 10
// Parameters: [true]

import { QueryBuilder } from 'miniorm';

// All user inputs are automatically parameterized
const safeQuery = new QueryBuilder('main_db', 'users')
    .select()
    .where({
        username: userInput, // Safe: value will be parameterized
        'email LIKE': `%${userInput}%` // Safe: value will be parameterized
    });

// The isIgnoreField and isIgnoreValue methods check for SQL injection attempts
try {
    const riskyQuery = new QueryBuilder('main_db', 'users')
        .select()
        .where({
            username: "admin'; DROP TABLE users; --" // Will be caught
        });
} catch (error) {
    console.error('SQL injection attempt detected:', error.message);
}

// The QueryBuilder blocks common SQL injection patterns
// - SQL comments (-- or /* */)
// - Multiple statements (;)
// - SQL keywords (UNION, SELECT, DROP, etc.)
// - Hex encoded values (0x...)
// - Unicode control characters
// - And many more

import { BaseEntityClass, QueryBuilder } from 'miniorm';

// Basic entity class
class User extends BaseEntityClass<User> {
    // Table name defaults to lowercase class name ('user')
    id!: number;
    username!: string;
    email!: string;
    active: boolean = true;
    created_at!: Date;
}

// Entity with custom table name
class AdminUser extends BaseEntityClass<AdminUser> {
    static $table = 'administrators'; // Custom table name

    id!: number;
    username!: string;
    permissions!: string[];
}

// Using entities with QueryBuilder
const userQuery = new QueryBuilder<User>('main_db', User);
const adminQuery = new QueryBuilder<AdminUser>('main_db', AdminUser);

// Type-safe queries
const activeUsers = await userQuery
    .select(['id', 'username', 'email']) // TypeScript checks field names
    .where({ active: true })
    .run();

// The result is typed as User[]
activeUsers.forEach(user => {
    console.log(user.username); // TypeScript knows these properties exist
});

import { QueryBuilder } from 'miniorm';

async function authenticateUser(email: string, password: string) {
    const user = await new QueryBuilder('main_db', 'users')
        .select(['id', 'name', 'email', 'password_hash', 'role'])
        .where({ email: email })
        .first();

    if (!user) {
        return { success: false, message: 'User not found' };
    }

    // Verify password (using a hypothetical password verification function)
    const passwordValid = await verifyPassword(password, user.password_hash);

    if (!passwordValid) {
        return { success: false, message: 'Invalid password' };
    }

    // Update last login timestamp
    await new QueryBuilder('main_db', 'users')
        .update({ last_login: new Date() })
        .where({ id: user.id })
        .run();

    // Return user without password hash
    const { password_hash, ...safeUser } = user;
    return { success: true, user: safeUser };
}

import { QueryBuilder } from 'miniorm';

async function getPaginatedUsers(page: number, pageSize: number, filters: any = {}) {
    const offset = (page - 1) * pageSize;

    // Get total count for pagination metadata
    const totalCount = await new QueryBuilder('main_db', 'users')
        .where(filters)
        .count();

    // Get page of users
    const users = await new QueryBuilder('main_db', 'users')
        .select(['id', 'name', 'email', 'role', 'created_at'])
        .where(filters)
        .order(['created_at'], 'DESC')
        .limit(pageSize)
        .offset(offset)
        .run();

    return {
        data: users,
        pagination: {
            page,
            pageSize,
            totalCount,
            totalPages: Math.ceil(totalCount / pageSize),
            hasNextPage: page * pageSize < totalCount,
            hasPrevPage: page > 1
        }
    };
}

import { QueryBuilder } from 'miniorm';

async function getMonthlyStats(year: number, month: number) {
    // Format date for SQL comparison
    const startDate = `${year}-${month.toString().padStart(2, '0')}-01`;
    const endDate = month === 12
        ? `${year + 1}-01-01`
        : `${year}-${(month + 1).toString().padStart(2, '0')}-01`;

    // Get new user count
    const newUsers = await new QueryBuilder('main_db', 'users')
        .where({ 'created_at >=': startDate, 'created_at <': endDate })
        .count();

    // Get active user count
    const activeUsers = await new QueryBuilder('main_db', 'user_logins')
        .where({ 'login_time >=': startDate, 'login_time <': endDate })
        .pluck('user_id')
        .then(ids => new Set(ids).size);

    // Get revenue stats
    const totalRevenue = await new QueryBuilder('main_db', 'orders')
        .where({ 'created_at >=': startDate, 'created_at <': endDate, status: 'completed' })
        .sum('amount');

    // Get top products
    const topProducts = await new QueryBuilder('main_db', 'order_items')
        .raw(`
            SELECT p.name, p.id, SUM(oi.quantity) as total_sold
            FROM <table> oi
            JOIN products p ON oi.product_id = p.id
            JOIN orders o ON oi.order_id = o.id
            WHERE o.created_at >= ? AND o.created_at < ? AND o.status = ?
            GROUP BY p.id
            ORDER BY total_sold DESC
            LIMIT 5
        `, [startDate, endDate, 'completed']);

    return {
        period: { year, month },
        newUsers,
        activeUsers,
        totalRevenue,
        topProducts
    };
}

import { Model, BaseEntityClass } from 'miniorm';

// Define an entity class
class User extends BaseEntityClass<User> {
    id!: number;
    name!: string;
    email!: string;
    active: boolean = true;
}

// Create a model instance
const userModel = new Model('main_db', User);

// Or with a string table name
const postsModel = new Model('main_db', 'posts');

import { Model, BaseEntityClass } from 'miniorm';

class User extends BaseEntityClass<User> {
    id!: number;
    name!: string;
    email!: string;
    active: boolean = true;
}

const userModel = new Model<User>('main_db', 'user');

// Find all users
const allUsers = await userModel.findAll();

// Find all users with specific fields
const userNames = await userModel.findAll(['id', 'name']);

// Find with conditions
const activeUsers = await userModel.find({
    fields: ['id', 'name', 'email'],
    where: [{ active: true }],
    order: ['created_at', 'DESC'],
    limit: 10,
    offset: 0
});

// Find a single user
const firstUser = await userModel.findOne({
    where: [{ active: true }],
    order: ['id', 'ASC']
});

// Shorthand for findOne
const user = await userModel.first({
    where: [{ email: 'test@example.com' }]
});

import { Model, BaseEntityClass } from 'miniorm';

class User extends BaseEntityClass<User> {
    id!: number;
    name!: string;
    email!: string;
    active: boolean = true;
}

const userModel = new Model<User>('main_db', User);

// Create a new user
const created = await userModel.create({
    name: 'John Doe',
    email: 'john@example.com',
    active: true
});

// Update users
const updated = await userModel.update(
    { active: false },
    { 'last_login <': '2023-01-01' }
);

// Delete users
const deleted = await userModel.delete(
    { 'created_at <': '2022-01-01' }
);

import { Model } from 'miniorm';

const userModel = new Model('main_db', 'users');
const scoreModel = new Model('main_db', 'scores');

// Count all records
const totalUsers = await userModel.count();

// Count specific field (non-null values)
const emailCount = await userModel.count('email');

// Maximum value
const highestScore = await scoreModel.max('points');

// Minimum value
const lowestScore = await scoreModel.min('points');

// Average value
const averageScore = await scoreModel.avg('points');

// Sum values
const totalPoints = await scoreModel.sum('points');

import { Model } from 'miniorm';

const userModel = new Model('main_db', 'users');

// Check if records exist
const hasAdmins = await userModel.exists({ role: 'admin' });

// Get values from a single column
const emails = await userModel.pluck('email');

// Execute raw SQL
const result = await userModel.raw(
    'SELECT COUNT(*) FROM <table> WHERE created_at > ?',
    ['2023-01-01']
);

// Access the underlying query builder for complex queries
const complexQuery = userModel.query()
    .select(['id', 'name'])
    .where({ active: true })
    .order(['created_at'], 'DESC')
    .limit(10);

const results = await complexQuery.run();

import { Model, MiniOrm } from 'miniorm';

// Setup database connection
MiniOrm.connection('main_db', {
    type: 'mysql',
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'app'
});

// Create model
const userModel = new Model('main_db', 'users');

// Disconnect from database
await userModel.disconnect();

// Reconnect to database
await userModel.reconnect();

// Run query after reconnection
const users = await userModel.find({
    where: [{ active: true }]
});

import { Model } from 'miniorm';

const userModel = new Model('main_db', 'users');

// Debug the current query
userModel.debug('now').find({
    fields: ['id', 'name'],
    where: [{ active: true }],
    order: ['created_at', 'DESC'],
    limit: 10
});

// Debug the final query at execution time
const result = await userModel.debug('final').find({
    where: [{ role: 'admin' }]
});

// Custom debug handler
const customDebug = await userModel.debug('final', (sql, data) => {
    console.log('SQL to execute:', sql);
    console.log('Parameters:', data);
    // Log to file, send to monitoring service, etc.
}).findAll();

import { MiniOrm, Model, BaseEntityClass } from 'miniorm';

// 1. Define entity class
class User extends BaseEntityClass<User> {
    id!: number;
    username!: string;
    email!: string;
    active: boolean = true;
    created_at!: Date;
}

// 2. Create a repository class
class UserRepository {
    private model: Model<User>;

    constructor() {
        this.model = new Model<User>('app_db', User);
    }

    async findById(id: number): Promise<User | undefined> {
        return await this.model.findOne({
            where: [{ id }]
        });
    }

    async findByEmail(email: string): Promise<User | undefined> {
        return await this.model.findOne({
            where: [{ email }]
        });
    }

    async getActiveUsers(page: number = 1, pageSize: number = 10): Promise<User[]> {
        return await this.model.find({
            where: [{ active: true }],
            order: ['created_at', 'DESC'],
            limit: pageSize,
            offset: (page - 1) * pageSize
        });
    }

    async createUser(userData: Omit<User, 'id' | 'created_at'>): Promise<boolean> {
        return await this.model.create(userData);
    }

    async updateUser(id: number, userData: Partial<User>): Promise<any> {
        return await this.model.update(userData, { id });
    }

    async deactivateUser(id: number): Promise<any> {
        return await this.model.update({ active: false }, { id });
    }

    async deleteUser(id: number): Promise<any> {
        return await this.model.delete({ id });
    }

    async getUserCount(onlyActive: boolean = false): Promise<number> {
        if (onlyActive) {
            return await this.model.query()
                .where({ active: true })
                .count();
        }
        return await this.model.count();
    }
}

// 3. Application usage
async function main() {
    // Setup database connection
    MiniOrm.connection('app_db', {
        type: 'mysql',
        host: 'localhost',
        user: 'app_user',
        password: 'secure_password',
        database: 'application'
    });

    const userRepo = new UserRepository();

    // Create a user
    await userRepo.createUser({
        username: 'john_doe',
        email: 'john@example.com',
        active: true
    });

    // Find user by email
    const user = await userRepo.findByEmail('john@example.com');

    if (user) {
        console.log(`Found user: ${user.username}`);

        // Update user
        await userRepo.updateUser(user.id, {
            username: 'john_doe_updated'
        });

        // Get active users
        const activeUsers = await userRepo.getActiveUsers(1, 10);
        console.log(`Active users: ${activeUsers.length}`);

        // Get user count
        const totalUsers = await userRepo.getUserCount();
        const activeCount = await userRepo.getUserCount(true);
        console.log(`Total users: ${totalUsers}, Active: ${activeCount}`);
    }

    // Clean up
    await userRepo.model.disconnect();
}

main();