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.
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 interesting scaling problems and how do you replicate normalized schemas. Thus denormalization became a thing, and then the emergence of NoSQL and document stores started to infect everywhere. The JOIN was a killer too, and then the discipline required to do sharding made it annoying to manage, so easier to manage solutions became a thing.
I'm looking at databases in a different light these days with more appreciation, but now the hot new thing is GraphQL makes things... interesting. I don't view GraphQL as a server-side solution, but a client solution to overcome the limits of HTTP/1.1. However GraphQL clients are exceptionally complicated, and I'm not sure they are worth it. The only problem is that to overcome them requires engineers "to know how to do things", but that is a hostile stance. People want to go fast and make progress, and GraphQL enables that.
GraphQL and SQL are not mutually exclusive at all.
GraphQL is, in no way, faster than any REST alternative in terms of implementation speed. If anything, it is slower, as you need to be extremely methodical with your API changes, as (same with REST I suppose) deprecating fields / entities, for mobile clients specifically, is a PITA unless your clients have really nicely built out forced upgrades.
What GraphQL _does_ give you, is type safety and extreme client flexibility . It is a better solution than REST in almost every scenario, other than the initial learning curve, which takes a couple weeks and then you know it forever.
Would I recommend some startup write a GraphQL API for their MVP? No, just get something working. Are you at a more medium-sized company looking to build out your much more permanent API? Then yes, you should probably strongly consider GraphQL.
Too bad nobody thinks whether or not they need the flexibility in the first place. MVPs with single clients using GraphQL for flexibility that is not needed or used are common.
GraphQL is a hammer, and now every project is a nail.
Anecdotal, of course, but the first thing all FE developers I've ever worked with do when they start/join a project is add/suggest GraphQL/Apollo.
Nobody considers how awful things look on the back-end when you need to cache or make magic happen to avoid thousands of N+1 queries.
I believe unfortunately it has become the only way many front-end developers learn to interact with any back-end, and now everyone's forced to use it regardless of its drawbacks.
Same with React. Facebook managed to get free training for all of their future hires. I hate the company but that was a genius move.
I have seen startup using GraphQL for mvp, precisedly because they could change and crank ui fast. They already knew GraphQL, I did not when I joined and learned to modify already existing one in a day or so.
I've been experimenting with SQL as an API language - including client-side SQL constructed in JavaScript - fir a couple of years with my Datasette project.
I'm using similar security tricks to you: read-only queries with a time limit, against SQLite rather than PostgreSQL.
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.
This makes a ton of sense to me, thanks for sharing your learnings.
I see very little value in using GraphQL, when you can just write SQL on the client!
We desperately need frameworks to better facilitate this, like Hasura or Django -- DB migrations, permissions, authentication, real-time subscriptions, Admin UI.
MongoDB could be an example, but it introduces yet another empire.
Redis is also easier.
The key is what are you designing against. If you design against a DB, then you may find that scaling beyond a single host with gotchas. But, if you have the discipline to keep everything within a document, then you can scale up easier as the relationships between documents is more relaxed.
However, cross document indexing and what-not creates more problems, and that in and of itself is an interesting challenge.
Given that I’ve seen 25TB MySQL databases, RDBS scale up juuuuust fine. Just... have someone on hand who understands them if you’re going to go that high.
Do you love SQL, or do you love relational algebra? Because actual SQL, the language, is pretty shitty.
Perhaps the best querying language I've ever used is Q-SQL, integrated into kdb+/q. Unlike SQL, it's actually part of the language (q/k) and, most importantly, it's modular and more expressive than SQL.
If you're interested in how we can do a lot better than sending strings to remote databases using an inexpressive and non-turing complete language, check it out: https://code.kx.com/q4m3/9_Queries_q-sql/
Not parent poster but I love relational algebra. But so far I have yet to use an alternative to SQL which is less bad. Most of them seem to be designed by people who do not understand SQL.
I agree with you, I haven't found an alternative that is less bad. But that doesn't mean that I like SQL. It is so inconsistent and uncomposible, with too many awkward ways to do different things.
I love the idea of a functional algebra for querying a database, and while SQL is acceptable, it is far from great in my opinion.
Take a look at Suneido. As a product, it's a weird thing that kinda stands by itself, and isn't particularly useful because of that. But the database and the query language is straight up relational algebra.
Same for me, even more after venturing into the NoSQL hype of the early 2010s, I implemented pretty successful systems using non-relational databases but over time I stick to a RDBMS as most and as far as I can.
Not only I prefer to work with SQL nowadays I also prefer SQL over any ORM in older codebases, ORMs are pretty useful for getting up to speed without caring about your persistence layer too much but after 17 years in this industry I've had my fair share of issues with ORMs to avoid them whenever I can.
Native SQL queries with placeholders for my parameters in their own files, loaded by my database driver to execute and return data is my go-to solution for data access, it's flexible, maintainable and readable if you treat SQL as your normal code (code reviews, quality standards, etc.).
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.
It sounds like you are more in love with relational algebra than SQL itself. Relational algebra is sound mathematics, but SQL used to express those ideas is horribly old and outdated. We have learned a lot about language design in the half century since its inception. We can do better.
I love SQL but I still think we can do better! The strength of SQL is the underlying relational model and relational algebra. Syntax wise SQL is somewhat clunky.
Linq in .net shows IMHO how queries can be expressed in a more consistent and composable syntax while still conforming to the relational model.
The work I do lately tends to be missing an sql layer and yes, I miss it a lot. I loved organizing my data at that layer and having such powerful ways to query it. It felt like I could eternally find ways to optimize it, and I really enjoyed learning year after year.
Lately I use nosql for very light data and otherwise our API outputs heavily cached and extremely simple data. Adding a database as a middleman wouldn’t make sense. Still fun, but I miss Postgres!
I like relational algebra. I dislike SQL. It harkens from a similar era as COBOL, and I dislike COBOL for procedural applications for the same reason. We've learned a lot about language design over the years and it is a shame that we've put very little effort into adopting new languages to address this particular problem space.
No, you're right, SQL is awesome. I mean, the syntax itself is pretty creaky, but it doesn't matter, the power to declaratively query structured data is the key. Everything that provides that power is awesome; SQL is just at the top of that heap in the breadth of its utility.
I really like the relational model, and I think its the best way to model data. Referential integrity is an awesome thing that removes the possibility for many bugs to exist.
As for SQL it has its warts, but im pragmatic when it comes to programming languages, like for example C, JavaScript, its also "ugly" but its often the best option anyway.
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.
Why? When I worked doing infrastructure and CI/CD automation we created a pipeline for deploying stored procedures, not that different from any other code lifecycle process.
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.
You can use Liquibase or Flyway and an automated deployment process to keep your SQL code in sync with non-SQL code (if needed.) For bonus points, you can make your stored procedures be callable by other stored procedures, create/teardown mock data, and do TDD where your test suite of stored procedure tests runs on build during deployment and either has a PASS and deploys or hits a FAIL and the deployment aborts.
I love SQL. I'd love someone to make it better for complex queries. Have you seen the enterprise SQL monstrosities. Why do we have ORMs if SQL is perfect?
We have ORMs because our programming languages object models are not relational, they are hierarchical - from C to Haskell, everyone goes for highly non-relational data representations.
So, when we interact with a relational DB we need some kind of layer to map between the world of relations in the DB and the world of objects in our program.
Not for the reason you're implying, actually for the opposite reason. I'm from the days of yore, just before ORMs became popular and it basically replaced a lot of boilerplate code, but it wasn't the SQL that was the bulk of it.
It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *.
Also, here's a now mainly historical pain most devs never encounter any more: Before ORMs and the various migrators, your object properties might not have the same name as your SQL columns. Yes, it was dumb when you did it, yes, it caused loads of bugs, yes, it actually happened quite a lot.
> It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *.
I am running a side project and yes, writing the native SQL statement to take your object and put it in the database is not a problem, put in the parameterised values and off you go.
But getting the data back from the database? Oh the horror. So much boilerplate in order to see if there are any records returned it all, if there are enough columns with the correct name for the kind of object you are making, if there is data or not in each column as appropriate for that specific column, if a given field can be coerced into being a string or an integer or a date or similar, then they're all marshaled into a dto object which is passed to the create new object validator. 800 lines of code later, and you may have an object back!
Dapper appears to be the sweet spot for me, I am still writing SQL queries and still designing the SQL tables myself, no orm magic here, but it handles the actual marshalling to and from an in-memory dto object versus data in the table for me, and that is very valuable time savings.
Most SQL libraries handle this all for you? You really don't have to do any of that.
It's perfectly fine to just 'know' that the data is going to be an int and just do `var id = rs.getInt(id")`.
I'm on my phone so it's too fiddly to write my own brief example, but if you get rid of the silly comments in this you'll see you can do it all in a few lines, just very boilerplate lines:
Completely agree. For example, why isn't there functionality to define aliases for complex expressions and then reuse those through a query. Simple query-local SQL functions would be nice too.
I'd love to see a "SQL-like" language that compiles down to SQL itself, much like Babel or TypeScript in the JavaScript world. I think the tricky thing is that there is no single SQL target.
> Completely agree. For example, why isn't there functionality to define aliases for complex expressions and then reuse those through a query. Simple query-local SQL functions would be nice too.
What about common table expressions? Or custom defined functions?
I use it a lot, because I know it fairly well. Not sure I "like" it; it's like a lot of food - I know what of the food varieties I prefer over others, even when I didn't immediately "like" any of them. More of a "got used to" type of thing.
What you (and I) like is not SQL per se but what comes through of the underlying relational model, that SQL hasn't screwed up. SQL as a syntax and semantics is a mess. It could have been better.
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.