Streaming SQL Query Results

You need to design and implement a system that can efficiently retrieve and process large result sets from a SQL database without loading the entire dataset into memory at once. This is crucial for performance and scalability when dealing with queries that might return millions of rows.

Problem Description

The goal is to create a mechanism that allows a client application to fetch rows from a SQL query one by one, or in small batches, as they become available from the database. This avoids the memory overhead associated with fetching all results simultaneously, which can lead to out-of-memory errors or significant performance degradation on large datasets.

Key Requirements:

1. Streaming Retrieval: The system must be able to retrieve rows from a SQL query in a sequential, streamed fashion.
2. Memory Efficiency: The client-side processing of query results should not require the entire result set to be held in memory.
3. Error Handling: Graceful handling of potential errors during query execution or row retrieval.
4. Resource Management: Proper closing of database connections and cursors once streaming is complete or interrupted.

Expected Behavior:

When a query is executed, the system should return a handle or an iterator that the client can use to request subsequent rows. Each request should yield the next available row. The process continues until all rows are fetched or the client explicitly stops requesting.

Edge Cases:

• Queries returning zero rows.
• Queries that take a very long time to execute.
• Network interruptions during streaming.
• Database errors occurring mid-stream.

Examples

Example 1:

Input:
Database:
+----+---------+
| id | name    |
+----+---------+
| 1  | Alice   |
| 2  | Bob     |
| 3  | Charlie |
+----+---------+

SQL Query: SELECT id, name FROM users WHERE id > 0;

Client Action: Request rows sequentially.

Output:
Row 1: { "id": 1, "name": "Alice" }
Row 2: { "id": 2, "name": "Bob" }
Row 3: { "id": 3, "name": "Charlie" }
(End of results)

Explanation: The client requests rows. The system fetches one row at a time from the database and returns it. Once all rows matching the query are exhausted, the streaming ends.

Example 2:

Input:
Database:
+----+---------+
| id | name    |
+----+---------+
| 1  | Alice   |
| 2  | Bob     |
| 3  | Charlie |
+----+---------+

SQL Query: SELECT id, name FROM users WHERE id > 5;

Client Action: Request rows.

Output:
(No rows returned, streaming ends immediately)

Explanation: The query returns an empty result set. The streaming mechanism correctly handles this by indicating that there are no rows to fetch.

Example 3: (Client Interrupts Streaming)

Input:
Database:
+----+---------+
| id | name    |
+----+---------+
| 1  | Alice   |
| 2  | Bob     |
| 3  | Charlie |
| 4  | David   |
+----+---------+

SQL Query: SELECT id, name FROM users WHERE id > 0;

Client Action:
1. Request row 1: { "id": 1, "name": "Alice" }
2. Request row 2: { "id": 2, "name": "Bob" }
3. Client decides to stop fetching further results.

Output:
(Streaming stops after the second row is returned)

Explanation: The client fetches a subset of the results and then terminates the streaming process. The system should ensure resources are cleaned up.

Constraints

• The SQL query can return up to 1,000,000,000 rows.
• Each row can have a maximum of 100 columns.
• The total size of a single row's data should not exceed 1MB.
• The system must be able to handle concurrent streaming requests for different queries.
• The implementation should be language-agnostic, focusing on the logic and interaction patterns.

Notes

Consider how to abstract the database interaction. You'll likely need to use a database cursor or a similar mechanism provided by the database driver that supports fetching rows iteratively. Think about how to represent the streamed results to the client – an iterator, a generator, or a callback-based approach are common patterns. How will you manage the underlying database connection and cursor lifecycle? Ensure that when the client finishes or an error occurs, these resources are properly released.