[soulofmischief]

So would you then have to do `getActualUsers(db.getUsers())` or `query(db.getUsers())`?

Still smells like in such a case the developer avoids the complications of abstraction or OOP by making the user deal with it. That's bad API design due to putting ideology before practicality or ergonomics.

[shortrounddev2]

In linq (C#)

    IEnumerable<User> getExpiredUsers(DbSet<User> users)
        => users.Where(u => u.ExpiresAt < DateTime.UtcNow);

Such simple logical expressions (called expression trees) get converted to SQL queries

[ryanrasti]

The is exactly the way forward: encapsulation (the function), type safety, and dynamic/lazy query construction.

I'm building a new project, Typegres, on this same philosophy for the modern web stack (TypeScript/PostgreSQL).

We can take your example a step further and blur the lines between database columns and computed business logic, building the "functional core" right in the model:

  // This method compiles directly to a SQL expression
  class User extends db.User {
    isExpired() {
    return this.expiresAt.lt(now());
    }
  }

  const expired = await User.where((u) => u.isExpired());

Here's the playground if that looks interesting: https://typegres.com/play/

[quails8mydog]

And anyone who calls that method may find themselves dealing with the implementation details of Entity Framework and whatever db provider you're using because it's a leaky abstraction.

[bulatb]

You would have an API that makes the query shape, the query instance with specific values, and the execution of the query three different things. My examples here are SQLAlchemy in Python, but LINQ in C# and a bunch of others use the same idea.

The query shape would be:

  active_users = Query(User).filter(active=True)

That gives you an expression object which only encodes an intent. Then you have the option to make basic templates you can build from:

  def active_users_except(exclude):
      return active_users.filter(User.id.not_in(exclude)

...where `exclude` is any set-valued expression.

Then at execution time, the objects representing query expressions are rendered into queries and sent to the database:

  exclude_criterion = rude_users()  # A subquery expression
  polite_active_users = load_records(
      active_users_except(exclude_criterion)
  )

With SQLAlchemy, I'll usually make simple dataclasses for the query shapes because "get_something" or "select_something" names are confusing when they're not really for actions.

  @dataclass
  class ActiveUsers(QueryTemplate):
      active_if: Expression = User.active == true()

      @classmethod
      excluding(cls, bad_set):
          return cls(
              and_(
                  User.active == true(),
                  User.id.not_in(bad_set)
              )
          )

      @property
      def query(self):
          return Query(User).filter(self.active_if)

  load_records(
      ActiveUsers.excluding(select_alice | select_bob).query
  )

[soulofmischief]

This is a better story because it has consistent semantics and a specific query structure. The db.getUsers() approach is not part of a well-thought-out query structure.

[t_mahmood]

As in Django querysets. But starts to get messy with complex queries.

[bulatb]

It can. SQLAlchemy has good support for types since 2.0, which helps a lot.