Is there something better than SQL?

Comments

• JackbyDev@programming.dev ⁨1⁩ ⁨year⁩ ago

  No, SQL is great for relational data. There’s like decades of research into it. It’s the best.

  source

  • sip@programming.dev ⁨1⁩ ⁨year⁩ ago

    relational databases have years of reseach into them, not the query language itself.

    sql was built so people other than devs can use it, but we got stuck with it.

    source

    • normalexit@lemmy.world ⁨1⁩ ⁨year⁩ ago

      SQL has been around and evolving since the 70s. It is an ISO/IEC standard and is portable across a multitude of databases.

      https://en.m.wikipedia.org/wiki/SQL

      source

      • -> View More Comments
    • lysdexic@programming.dev ⁨1⁩ ⁨year⁩ ago

      sql was built so people other than devs can use it, but we got stuck with it.

      Not really. Being designed with UX in mind, so that it sacrifices conciseness for readability, does not make it something for “people other than devs”.

      Likewise, BASIC was also developed with UX in mind, and no one in their right mind would ever claim that it’s not a programming language.

      source
  • r1veRRR@feddit.de ⁨1⁩ ⁨year⁩ ago

    SQL is horrible as a language to read or write. There’s a million different variants, because it lacks so many basic things. And when used in other code, you generally end up string concatinating one language in another language, with all the HORRIBLE bugs something like that brings about.

    Imagine Backend People said we should just write adhoc Javascript for the frontend by concatinating the “correct” code in the backend.

    source

    • JackbyDev@programming.dev ⁨1⁩ ⁨year⁩ ago

      You should use parameterization, not concatenation.

      source
• koreth@lemm.ee ⁨1⁩ ⁨year⁩ ago

  SQL, where injection is still in the top 10 security risks

  This is absolutely true, but it’s not what it looks like on the surface, and if you dig into the OWASP entry for this, you’ll see they talk about mitigation.

  You can completely eliminate the possibility of injection attacks using well-understood technologies such as bind variables, which an ORM will usually use under the covers but which you can also use with your own queries. There are many, many database applications that have never once had a SQL injection vulnerability and never will.

  The reason SQL injection is a widespread security risk, to be blunt, is that there are astonishingly large numbers of inexperienced and/or low-skill developers out there who haven’t learned how to use the tools at their disposal. The techniques for avoiding injection vulnerability are simple and have been well-documented for literally decades but they can’t help if a lousy dev decides to ignore them.

  Now, a case could be made that it’d be better if instead, we were using a query language (maybe even a variant of SQL) that made injection attacks impossible. I agree in principle, but (a) I think this ends up being a lot harder than it looks if you want to maintain the same expressive power and flexibility SQL has, (b) given that SQL exists, “get bad devs to stop using SQL” doesn’t seem any more likely to succeed than “get bad devs to use bind variables,” and © I have too much faith in the ability of devs to introduce security vulnerabilities against all odds.

  source

  • frezik@midwest.social ⁨1⁩ ⁨year⁩ ago

    It’s also worth noting that the current entry on OWASP is injection generically. It includes SQL, but it also covers things like HTTP links where you concatenate in unchecked user input.

    I’m also going to put the blame squarely on PHP for SQL injection attacks hanging on for so long, particularly when combined with MySQL. That DB didn’t support bind variables for a long time. Other languages may have used a library that simulated bind variables for you. Barring that, they tended to always always always show how to use a sanitation function even if it was the first mention in the first tutorial. That creates a culture in the language of writing safe SQL code. Not PHP, though; the sanitation functions were there, but they never gave them the prominence that they so badly needed.

    source
• cwagner@lemmy.cwagner.me ⁨1⁩ ⁨year⁩ ago

  Maybe a hot take, but if SQL injection is still an issue, you have no business developing anything. This is a solved issue and had been for years.

  source

  • onlinepersona@programming.dev ⁨1⁩ ⁨year⁩ ago

    “If you don’t know what I know, you shouldn’t be here”

    “I don’t make mistakes that end up #3 on the vulnerability list, and if you do, I don’t want you around me”

    I find that a gate keeperish attitude.

    source

    • cwagner@lemmy.cwagner.me ⁨1⁩ ⁨year⁩ ago

      Alas, that’s not what I said

      source
    • Miaou@jlai.lu ⁨1⁩ ⁨year⁩ ago

      Gates exist for a reason

      source
  • boo@lemmy.one ⁨1⁩ ⁨year⁩ ago

    I have seen that this is still a problem, even in established enterprise companies.

    source

    • cwagner@lemmy.cwagner.me ⁨1⁩ ⁨year⁩ ago

      How? I never worked in a big company, but do they just have absolute beginners without any guidance writing code that’s then never checked?

      source

      • -> View More Comments
  • Anders429@programming.dev ⁨1⁩ ⁨year⁩ ago

    And yet, it still happens all the time.

    source
