Why you should learn SQL

https://www.executeprogram.com/blog/why-you-should-learn-sql

by arkj • 5 years ago

69 135 5 years ago

simonw• 5 years ago

Of all of the skills I started to pick up twenty years ago, SQL and Bash are the two that have continued to serve me well for my entire career.

Akronymus5• 5 years ago

And probably learning where and how to find information you need.

throwaddzuzxd2• 5 years ago

Agree. Regex literacy is a big skill to have too.

tanin3• 5 years ago

I've built a desktop app that enables you to load CSV files and perform SQL on the CSVs.

I imagine it would be handy for anyone who starts learning SQL because you don't need to setup the database or use command-line.

If you are interested, please try it out: https://superintendent.app

rmnclmnt• 5 years ago

So it’s a visual wrapper around SQLite?

tanin3• 5 years ago

Yes! with few additionalfunctionality added (e.g. regex, parse_date, tsv).

Initially, I wanted it for myself because I used Excel a lot but I never loaded CSVs into a database (dunno why). Using SQL is much more convenient than Excel's formula.

Pamar2• 5 years ago

It's nice. Windows only? (I mostly use a Mac at home).

EDIT: no, it's Win/Mac/Linux, apologies...

mr_toad2• 5 years ago

There’s also csvkit.

tanin3• 5 years ago

Yes, it supports all 3 platforms. Hope you enjoy it!

zeta0134• 5 years ago

I use SQL, or something like it, on pretty much any project that needs to store any significant amount of data. Easily the most bang for my buck out of any of the skills I picked up in high school.

Even if it's not SQL proper, you should learn a query language of some sort and get used to the idea of asking an engine to help you sort and filter your data in a consistent way. Once you have that mental model down, you'll find you can migrate between the SQL-likes and the SQL-not-so-likes comfortably, and pick the best tool for the job at hand.

asutekku• 5 years ago

Even as a PM, SQL is one of my most valuable skills. I can handle trivial tasks myself without bothering data analyst and speed up the processes by a lot

eurasiantiger6• 5 years ago

SQL is going to eventually die because of its three-pronged relationship model that implies query-time complexity. Learn it if you want, but especially backend and analytics people would be better off learning Gremlin or Cypher.

Edit: downvoting this is not going to make it untrue.

Pamar2• 5 years ago

For everyone (like, for example, me) who has never heard these two names before (or not as replacements for SQL at least...) maybe the following might be useful:

https://stackoverflow.com/questions/13824962/neo4j-cypher-vs...

holoduke2• 5 years ago

You are getting doenvoted because it's untrue. So many faang companies using traditional SQL. And for good reasons. Not to say your two examples are bad. They have their own benefit. But don't become the guy who believes in one replacement solution for all without the knowledge of all. It's annoying

eurasiantiger6• 5 years ago

And how many FAANG companies are using a graph database?

helsinkiandrew3• 5 years ago

That is a truism everything is eventually going to die - but SQL has been around for longer than just about all development technologies today and probably will be around a lot longer than things that are popular now. I would guess that it will be around when people have move on from React, Gremlin, and Cypher.

mr_toad2• 5 years ago

For simple queries, row adjacency will beat the graph hands down. And RSBMSs are hard to beat for transactional processing.

eurasiantiger6• 5 years ago

Plenty of graph databases, even distributed ones, come with transactional guarantees and ACID to boot. Not everything is eventual consistency.

anigbrowl• 5 years ago

Eventually is a long time. SQLite is committed to supporting its file format out to 2050 and it's the recommended format for data archival by the Library of Congress. I suspect SQL will still be going strong long after my final exit().

Thing is, it's pretty neat in many respects (simple enough to do simple things easily, surprisingly flexible), and I say that as someone who really likes graph databases.

hashhar2• 5 years ago

You're getting downvoted most probably because your comment provides no arguments why. Why would I be better off with Gremlin or Cypher?

eurasiantiger6• 5 years ago

Because it eliminates the complexity of joins: both the mind-complexity and the algorithmic complexity.

DemocracyFTW• 5 years ago

I for one am in theory very interested in graph DBs, but in practice I yet have to see how do implement a solutions for my particular set of data (and my ideas about the data) in a graph DB without it becoming a huge ball of spaghetti.

