Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why you should learn SQL

executeprogram.com

Post reply on HN
Print view View original

111–120 of 137 posts

Re: Why you should learn SQL

#111

Post by goto11 »

BulgarianIdiot wrote: post #110
goto11 wrote: post #108

Earlier quoted context omitted.

Some of Dates gripes are valid, but some are quite questionable. In SQL you enforce uniqueness of rows by defining a primary key. Dates concern seem to be that you can have duplicate rows if no primary key is defined, but why would you do that in the first place? A table should always have a primary key. His gripe against nulls are controversial - E.F.Codd suggested nulls himself, so when Date claims null have no pla…

What's his alternative to null in a nutshell?

Sentinel values of the same type, e.g. -1 to indicate a missing integer.

Re: Why you should learn SQL

#113

Post by mulmen »

BulgarianIdiot wrote: post #106
sa46 wrote: post #101

Earlier quoted context omitted.

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words: > SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other langu…

It seems a lot of those things you list as "wrong" come from lack of understanding of the reasons behind these choices, and born out of pure idealism in vacuum. For example, allowing duplicate rows is irrelevant, because if you have a primary key, there are no duplicate rows. SQL doesn't require primary keys because relational algebra has no such concept as a "primary key". There are just keys. However without defini…

Totally agree with your post but wanted to add on to this part:

> More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system.

PostgreSQL actually supports user-defined types[1]! You could do something like define [2] a “kilogram” type and a “pound” type and then summing or joining on the column is safe.

That could still get sticky with grams and kilograms. Also, pounds are defined in terms of kilograms. So you could also define a “weight” type. This can have semantics like DATE[3] or BOOLEAN[4] so inserting “1kg”, “1kilogram”, “1000g” or “2.204623lb” all store the same value.

You can then use (define) format functions like TO_CHAR(weight, string) to display weights in grams, lb or whatever you need.

Of course, the other argument here is that all you really need are the primitive types and normalization. A “weight” table can just have a “unit”::string column. You can do conversions with another table such as “from_unit”::string, “to_unit”::string and “multiple”::float. Your “product_weight” table would then just have foreign key relationships to the weight and conversions tables.

[1]: https://www.postgresql.org/docs/current/xtypes.html

[2]: https://github.com/df7cb/postgresql-unit

[3]: https://www.postgresql.org/docs/13/datatype-datetime.html#DA...

[4]: https://www.postgresql.org/docs/13/datatype-boolean.html

Re: Why you should learn SQL

#114

Post by elmo2you »

pjmlp wrote: post #109
elmo2you wrote: post #93

Earlier quoted context omitted.

You are right, regarding the wastefulness. Additionally, there are extra risks involved when data needs to be transferred between a storage and compute facility. To a degree I get why architects these days like to separate everything into individual narrow and easier to manage/tuned services. But separating business logic from the data it runs on may equally be a fundamental mistake, when it comes to guarding integri…

There is also a security aspect to it, with stored procedures, even with stolen creditials there is very granular acess, that would have to be otherwise provided with tons of views. I think it is a consequence of six month bootcamps and then be allowed to call themselves "engineers" (in countries that allow such things).

> There is also a security aspect to it, with stored procedures, even with stolen creditials there is very granular acess, that would have to be otherwise provided with tons of views.

In fact, now that you mention this .. I've seen a few times how an application had complete granular access control on every individual record, based on the specific (personal) user credentials you would connected with to the database (PostgreSQL). There are some really interesting things possible. It always makes me cringe (at least a bit), when I see yet another application access a database with just a single set of (admin) user credentials. So much missed potential.

> I think it is a consequence of six month bootcamps and then be allowed to call themselves "engineers"

Certainly true, but I think commercial companies that constantly try to market their products/services as "making everything so much more easier, without a need to understand all that pesky complicated matter" are at least equally as much to blame for the apparent degradation of skills and knowledge.

I'm well aware that almost every generation looks at the younger generation thinking: "what little do these young people actually know". To a degree no doubt in their own ignorance, lacking real understanding of all the new things currently in use. On the other hand, I think this shift from actually understanding technology towards professionals increasingly just becoming convenient (and easy to replace/retrain) "tool jockeys" is nonetheless a real thing too.

That personally saddens me, because I know how much easier it will be for greedy commercial interests (think the current tech giants) to manipulate/hijack technology, with total disregard for actual technological progress (or even quality). A growing group of "professionals" who can't even tell the difference anymore, sure won't help.

Re: Why you should learn SQL

#115

Post by throwaddzuzxd »

