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

121–130 of 137 posts

Re: Why you should learn SQL

#121

Post by majewsky »

smitty1e wrote: post #102
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…

One nifty use-case for ORMs is decoupling the logic from the data. I can prototype in Python/SQLAlchemy/SQLite and deploy to Python/SQLalchemy/PostGreSQL via a pipeline with confidence that I'm actually managing the complexity. Cracking open some legacy code with vast swaths of embedded SQL is a source of much weeping and gnashing of teeth when the time comes for maintenance.

I did this for the longest time, testing with SQLite and running with PostgreSQL, until I discovered that this was completely unnecessary extra complexity. It turns out that setting up and starting a fresh PostgreSQL database takes about one second, so it's just as easy to just spawn a Postgres for the unit tests. And now I get to enjoy some immensely useful Postgres-specific SQL syntax like "FOR UPDATE SKIP LOCKED".

Re: Why you should learn SQL

#122

Post by mikewarot »

If you learn SQL, please take the time to learn how to pass parameters without putting them in the query.

Example in python:

  query = """Update employee set Salary = %s where id = %s"""
  tuple1 = (8000, 5)
  cursor.execute(query, tuple1)
It is very important that the parameters of a query never become part of the command string, or a whole class of injection attacks become possible.

At this point, you could still run into problems if your parameters and command string aren't in sync, so watch out for that as well. (Usually this would happen later as the code was modified for new requirements)

Re: Why you should learn SQL

#123

Post by setr »

mike_hock wrote: post #33
kumarvvr wrote: post #30

Earlier quoted context omitted.

The closest that many have come to is to develop ORM libraries. LINQ + EntityFramework Core is my favorite.

He said better than SQL.

LINQ is a much nicer SQL IME, at least for CRUD operations — but I generally don’t use the “objects” part of it except for very rudimentary insert/deletes.

However group by specifically seems to be a hack, and it falls on its face in weird ways almost every time I use it. I’m not sure how much I like it’s “AST rewriting” model, but it works great when it works. Just the LET command alone justifies it

Re: Why you should learn SQL

#125

Post by cultofmetatron »

ya'll need to try elixir's sql system Ecto.

It doesn't try to wrap sql in objects. instead it exposes a dsl that lets your write sql in elixir and get elixir structures.

The end result is that the impedance mismatch is minimal

https://www.irrationalpixels.com/posts/database-modeling-wit...

Re: Why you should learn SQL

#126

Post by takeda »

BiteCode_dev wrote: post #78
takeda wrote: post #72

Earlier quoted context omitted.

If you use IntelliJ IDE in their commercial version (at least of PyCharm) they integrated DataGrip. Basically if you connect to your database and give an option to fetch your schema the IDE starts scanning for strings and if it detects SQL it provides IDE features to it as well (like auto completion, and some refactoring etc). I think ORM and query builders were trying to hack around to make IDEs understand SQL, when…

No, ORM provides an inspectable central entry point for your models with a standardized API. That's the feature. That's why you get a great django ecosystem: the ORM abstractions allow all libs to rely on the fact the rest of the code access the model the same way.

The issue is that if you try to squeeze a relational model into object oriented model you won't get an efficient solution.

Solutions like django might be good when you're starting the project or it is something very simple, otherwise you'll have to fight with it to get something done more efficiently.

My point is that using plain SQL (I personally prefer asyncpg as it provides interface matching postgresql) is actually also easy. Especially if you have IDE that supports it.

I also realized that with this approach I rarely need to even transform the data in any way. Usually whatever I want to do I can get in a single SQL statement (even for things that have some hierarchy, thanks to aggregation functionality). So in the end the function just gets data and displays it.

Re: Why you should learn SQL

#127

Post by thrill »

reedf1 wrote: post #75
cratermoon wrote: post #23

I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it. It's like FORTRAN, except FORTRAN has had the decency to stay in use where it's really the best choice. But SQL is out there, like Clippy. "Hey, I see you're collecting some data. SELECT TRUE FROM HELP WHERE COLLECTING_DATA IS TRUE

The monkey's paw - you get your wish, but now instead of one annoying query language, you have two!

Standards - the thing that we need more of and that we have too many of.

Re: Why you should learn SQL

#128

Post by laurent92 »

elmo2you wrote: post #114
pjmlp wrote: post #109

Earlier quoted context omitted.

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 (…

> 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 wanted to use separate credentials, but the problem with app credentials which match userland credentials is that connection pools require that you use one single set of credentials. And they exist because opening a cnx to the db is slow, so it goes faster when you preopen 20 connections.

Re: Why you should learn SQL

#129

Post by BiteCode_dev »

takeda wrote: post #126
BiteCode_dev wrote: post #78

Earlier quoted context omitted.

No, ORM provides an inspectable central entry point for your models with a standardized API. That's the feature. That's why you get a great django ecosystem: the ORM abstractions allow all libs to rely on the fact the rest of the code access the model the same way.

The issue is that if you try to squeeze a relational model into object oriented model you won't get an efficient solution. Solutions like django might be good when you're starting the project or it is something very simple, otherwise you'll have to fight with it to get something done more efficiently. My point is that using plain SQL (I personally prefer asyncpg as it provides interface matching postgresql) is actual…

Sure if you don't need to build an ecosystem, don't need introspection and can forgo integration, sql is indeed easy.

Also, SQLA proves that an ORM doesn't have to prevent you from getting an efficient solution, you just have to offer several layers of granularity.

Eventually, it's not an or proposition. In django you do use raw sql when you need so. but your auth system doesn't, and a plugin will solve it for you.

Re: Why you should learn SQL

#130

Post by laurent92 »

elmo2you wrote: post #93
pjmlp wrote: post #47

Earlier quoted context omitted.

Anyone that does work on the client that should stay on the server, sending wasted data across the wire has already lost it. To this day I keep writing stored procedures, no need to multiline strings. And for the rest just use either myBatis or jOOP, run away from Hibernate.

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…

The problem with SQL is integration with the rest of the code. I’ve already mentioned that Java not supporting multiline strings made it exceedingly hard to write SQL in-app and incentivized the use of frameworks; It is equally true that it is easier to deploy a .jar rather than a jar + stored procedures.

To update a stored procedure, you have to execute SQL (as in, load a client, deal with exceptions, then actively load an instruction and execute it), then deal with “what happens if there is already a procedure with the same name, what if it’s not ours, what if you don’t have permissions”, etc. If the upside of SQL is that is is functional, ie you declare what results you want and let the DBMS decide how to execute your query (ultimate declarative language), it is really funny that the DDL is instead an instructive language where you provide commands, and it fails miserably if you give the same instruction twice. They got it all wrong! DDL should be descriptive not prescriptive! It should not be “CREATE TABLE” but “TABLE ___ IS ___”!

It’s all about difficulty; If Postgres accepted “git push” to deploy stored procedures, we’d immediately see webservers implemented in SQL ;)

Post reply on HN