If you're so enthusiastic about graph DBs that's good for you; as for me I enjoy having named rectangular sets of data (i.e. tables and views) consisting of smaller entities (columns / fields) that follow formally described rules (types, domains, check constraints, foreign keys and higher-order invariants). I do have a number of gripes about SQL but right now I feel quite happy about having achieved a reasonable way to integrate an RDBMS (SQLite) into my application environment (NodeJS) where I can fluently switch between formulating solutions in SQL or in JS with few restrictions.

Programming means you have to constantly keep a number of balls in the air, all the time. Having my data in handy tables is a big boon and not something I currently can expect a graph DB to do for me.

Hypergraphe• 5 years ago

The point of this article is more on the importance of data integrity in databases than on SQL itself.

abhinav222• 5 years ago

SQL is an important skill to have, in particular one should learn about database normalization and the normal forms - very important stuff!

eurasiantiger6• 5 years ago

All of it is completely irrelevant unnecessary complexity. Learn to use Tinkerpop.

bottled_poe• 5 years ago

Curious that 99.99% of applications use an SQL database, must just be a lot of really bad developers. Am I so out of touch? No, no, it’s the children who are wrong.

Akronymus5• 5 years ago

And even if you don't want to normalize, at least don't make the DB basically incomprehensible.

Speaking from experience, a "abnormalized" db sucks. https://news.ycombinator.com/item?id=27842820

abhinav222• 5 years ago

Yeah one should know why it’s important to normalize, and then when they go a different path, it is done consciously and not because they are inexperienced and didn’t really know any better.

Folks out there who don’t want to learn the ins and outs of database theory - please don’t; read up on it, it’s a very important skill & knowledge to have and doesn’t take long to master. It does take a few days, perhaps weeks, for all the ideas to “click” in your head.

Whilst we having many fads in IT, there are certain sound principles of computer science developed over 40-50 years which one should learn :-) Know the rules before you break them!

urthor3• 5 years ago

There's reasons for one wide table.

The traditional SQL database uses row based data structures.

When you shift to columnar data formats for the table, parquet or any columnar DB, the normalization rules which were developed for row based become extremely different.

The brave new world now is in-memory DBs.

All those 1970s rules, which make sense for row based tables stored on disk, don't apply to data in RAM.

At all.

So it's going to get very interesting.

mulmen3• 5 years ago

SQL is a query language. Data normalization is a relational design methodology. They aren’t actually related except that relational databases often use SQL and benefit from normalization. You can normalize data models without SQL and you can use SQL to interact with denormalized data.

holoduke2• 5 years ago

I am using spring hibernate. It's so powerful. I don't need to write and understand SQL anymore. Sarcasm off: I see more and more juniors without SQL skills. Various reasons they give me. From the nosql plague to some cloud solutions to the above example. SQL seems to be not so fun anymore. I have a hard time understanding that. Since data storage is such a fundamental part of your application. I would even argue that the data structure to some degree influences the application architecture. Specifically if you need performance and security.

laurent923• 5 years ago

SQL is useful but we’ve spent decades to abstract it away, because the Java/SQL articulation is always circumvoluted, and juniors do without.

Then they load two tables in Java and join them manually in memory; or they fetch the principal record, and use the getter to retrieve the dependency, so Hibernate does one query per row.

I accuse all ORMs, especially Hibernate, of making it too easy to skip SQL, and I accuse Java of not supporting multiline strings. We have failed, in most languages, at making it easy enough to write SQL yourself. JPA is nice to cache records in memory, but it’s been way more harmful to performance that it became hard to write SQL directly.

pjmlp5• 5 years ago

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.

scns4• 5 years ago

If you are able to use Kotlin, you can write multiline strings.

ivolimmen• 5 years ago

I detest hibernate and the whole JPA that came after it. It's more complex than using SQL directly. The whole mapping thing looks simple enough but when I create a one to many relationship on a new project from scratch I always need to reread the documentation because it is not working the way I expected it to work. The reason hibernate was created was to simplify data access. Sorry but I think it failed miserably. On personal projects I use SQL directly. It's easy, fast and simple.

elmo2you3• 5 years ago

I think you are complete right, but maybe I believe so in part because of my age.

To me the general undertone in all the arguments I have heard over time is either "we now have this tools/service solving that us" or "why would I need to learn all this (complicated) stuff, I've done fine without it".

The first variant I just never have seen being true, but that might be hard to see by someone who is reluctant to learn/understand anything about relational data organization.

