Post AX5t70veTceW2mRdEO by louis@emacs.ch
(DIR) More posts by louis@emacs.ch
(DIR) Post #AX5sQASPAv6p8wKlto by louis@emacs.ch
2023-06-26T20:05:22Z
0 likes, 0 repeats
Just realized, by not using any kind of SQL framework or ORM:I can leverage the full power of my DB's SQL (in this case PostgreSQL)stay independent of a one-person-maintained third-party librarymake part of my business logic more portable [i.e. across different programming languages]maximize "the principle of least surprise"On the other side, squeezing SQL into a string is ugly in any programming language.What say you?#sql #development
(DIR) Post #AX5skXNRfWau1Ij916 by jazz@hachyderm.io
2023-06-26T20:09:04Z
0 likes, 0 repeats
@louis that's what i am doing. But using something like sqlx for go.
(DIR) Post #AX5t70veTceW2mRdEO by louis@emacs.ch
2023-06-26T20:13:09Z
0 likes, 0 repeats
@jazz sqlx gives a little sugar on top of the bare bones sql package, I use it too.Have you ever tried sqlboiler? That's why I'm moving away from.
(DIR) Post #AX5tfA7CpQmMNf8Fea by jazz@hachyderm.io
2023-06-26T20:19:20Z
0 likes, 0 repeats
@louis i looked at it. But just kept at sqlx.I try to keep the database as simple as possible, and keep to simple sql. Been burned earlier with ORMs in Java, so I prefer it to be simple.Elixirs ecto might be a notable exception
(DIR) Post #AX5uW963LWkKZMrDQe by otfrom@functional.cafe
2023-06-26T20:28:53Z
0 likes, 0 repeats
@louis someone must have made a datalog for postgres (without having to go full xtdb)
(DIR) Post #AX5zFCbTYQaEIGIMOe by defanor@emacs.ch
2023-06-26T21:21:52Z
0 likes, 0 repeats
@louis I gave up on ORMs a while ago, and tend to do quite a bit inside the DBMS (relying on stored procedures, security policies, constraints and checks, etc). Complex query building is error-prone and cumbersome, whether it happens inside a stored procedure or inside an external program, yet hard to avoid sometimes. To avoid that, I experimented with taking SQL queries directly from clients and executing them, but only for hobby projects so far. PostgREST and similar tools help to avoid much of that if the goal is simply to expose a database over HTTP, which is often the case, though it only handles typical queries, and does feel somewhat like an ORM.I think those approaches help to minimize the awkwardness of embedding (or even dynamically constructing) SQL as a string, and I try to be content with the remaining bits of it. After all, we mostly manipulate sources in other languages as strings as well, generally being far from structured editing, and it is not that bad. Though could be better.
(DIR) Post #AX62CqcYc6oJOoMzr6 by galdor@emacs.ch
2023-06-26T21:55:02Z
0 likes, 0 repeats
@louis I always use simple SQL strings, it does the job. In Go, backticks helps writing requests.
(DIR) Post #AX62NJJslcUrIpckLo by offset___cyan@emacs.ch
2023-06-26T21:56:58Z
0 likes, 0 repeats
@louis I read this really interesting blog post about using GADTs to avoid ORMs and ugly strings (and have type-checked queries): https://gopiandcode.uk/logs/log-ways-of-sql-in-ocaml.html
(DIR) Post #AX62qJjEKH72y7JNU8 by redfish@emacs.ch
2023-06-26T22:02:10Z
0 likes, 0 repeats
@louisWhile it's a desirable goal, putting together SQL strings by hand is a pain. SQLalchemy Core has been awesome for using the whole PostgreSQL hog (Core is separate from the ORM, which also looks pretty good as far as ORMs go, but I haven't really used it)
(DIR) Post #AX6so0taZRNlb3Nagi by gothnbass@linuxrocks.online
2023-06-27T07:44:22Z
0 likes, 0 repeats
@louisI'll say that I really like Cypher's feature of parameterised queries, because it (mostly) lets me avoid string-concatenation.Haven't used SQL in a few too many years, but doesn't it have something similar?
(DIR) Post #AX72ZxJ47K0dx8EP8y by sqrtminusone@emacs.ch
2023-06-27T09:33:55Z
0 likes, 0 repeats
@louis Well, here are some of my thoughts. I used #postgres + #sequelize (nodejs) at work for a few years, #postgres + #sqlalchemy a few times, and a few others at basic level.This turned out to be long :D (1/3)As for "the full power of my DB's SQL", I often use raw SQL in ORM constructs, usually for calculated attributes, complex where/order clauses, to call database functions (I used pg_trgm recently), etc. Alternatively, in case the query is too complex (usually something with GROUP BY), I may use the ORM to generate parts of a raw SQL query, mostly where/order/pagination.The where/order/pagination part is nice because our frontends have lots of tables. The table library (called ag-grid) provides different filters for different fields, AND/OR conditions for filters, etc. So we've come up with a solution that plugs the where/order/pagination parameters from ag-grid into #sequelize. This makes adding/maintaining forms/db tables quite a bit easier.A few other "developer experience" advantages I can think of:- #sequelize "scopes", aka reusable pieces of queries tied to models, are rather handy.- It's also easier to reuse and recombinate pieces of ORM code (which are js objects in this case) than it is to concatenate SQL strings in the correct way. In one project I have multiple tables (with JSON documents :D) that have a rather complicated access system, e.g. rights on the document depend on the user's global rights, department rights (departments also have a hierarchy), rights on the parent document... And one class that generates the pieces of queries that filter the relevant tables.- ORM constructs usually take less code and fewer "units of thought", e.g. an ORM can: - specify JOIN conditions for you - automatically send a COUNT(*) query with a normal select query for pagination, and strip the COUNT(*) query of unnecessary clauses (such as left joins) - automatically generate subqueries to implement querying a 1:n relationship with pagination
(DIR) Post #AX7h8o6oImjhjEfkno by emaksovalec@emacs.ch
2023-06-27T17:08:25Z
0 likes, 0 repeats
@louis using #csharp #linq was the only time when I enjoyed working with SQL. Its a DSL that straddles OOP-SQL dichotomy very well. And it works with native container types perfectly. One would think that such #sql DSL would be more easily found in #lisp / #scheme land
(DIR) Post #AX7nG3XNw80TKcwU9A by louis@emacs.ch
2023-06-27T18:16:57Z
0 likes, 0 repeats
@emaksovalec Linq is what SQL should look like in 2023. SQL was designed as a user interface, not an API. But nowadays is basically used as such in 99.9% of all cases.But, as with any ISO standard, it well never change and yet, the differences between majors RDBMS are blatant. Instead every system puts more and more features on top of it.It is not the complexity of the relational model as such but SQL that spawned a generation of NoSQL databases. Yet, I find the relational model and SQL still unmatched in its power and flexibility.
(DIR) Post #AX8lINSip25gxLhTjE by baron42bba@emacs.ch
2023-06-28T05:29:41Z
0 likes, 0 repeats
@louis There are 2 reasons why I still love Perl to talk to databases: prepared statements in DBI and HERE documents. By using both of them sql looks nice. I even wrote a small eMacs functions to look around the cursor for the sql statement and open it in a sql-Mode buffer. Preparing your sql statements prevents sql injection as well. Lately I learned about prepare_cached. If your functions get called multiple times Perl is recycling the prepared statement call.
(DIR) Post #AX8n5bM63YH2BldYo4 by keith@mastodon.nz
2023-06-28T05:49:45Z
0 likes, 0 repeats
@louis what I do is have .sql files with my sql,code and embed them as resources and then use Dapper.NET to do mappings from SQL to types. Works well, I wrote a wee library to help with it, https://github.com/keithn/katoa.queries which loads the sql from embedded resources is also nuget package. Though I actually have made a better version, just haven't published it!