Designing an Extensible Query API Using a DSL

Introduction

While developing internal admin tools, one of the most frequent requests is allowing users to freely query the data they need.

Initially, I implemented traditional list APIs that accepted query parameters for filtering and sorting by column. As the number of data types increased and maintenance costs grew, this approach quickly became difficult to scale.

During this process, I noticed that OpenSearch already solves a very similar problem using a DSL. That observation led me to design and implement a DSL-based query system tailored to our internal tools.

After validating it in production, we adopted this approach as a standard pattern across the system.

What Is a DSL?

DSL stands for Domain-Specific Language, a language designed to solve problems within a specific domain. Unlike general-purpose languages such as Java, JavaScript, or Python, DSLs focus on expressing a limited set of concepts more clearly and concisely.

Well-known examples include:

• SQL
• Regular Expressions
• GraphQL

In this system, the DSL is used to describe query intent, not database-specific implementation details.

Why Not GraphQL?

I seriously considered adopting GraphQL to solve this problem, but ultimately decided against it.

The reasons were:

1. Lack of team experience
   
   No one on the team had prior experience with GraphQL, which would introduce a learning curve.

2. Multiple data sources
   
   Since we use PostgreSQL, MongoDB, and OpenSearch, each data source would require its own resolver implementation.

3. Filtering and sorting still need custom definitions
   
   Even with GraphQL, filter and sort logic would still need to be defined. In that case, defining a shared DSL felt more straightforward.

4. Operational overhead
   
   Introducing Apollo Client and Server felt too heavy for an internal admin tool.

Given these trade-offs, we decided to introduce a custom DSL instead of GraphQL.

Design and Implementation

Overall Architecture

We use three primary data sources:

• PostgreSQL
• MongoDB
• OpenSearch

I defined a common DSL interface and implemented a dedicated DSL builder for each data source. The DSL acts as an intermediate layer, while each builder converts it into a database-specific query.

DSL Interface Definition

export interface Dsl {
  query?: DslFilter[];
  sort?: DslSort[];

  limit?: number;
  page?: number;
}

export type DslFilter = DslGroup | DslCondition;

export interface DslGroup {
  op: DslGroupOperator;
  filters: DslFilter[];
}

export type DslCondition =
  | {
      key: string;
      op:
        | typeof DslOperator.equal
        | typeof DslOperator.notEqual
        | typeof DslOperator.greaterThan
        | typeof DslOperator.greaterThanOrEqual
        | typeof DslOperator.lessThan
        | typeof DslOperator.lessThanOrEqual
        | typeof DslOperator.exists;
      value: unknown;
    }
  | {
      key: string;
      op: typeof DslOperator.between;
      from: unknown;
      to: unknown;
    }
  | {
      key: string;
      op: typeof DslOperator.in | typeof DslOperator.notIn;
      values: unknown[];
    }
  | {
      key: string;
      op:
        | typeof DslOperator.contains
        | typeof DslOperator.notContains
        | typeof DslOperator.startsWith
        | typeof DslOperator.endsWith
        | typeof DslOperator.regex
        | typeof DslOperator.match;
      value: string;
    };

export interface DslSort {
  key: string;
  order?: DslSortOrder;
}

Design considerations:

• DslGroup supports logical AND / OR composition.
• DslOperator explicitly defines allowed operations.
• page and limit support offset pagination and allow future cursor-based pagination.

Example DSL Payload

{
  "query": [
    {
      "key": "id",
      "op": "gt",
      "value": 3435
    }
  ],
  "page": 1,
  "limit": 10
}

Backend Implementation

We exposed a single endpoint POST /{domain}/search

The request body accepts the DSL directly. I implemented a DSL-based repository layer where each repository delegates query construction to its DSL builder.

// Service
public async search(dto: UserSearchDto): ServiceReturnType<UserSearchResponse> {
  const result = await this.userRepo.dsl.listWithOffset(dto.query);

  return {
    itemList: result.itemList,
    meta: result.meta,
  };
}

// BaseRepository
public async run(dsl: Dsl) {
  const pipeline = this.dslBuilder.build(dsl);
  return await this.model.aggregate(pipeline);
}

public async listWithOffset(dsl: Dsl): Promise<PaginationResult<Schema>> {
  const { limit = 10, page = 1 } = dsl;

  const itemList = await this.run(dsl);
  const count = await this.model.countDocuments(dsl.query);

  return {
    itemList,
    meta: {
      totalPage: Math.ceil(count / limit),
      currentPage: page,
      totalCount: count,
      currentCount: itemList.length,
    },
  };
}

Service layers simply pass DSL objects, while repositories handle transformation and execution. This keeps query logic centralized and extensible.

Critical Considerations

Since clients can construct queries freely, the backend must enforce strict constraints

OR-Based Authorization Bypass

If authorization conditions are appended naively using OR, clients may craft queries that bypass access control.

Regex-Based DoS Risks

Operators such as contains often rely on regex matching.

If unrestricted, this can lead to:

• Catastrophic backtracking
• Database performance degradation

Mitigations include:

• Limiting input length
• Restricting allowed patterns
• Avoiding unbounded wildcards

Filtering on Non-Indexed Fields

Allowing filters or sorting on every field is dangerous at scale.

Once datasets grow beyond millions of records:

• Filters and sorts should only be allowed on indexed fields
• DSL validation should reject unsupported properties

Frontend Implementation

Because the backend API is standardized, we were able to build a generic table component.

• Filters, sorting, and pagination are all represented as DSL
• Business logic lives inside the table component
• Pages only define column metadata

export function UserPage() {
  const query = useUserSearchQuery();

  return (
    <DslApiTable<User>
      query={query}
      columns={[
        {
          key: "id",
          label: "ID",
          sortable: true,
        },
        {
          key: "email",
          label: "Email",
          filter: {
            operators: [
              DslOperator.equal,
              DslOperator.contains,
            ],
          },
        },
        {
          key: "createdAt",
          label: "Created At",
          filter: {
            operators: [
              DslOperator.between,
            ],
          },
          sortable: true,
        },
      ]}
    />
  );
}

The key idea is that the entire table state is represented by a single DSL object. This eliminated the need to implement page-specific query logic.

Conclusion

I designed and implemented this DSL-based query system end-to-end and integrated it into our existing admin tools.

As a result, we significantly reduced duplicated query logic across both backend and frontend, and development speed improved noticeably.

Because query construction is client-driven, authorization, performance constraints, and malicious query defense must always be enforced on the backend.

This approach works extremely well for internal admin tools.
For public APIs, additional validation and stricter safeguards would be required.