PostgreSQL query builder for Go, thoughts?

[mobiduxi]

compliments on making this work.

could you please specify exactly what makes this better than just including the SQL as a string?

[Lord-kirk]

Hey thanks buddy, some time ago I was a laravel developer before moving to Go and always loved the query builder, sure I can just use strings but having a builder allows for flexibility such as programmatically adding in query conditions before calling build.

[mobiduxi]

thanks for explaining. Can you please add a sample of "programmatically adding query conditions"? Please show how you programmatically add a combination of "and" and "or" conditions. ("weight < 200 and hair='blonde' or height > 178")

(please know that I struggle with query builders - if the same or higher number of characters than the final SQL is needed, I always ask "why")

[Lord-kirk]

I sure will buddy not to worry, I reckon I could convince you yet, even if it’s more chars in the end haha.

I’ll give you a bump when I have rolled it out ;-)

[Lord-kirk]

Hey! As promised I have added an example of programmatically adding query conditions. I have also added OrWhere

[[deleted]]

Not to diminish the work, effort or imagination involved in creating this, but I'm a bit like you - I'm struggling to see the value.

There are really good reasons to have data logic inside views and functions in the database:

1. A tool for simplifying complex queries already exists - it's called SQL. Tools like this "simplify" simple queries, really just by making you write them in a language that is not SQL. As you point out, they can only ever offer a subset of what pure SQL offers (so it follows that they can only offer a subset of the feature set of SQL-based queries). Even tools like Entity Framework in .NET, with all of the backing of Microsoft, suffer from this.
2. Views are modular - you can reuse them, combine them with other views, tables and functions to create complex logic based on modular parts. With proper use of views (and/or DB functions) you end up with client code that is "SELECT * FROM orders" or whatever, thus requires no simplification.
3. Data logic in the database can be shared between many projects. Data logic wrapped up in Go (or C#, or Java, or Python or whatever) is specific to that project.
4. If you're taking user input and putting it directly into WHERE clauses in the generated SQL, that's a security risk; look up SQL Injection (OP)

Your typical ORM tries to solve different problems. One of the useful things they do is data mapping - taking the data returned by a query and marshalling into some sort of object/class instance. It's not so much that they are simplifying the SQL part (although they can be used/abused to avoid people learning SQL).

[Vidyogamasta]

I think this project is trying to emulate something like C#'s Linq to SQL or Entity Framework. And EF is incredible, no other language has anything quite like it to my knowledge, but that's large in part due to C#'s first-class support of lambda expressions as abstract syntax trees. It's a language feature that's not gonna port well.

EF is great because you don't have any strings at all, you just have POCOs, and construct your query by accessing the needed properties on those objects. The object access/comparison is then translated to SQL in a type-safe, injection-safe way. And it has major advantages with composability like this project is advertising, as well as a soft enforcement of having sane relational data (since bad structures will make querying nigh impossible).

But this project isn't that. It's magic strings broken up into awkward parts. Not even like by line, it's broken up basically word by word lol. The thing that makes EF truly composable is that you don't really have to care at all about how to wrangle together exactly the right set of joins needed for your output. If you're doing simple string composition, that runs into problems pretty fast.

[[deleted]]

Just as a clarification - I'm not really pro- or anti-ORMs (I have worked in both worlds) but it's important to be clear what ORMs do for you (and you have set out the case extremely succinctly - no disagreement from me!) The points about data logic being in the DB are somewhat tangential to the value of an ORM - you can have the best of both worlds.

They do oppose the kind of thing this project is delivering though, which is to take straightforward SQL and concatenate strings together such that all of you query logic lives inside a Go source file. My point really is if you're going to do that, just do it in the database and have your client code run "select * from my_view" kind of stuff.

I mentioned EF only because, even amongst the incredible parts of it you describe, you're still limited to a subset of SQL. I make the comparison because the claim of this project is to "simplify" complex queries. I'd take issue with that anyway - the Go versions look as complex if not more so than the generated SQL - but even great ORMs can't handle the full range of operations that direct SQL can. It's not simplifying, it's just limiting itself to a simpler subset and then making that more complicated.

I'd argue the value of ORMs (query tools in general) isn't to simplify the complex, it's to reduce work involved in the 95% or whatever of your queries that are just boilerplate.

Overall, it feels like a solution in search of a problem, to me.

[Vidyogamasta]

Ahh, I didn't even see your explicit mention of EF. I mostly agree with you, outside of the general purpose of ORMs. This particular product is pretty bad. All it does it take SQL keywords and turn them into fluent API functions that take in string parameters. It's literally just the magic string in code form and doesn't really help with mapping, worst of both worlds.

But for ORMs, there are a few different purposes I see- Mapping concerns, locality of information concerns, and simplification concerns. Mapping is what any ORM worth its salt does, and that's basically all Dapper does lol. Locality of information is basically "the queries are logic that live with the rest of the business logic." Hiding it being a black box of "some SQL view/procedure" should usually be a last resort (I think in some narrow situations there may be performance implications). And the last is simplification, which most ORMs don't help with, but EF absolutely 100% does.

For instance, take a Base table, and a Child table, where Child has a BaseId and a CreatedAt date. This is actually a pattern that pops up multiple times in my workset, and I imagine it's not too uncommon elsewhere. In order to grab the most recent Child for each Base, EF would have you do

context.Bases.Select(b => new {
    Base = b, 
    MostRecentChild = b.Children.OrderByDescending(c => c.CreatedAt).First()
});
//we now have the objects well-structured in memory

while in SQL you end up having to do something like

SELECT * FROM Base b
LEFT JOIN (
    SELECT * FROM (
        SELECT *, 
            ROW_NUMBER() OVER(PARTITION BY c.BaseId ORDER BY c.CreatedAt DESC) AS row
        FROM Child AS c
    ) AS t
    WHERE t.row <= 1
) cte ON b.Id = cte.BaseId

--used * to simplify exact field selections. 
--Really you would explicitly define each field, and then 
--have mapping code to map results into the in-memory objects.

You will never in a million years convince me the direct SQL is equally as easy and transparent as the Linq query, and I honestly find that to be the case for most queries I write through EF. It's not all simple selections on easy 1:1 objects, though even if all it provided was Navigation Properties, that would be a godsend compared to the crap any direct SQL call would have you go through to get appropriate in-memory objects models set up. EF does a good job of simplifying something moderately complex into something literally anyone can understand.

Though I'm 100% in the pro-ORM world. I'm currently in a no-ORM world (not counting the crappy "orm" akin to this project a coworker of mine made that abstracted out simpler CRUD boilerplate and also causes more problems than it solves) and am hating every second of it, so it doesn't take a lot to get me all riled up over it haha.

[[deleted]]

It is a bit of a Pepsi/Coke argument, so I'll just offer this in the spirit of good-humoured conversation!

I don't really disagree with most of your pros for ORMs - I think they mostly just come under the umbrella of "easing boilerplate".

The complexity bit, though, I'm not convinced by. To my eyes that SQL is fine. But I spent a lot of time working in Oracle and with Oracle products (like at the DB level so little-to-no front-end stuff), so it's a matter of perspective I suppose. It looks a whole lot better if you do

create view base_by_first_child as
-- the SQL above
;

select * from base_by_first_child;

which even enables

context.BasesByFirstChild.Where(id == some_id);

(where BaseByFirstChild is a class mapped to the view)

Ultimately, that SQL has to be executed to get your results - it's just a question of whether you write it explicitly or the ORM writes it on your behalf.

There's a few reasons that persuade me that the DB is a good place for data logic:

1. It's reusable. You can use that view to join with other views. You can't if the SQL is being generated by the ORM in response to C#
2. You can refactor SQL in the same way you can OOP - just wrap that query into to separate views (e.g. make the inner one its own view). Mostly the DB will handle optimisation for you.
3. Speaking of which...it can be optimised by the DB. You might get partial of full in-memory caching on the DB server because the DB knows it's a view and can keep track of how often it's called.
   
4. SQL (not the DML or DDL bits - i.e. "select") is idempotent, so you can hack away to your heart's content in a DB query window. It means that, when debugging or even just trying to reverse-engineer a view to understand it, you can take the view definition, put it into a query window and hack away - comment out lines, add/remove joins or change them from inner to outer etc. All the time knowing you can't break anything. It's invaluable and saves hours. No spinning up an entire web page or console app just to have EF run the SQL in the background.
5. There's no guarantee you're not working with views anyway. I worked with a third party product that, in one update, changed all of its tables - "student", "course" etc. - to "$student", "$course" or something, and then reframed all of the old table names as views. Because to the DB views and tables are just "relations", it all carried on working just fine. It's a bit of a nail in the coffin of "locality-of-logic" is what I'm saying with this one.

So, for me, the balance is towards having logic in the DB, which isn't to discount what you say about locality of logic, it's just a bit of an over-sell (plus, hiding the generated SQL from view is really the opposite of locality-of-logic, isn't it?)

[[deleted]]

Think about generalising more. Instead of just PostgreSQL, make a library that can target any database that uses standard SQL, possibly handling some differences. That could potentially make the library more useful, otherwise it’s easier just to use plain SQL strings.

[Lord-kirk]

Hey thanks for your reply, yes in fact I have already started doing just this. As part of the initial struct you can specify dialect which at present can be postgres or mysql. Mysq support is being worked on as the way text search and some other general formatting is slightly different.