Okay. This has been driving me crazy. Sasin suggests shrinking down and combining methods.

Charles (if I've understood correctly) appears to be suggesting extreme simplification and encouraging writing of SQL.

So, what I'm thinking about doing is reducing the whole thing to just five essential methods (a 75% reduction).

The methods that I would consider essential are;

query() - Essential for raw SQL, schema changes, and running .sql files.
query_bind() - The primary way to get data safely using parameters.
insert() - Handles the heavy lifting of lastInsertId() for single rows.
update() - Simplifies the most common "update by ID" pattern.
insert_batch() - Handles high-volume data insertion with a single trip to the DB.


With those five we should be able to do everything plus we can revert to doing it the way that it works with Trongate v1.

The following is from Google Gemini:

By sticking to these five, you have effectively removed all the "convenience" methods that actually caused cognitive bloat.

In V1: A developer has to choose between get(), get_where(), get_where_custom(), get_one_where(), get_many_where(), and get_where_in(). That is six different ways to do the same thing.

In V2: There is only one way: query_bind().

This makes your framework "AI-Native." An AI won't get confused by which "get" method to use; it will simply write the SQL and bind the parameters. It also forces developers to understand the SQL they are writing, leading to better-optimized queries in the long run.

As I can see, there are two main paths to go down here:

1. Go the "guessable/intuitive" way with all the convenience methods.
2. Extreme simplification and encouraging writing SQL.

I think either one would work, as long as you stick to it and go full force down that path.
Just please don't do anything in between, like many other frameworks.

The extreme simplification path, however, would align a little bit better with the general philosophy of Trongate v2.

Well, yes. I usually default to writing raw SQL or DQL as I find it simpler and more maintainable over time.

There may still be some where it’s arcane to them and would rather use an object syntax.

That was what I was trying to illustrate above.
Accept a string or a \Stringable object, either way it’s just (string)$sql in the end :)

Hi David,
Yes, you are correct, that is what I was getting at.
Abstracting away the tedium may benefit humans, but there is nothing all that tedious for an AI at the speeds at which it operates. Let's not forget that it operates on guess work, which is exactly why the less guessing that is required of it, the fewer errors you'll end up with.
To attempt to build SQL statements out of a set of parameters in a rigid query function, requires many such functions, and they only serve the most basic of queries, and you can continue to service basic queries in that manner. However, if attempting to be transparent to the child mind of an AI (albeit a very fast mind) in any given application, it is impossible to know what the application will need in terms of queries, or how complicated they may turn out to be. No doubt you can write a very clever function that handles a complicated query, but it doesn't afford transparency for an AI to know what will ultimately be the outcome.

At the end of the day, only the application programmer (or the AI) knows what is needed from the database, he/she/it alone can fashion his/her/its own queries, and pass them into the database class for execution. I'm guessing the AI can write it's own SQL queries once it becomes familiar with SQL syntax and the database itself. Treat all queries as you would an instance of query_bind(), and treat all queries as custom queries.

Instead, of trying to remember all of the query functions that have been pre-written, and the limitations they create, I opted to write all of my queries and use query() or query_bind(), thus bypassing the "horseradish".

I'm ready for the flogging.
Go ahead, hit me! May I have another, sir?

Let me just say, Sasin is great and he's right here with me (I'm currently live streaming). I bow down to all you.

However, Charles - what you've dished out over the last 24 hours has been outstanding. You're absolutely 100% right on all points.

I have therefore rewritten the Db.php file taking on board what you've said. We now have a bare min amount of methods.

Good call. Simplicity always wins. Thank you!

Well, I think that Charles is correct in query and query_bind. Remember the goal is simplicity and feeding the Ai to produce results, not hallucinations.

I'm firmly in the "write SQL directly" camp, and honestly think most of Trongate's existing query methods aren't worth keeping.

Beyond query() and query_bind(), I can count on one hand how many times I've used get_one_where() etc.

Why?

Performance control - If you're half clued up (and we know Trongate developers are), your hand-written SQL will invariably be more efficient. Why select all columns when you only need three? Abstractions encourage lazy querying and a lack of awareness around index use.

AI assistance - AI tools handle standard SQL far better than framework-specific abstractions. There's decades of SQL training data; Trongate custom methods, not so much.

Transparency - When debugging, I can copy the SQL, run it directly in the database, EXPLAIN it, optimise it. With abstractions, I'm reverse-engineering what SQL they actually generate.

Maintenance - Raw SQL is immediately readable to any developer with database experience. Framework abstractions require learning yet another DSL.

The security argument:

Yes, raw SQL opens the door to SQL injection - but abstractions don't fully mitigate this. You still need developers who understand parameterization. And query_bind() forces proper parameterization anyway, so the footgun is already gone if you use it consistently.