The second variant I can to some degree sympathize with, because it does seems daunting to me, how much time/effort the young kids these days have to spend on keeping up with the ever changing landscape of new smoke and mirrors technology (very little of it fundamentally new or innovative). Learning something that appears to be far removed from the tools they already know will no doubt feel like a challenge and maybe therefore also as a waste of time.

However, the apparent resistance to learn about something as relational data organization (because this is actually about far more than just SQL), feels to me as rather troubling regarding the quality of software design.

The way I've hears some people talk about SQL and relational data organization, made me even wonder if they should even be allowed to call themselves software engineers. To me they sounded like architects telling me that concrete is such an outdated and unstructured mess that they no longer need (to know about) it to design buildings.

The problem is, buildings and software will be build anyways (for there is a need) .. and if their architects can get away with saying they don't need to know about some fundamental theory, then good luck with the quality of that software. No amount of tools/services that supposedly abstract the challenges of good structured data design away, can ever solve that problem.

devnull3• 5 years ago

At times it might make sense not to use SQL. That is fine. But I think there is a value in learning SQL. Some reasons:

* Learning effort to Reward ratio is good. A substantial syntax is valid for all of the DBs that support SQL.

* Very easy to remember the syntax. I have not used SQL in a long time but I can still write one without much effort (including the nested queries)

* Pareto: About 70-80% of data retrieval use-cases are well covered in SQL syntax (or a combination of multiple queries like union, creating temp table, etc)

* Tooling: Free & commercial tools which connect to multiple data-sources and provide SQL e.g [1]

[1] https://www.jetbrains.com/datagrip/ (I am not associated with jetbrains)

scns4• 5 years ago

Or Idea Ultimate which contains Datagrip's functionality and the other languages they made IDEs for, except CLion (C/++, Rust debugger) and Rider (C/F#). Android Studio can connect to the Apps SQLite db. There is a plugin for Vim to connect to DBs, Written by Tim Pope.

Just a content user.

(edit) Links for the curious:

https://github.com/tpope/vim-dadbod

There are a UI and completions for it too:

https://github.com/kristijanhusak/vim-dadbod-ui

https://github.com/kristijanhusak/vim-dadbod-completion

drloser• 5 years ago

select * from argument a where a.subject like '%sql%'

KingOfCoders2• 5 years ago

We're currently moving our Druid/Kafka/Spark setup to BigQuery because of how simple SQL is.

hashhar2• 5 years ago

You might find https://trino.io/ interesting. It allows you to bolt on a MPP SQL execution engine on top of any data source including pre-built connectors for Druid and Kafka.

It's all ANSI SQL and the best part is you can combine data from heterogenous sources. e.g. You can join data between a topic in Kafka and a table in Druid or even between Kafka, S3 and your RDBMS.

Disclaimer: I'm a maintainer of the project.

dominotw• 5 years ago

My employer is going to the other way from snowflake -> druid to reduce high response latencies/ improve freshness.

I am assuming your use cases can tolerate increased latency?

KingOfCoders2• 5 years ago

Yes they can, we will move low latency to a smaller, different pipeline.

lordnacho• 5 years ago

You should learn SQL because it's likely to be a very different language to the ones you already know. In SQL you don't tell the DB how to get the data (for loops and pointers) but what the result should look like, and the DB figures out an execution plan. This style is well worth practising and appreciating.

SilverRed2• 5 years ago

and the DB figures out an execution plan.

And then you spend the next hour trying to decipher the plan and work out how to get it to do something that isn't insanely slow.

nesarkvechnep• 5 years ago

Maybe create foreign keys and indexes in your Rails migrations

rdedev• 5 years ago

When I first encountered SQL i hated it as a language. But then the more I understood the working of RDBMS the more I appreciated its use cases. ETL is probably best handled by the DB if you don't want to get down into optimising the ETL process. I feel having tight loops in SQL and having another programming language as a glue between them is a decent tradeoff but you have to keep data transfer in mind.

cratermoon4• 5 years ago

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

urthor3• 5 years ago

Nobody else has invented any other declarative language it seems.

Come on aspiring post-docs, do your thing and launch a new programming language.

If MIT can launch Julia someone can launch a language that's not SQL.

pjmlp5• 5 years ago

Like Kusto?

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/c...

https://docs.microsoft.com/en-us/azure/data-explorer/

BiteCode_dev6• 5 years ago

Edgeql is trying.