• Imacat@lemmy.dbzer0.com ⁨1⁩ ⁨year⁩ ago

  I’m absolutely biased as a data engineer who loves SQL, but there are some good reasons why SQL has been the de facto standard for interacting with databases since the 80s.

  One of its draws is that it’s easy to understand. I can show a stakeholder that I’m selecting “sum(sale_amount) from transactions where date=yesterday” and they understand it. Many analysts are even able to write complicated queries when they don’t know anything else about programming.

  Since it’s declarative, you rarely have to think about all the underlying fuckery that lets you query something like terabytes of data in redshift in minutes.

  Debugging is often pretty nice too. I can take some query that didn’t do what it was supposed to and run it over and over in a console until the output is right.

  source

  • lysdexic@programming.dev ⁨1⁩ ⁨year⁩ ago

    I’m absolutely biased as a data engineer who loves SQL, but there are some good reasons why SQL has been the de facto standard for interacting with databases since the 80s.

    I find it funny how the people who actually have to wrangle data swear by SQL as awesome, but there are always random hacks coming out of the woodwork, who don’t even look at SQL at all, with sweeping statements claiming SQL sucks because reasons.

    It’s like the most opinionated people against SQL are the ones who don’t use SQL.

    source
  • sip@programming.dev ⁨1⁩ ⁨year⁩ ago

    it’s just a drag to veer from. it’s not particularly good, but good enough to stick around.

    source
• helloben@programming.dev ⁨1⁩ ⁨year⁩ ago

  I have found that usually the problem isn’t SQL itself, it’s the design of the database or the application or both that makes it slow or hard to use because databases are tricky and it’s very easy to make them slow and difficult to optimize. This goes for any database, SQL or otherwise.

  I think the reason that you see so many people use relational databases for relational data is that they were designed for it and have been optimized to do that job for almost 50 years at this point, the right tool for the job if you will. That’s also why I don’t think any language builds it in, there isn’t one type of database that is good at every scenario so it would be difficult to pick a default as a language. Also keep in mind, most large applications are deployed across many servers so anything that uses a local file is out of the question.

  As far as performance directly though, I’m definitely not a DBA but I have spent a lot of time helping people troubleshoot slow databases and it seems many people write apps and design databases based only on how they want to store the data and not how the actual data is consumed. The other thing I think most folks don’t seem to realize (including myself for a long time) know the need for creating good indexes based on how you are accessing the data. It’s not an easy problem to solve for sure though.

  source

  • Semi-Hemi-Demigod@kbin.social ⁨1⁩ ⁨year⁩ ago

    Database optimization goes from the design of the schemas to the queries used to the configuration to the actual hardware it’s running on. It’s one of the hardest things to optimize in all of tech.

    source
