Building a Robust SQLite Query Builder in TypeScript: Lessons from an Arabic Dictionary App

As developers, we often find ourselves writing repetitive SQL queries, especially when dealing with client-side databases like SQLite in mobile or desktop applications. While raw SQL offers ultimate control, it can quickly become cumbersome, error-prone, and hard to maintain as your application grows. This is where a well-designed query builder shines.

During the development of my Arabic Roots Dictionary application, I faced this exact challenge. The app required numerous dynamic queries for searching roots, derivatives, and managing dictionary data. Instead of scattering raw SQL strings throughout the codebase, I decided to build a custom SQLite query builder in TypeScript. This journey was incredibly insightful, and I'd love to share the key learnings and the architectural patterns that emerged.

Why a Custom Query Builder?

You might ask, "Why not use an existing ORM or query builder library?" While excellent options exist, a custom solution offered several advantages for my specific needs:

• Tailored for SQLite: I could optimize for SQLite's nuances and features without the overhead of a general-purpose SQL library.
• Lightweight: Avoided pulling in large dependencies that might impact bundle size or performance, especially crucial for a mobile app.
• Type Safety: TypeScript's static analysis provided invaluable type safety for column names, operators, and return types, catching errors early.
• Control and Flexibility: Complete control over the generated SQL, which was important for debugging and understanding performance.
• Learning Experience: A fantastic opportunity to deepen my understanding of database interactions and software design patterns.

The Core Architecture: Chainable and Type-Safe

The goal was to create a fluent, chainable API that felt natural to write, similar to popular ORMs, while strictly enforcing types. The solution involved a BaseQueryBuilder class and specialized subclasses for SELECT, INSERT, UPDATE, and DELETE operations.

1. Defining the Building Blocks (Types)

First, I defined the essential interfaces for WHERE conditions, JOIN conditions, and ORDER BY clauses. This is where TypeScript truly shines:

// types.ts
export interface WhereCondition {
  column: string;
  operator: '=' | '!=' | '>' | '<' | '>=' | '<=' | 'LIKE' | 'IN' | 'NOT IN' | 'IS NULL' | 'IS NOT NULL';
  value?: any;
}

export interface JoinCondition {
  type: 'INNER' | 'LEFT' | 'RIGHT' | 'FULL OUTER';
  table: string;
  on: string;
}

export interface OrderByCondition {
  column: string;
  direction: 'ASC' | 'DESC';
}

2. The BaseQueryBuilder: Common Functionality

The BaseQueryBuilder acts as the foundation, encapsulating common query components like WHERE, JOIN, ORDER BY, LIMIT, and OFFSET.

export abstract class BaseQueryBuilder {
  protected _table: string = '';
  protected _whereConditions: WhereCondition[] = [];
  protected _joins: JoinCondition[] = [];
  protected _orderBy: OrderByCondition[] = [];
  protected _limit?: number;
  protected _offset?: number;

  // Chainable methods would go here...

  protected buildWhereClause(): { clause: string; params: any[] } { /* ... */ }
  protected buildJoinClause(): string { /* ... */ }
  protected buildOrderByClause(): string { /* ... */ }
  protected buildLimitClause(): string { /* ... */ }

  abstract toSQL(): { query: string; params: any[] };
}

Key Learnings:

• Chainable API: return this; in each method enables fluent usage.
• Parameterized Queries: Protects against SQL injection and handles SQLite types correctly.
• Abstract toSQL(): Enforces concrete builders to define their own SQL generation logic.

3. Specialized Builders

Each concrete query builder extends BaseQueryBuilder and adds its own specific methods and toSQL() logic.

SelectQueryBuilder

export class SelectQueryBuilder extends BaseQueryBuilder {
  private _columns: string[] = ['*'];
  private _distinct: boolean = false;
  private _groupBy: string[] = [];
  private _having: WhereCondition[] = [];

  select(columns: string | string[] = '*'): this { /* ... */ }
  distinct(): this { /* ... */ }
  groupBy(columns: string | string[]): this { /* ... */ }
  having(column: string, operator: WhereCondition['operator'], value?: any): this { /* ... */ }

