A concise type for a table?

[cakoose]

Let's say I have a DB with a users table and the appropriate Mammoth defineTable and defineDb calls.

How can I get a concise type for that table, e.g. for use in a function signature:

function processUser(user: ???) { ... }

[martijndeh]

Do you mean a type for a row from that table, or the user table in the db instance?

Regarding the type of the table itself, I generally pass the whole Db instance around and use it in some sort of repository where I then access those tables.

[cakoose]

Whoops, I meant a row from the table.

[martijndeh]

Check. There is not a clean way to get the resulting row from a specific table. There is some internal tooling to get the type from a query though. Conceptually I think this is closer to what is actually happening: queries are returning rows and they may or may not match all the columns of a table, right?

What I sometimes do if I want the row from a specific query in a type, and I keep this type in the same module, I put the query in a function and call type Foo = ReturnType<typeof myQueryFn>[number].

As a helper, Mammoth could return a type e.g. TableRow to help you with your use case though. I do wonder why you don't want the type from the query though. What do you think?

[cakoose]

I want a User type that is equivalent to a single row in the "users" table. I pass that object around sometimes and so I need something to put in function signatures.

Currently I'm defining User manually, but I want to ensure it doesn't diverge from the actual DB table type.

The ReturnType thing could work if there was some way to do SELECT *, e.g.:

async function selectStarFromUsers() {
    return await db.selectStar().from(db.users);
}

type User = ReturnType<typeof selectStarFromUsers>[number]  // Also need to unwrap the `Promise` somehow...

[martijndeh]

Currently I'm defining User manually, but I want to ensure it doesn't diverge from the actual DB table type.

That's great. As long as you have it map from the a Mammoth return value somewhere it's at least going to break if it's diverging.

Because it's a little annoying to manually type the table in your own type again, I added TableRow<T>. This doesn't omit the id, or do anything opinionated. If you want that you'd have to do it yourself.

For example, the get the type of a row from table foo you can do this:

export type Foo = TableRow<typeof foo>;

export const foo = defineTable({
  id: uuid().primaryKey().default(`gen_random_uuid()`),
  createDate: timestampWithTimeZone().notNull().default(`now()`),
  name: text().notNull(),
  value: integer(),
});

[martijndeh]

Also, in the next version you'll be able to use the .star() e.g.:

db.select(db.user.star()).from(db.user);

which will produce the below SQL

SELECT user.* from user

and automatically give you the whole type as a result.

[cakoose]

TableRow is what I was looking for, thanks!

Tangent about .star(): what if someone has a field named star? Maybe something like db.user.STAR or db.user['*'] is safer. (Not a major issue for me, just wanted to raise the concern.)

[martijndeh]

You are right. This creates a possible collision. Perhaps db.select(star(db.user)).from(db.user) is a better alternative? db.user['*'] is also possible and close to SQL but it looks so ugly. :|

[cakoose]

Since star is a common word, I think some people (like me) would use a qualified import, e.g.

import * as mammoth from '...'
...
db.select(mammoth.star(db.user)).from(...)

That's not so bad.

Also, there's the full SELECT * FROM ... and the table-specific SELECT t1.*, t2.f1 FROM .... Not sure if it's worth addressing both of those.

[martijndeh]

A change to this just hit master. There may be some edge cases left to support but a straightforward db.select(star()).from(..) with different joins should just work.