masklinn• 5 years ago

Nobody else has invented any other declarative language it seems.

Postgres was originally built on one: https://en.m.wikipedia.org/wiki/QUEL_query_languages

There’s also D/D4/Dataphor, though i’m less convinced.

Unsurprisingly that mostly died out after sql took over everything.

goto116• 5 years ago

There are multiple alternatives. Problem is SQL is "good enough" and ubiquitous and deeply entrenched. It will be extremely difficult for any alternative language to unseat SQL.

kumarvvr3• 5 years ago

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

mike_hock• 5 years ago

He said better than SQL.

xupybd2• 5 years ago

I'm normally not a fan of ORMs but I've had good experiences with Entity Framework.

I really like the automatic migrations.

For small apps and a single dev it hits a sweet spot.

I find .Net really good at being kitchen sink included and business ready. If you're making internal small apps I've yet to find a better platform. Especially if you're in an MS Office heavy environment.

pjmlp5• 5 years ago

Kusto, https://docs.microsoft.com/en-us/azure/data-explorer/

downWidOutaFite• 5 years ago

EdgeQL looks promising https://www.edgedb.com/docs/edgeql/overview

BulgarianIdiot7• 5 years ago

I've spent most of my career hoping that something - anything that's better than SQL will come along and replace it.

So, if we put aside Clippy jokes and so on, what's your problem with SQL exactly and what does "better than SQL" mean to you?

Because what I saw in the last couple of decades is the NoSQL movement lose air, and half the products representing it adding some sort of SQL dialect support, which if you think about it is hilarious admission of defeat.

SQL is exceptionally good at being a relational algebra language, and if you think in terms of relational algebra, you might find out there's nothing wrong with it, aside from superficial remarks like "FROM should come before SELECT".

BiteCode_dev6• 5 years ago

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

strbean• 5 years ago

When I heard "NoSQL", I thought we were getting an alternative query language for working with relational data, not throwing away the entire concept of RBMS's.

sa46• 5 years ago

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 language become available as a better alternative to it.

Summarizing Date's points:

- Real relations don't contain duplicate tuples but SQL tables allow duplicate rows.

- Tuples and therefore relations don't ever contain nulls. Personally, I have trouble understanding how to support this limitation given LEFT JOIN.

- Domain types only provide type aliases not a new type. For example, you can compare or join on different domain types if the base type is the same. Date uses the example of `p.weight = sp.qty` to show a comparison on domain types that shouldn't be allowed.

- SQL has many ways to express the same query. His example shows at least 12 ways to answer "Get part numbers for parts that either are screws or are supplied by supplier S1, or both."

- SELECT DISTINCT should have been the default instead of SELECT ALL.

My personal gripes:

- SQL is quite chatty, so much so that you can omit words in some incantations.

- It's hard to dynamically build queries. It composes poorly for anything dynamic, even simple things like ordering by a column name.

[1] https://en.wikipedia.org/wiki/Christopher_J._Date

cratermoon4• 5 years ago

- actual abstract data types

- object (or rather, tuple) identity

- replace null with something to indicate missing or unknown. Have sensible null object behavior

And the big one that gives every relational algebra purist fits: make graph theory a first-class citizen of the data model.

cratermoon4• 5 years ago

half the products representing it adding some sort of SQL dialect support, which if you think about it is hilarious admission of defeat.

I don't see that as an admission of defeat. I see that as an attempt to provide some of the things that are good about SQL without dragging in the entire hairball of the language. Along the way, by choosing which subset of SQL to emulate the designers get to incorporate programming concepts that have come along since SQL. Things like user-defined ADTs, lambda expressions, and extensibility. Oh wait, LISP had all those before relational algebra was a gleam in the eyes of Date & Codd.

wvenable• 5 years ago

SQL may look ugly but it got the fundamentals correct.

It's sort of like Git in that way.

SQL is hard to replace because most replacements are either not radical enough to make migration worth it or so radical that it's something else entirely.

setr3• 5 years ago

The problem with SQL is that it doesn’t have the fundamentals correct — it doesn’t compose well, the language is an arbitrary hodgepodge of keywords, it conflates data administration with data manipulation/retrieval, it runs a ternary logic under boolean terms — leading to both potential for false negatives and false positives, it lacks a real standard (it has one, but it standardizes very little, and gets extended arbitrarily), it lacks any useful standard for libraries — causing your choice of DB to essentially also be your choice of standard library.

