[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.