• Kissaki@feddit.de ⁨1⁩ ⁨year⁩ ago

  most programmers aren’t DB experts and the SQL they output is quite often terrible.

  Isn’t that looking at it the wrong way / missing the point?

  If you’re fine with simple queries, use an ORM of your tech stack. Once you have to understand querying performance and weigh usage patterns, constraints, and cache performance and size, then it’s not about SQL anymore. It’s about understanding the DBMS you use.

  You may ask “why can’t I use a different language for the querying part of it”. But I haven’t seen anything better yet.

  Having a common, well-understood, established, documented, inter-product compatible language has a lot of value.

  Getting back to the original quote: I don’t think anything else would serve bad developers / non-experts any better.

  source

  • onlinepersona@programming.dev ⁨1⁩ ⁨year⁩ ago

    If you’re fine with simple queries, use an ORM of your tech stack. Once you have to understand querying performance and weigh usage patterns, constraints, and cache performance and size, then it’s not about SQL anymore. It’s about understanding the DBMS you use.

    Simple queries don’t result in simple SQL. How many joins and subqueries do you think an SQL query would require in order fulfill “Give me the top 10 artists of the 90s whose albums were nominated for the MTV awards but didn’t win”?

    In Django looks something like

    nineties = (date(1,1,1990), date(31, 12, 1999)
    album_range=Q(albums__release_date__range=nineties)
    artists = Artists.objects.annotate(
      albums_sold=Sum("albums__sales", filter=album_range)),
    ).filter(
      album_range,
      nominations__date__range=nineties,
      nominations__won=False
    ).order_by("-albums_sold")
    top_artists = artists[:10]
    

    What if one method wants the result of that but only wants the artists’ names, but another one wanted additional or other fields? In django you could simply use artists.only(*field_names) and each method would provide a different set of field names. What would that look like without a capable ORM? Do you think somebody would refactor the method to add a field_names argument? In my experience the result is a bunch of copy pasted queries that modify the query itself to add the fieldnames.

    Another common thing is querying related objects. Say you simply wanted to have information about the record label of the aforementioned artists while handling the artists. A many-to-one relationship (artist has one record label, record label has many artists). You could either artist.record_label while in your for-loop, but that would trigger an query for every artist (1+n problem). Or in django that’s artists.select_related(“record_label”) and it will get all the record_labels in the same query.
    If it’s a many-to-many relationship for example “festivals”, then .prefetch_related() will first select the artists, then make a second query of festivals of those artists, and artist.festivals would be available.

    An ORM like django makes that simple. SQL, does not.

    So, before we even get to the DB optimisation part (which indices to create, whether a view is better or now, which storage engine to use, WAL size, yadayadayada), there’s an entire interface / language that makes writing bad code very easy.

    source

    • BitSound@lemmy.world ⁨1⁩ ⁨year⁩ ago

      I’m too lazy to convert that by hand, but here’s what chatgpt converted that to for SQL, for the sake of discussion:

      SELECT 
          a.id,
          a.artist_name -- or whatever the name column is in the 'artists' table
      FROM artists a
      JOIN albums al ON a.id = al.artist_id
      JOIN nominations n ON al.id = n.album_id -- assuming nominations are for albums
      WHERE al.release_date BETWEEN '1990-01-01' AND '1999-12-31'
      AND n.award = 'MTV' -- assuming there's a column that specifies the award name
      AND n.won = FALSE
      GROUP BY a.id, a.artist_name -- or whatever the name column is in the 'artists' table
      ORDER BY COUNT(DISTINCT n.id) DESC, a.artist_name -- ordering by the number of nominations, then by artist name
      LIMIT 10;
      

      I like Django’s ORM just fine, but that SQL isn’t too bad (it’s also slightly different than your version though, but works fine as an example). I also like PyPika sometimes for building queries when I’m not using Django or SQLAlchemy, and here’s that version:

      q = (
          Query
          .from_(artists)
          .join(albums).on(artists.id == albums.artist_id)
          .join(nominations).on(albums.id == nominations.album_id)
          .select(artists.id, artists.artist_name)  # assuming the column is named artist_name
          .where(albums.release_date.between('1990-01-01', '1999-12-31'))
          .where(nominations.award == 'MTV')
          .where(nominations.won == False)
          .groupby(artists.id, artists.artist_name)
          .orderby(fn.Count(nominations.id).desc(), artists.artist_name)
          .limit(10)
      )
      

      I think PyPika answers your concerns about

      What if one method wants the result of that but only wants the artists’ names, but another one wanted additional or other fields?

      It’s just regular Python code, same as the Django ORM.

      source