As a language, SQL is a downright mess. The only thing it really got right was being based on the relational algebra and model.

It’s also why language tooling is utter garbage is the DBA world. The lack of real Autocorrect/suggestion, code formatters, useful error messages, static typing (the tables & views are strictly typed, but your queries can’t be?), the lack of interoperability, etc all stem from how awful SQL is at being a programming language. Jumping from C# to SQL is like viewing a portal into the 80’s — it’s ridiculous. Hell, the standard itself is behind a damned paywall. If that’s not a signal of something going horribly wrong, I don’t know what is

What I’ve never understood is why open source databases don’t have alternative frontends to SQL — where’s my first-class support for datalog in Postgres? Or even just a SQL dialect that lets me put FROM before SELECT so autocomplete can do something useful for once?

reedf1• 5 years ago

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

thrill• 5 years ago

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

helsinkiandrew3• 5 years ago

The SQL langauge is old, strange, and important.

I've always found it strange that something hasn't replace SQL, particularly for traditional 'web' client/server applications. But SQL is probably the only technology/language that has survived in the past 30 years of my career.

99% of their SQL databases probably don't require transactions and ACID behaviour. There nearly always needs to be a layer to convert the rows into a format for use (blobs of JSON for a front end etc). Complex queries are hard enough to be used as standard interview questions.

I guess the out of the box performance, availability and reliability of SQL databases, that 0.1% of times when you need ACID writes and transactionality, and the (just about) interoperable SQL makes it an easy choice - no one was fired for choosing SQL.

gopalv• 5 years ago

99% of their SQL databases probably don't require transactions and ACID behaviour

99% of applications do not require multi-statement transactions or rollbacks, but basic ACID is hard to live without (at least the 'D' and 'C' parts).

You probably don't need a massively parallel transaction system, perhaps you just need to isolate writers out but the fact is that most of what you need comes for "free" with a standard SQL database + failovers.

It's not that no one was fired for picking it, but that people can be hired to fix it when it doesn't quite work as well as it should.

kumarvvr3• 5 years ago

99% of their SQL databases probably don't require transactions and ACID behavior.

Until they do. Developer mind share for the data storage aspect of their applications is much simplified due to ACID.

Just look at the documentation of NoSQL databases. A lot of code is for integrity checks and verification.

jeltz• 5 years ago

Over 90% of the projects I have been involved in have required ACID. In fact I can't think of any that used a database and did not require ACID so I think your 99% should be inverted: 99% of all databases require ACID. This is evident from how even MonogoDB is ACID now with their new storage engine.

NorwegianDude2• 5 years ago

I'd say that if you think transactions is needed just in 0.1 % of usecases then you probably have a lot of bugs in your application, or you have a very simple one.

Transactions are used quite often, and there isn't really any other options.

helsinkiandrew3• 5 years ago

My apps may be simple and have lots of bugs but I'd still say that the vast majority of SQL calls in all apps are to read data that isn't being written to at all or very infrequently - hours/days/weeks and reading possibly temporally inconsistent data isn't important.

boiler_up800• 5 years ago

Working on a new SQL client https://arctype.com based on this very thesis! SQL tooling is rough tho. My first intro was pgadmin and MySQL workbench which I feel serves to turn a lot of beginners off. If you have to get Python 3 packaging right to even start writing queries.. it’s just a very tough journey.

My other gripe is about ‘intermediate SQL’ - CTEs, window functions, stored procedures. Intentional practice was needed for me to be able to use those! Unlike Python where you pretty much become a functional programmer without realizing it over time.

robertwt7• 5 years ago

It is indeed very helpful, actually my first class in CS colleague was SQL in oracle. I think that's one of the class that is most valuable to me without my realising it. Until now I still remember most of the language because I used it every day, it has similar syntax and understanding even when I convert to Mysql or Postgres.

Without realising it is one of the most important fundamentals that is nailed in your head, suddenly you just understand when you look at raw SQL from your ORM to debug, what went wrong etc.

NorwegianDude2• 5 years ago

SQL is constantly getting new features. The newest is SQL:2016. So while it's old, there is a lot of newer things too.

ideamotor2• 5 years ago