Animats wrote: post #92
frogcoder wrote: post #54

I used to love ORM, I used it everywhere. Writing another language in the language I am coding is wrong. ORM simplifies my programs. No, ORM does not simplify coding! It's a big complex adapter which does not fit many cases. RDBM itself is complex enough, let's put another complex abstraction above it so we can forget about the tables and columns and joins and foreign keys. Complexity added upon another complexity do…

" I still hate to compose SQL in code, but there is no better way." The one big problem with SQL is intermixing query language and query parameters. This requires escaping and is the source of many PHP vulnerabilities. Instead of do_sql('SELECT * FROM tab WHERE name = "abc"') there should have been a standard where you wrote do_sql('SELECT * FROM tab where name = V1", "abc") or something like that. So you don't do st…

I don't know if it's a standard but both Postgres and MySQL have PREPARE statements, and any sane language driver will implement it correctly

Re: Why you should learn SQL

#117

Post by reportgunner »

frogcoder wrote: post #54

I used to love ORM, I used it everywhere. Writing another language in the language I am coding is wrong. ORM simplifies my programs. No, ORM does not simplify coding! It's a big complex adapter which does not fit many cases. RDBM itself is complex enough, let's put another complex abstraction above it so we can forget about the tables and columns and joins and foreign keys. Complexity added upon another complexity do…

> Writing another language in the language I am coding is wrong.

Yeah but you're writing your strings in english anyway.

Re: Why you should learn SQL

#118

Post by _y5hn »

Animats wrote: post #92
frogcoder wrote: post #54

I used to love ORM, I used it everywhere. Writing another language in the language I am coding is wrong. ORM simplifies my programs. No, ORM does not simplify coding! It's a big complex adapter which does not fit many cases. RDBM itself is complex enough, let's put another complex abstraction above it so we can forget about the tables and columns and joins and foreign keys. Complexity added upon another complexity do…

" I still hate to compose SQL in code, but there is no better way." The one big problem with SQL is intermixing query language and query parameters. This requires escaping and is the source of many PHP vulnerabilities. Instead of do_sql('SELECT * FROM tab WHERE name = "abc"') there should have been a standard where you wrote do_sql('SELECT * FROM tab where name = V1", "abc") or something like that. So you don't do st…

Unless you're using hardcoded values, using bind variables is the only proper way. Otherwise, the DBMS has difficulty recognizing the same query pattern for performance, and you're open to injection vulnerabilities for security.

https://www.databasestar.com/sql-bind-variables/

The big problem is the shitty workplace environment of today that discourages sharing, learning and proper software development.

A proper build-pipeline will include security scanners that detects these vulnerabilities automagically.

Re: Why you should learn SQL

#119

Post by BiteCode_dev »

BulgarianIdiot wrote: post #91
BiteCode_dev wrote: post #80

Earlier quoted context omitted.

It's super verbose, doesn't compose well with the host language primitives, is hard to introspect, full of gotchas and very low level.

Databases have no "host" they have "clients" which are heterogenous. So directing this critique at SQL is completely unfounded. .NET added LINQ and now basic queries are integrated. It was not up to SQL, it was up to .NET And calling it "very low-level" makes me question if you understand what SQL does and is. It's probably the only mainstream 4th generation language I can think of. https://en.wikipedia.org/wiki/Four…

Host language, as in the language of your app that embeds some sql code, not host related to the db. The host language may or may not be the client language.

It's low level because it lacks abstraction and anything outside the data query declarative paradigm is subpar. It has no notion of anything outside of itself, so it's a very restrictive inflexible dsl living in it's own bubble. And even worst, it lacks elegant abstractions for problems we've have had for decades, such as pagination, hierarchy, etc

Also, using linq as an example rather shows your lack of understanding of the field of abstractions for sql. The state of the art ORM is currently SQLA, the leading innovation to replace sql as a dsl is edgeql, both are addressing sql pain points while aiming at allowing you to do all of what sql could do.

Re: Why you should learn SQL

#120

Post by BiteCode_dev »

goto11 wrote: post #104
BiteCode_dev wrote: post #80

Earlier quoted context omitted.

It's super verbose, doesn't compose well with the host language primitives, is hard to introspect, full of gotchas and very low level.

In what way is SQL "low level"? I would consider it a very high level DSL. Agree about the verbosity. The syntax is quite clunky.

Higher level than C, sure. But compared to modern programming language, it lacks primitives, important abstractions, asks you to do many things manually and have way to many gotchas to be on par of what we consider high level in 2021.
Post reply on HN