• koreth@lemm.ee ⁨1⁩ ⁨year⁩ ago

  it would be great to “just” have a DB with a binary protocol that makes it unnecessary to write an ORM.

  Other people have talked about other parts of the post so I want to focus on this one.

  The problem an ORM solves is not a problem of SQL being textual. Just switching to a binary representation will have little or no impact on the need for an ORM. The ORM is solving the problem that’s in its name: bridging the conceptual gap between an object-oriented data model and a relational data model. “A relational data model” isn’t about how queries are represented in a wire protocol; instead, it is about how data, and relationships between pieces of data, are organized.

  So, okay, what if you get rid of the relational data model and make your database store objects directly? You can! NoSQL databases had a surge in popularity not too long ago, and before that, there have been lots of object databases.

  What you’re likely to discover in an application of any real complexity, though, and the reason the industry has cooled somewhat on NoSQL databases after the initial hype cycle, is that the relational model turns out to be popular for a reason: it is extremely useful, and some of its useful properties are awkward to express in terms of operations on objects. True, you can ditch the ORM, but often you end up introducing complex queries to do things that are simple in SQL and the net result is more complex and harder to maintain than when you started. (Note “often” here; sometimes non-relational databases are the best tool for the job.)

  And even in an object database, you still have to know what you’re doing! Storing objects instead of relational tuples won’t magically cause all your previously-slow queries to become lightning-fast. You will still need to think about data access patterns and indexes and caching and the rest. If the problem you’re trying to solve is “my queries are inefficient,” fixing the queries is a much better first step than ditching the entire database and starting over.

  source

  • ursakhiin@beehaw.org ⁨1⁩ ⁨year⁩ ago

    Even some of these issues ORMs are solving can be solved without them by caching a view of the data in structure of the object. Relational DBs are extremely well tuned for looking up and caching data in an easy to view manner.

    Somebody else pointed out the problem is bad devs not learning their tools. I’d go so far as to say DB knowledge can (and was due a while) be a specialized field full of skills that will fall by the wayside for most devs because we aren’t doing super complex things in a single DB anymore with the preference going toward microservices. There’s no need to flex those skills and they depreciate over time.

    source
• 601error@lemmy.ca ⁨1⁩ ⁨year⁩ ago

  I am both a (T-)SQL expert and a language design enthusiast. IMO, SQL the language is mediocre in its grammar and extremely resistant to cleanliness. Once you get past that, the things you can actually do with it are extremely useful.

  I’d love for a better syntax to exist, but it’s a Herculean task to make one. Modern SQL dialects have gargantuan, labyrinthine grammars, and they grow with each new product version. It’s a lot easier to keep adding to that than to build a feature-complete replacement. This is also the reason why most ORMs are so frustratingly limiting: it’s too much work to support the advanced features of one SQL dialect, let alone multiple.

  source
• glue_snorter@lemmy.sdfeu.org ⁨1⁩ ⁨year⁩ ago

  the translation step from binary (program) -> text (SQL) -> binary (server)

  Your concern about this is misguided. Inter-process communication always has to cross a barrier, by definition.

  I take it http also feels wrong to you?

  Binary protocols do exist, e.g. gRPC, but they incur costs of their own.

  source

  • onlinepersona@programming.dev ⁨1⁩ ⁨year⁩ ago

    the translation step from binary (program) -> text (SQL) -> binary (server)

    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?

    Does this not answer your question?

    source
• BlackXanthus@lemmy.world ⁨1⁩ ⁨year⁩ ago

  I don’t know if there is, but it feels like the email protocol problem.

  Like, while the protocol sucks in many, many ways, it would take something revolutionary to replace it because it’s everywhere.

  It’s been around so long that everything talks the protocol, the binaries that handle it are mature and stable.

  Then you have to ask: what would you replace it with? It does the job it’s designed to do very well. There’s nothing the matter with the protocol, and it’s still fit-for-purpose.

  That doesn’t mean there aren’t problems - spam, bad actors, and so on, but ultimately that’s not the fault of the protocol (though, maybe, for email, people have been arguing about protocol-level ways of dealing with spam for years).

  I don’t have an answer, but I feel like there should be one, but I doubt the is.

  source

  • cerement@slrpnk.net ⁨1⁩ ⁨year⁩ ago

    (have heard a similar argument about Python – there’s no killer app and it’s not the best, but it’s good enough and just keeps going)

    source
  • recursive_recursion@programming.dev ⁨1⁩ ⁨year⁩ ago

    Then you have to ask: what would you replace it with? It does the job it’s designed to do very well. There’s nothing the matter with the protocol, and it’s still fit-for-purpose.

    Rust once it’s matured I’d guess

    source

    • lnsfw3@lemmynsfw.com ⁨1⁩ ⁨year⁩ ago

      We’re replacing protocols with Rust now?

      source

      • -> View More Comments
