We Can Do Better Than SQL • Hacker News Board

Re: We Can Do Better Than SQL

#171

Post by Akronymus » Tue, Aug 11, 2020, 8:22 AM UTC

lmm wrote: post #158

Akronymus wrote: post #146

Earlier quoted context omitted.

I love SQL for making it possible to almost trivially enforce most business rules. Such as: You can only use one of the in another table specified values in this field.

I struggle to understand this viewpoint. In my experience business rules are harder to express in SQL than in practically any first-class programming language. "Is this value one of this list of values" - whether that list is hardcoded or dynamically obtained - is completely trivial. (Of course if you apply some double standard where editing your "source code" requires multiple approvals whereas changing your "databa…

With SQL you can enforce it within the datastore. And if some requirement changes, you can instantly enforce it. Eg.: A new foreign key constraint.

Also: It is centrally managed, so you don't need to look all over the source code to find the constraints and don't need duplicates either, if you can, for example,change the data at multiple points.

Re: We Can Do Better Than SQL

#172

Post by orogor » Tue, Aug 11, 2020, 8:23 AM UTC

tus88 wrote: post #92

I shall name it...NoSQL.

Data integrity, speed , replication Pick two. Nosql gave up integrity. If you are fine with your bank account sometime missing your salary, great. Its not quite the same as twitter sometime missing a comment.

Re: We Can Do Better Than SQL

#173

Post by igeligel_dev » Tue, Aug 11, 2020, 8:25 AM UTC

bjo590 wrote: post #93

Am I the only full stack dev that likes SQL? SQL is an incredibly expressive and flexible way to read, store, and update data. It's ubiquitous, so the SQL skills I learned six jobs and three industries ago are still relevant and useful to me today. Relational Databases and SQL are heavy lifters that I often relay upon to build projects and get things done.

Same for me. I love it. I always avoided the NoSQL things so far since the use-case is mostly unstructured data in comparison to normal SQL databases. I think what most engineers struggle with is just thinking of rows and columns as data. It is a separate thing to create schemas, work with them, inserting data, manipulating data and reading data. These are all different skills. Creating the proper queries is like learning an own language. Most programming languages come up with their own ORMs like the LINQ style of C# (EntityFramework) or all other ORMs like TypeORM for TypeScript, the Django ORM or Hibernate. So overall, to learn how to handle data you have to understand SQL basically + the abstraction layer. Sounds a lot harder than having data in a simple object.

Personally, I was lucky because I had great courses, even in highschool, regarding SQL including: How are rows working, what is normalization, how does it help with data and so on. So naturally I developed some feeling on how to handle database tables.

Re: We Can Do Better Than SQL

#174

Post by kchoudhu » Tue, Aug 11, 2020, 8:26 AM UTC

I do not know what the standard data query language of 2120 will look like, but I do know it is going to be called SQL.

Re: We Can Do Better Than SQL

#175

Post by Akronymus » Tue, Aug 11, 2020, 8:26 AM UTC

sozy777 wrote: post #166

Akronymus wrote: post #142

Earlier quoted context omitted.

At work we almost exclusively use pure stored procedures[1] and everything is normalized very well. It is an absolute joy to write SQL, because of how terse it is while still being very readable. Trying to implement business rules about data relations outside of the DB is a nightmare. [1] We use dynamic SQL within stored procedures for pivots.

You lose source control on your procedures. How you deal with that?

We use daily backups of the whole server, so if we really need to rollback, we can. And if we absolutely need source control, we could write a procedure in combination with a trigger to automatically write the stored procedure to some source control.

Also there are tools out there that provide that functionality. Found with a few seconds of searching. https://host.apexsql.com/sql-tools-source-control.aspx

Re: We Can Do Better Than SQL

#176

Post by shean_massey » Tue, Aug 11, 2020, 8:33 AM UTC

unodgs wrote: post #106

mathgladiator wrote: post #96

Earlier quoted context omitted.

No, I'm with you and prefer SQL for many tasks. SQL got a bad rap in many ways due to security issues, databases in general, and "web-scale". SQL as a language within other languages is a nightmare from a security standpoint, and if language integrated query was more common across languages earlier on then this wouldn't have been an issue. Databases generally depend on normalization, but normalization comes with inte…

Recently I tried using SQL directly on the frontend: https://medium.com/@unodgs/sql-on-the-frontend-react-postgre... as an alternative to GraphQL which I also find too complicated. You might find this interesting.

It took me a solid month before graphql finally "clicked" but there's no going back now, I absolutely prefer it over rest now. 10/10 would do again and do suggest everybody give it a try.

Re: We Can Do Better Than SQL

#177

Post by MaxBarraclough » Tue, Aug 11, 2020, 8:38 AM UTC

ianamartin wrote: post #63

I love critiques of SQL about implementations of NULL. "NULL is so special that it's not equal to anything, not even itself!" Like, duh. WTF should NULL be equal to? Anytime I see people making this kind of argument about "doing better than SQL" I can immediately tell they are pretty much fucked in the head. Good luck, edgedb peeps. You haven't got a clue.

> WTF should NULL be equal to?

In programming languages, NULL tends to be equal to itself. Why couldn't it be the same way in SQL?

Re: We Can Do Better Than SQL

#178

Post by woile » Tue, Aug 11, 2020, 8:38 AM UTC

iddan wrote: post #162

quickthrower2 wrote: post #120

Earlier quoted context omitted.

I was hoping for something more left field myself. If SQL is based on tables, what about a QL based on relations only. Columns of data that are related, the "TABLE" implementation detail doesn't need to factor into it.

This is what we are trying to do with Cayley https://github.com/cayleygraph/cayley

The website needs a link to the documentation, I just couldn't find it :/

Re: We Can Do Better Than SQL

#179

Post by Akronymus » Tue, Aug 11, 2020, 8:41 AM UTC

AmericanChopper wrote: post #156

Akronymus wrote: post #145

Earlier quoted context omitted.

>footguns Ran into one recently. Where a table was joined either to one or the other table, based on if a value was null in the first one. This was fine, until we added a where clause to a, through multiple joins, base table for both options. This tanked the performance >1000x.[1] If we just returned the value it had basically no impact. We tried solving it with using the result set as a base for a select where we di…

Imo, polymorphic associations are one of the key areas that the relational model in general struggles. You can do them in most RDBMS, but they’re always a bit janky. Even when you’re just modelling your schema, you really have to think quite hard about it, and you’ll really struggle to preserve simplicity.

In this case it was a

left join sometable on sometable.someuid = isnull(someothertable.someuid, somethirdtable.someuid)

I guess that is such an uncommon case that it tripped up the optimizer completely.

Also: Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it.

Edit: Both tables were actually the same one, just retrieved via different joins, so different data.[1]

[1]One was a company, the other was the company we need to send money to. This is for when deal with a daughter company but pay the parent company directly, for example.

Re: We Can Do Better Than SQL

#180

Post by disgruntledphd2 » Tue, Aug 11, 2020, 8:46 AM UTC

gorgoiler wrote: post #165

I often find SQL is extremely good for explaining what you are trying to do. It is of course a declarative language, but more than that it does what a good language should do: explain in both directions. Languages need to tell the machine what to do, and to tell the person reading the code what it was the original author was supposed to be doing. Many bugs happen when the two don’t match up, and the maintainer is oft…

Mostly, unless the author was enamoured of subqueries, or believes that the only acceptable name for a CTE is cte1, 2 etc.

But yeah, as long as you know what the underlying tables look like and what they enforce, then SQL is really easy to maintain and understand.