I just keep getting faster at SQL. It's fun. I used CROSS LATERAL JOIN last night to find the nearest regions to points. Super fast and easy: https://carto.com/blog/lateral-joins/. Right on the data. SQL does change over time, as Postgres adds features, but especially when you use MADlib, Postgis, and other extensions. I mean ... https://postgis.net/docs/reference.html. And everything I learned when I started is still relevant. It's my go-to crunch tool for data processing.

ideamotor2• 5 years ago

I just want to throw out there, one of the weaknesses I've seen is the lack of fast options for pivoting (wide <-> long). I've used the tablefunc module: https://www.postgresql.org/docs/13/tablefunc.html but it's not particularly fast in my experience (maybe they've improved it since it has been awhile) and the syntax is a bit odd. I'm spoiled with R (newer version) tidyr's pivot_wider and pivot_longer.

Gosh, yes it's been awhile. I posted this, ahem, over 8 years ago: https://stackoverflow.com/questions/15415446/pivot-on-multip.... Guess what, while to many things change, I still use SQL.

I should, uh, try it again. Or try PL/R, PL/Python, or PL/pgsql. There are options.

frogcoder• 5 years ago

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 does not make a polished interface.

If you want to use ORM, you have to learn both SQL and ORM with some depth at least. When problems occur, you have to debug both.

20 years in, I am still learning about RDBM. I still hate to compose SQL in code, but there is no better way.

Databases are used in every situation. Want to store data? Database. Want to communicate between processes? Database. Want to store logs? Database. There are different solutions for different scenarios, but as long as the DB chugs along, why? Sometimes it's sickening.

So 20 years in, the ROI of learning SQL is great. Please don't avoid learning about RDBM by using an ORM. Just learn it.

Actually, I'm not without ORM now a days. Light-weight ORM like Dapper serves well, it does not abstract out the concepts of RDBM, but make things easier.

BulgarianIdiot7• 5 years ago

Writing another language in the language I am coding is wrong

That's not what's wrong. But rather writing in some language within a string literal is what feels wrong to you.

With better integration it'd be natural. The problem is, of course, there's no single SQL dialect.

takeda2• 5 years ago

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 in reality this approach is what actually was needed.

BiteCode_dev6• 5 years ago

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.

Animats• 5 years ago

" 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 string operations on the query parameters.

setr3• 5 years ago

Aren’t you referring to parameterized/prepared queries? Any proper DB driver/library should have this feature.

    do_sql('SELECT * FROM tab where name = ?", "abc")

https://www.php.net/manual/en/pdo.prepared-statements.php

I recall getting curious when I was younger where the deranged advice of “sanitize your queries” came from — realizing that you should be able to simply tell the database that this is a string, not part of the query itself. SQL injection should barely exist as a concept, let alone be the #1 web vulnerability.

IIRC it turned out MySQL supported parameterized queries for ages, but the stdlib php MySQL library just didn’t add support for it. This discovery solidified my understanding that PHP has been giving developers brain damage for decades.

throwaddzuzxd2• 5 years ago

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

_y5hn• 5 years ago

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.

smitty1e• 5 years ago

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.

majewsky• 5 years ago

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".

reportgunner• 5 years ago

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

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

axiosgunnar• 5 years ago

This article is an ad for an online course.

aslkdjflkajsdf• 5 years ago

I'd like to see SQL as a first class citizen in a static typed language with tuples & record polymorphism.

disclaimer: I hate ORMs.

Something of interest: https://smlsharp.github.io/en/

rawoke083600• 5 years ago

Wow I've been digging into the ML languages these last few months. First time I heard of SMLSharp. Looks very good :)

throwaway81523• 5 years ago

You might look at the now defunct ur/web if you haven't. An old snapshot of the web page:

https://web.archive.org/web/20170429201116/http://impredicat...

Digit-Al• 5 years ago

It's a real testament to Chamberlin and Boyce that no one has been able to come up with anything better in ~45 years.

xupybd2• 5 years ago

Abstracting the persistence layer is important, but also unique to your application. ORMs come with some nice features however relying on a full ORM feature set can lead to leaky abstraction. Not to mention you have to learn another DSL to use most ORMs. Why not just use SQL. A nice custom wrapper around binding query parameters is all I need most of the time.

If you use an Onion or Clean Architecture you'll push the SQL into its own layer and you don't need an ORM.

mikewarot• 5 years ago

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)

ChrisArchitect• 5 years ago

(2019)

cultofmetatron• 5 years ago

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...

Why you should learn SQL

Related Stories