  toSQL(): { query: string; params: any[] } {
    // Build SELECT query from all clauses
  }
}

UpdateQueryBuilder (with Safety Check)

export class UpdateQueryBuilder extends BaseQueryBuilder {
  private _data: Record<string, any> = {};

  set(columnOrData: string | Record<string, any>, value?: any): this { /* ... */ }

  toSQL(): { query: string; params: any[] } {
    const { clause: whereClause, params: whereParams } = this.buildWhereClause();
    if (!whereClause) {
      throw new Error('WHERE clause is required for UPDATE queries');
    }
    // Build UPDATE statement...
  }
}

4. The QueryBuilder Factory

A static factory class simplifies usage:

export class QueryBuilder {
  static select(columns?: string | string[]): SelectQueryBuilder {
    return new SelectQueryBuilder().select(columns);
  }

  static from(table: string): SelectQueryBuilder {
    return new SelectQueryBuilder().select().from(table);
  }

  // insert(), update(), delete() methods
}

5. Database Executor: Bridging to SQLite

The DatabaseExecutor bridges your query builder to the SQLite engine:

export interface DatabaseInterface {
  execAsync(query: string, params?: any[]): Promise<any>;
  getAllAsync(query: string, params?: any[]): Promise<any[]>;
  getFirstAsync(query: string, params?: any[]): Promise<any>;
  runAsync(query: string, params?: any[]): Promise<{ lastInsertRowId: number; changes: number }>;
}

export class DatabaseExecutor {
  constructor(private db: DatabaseInterface) {}

  async select(builder: SelectQueryBuilder): Promise<any[]> {
    const { query, params } = builder.toSQL();
    return await this.db.getAllAsync(query, params);
  }

  // insert(), update(), delete() methods
}

Integration with the Arabic Dictionary App

Before (Raw SQL)

async searchRootsRaw(query: string): Promise<Root[]> {
  const sql = `SELECT * FROM roots WHERE root_normalized LIKE ? LIMIT 50`;
  return await this.db.getAllAsync(sql, [`%${query.trim()}%`]);
}

After (Using Query Builder)

async searchRootsWithQueryBuilder(query: string, dictionaryFilter?: string | null, limit = 50): Promise<Root[]> {
  this.ensureInitialized();
  if (!query.trim()) return [];

  const normalizedQuery = query.trim();

  let queryBuilder = QueryBuilder
    .select([
      'r.id', 'r.root_display', 'r.root_normalized', 'r.root_no_diacritics',
      'r.total_derivatives_count', 'r.main_derivatives_count', 'r.extracted_derivatives_count',
      'r.dictionary_source', 'r.root_length', 'r.full_text'
    ])
    .from('roots r')
    .where('r.root_normalized', 'LIKE', `%${normalizedQuery}%`)
    .orderBy('r.root_length')
    .orderBy('r.root_display')
    .limit(limit);

  if (dictionaryFilter) {
    queryBuilder = queryBuilder.where('r.dictionary_source', '=', dictionaryFilter);
  }

  return await this.executor!.select(queryBuilder);
}

Benefits

• More Readable: Clear intent.
• Less Error-Prone: Type-safe and parameterized.
• Easier to Modify: Just chain more methods.
• Safer: Enforced WHERE clause in destructive queries.

Challenges and Further Considerations

• Complex Union Queries: Raw SQL may still be preferable in highly complex scenarios.
• Schema Awareness: Integrating a schema definition would increase type safety.
• Error Handling: Must be robust in the executor layer.
• Extensibility: Consider future support for advanced SQL features.

Conclusion

Building a custom SQLite query builder was a significant learning experience and a valuable addition to my Arabic Roots Dictionary application. It taught me the power of the Builder design pattern, the importance of type safety in database interactions, and how to create a more maintainable and robust data access layer.

While external libraries are often a great choice, sometimes a custom, lightweight solution tailored to your specific needs can offer unparalleled control and a deeper understanding of the underlying mechanisms.

If you're working with client-side databases and finding yourself repeating SQL or struggling with query complexity, I highly recommend considering building your own query builder. It's a rewarding challenge that pays dividends in code quality and developer experience.