Post Are6Obp9SCXK2N6w6a by hexaheximal@mastodon.social
 (DIR) More posts by hexaheximal@mastodon.social
 (DIR) Post #Are6Obp9SCXK2N6w6a by hexaheximal@mastodon.social
       2025-03-02T02:37:07Z
       
       0 likes, 0 repeats
       
       Here's an sqlc + postgres query, used to fetch a list of posts to be displayed on a user's profile:-- name: GetPostsByAuthor :manySELECT * FROM posts WHERE author = $1;Can you spot the problem with this query? ;)#SQL #PostgreSQL #Postgres #SQLC #Go #GoLang
       
 (DIR) Post #Are6Oci69uUMmmooka by divVerent@blob.cat
       2025-03-02T13:44:13.602635Z
       
       0 likes, 0 repeats
       
       @hexaheximal Ignoring the possibility of SQL injection or not depending on what this `$1` is or isn't, the next obvious problem is that the output set is unbounded - both in number of records, and it also likely returns unnecessary columns. One should always explicitly list the columns one wants in machine queries, `SELECT *` is for human use only. Otherwise you will sooner or later either accidentally include `BLOB` columns of multiple megabytes per row that you don't even need (or maybe needlessly expensive computed columns), or your code actually depends on the order of columns and breaks when adding another one.The exception might be when `posts` is a view that this very code module defined. Then using `SELECT *` might be OK.
       
 (DIR) Post #Are6dqSTKIthlglfSC by divVerent@blob.cat
       2025-03-02T13:46:58.402913Z
       
       0 likes, 0 repeats
       
       @hexaheximal Should add: in case this code mitigates the problem of missing LIMIT by using some kind of row cursor and basically stopping the iteration when having enough data, one very likely will want to see an ORDER BY clause, or else the results can be rather useless. Even if due to proper use of primary keys or indexes the order is already defined, it is good style to use ORDER BY anyway if the code actually relies on the order (the DB engine then will just ignore it and not sort needlessly).