Query Builder - OOD SQL Query Builder with Fluent Interface

Question

## Problem

Design a SQL query builder class with a fluent (method-chaining) interface that constructs a SELECT query string.

```python
class QueryBuilder:
    def select(self, *columns: str) -> 'QueryBuilder': ...
    def from_table(self, table: str) -> 'QueryBuilder': ...
    def join(self, table: str, on: str) -> 'QueryBuilder': ...
    def where(self, condition: str) -> 'QueryBuilder': ...
    def order_by(self, column: str, direction: str = "ASC") -> 'QueryBuilder': ...
    def limit(self, n: int) -> 'QueryBuilder': ...
    def build(self) -> str: ...
```

## Example

```
query = (
    QueryBuilder()
    .select("u.name", "o.total")
    .from_table("users u")
    .join("orders o", "o.user_id = u.id")
    .where("u.active = 1")
    .order_by("o.total", "DESC")
    .limit(10)
    .build()
)
# "SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id WHERE u.active = 1 ORDER BY o.total DESC LIMIT 10"
```

## Follow-ups

1. How do you handle calling `where` multiple times -- AND-chain them or replace?
2. How do you guard against SQL injection in the `where` clause if raw strings are passed?
3. Add support for `GROUP BY` and `HAVING`.
4. How would you unit test `build()` without actually running against a database?

Query Builder - OOD SQL Query Builder with Fluent Interface

Question Details

Full Details

Topics

More from Amazon