jpsql: a query builder born of frustration#
Years ago, the business’s data layer was a mess in two directions at once. The ORM was too slow for the volume the operation ran at - object hydration, implicit queries, and a mapper between me and a database I understood perfectly well. The alternative, string-interpolated SQL, was worse in a different way: uninterpretable at a glance, and so easy to get wrong that composing a query from parts felt like defusing something.
jpsql was the answer I built and then ran for years. The idea is that a query is not a string you concatenate; it is a structure you compose out of typed pieces. Columns, values, functions, and operators are objects. Comparison and arithmetic operators on a field return typed expressions, so ordinary Python reads like the SQL it will become - and every string value is escaped automatically, so there is no dangerous way to use it.
_start = '2026-09-01 00:00:00' # plain strings with datetime values
_date = '2026-09-27 23:59:59'
shelf_rows = mysql().fetch(
db_name='chain',
table='shelf_count',
where=[
FIELD('datetime') >= _start,
FIELD('datetime') <= _date,
]
)
A real call, as it runs in production. The pieces that look like plain Python - FIELD('datetime') >= _start - are not strings waiting to be concatenated. They are typed objects that know the column they refer to and the comparison they represent, and jpsql turns them into SQL. Values are never interpolated into text by hand; every string value gets the escape treatment automatically, and every operator is an object, so there is no "unsafe" way to use it. Conditions compose as a list, which is the part that pays off in real code.
That composability is what unlocks the interesting part: you can build queries with ordinary logic. A function builds a list of conditions, uses switches and parameters, and out comes a rich API surface that generates dynamic queries - sometimes with validation, sometimes with triggers, sometimes composing multiple queries conditionally. We try to keep them as simple as needed, without pushing database-specific logic up into the application layer. They do sometimes grow in complexity, especially when one library function serves many different systems or has survived a few schema migrations. But that's okay - the composition is still checked by Python, not by careful string concatenation, so complexity stays manageable instead of turning brittle.
The other half of jpsql is what made it survive production: it was built for the realities of a busy database, not a demo. It abstracts over two runtimes (native MySQLdb under CPython with gevent compatibility, a pure-Python fallback under PyPy), streams large result sets through server-side cursors, and measures result-row size to stop an out-of-memory before it happens. It is not a general-purpose ORM replacement - it is the layer the business’s libraries were built on, and it worked, for years, because it matched how the operation actually used its data.
Built around those libraries is something I use every day and rarely think about anymore: most client apps never talk to the database directly. They talk to a database server over a JSON API. An exporter metaclass creates module proxies that wrap the database libraries, so switching an application from a direct connection to the shared server is a one-line change:
# Replace this: import saleslib # With this: from clientlib import saleslib
Nothing else changes - same functions, same return shapes. That indirection earns its keep in quiet ways: the server can optimize and cache, contention drops, and the database library can be restarted without touching the application servers. The payoff I care most about is schema changes. A single library function might serve a dozen application servers, and a schema change would normally break every one of them. Behind the server, only the response shape has to stay the same - so a schema change goes live with no downtime and no application restarts.
AgentLib: the same philosophy, a decade later#
When I started building agents against real systems, the tooling repeated the same two failures jpsql had been a reaction to. Heavy frameworks inserted a thick abstraction between me and the model - the new ORM. And the escape hatch, hand-rolled prompt-and-parse code, was the new string-interpolated query: unreadable and easy to get wrong.
AgentLib takes the same object-based approach jpsql did. A tool is a typed object - a Python function signature plus a Pydantic model - not a blob of JSON schema you assemble by hand. Composition is just Python. The framework stays close enough to the provider API that you can always see what the model actually sees, the same way jpsql stayed close enough to the wire that you could always see the SQL.
from agentlib import BaseAgent
class PricingAgent(BaseAgent):
model = 'google/gemini-3.7-flash'
system = "Reprice listings within the enforced margin floor."
@BaseAgent.tool
def reprice(self, sku: str = "Listing SKU",
price: float = "New price"):
"""Set a listing price."""
self.respond(apply_price(sku, price))
The parallel is deliberate. In jpsql, the typed object was the query fragment. In AgentLib, it is the tool: a decorated method whose signature becomes the schema the model calls against. The docstring becomes the tool description, self.respond(...) ends the loop and hands the result back to the caller, and Pydantic validates whatever the model tried to pass. In both frameworks, the type system carries the contract so the composition can be trusted.
The difference is a decade of hindsight. jpsql solved composition and safety; AgentLib solves those plus the problems that only appear when the caller is a model - bounded visibility, durable state, context that survives a long session. But the conviction underneath is identical, and it is the reason both tools are still standing.
Why I build my own - sometimes#
Neither of these was a first resort. Before writing jpsql, I surveyed the options extensively, tested a few in real work, and concluded that the ergonomics of what existed did not meet my needs. Same story with AgentLib. The decision to build comes at the end of that process, not the beginning.
These two earn their complexity, because both of them are taming something genuinely hard. Agent frameworks in particular are more complex than most libraries - the caller is stochastic, and the framework's whole job is to harness that into something you can run in production. I keep as much as I can in the application layer and the framework itself slim, and compared to what else is out there I think it hits a balance of simplicity and capability you can't find elsewhere. It's also alive: when a fix belongs in the framework, it gets patched there, and when a feature turns out to be generic enough that multiple applications need it, it moves down into the framework. Most days I don't think about these tools at all - I just use them. But when I do think about them, it's because they earned it.
That said, none of this is ideology. I reach for the tool that gets the job done. In these cases, at these times, that meant writing a low-level library - but I would have been more than happy to use an existing framework if one had fit, and I still would be. Working on a team, the stack isn't my call, and I am happy and comfortable using the house tools. There are plenty of situations where industry-standard tooling matters more than elegance or ergonomics - hiring, onboarding, maintenance, the next person to read the code - and recognizing that is part of the same judgment that tells you when it's worth building your own.