• normalexit@lemmy.world ⁨1⁩ ⁨year⁩ ago

  SQL is a great tool to have in your toolbox. From creating adhoc reports to building any kind of backend, it always seems to come in handy.

  There are obviously non relational databases available and other approaches to persisting information, but there are some desirable characteristics of traditional relational databases.

  Oftentimes I find if I’m struggling it is because of a missing abstraction. Views, functions, etc. are available to extract higher level ideas and remove duplication.

  source

  • sip@programming.dev ⁨1⁩ ⁨year⁩ ago

    I think you missed the point. OP is asking for an alternative to communicate with a relational DB.

    source

    • onlinepersona@programming.dev ⁨1⁩ ⁨year⁩ ago

      Yes, exactly. You got it.

      source
• dudeami0@lemmy.dudeami.win ⁨1⁩ ⁨year⁩ ago

  SQL is the industry standard for a reason, it’s well known and it does the job quite well. The important part of any technology is to use it when it’s advantageous, not to use it for everything. SQL works great for looking up relational data, but isn’t a replacement for a filesystem. I’ll try to address each concern separately, and this is only my opinion and not some consensus:

  Most programmers aren’t DB experts: Most programmers aren’t “experts”, period, so we need to work with this. IT is a wide and varied field that requires a vast depth of knowledge in specific domains to be an “expert” in just that domain. This is why teams break up responsibilities, the fact the community came in and fixed the issues doesn’t change the fact the program did work before. This is all normal in development, you get things working in an acceptable manner and when the requirements change (in the lemmy example, this would be scaling requirements) you fix those problems.

  translation step from binary (program): If you are using SQL to store binary data, this might cause performance issues. SQL isn’t an all in one data store, it’s a database for running queries against relational data. I would say this is an architecture problem, as there are better methods for storing and distributing binary blobs of data. If you are talking about parsing strings, string parsing is probably one of the least demanding parts of a SQL query. Prepared statements can also be used to separate the query logic from the data and alleviate the SQL injection attack vector.

  Yes, there are ORMs: And you’ll see a ton of developers despise ORMs. They is an additional layer of abstraction that can either help or hinder depending on the application. Sure, they make things real easy but they can also cause many of the problems you are mentioning, like performance bottlenecks. Query builders can also be used to create SQL queries in a manner similar to an ORM if writing plain string-based queries isn’t ideal.

  source
• Olap@lemmy.world ⁨1⁩ ⁨year⁩ ago

  ElasticSearch tried. It’s without a doubt one of the worst query languages to use. Nobody can remember how to write the queries, or even reading the aggregations. You literally need the docs open on another monitor.

  A solid No from me. But I also never got the ORM hate, ActiveRecord on Ruby made it devine imo, sequelize on JS is fine, Diesel in rust is good

  source

  • Zikeji@programming.dev ⁨1⁩ ⁨year⁩ ago

    ORM with an easy and straightforward way to execute custom queries is the sweet spot to me. Let the ORM handle the basics (and migrations), override / add optimized queries where relevant.

    source
• atheken@programming.dev ⁨1⁩ ⁨year⁩ ago

  I used to be full on the ORM train. Now I’m a little less enthusiastic. What I actually think people need most of the time is something closer to ActiveRecord. Something that can easily map a result set into a collection of typed objects. You still generally write parameterized SQL, but the work of translating a db decimal into the correct target type on a record object in your language is handled for you. In .net, Dapper is a good example.

  I also think most people overemphasize or talk about how other programmers “suck at SQL” waaayy too much.

  IMO, for most situations, these are the few high-level things that devs should be vigilant about:

  • parameterize all sql.
  • consider the big-o of the lookups/writes (sometimes an app join or pulling a larger set and filtering in memory is better than crafting very complex projections in sql). This is a little harder to analyze with an ORM, but not by much if you keep the mappings simple and understand the loading semantics of the ORM.
  • understand the index coverage of queries and model table keys properly to maintain insert performance (monotonically increasing keys).
  • stop fixating on optimizing queries that run in a few seconds, a few times a day. Optimize the stuff that you run on every transaction - if you need to.

  On most of those points, if you don’t have aggregate query counts/metrics on query performance on your clusters, starting to get cute with complex queries is flying blind, and there’s no way to prioritize what to optimize.

  For the vast majority of cases, simple, obvious selects that don’t involve special db features are going to do the job for most applications. When the database becomes a bottleneck, there are usually most more effective ways to handle them than to try to hand optimize all the queries.

  source

  • r1veRRR@feddit.de ⁨1⁩ ⁨year⁩ ago

    Without a DSL for writing SQL, any sufficiently complex program will end up with string concatinating all over the place. Basically, writing a language with ZERO checks or highlighting or anything. That’s asking for trouble.

    But coming from Java, I agree that some ORMs go way too far.

    source

    • atheken@programming.dev ⁨1⁩ ⁨year⁩ ago

      It’s necessarily complexity that is easily encapsulated in methods.

      If those methods are under test to verify their behavior, trivial typos can be detected instantly, without adding another dialect and more conceptual overhead to a project.

      If those methods are not under test, then there’s a tiny bit of help by using a DSL that can be compile-time checked.

      source
