Concise type for a field?

[cakoose]

[Another question about using Mammoth types in function signatures! Please let me know if these should not be put under issues.]

I have a helper function that gets a single record from a table, e.g.:

function fetch(db: Db, id: string): Promise<Array<{name: string, email: string}>> {
    db.select(
        db.user.name,
        db.user.email,
    ).from(db.user).where(db.user.id.eq(id));
}

What if I want to allow the caller to specify additional fields for inclusion in the SELECT, e.g.:

function fetch(db: Db, id: string, ...fields: ???): Promise<Array<???>> {
    db.select(
        db.user.name,
        db.user.email,
        ...fields,
    ).from(db.user).where(db.user.id.eq(id));
}

Is there a way to do this?

[martijndeh]

So, fields would either be an array with type Columns or Expressions. These are actually not exported yet so Mammoth would need to export this.

Something like the below may work then. Is that what you're looking for?

function fetch<T extends Expression<any, boolean, string>>(db: Db, id: string, ...fields: T[]) {
    return db.select(
        db.user.name,
        db.user.email,
        ...fields,
    ).from(db.user).where(db.user.id.eq(id));
}

[Sure, issues is fine. Then at least people can read along if they are interested.]

[cakoose]

Yeah, I think that's roughly what I'm looking for.

But is there a way to ensure the specified fields are from the correct table?

[martijndeh]

In your case, in your fetch function example above, I think you could use a type of Column<string, 'user', any, boolean, boolean, any> to limit only columns from the user table.

In Mammoth there is no constraint on which table you can select in the select fn. This is a bit difficult to achieve because of Mammoth's goal to stick to SQL as close as possible: the select() is before the from() so we cannot know which columns will be ok to select.

There is an idea to at least change the type of the column if it's not in any of the from items e.g. change type to never if it will cause an issue (or change the whole result set to never, even better I guess). This is similar to how columns are automatically marked as nullable if they are left joined.

[cakoose]

That works for me. Thanks!

In Mammoth there is no constraint on which table you can select in the select fn. This is a bit difficult to achieve because of Mammoth's goal to stick to SQL as close as possible: the select() is before the from() so we cannot know which columns will be ok to select.

Putting .from first might not be a bad idea. There's already some precedent in LINQ

        // Specify the data source.
        int[] scores = new int[] { 97, 92, 81, 60 };

        // Define the query expression.
        IEnumerable<int> scoreQuery =
            from score in scores
            where score > 80
            select score;

I believe they chose that order for better auto-complete in the IDE. That order also matches the logical order of operations when evaluating a query.

I understand the idea of wanting to match SQL closely, but Mammoth's other goal is static type safety, so maybe the tradeoff is worth it?

[martijndeh]

Sticking close to SQL is considered more important than being complete type safe by Mammoth. We want to avoid creating our own SQL dialect and have newcomers learn a new abstraction. I touched this briefly at https://nullbyt.es/a-new-typescript-postgres-query-builder/.

In any case, I'll get a new type in soon so you can have your own columns specified from the outside (in your fields example).

[frabbit]

How about an additional selectFrom method to get the best of both worlds?

[martijndeh]

Well, it won't cover all use cases, as you might also want to join with and select, etc.

For now, anything new in addition to SQL I leave up to user-land e.g. a more ORM-ish library. Maybe in the future when Mammoth is more complete we can create more safety by introducing these new ways in the library itself.

[martijndeh]

It's now easier to write your own selectFromX, see #218. This should also provide a solution to the original request.