Generic `ResultSet` for better typing

[asyncmacro]

The db.execute method currently returns a non-generic ResultSet type. However, in some cases, the column and row types are known beforehand. By making ResultSet generic, developers could provide these types, enhancing the developer experience (DX).

For instance, a table with columns imageId, filename, and data only provides a length property when using the standard db.execute return type. To return the data column in a Response object (new Response(result.data)), a workaround like this is needed:

type ExtendedResultSet<T> = ResultSet & {
  rows: T[];
};

This allows functions to return an ExtendedResultSet<ImageRow>, where the rows property is typed based on the specific row type.

This approach saves time and reduces debugging when table modifications occur. I'm Happy to contribute to this improvement if the team approves.

[mustaqimarifin]

i second this!

[DaBigBlob]

@abdelkd can you elaborate a little more on the behavior of ResultSet you desire?

[levithomason]

@abdelkd can you elaborate a little more on the behavior of ResultSet you desire?

Yes.

Need

When writing rows to a table, we adhere to a schema for the columns. When querying that table, we know the schema of the columns that will be returned as rows in the ResultSet. We want the resultSet.rows to be typed to match our specific column names and value types.

Problem

The typing for the ResultSet['rows'] is hard coded to Array<Row>, where Row is an interface with its keys set as 'length'|string|number and values to Value i.e. (string|number|bigint|ArrayBuffer|null):

// @libsql/core/lib-esm/api.d.ts

export interface ResultSet {
    rows: Array<Row>;
    ...

export interface Row {
    length: number;
    [index: number]: Value;
    [name: string]: Value;
}

The keys/values should be the actual column names and types returned from the query. Instead, we get errors.

type File = { path: string, content: string, meta: { createdBy: User } }

const { rows } = await db.execute(`SELECT * FROM files`);
const file = rows[0];

file;                // Hardcoded to Row, should be File
file.meta;           // Hardcoded to Value => string|number|bigint|ArrayBuffer|null
file.meta.createdBy; // ERROR - Value has no createdBy

Hack

This is a counter example, please fix! 🙂

const file = rows[0] as unknown as File; // cast Value to unknown to recast to File 😞

Solution

Turso doesn't know what its consumer's column names and values will be. Instead of hardcoding types essentially to string|number: multiple, it should allow the consumer to pass in their types as generics. Then, the returned rows will be typed to the consumer's actual types.

Here is how libsql would update typings for the execute to allow the rows to be typed by the user:

// @libsql/core/lib-esm/api.d.ts

export interface Client {
    execute<TRow extends Row>(stmt: InStatement): Promise<ResultSet<TRow>>;
    ...

export interface ResultSet<TRow extends Row> {
    columns: Array<keyof TRow>;
    columnTypes: Array<string>;
    rows: Array<TRow>;
}

Now, we can pass a generic argument, where we can inform the query of what the rows are:

type File = { path: string, content: string, meta: { createdBy: User } }

const { rows } = await db.execute<File>(`SELECT * FROM files`);
const file = rows[0];

file;                // File
file.meta;           // File['meta'] => { createdBy: User }
file.meta.createdBy; // File['meta']['user'] 🤗

This would need to be done for all similar methods and return types. So that all return types can be typed by the user.

---

Caveat - I didn't compile/validate the details here, but it should be enough to get the idea.

[kevinfiol]

Making some noise on this one. Doing const foo = rows[0] as unknown as Foo; gets old fast.

Different API, but for reference, jsr.io/@db/sqlite does something like const row = select.get<Foo>({ param }); where row will then be of type Foo | undefined.

[Pulkit7070]

PR open for this: #344