• r1veRRR@feddit.de ⁨1⁩ ⁨year⁩ ago

  I’d recommend everyone check out prql-lang.org. It’s SQL, but readable and writable in a sane way.

  And no, SQL is NOT readable or writable for anything involving more than a single join.

  source

  • BitSound@lemmy.world ⁨1⁩ ⁨year⁩ ago

    I’m pretty excited about PRQL. If anything has a shot at replacing SQL, it’s something like this (taken from their FAQ):

    PRQL is open. It’s not designed for a specific database. PRQL will always be fully open-source. There will never be a commercial product.

    source
  • Sigmatics@lemmy.ca ⁨1⁩ ⁨year⁩ ago

    The main advantage being more concise syntax?

    source
  • BehindTheBarrier@programming.dev ⁨1⁩ ⁨year⁩ ago

    I’m not too much of a fan of the SQL equivalent of SELECT not being at the top. Granted I’m fairly sure there are some arguments for it, since select is optional there’s a higher mental load trying to figure out what is actually being returned imo. At least from looking at for the first time.

    On the other hand, i’d kill for f-strings, the top N in group (which is nigh unreadable in SQL), and null handling that doesn’t require me to write either COALSECE or NVL too often. The joins were a little less pretty though, I’m quite fond of normal SQL joins since they are very reasonable unless chained beyond the line count your screen can show.

    source
  • luckystarr@feddit.de ⁨1⁩ ⁨year⁩ ago

    You can write selects with many joins, as long they are regular and either add a column or reduce the result set. You have to write the joins explicitly though. Just shoving all of the restrictions into the where clause will definitely confuse everybody.

    source
• CameronDev@programming.dev ⁨1⁩ ⁨year⁩ ago

  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.

  source

  • onlinepersona@programming.dev ⁨1⁩ ⁨year⁩ ago

    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.

    source

    • CameronDev@programming.dev ⁨1⁩ ⁨year⁩ ago

      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?

      source
• usernamesaredifficul@hexbear.net ⁨1⁩ ⁨year⁩ ago

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

  source

  • onlinepersona@programming.dev ⁨1⁩ ⁨year⁩ ago

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

    source
• fubo@lemmy.world ⁨1⁩ ⁨year⁩ ago

  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.

  source

  • bill_1992@lemmy.world ⁨1⁩ ⁨year⁩ ago

    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.

    source

    • koreth@lemm.ee ⁨1⁩ ⁨year⁩ ago

      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.

      source
• makr_alland@lemmy.world ⁨1⁩ ⁨year⁩ ago

  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.

  source

  • onlinepersona@programming.dev ⁨1⁩ ⁨year⁩ ago

    Thanks, checking those out now.

    source
  • glue_snorter@lemmy.sdfeu.org ⁨1⁩ ⁨year⁩ ago

    Shame I had to scroll this far to find PRQL

    source
• KrokanteBamischijf@feddit.nl ⁨1⁩ ⁨year⁩ ago

  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.

  source
• bill_1992@lemmy.world ⁨1⁩ ⁨year⁩ ago

  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, but it’s been “solved” as much as it possibly can.

  source
• hperrin@lemmy.world ⁨1⁩ ⁨year⁩ ago

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

  source