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 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.
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'm using similar security tricks to you: read-only queries with a time limit, against SQLite rather than PostgreSQL.
More here: https://simonwillison.net/2018/Oct/4/datasette-ideas/ and https://github.com/simonw/datasette
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.
My most wanted feature is SQL type providers, like Rezoom.SQL - https://github.com/rspeele/Rezoom.SQL
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.
It's true that in the 2000-2010 period many SQL implementations struggled to scale with growth in websites (many other parts of the webstack did too).
The question is what is it going to cost in either licensing solutions or engineering effort.
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/
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.
https://suneido.com/info/suneidoc/Database/Queries/Syntax.ht...
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.).
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.
Linq in .net shows IMHO how queries can be expressed in a more consistent and composable syntax while still conforming to the relational model.
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!
Me and my companion moved everything to stored procedures. Now every IT-department says that the product is VERY stable.
This piece reads like Joey being unable to open a carton of milk [1], "there's gotta be a better way!".
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.
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 would you do that?
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
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.
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.
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.
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:
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.
What about common table expressions? Or custom defined functions?
with X as ( select... )
It's widely used for analysisWhat's an example of what you're trying to do? I think views, user-defined functions, routines, and select aliases would cover all the bases...
So the jquery of SQL, then.
it's alright, it's consistent enough, it's good
I'm reading about datalog and prolog more and more but sql is ok
To me relational algebra is the beauty queen, and SQL is the "beauty mark" that prevents it from reaching perfection.
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.