Is there something better than SQL?

If you’re modeling relational data, it doesn’t seem like you can get around using a DB that uses SQL, which to me is the worst: most programmers aren’t DB experts and the SQL they output is quite often terrible.

Not to dunk on the lemmy devs, they do a good job, but they themselves know that their SQL is bad. Luckily there are community members who stepped up and are doing a great job at fixing the numerous performance issues and tuning the DB settings, but not everybody has that kind of support, nor time.

Also, the translation step from binary (program) -> text (SQL) -> binary (server), just feels quite wrong. For HTML and CSS, it’s fine, but for SQL, where injection is still in the top 10 security risks, is there something better?

Yes, there are ORMs, but some languages don’t have them (rust has diesel for example, which still requires you to write SQL) and it would be great to “just” have a DB with a binary protocol that makes it unnecessary to write an ORM.

Does such a thing exist? Is there something better than SQL out there?

CameronDev,

I agree that SQL is hard, and optimising it is harder, but is a bespoke binary protocol per DB really easier?

You can usually connect to the DB directly via an ODBC driver, but that will involve SQL anyway.

onlinepersona,

A protocol that forces the use of a query builder or ORM would be easier and better, I think. The DB developer would have to provide a library, which means one would have to learn the library, not a new language.

CameronDev,

Making raw sql access harder for applications is probably a good idea. Perhaps with something like rusts “unsafe”, so you can still do it, but you have to deliberately make the decision to do so.

The other users for raw SQL are DB administrators, and I dont think youll be able to take SQL away from them as easily. I dont actually know what or how DB administrators work, but I dont think they are developers a lot of the time, so requiring them to write code to do their job might be a non-starter?

usernamesaredifficul,

The difficulty is in database design being tricky not the language. There’s no getting around that by using a different language

onlinepersona,

I disagree. Database design is tricky, yes, but with SQL, you now have two problems.

StarkillerX42, (edited )

There are other query languages. InfluxDB develops one called Flux. It’s a master class on why corporate led open source can really suck. Daily use features get deprecated all the time and each new release is breaking. The documentation is horribly insufficient and the language syntax is usually not clear and it wraps many behaviors into one vague function name. Yes, SQL is pretty impressive.

jochem,

With Influx 3 the preferred language is gonna be their SQL dialect. Flux is on its way out and I suspect it will get deprecated in the near future. Flux saw very slow adoption and SQL can do everything as well.

StarkillerX42,

That’s the most Influx-like decision ever.

fubo,

One alternative to both raw SQL and and ORM is a query builder, a procedural library for constructing database queries. Query builders typically don’t have the object/relational “impedance mismatch” of ORMs; they don’t encourage you to pretend that records in the database are the same as objects in your code. But they give you a syntax that looks more like your programming language, and automatically handles escaping (and thus, resistance to injection attacks).

However, query builders often don’t expose all the power of your database. If you’re using PostgreSQL, you’ve got one hell of a powerful set of tools in there. It’s often worth spending the time to master them just so you don’t end up reinventing the stone-age wheel on top of a warp-speed hovercraft.

bill_1992,

I’ve been using Jooq to build my queries (and run them). Beats the hell out of writing prepared statements in strings.

Not sure what power I’m missing though, I’ve been able to do everything via Jooq that I want to do.

koreth,

You’re not missing much power with jOOQ, in my opinion as someone who has used it for years. Its built-in coverage of the SQL syntax of all the major database engines is quite good, and it has easy type-safe escape hatches if you need to express something it doesn’t support natively.

makr_alland,

I like the ideas behind PRQL, although I’ve never used it in an actual project. It compiles down to SQL but has a clearer model based on pipeline and a much better suntax.

Malloy is a similar project but I haven’t look into it yet.

glue_snorter,

Shame I had to scroll this far to find PRQL

onlinepersona,

Thanks, checking those out now.

KrokanteBamischijf,

Sounds like it’s not really SQL as a query language but rather the whole database paradigm that’s the problem here.

Look into noSQL databases and their respective drivers. They often use JSON-like syntax and are more likely to be seamlessly integrated with whatever programming language you’re using.

If a search engine won’t point you in the right direction I’d suggest having a look at MongoDB, which is well documented and fairly accessible to mess around with.

bill_1992,

The point about a binary protocol is interesting, because it would inherently solve the injection issue.

However, constructing an ad-hoc query becomes tedious, as you’re now dealing with bytes and text together. Doing so in a terminal can be pretty tedious, and most people would require a tool to do so. Compare this against SQL, where you can easily build a query in your terminal. I think the tradeoff is similar to protobuf vs json.

You could do a text representation (like textproto), but guess what? Now injection is an issue again.

Another thing would be the complexity of client libraries. With SQL client libraries, the library doesn’t need to parse or know SQL - it can send off the prepared statement as-is. With a binary protocol, the client libraries will likely need to include a query builder that builds the byte representation since no developers are going to be concatenating bytes by hand, which makes the bar higher for open-source libraries. This also means that if you add a new query feature to your DB, all client libraries will likely need to be updated to use the feature.

And you’re still going to need to tune and optimize queries for this new DB. That’s just the nature of the beast: scaling is hard especially when you can’t throw money at the problem.

Quite frankly, it’s a lot of hard tradeoffs to not need to use prepared statements or query builders. Injection is still is an issue for SQL today, but it’s been “solved” as much as it possibly can.

hperrin,

Yes, but they’re not ubiquitous, so you gotta research them.

Spore,

Datalog. Basically relational, but have much simpler syntax and semantics,and is able to convey more complicated queries in a composable manner. Mainly used in Clojure-based databases where actual programs instead of strings are used.

onlinepersona,

🤔 interesting. First order logic for querying. I found CozoDB which might be useful. Still not sure about how to map objects to the DB. The tutorial is not very relatable. Lots of numbers and single letters.

Thanks for the hint though. I’ll check it out.

  • All
  • Subscribed
  • Moderated
  • Favorites
  • •
  • random
  • uselessserver093
  • Food
  • aaaaaaacccccccce
  • [email protected]
  • test
  • CafeMeta
  • testmag
  • MUD
  • RhythmGameZone
  • RSS
  • dabs
  • Socialism
  • KbinCafe
  • TheResearchGuardian
  • Ask_kbincafe
  • oklahoma
  • feritale
  • SuperSentai
  • KamenRider
  • All magazines
    •