With a good library you could do just that, by having the functions return only queries and then expand them to the actual values (by interacting with the DB) after applying the filtering to it?

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.

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

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.

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/

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
  )

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.

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

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

Depends on the programming environment: if it's a O(n) operation anyway, meaning you don't the times indexed, and your computation is colocated with the data and the db interface is using lazy sequences...

(Also, real-life systems of course do things inefficiently all the time)

> No one would retrieve all users and then filter them within the program.

No one _should_ do that, but that's a common enough problem (that usually doesn't get found until code is running in production). I suspect with the rise of vibe coding, it's going to happen more and more.

Sometimes it's forced by using the wrong database in the first place, or the wrong data structure. It can be less pain to do a bit of post-processing in the application layer than to unpick either of those.