Database portability argument:

This is usually YAGNI. If you were planning multi-database support, you'd need to abstract from day one - not bolt it on later. And in practice, most projects either:

Start with PostgreSQL if they need its features
Stay on MySQL/MariaDB forever
Rewrite the entire app when scaling demands it anyway

More often than not, the perceived need to "upgrade" to PostgreSQL can be solved by finding inefficient queries and adding proper indexes.

Hence my warning not to try adding multi database support to Trongate. Not worth it, you'll tie yourself in knots trying to do it, and you can't do it whilst keeping to the core principles of Trongate.

Where it does make sense:

There's a category of operations where abstraction can be useful - convenience wrappers for repetitive, unoptimizable queries, eg:


These work because:

The SQL is always identical (zero optimisation opportunities)
You write them constantly (high repetition cost)
They do exactly what the method name says (no hidden complexity)
They're single-purpose and obvious

Pagination is a good example - it requires two queries (COUNT + SELECT with LIMIT/OFFSET), involves page calculation logic, appears on every list view, and has zero optimization opportunities. That's worth abstracting.

But as none of these are in the existing Model, it's a somewhat moot point.

My rule of thumb is: "If the method name tells you exactly what SQL it runs, and the arguments are obvious, it's probably a good abstraction. If you have to read the source code to know what it's doing, it's probably not."

Methods like get(), get_where(), get_many() fall into an awkward middle ground - they're not saving enough typing to justify learning another layer, and they encourage selecting all columns when you rarely need them all.

For Trongate's typical use case (solo developers or small teams building performant PHP applications), raw SQL with query_bind() + a handful of convenience methods for genuinely repetitive operations is the sweet spot. Keep the power, lose the unnecessary abstraction layer.

Would rather see development effort go into things like batch operation helpers (chunking, progress tracking, transaction management) than maintaining methods that are barely shorter than writing the SQL directly. Batch operation helpers are, in my experience, genuinely useful.

Thank you.

I had spent ages going round in circles, holding votes with groups of AI engines - asking questions like this: "Which method name is best for fetching a record:

'find()', 'find_one()', 'findOne()', 'find_by_id()', 'findById()', 'find_one_by_id()', 'findOneById()', 'find_by()', 'findBy()', 'find_where()', 'findWhere()', 'find_one_where()', 'findOneWhere()', 'find_single()', 'findSingle()', 'fetch()', 'fetch_one()', 'fetchOne()', 'fetch_by_id()', 'fetchById()', 'fetch_one_by_id()', 'fetchOneById()', 'fetch_where()', 'fetchWhere()', 'fetch_single()', 'fetchSingle()', 'get()', 'get_one()', 'getOne()', 'get_by_id()', 'getById()', 'get_one_by_id()', 'getOneById()', 'get_by()', 'getBy()', 'get_where()', 'getWhere()', 'get_one_where()', 'getOneWhere()', 'get_single()', 'getSingle()', 'load()', 'load_one()', 'loadOne()', 'load_by_id()', 'loadById()', 'load_one_by_id()', 'loadOneById()', 'load_where()', 'loadWhere()', 'load_single()', 'loadSingle()', 'retrieve()', 'retrieve_one()', 'retrieveOne()', 'retrieve_by_id()', 'retrieveById()', 'retrieve_one_by_id()', 'retrieveOneById()', 'retrieve_where()', 'retrieveWhere()', 'retrieve_single()', 'retrieveSingle()', 'select_one()', 'selectOne()', 'select_by_id()', 'selectById()', 'select_one_by_id()', 'selectOneById()', 'select_where()', 'selectWhere()', 'select_single()', 'selectSingle()', 'read()', 'read_one()', 'readOne()', 'read_by_id()', 'readById()', 'read_one_by_id()', 'readOneById()', 'read_where()', 'readWhere()', 'read_single()', 'readSingle()', 'lookup()', 'lookup_one()', 'lookupOne()', 'lookup_by_id()', 'lookupById()', 'lookup_where()', 'lookupWhere()', 'lookup_single()', 'lookupSingle()', 'first()', 'first_where()', 'firstWhere()', 'one()', 'one_where()', 'oneWhere()', 'single()', 'single_where()', 'singleWhere()', 'row()', 'row_by_id()', 'rowById()', 'row_where()', 'rowWhere()', 'record()', 'record_by_id()', 'recordById()', 'record_where()', 'recordWhere()', 'entity()', 'entity_by_id()', 'entityById()', 'entity_where()', 'entityWhere()'"

It took ages.

Turns out, the correct answer to the question is, 'bullschitt'.

Remembering how to do a basic SQL query is better easier than learning the names of a bunch of methods.

I'm really glad that I brought this topic up. We've had some really great comments and it has inspired me to change the whole approach.

Many thanks indeed!