• xmunk@sh.itjust.works
    link
    fedilink
    arrow-up
    179
    arrow-down
    3
    ·
    9 months ago

    Speaking as a Senior Dev specialized in database access and design… you don’t have to use all caps - SQL is actually case agnostic.

    But… but my fucking eyes man. I’m old, if your branch doesn’t have control keywords in all caps I’m going to take it out back and ol’ yeller it.

    There are few hills I’ll die on but all caps SQL and singular table names are two of them.

    • Nolegjoe@lemmy.world
      link
      fedilink
      arrow-up
      73
      arrow-down
      2
      ·
      9 months ago

      I’m a sql developer, and I am completely the opposite to you. I will find it incredibly difficult to read when everything is in caps

      • SorteKanin@feddit.dk
        link
        fedilink
        arrow-up
        26
        arrow-down
        1
        ·
        9 months ago

        Same, I prefer lower case. Every other language has keywords in lower case, why do you need to shout when writing sql?

        • hikaru755@feddit.de
          link
          fedilink
          arrow-up
          24
          arrow-down
          3
          ·
          edit-2
          9 months ago

          I understand it as an attempt to get very basic, manual syntax highlighting. If all you have is white text on black background, then I do see the value of making keywords easy to spot by putting them in all caps. And this probably made sense back when SQL was first developed, but it’s 2023, any dev / data scientist not using a tool that gives you syntax highlighting seriously needs to get with the times

          • xmunk@sh.itjust.works
            link
            fedilink
            arrow-up
            13
            arrow-down
            1
            ·
            edit-2
            9 months ago

            Partially, yes. I personally use an IDE with excellent syntax highlighting and those have been around for at least two decades. You are, however, often transplanting your SQL between a variety of environments and in some of those syntax highlighting is unavailable (for me at least) - the all caps does help in those rare situations.

            More importantly though it helps clearly differentiate between those control keywords (which are universal) and data labels (which are specific to your business domain). If I’m consulting on a complex system that I only partially understand it’s extremely helpful to be able to quickly identify data labels that I’m unfamiliar with to research.

          • Stumblinbear@pawb.social
            link
            fedilink
            arrow-up
            9
            arrow-down
            1
            ·
            edit-2
            9 months ago

            Please tell me what IDE you’re using that’s capable of highlighting SQL syntax that’s embedded inside another language source file

            Also please fucking stop with the “it’s current year stop x.” The year is not an argument.

            • hedgehog@ttrpg.network
              link
              fedilink
              arrow-up
              4
              ·
              9 months ago

              JetBrains IDEs - IntelliJ, WebStorm, PyCharm, GoLand, etc., all support highlighting SQL embedded in another source file or even inside markup files like YAML. Does your IDE not support this?

            • hikaru755@feddit.de
              link
              fedilink
              arrow-up
              2
              ·
              9 months ago

              As the other commenter said, the Jetbrains IDEs do this perfectly fine. Although I’d also argue that if you’re working with SQL from within another language already, a DSL wrapper is probably gonna be the better way to go about this.

              • Stumblinbear@pawb.social
                link
                fedilink
                arrow-up
                2
                ·
                edit-2
                9 months ago

                Unfortunately RustRover is still garbage for actual usage. And I refuse to use an ORM when I can just write the SQL in a more common syntax that everyone understands across every language instead of whatever inefficient library-of-the-week there is. Raw SQL is fine and can be significantly more performant. Don’t be scared.

                • hikaru755@feddit.de
                  link
                  fedilink
                  arrow-up
                  2
                  ·
                  9 months ago

                  I’m not talking full blown ORM here, not a fan of those either. I’m talking about some light weight wrapper that basically just assembles SQL statements for you, while giving you just a little more type safety and automatic protection against SQL injection, and not sacrificing any performance. I’m coming from the JVM world, where Jooq and Exposed are examples of that kind of thing.

          • Bonehead@kbin.social
            link
            fedilink
            arrow-up
            10
            arrow-down
            2
            ·
            edit-2
            9 months ago

            it’s 2023, any dev / data scientist not using a tool that gives you syntax highlighting seriously needs to get with the times

            You say that as if AS400 systems with only console access don’t exist anymore.

            • hikaru755@feddit.de
              link
              fedilink
              arrow-up
              3
              arrow-down
              1
              ·
              9 months ago

              Well then use all-caps keywords whenever working on those systems, I don’t care. But an edge case like that shouldn’t dictate the default for everyone else who doesn’t have to work on that, that’s all I’m saying.

              • Bonehead@kbin.social
                link
                fedilink
                arrow-up
                5
                arrow-down
                1
                ·
                9 months ago

                There are several cases where you’ll be limited to console only, or log files, or many many other situations. Good coding practices just makes life easier all around.

          • jaybone@lemmy.world
            link
            fedilink
            arrow-up
            3
            ·
            9 months ago

            Also some people are color blind.

            Also you might need to ssh in somewhere and vi some code or tail a log file where you don’t have color support.

            • hikaru755@feddit.de
              link
              fedilink
              arrow-up
              2
              arrow-down
              1
              ·
              9 months ago

              My ide isn’t limited to color when it comes to highlighting, so being color blind generally shouldn’t be a problem. Set keywords to underlined, bold, italic, whatever works for you.

              Your other examples I can see, but at least at my work those are rare edge cases, and I’d rather optimize for the brunt of the work than for those. Of course at other places those might be much more of a concern.

          • xmunk@sh.itjust.works
            link
            fedilink
            arrow-up
            3
            ·
            9 months ago

            Yea - you want the structure in a recognizable form so that you can quickly confirm code patterns for comprehension.

      • xmunk@sh.itjust.works
        link
        fedilink
        arrow-up
        18
        ·
        edit-2
        9 months ago

        Sorry, to clarify, not everything is in all caps. I’ll append my prefered syntax below

        WITH foo AS (
            SELECT id, baz.binid
            FROM
                    bar
                JOIN baz
                    ON bar.id = baz.barid
        )
        SELECT bin.name, bin.id AS binid
        FROM
                foo
            JOIN bin
                foo.binid = bin.id
        

        The above is some dirt simple SQL, when you get into report construction things get very complicated and it pays off to make sure the simple stuff is expressive.

          • xmunk@sh.itjust.works
            link
            fedilink
            arrow-up
            9
            ·
            9 months ago

            I’ve seen both approaches and I think they’re both quite reasonable. An indented join is my preference since it makes sub queries more logically indented… but our coding standards allow either approach. We’ve even got a few people that like

            FROM foo
            JOIN bar ON foo.id = bar.fooid
            JOIN baz ON bar.id = baz.barid
            
        • Cold_Brew_Enema@lemmy.world
          link
          fedilink
          arrow-up
          1
          ·
          9 months ago

          Um you forgot the semicolon before with assuming there isn’t one in the previous statement. Syntax error. Code review failed

          • xmunk@sh.itjust.works
            link
            fedilink
            arrow-up
            5
            ·
            9 months ago

            There’s no way we’re running in multi statement mode… I like my prepared queries, thank you very much.

      • Steeve@lemmy.ca
        link
        fedilink
        English
        arrow-up
        3
        arrow-down
        1
        ·
        9 months ago

        I’m at a Data Engineer and I alternative caps lock and non caps lock at random

      • idunnololz@lemmy.world
        link
        fedilink
        arrow-up
        2
        arrow-down
        1
        ·
        edit-2
        9 months ago

        I believe this has been proven. It’s because capital letters all have the same shape whereas lower case letters do not. So your brain can take shortcuts to reading lower case but cannot with upper case.

        Also most if not all editors will highlight SQL keywords so it’s probably not too hard to discern SQL commands and everything else in modern day.

    • erogenouswarzone@lemmy.ml
      link
      fedilink
      English
      arrow-up
      19
      arrow-down
      1
      ·
      9 months ago

      The place I work decided to name all tables in all caps. So now every day I have to decide if I want to be consistent or I want to have an easy life.

      • xmunk@sh.itjust.works
        link
        fedilink
        arrow-up
        11
        ·
        9 months ago

        Fuuuuck. That’s why I love postgres… and fuck anyone that requires double quoted identifiers for special casing.

        • icydefiance@lemm.ee
          link
          fedilink
          arrow-up
          6
          ·
          edit-2
          9 months ago

          Postgres normalizes table and field names to lowercase, unless you put them in quotes. It’s also case sensitive.

          That means if you use quotes and capital letters when creating the table, then it’s impossible to refer to that table without using quotes.

          It also means if you rename the table later to be all lowercase, then all your existing code will break.

          Still a much better database than MySQL though.

          • xmunk@sh.itjust.works
            link
            fedilink
            arrow-up
            2
            ·
            9 months ago

            I’m quite aware… basically it means that novice devs can create a table in camelCase and query in camelCase… but you can clean it all up as long as they didn’t realize you needed double quotes.

            • icydefiance@lemm.ee
              link
              fedilink
              arrow-up
              2
              ·
              edit-2
              9 months ago

              Fair point. I always disliked the design because ORMs pretty much always use quotes, so an entity-first approach can create a lot of tables with capital letters if you’re not careful, which is then really annoying if you need to use raw SQL for anything.

      • xmunk@sh.itjust.works
        link
        fedilink
        arrow-up
        15
        ·
        9 months ago

        It’s an English literacy thing - we have several non-native English speakers and using only singular avoids making those folks’ lives harder. Besides it’s really nice to autopilot that categoryid is a foreign key to the category table. It also simplifies always plural words… I haven’t yet written CREATE TABLE pants but if I ever do there’s zero chance of me creating a pantid.

          • xmunk@sh.itjust.works
            link
            fedilink
            arrow-up
            4
            ·
            9 months ago

            I tend to use underscores on join tables so table foo_bar would have a fooid and a barid. I have somewhat soured on this approach though since there are a lot of situations where you’ll have two m-m relationships between the same two tables with a different meaning… and having a fixed formula for m-m tables can make things ugly.

            If I get to design another greenfield database I’ll probably prefer using underscores for word boundaries in long table names.

        • Faresh@lemmy.ml
          link
          fedilink
          English
          arrow-up
          4
          ·
          9 months ago

          I always thought they should be singular to be closer to the names we give entities and relations in a entity-relation diagram.

      • Skyrmir@lemmy.world
        link
        fedilink
        arrow-up
        8
        ·
        9 months ago

        Look at you with your color vision being all elitist. Some of us old bastards don’t see them pretty rainbows so much any more.

  • umbraroze@kbin.social
    link
    fedilink
    arrow-up
    158
    arrow-down
    2
    ·
    9 months ago

    Technically, SQL is case-insensitive.

    Practically, you want to capitalise the commands anyway.

    It gives your code some gravitas. Always remember that when you’re writing SQL statements you’re speaking Ancient Words of Power.

    Does that JavaScript framework that got invented 2 weeks ago by some snot-nosed kid need Words of Power? No. Does the database that has been chugging on for decades upon decades need Words of Power? Yes. Words of Power and all the due respect.

  • rwhitisissle@lemmy.ml
    link
    fedilink
    arrow-up
    80
    arrow-down
    31
    ·
    edit-2
    9 months ago

    The phrase “SQL programmers” is so fucking weird. SQL isn’t a programming language. It’s a query language. You don’t “program” things with SQL. You utilize SQL as a component of programs for data insertion and lookup, but the actual logic of execution is done in a programming language. Unless you’re doing Oracle PL/SQL, in which case why are you giving money to Oracle?

    Edit: Damn, this comment made people mad.

    • oce 🐆@jlai.lu
      link
      fedilink
      arrow-up
      41
      ·
      9 months ago

      Your knowledge of data engineering may be limited. SQL is predominant in data processing nowadays. FOSS tools such as DBT allows to write efficient data processing pipelines with SQL and some YAML config without the need for a general purpose coding language.
      Why would anyone want that? Because SQL has the interesting property of describing the result you want rather than describing how to compute it. So you can put inside the database, a query engine with decades of optimizations, that will make a much better job at finding the best execution plan than the average developer.
      It also means it’s easier to train people for data processing nowadays.

      • NOT_RICK@lemmy.world
        link
        fedilink
        English
        arrow-up
        3
        ·
        9 months ago

        Learning DBT was pretty easy for me as a data analyst. Now I’m contributing to my company’s data warehouse instead of just pulling existing data.

    • KarmaPolice@lemmy.world
      link
      fedilink
      arrow-up
      38
      ·
      9 months ago

      Most database engines support stored procedures. You don’t need to give money to oracle, you can give it to Microsoft instead.

    • SomeNewThing@reddthat.com
      link
      fedilink
      arrow-up
      34
      ·
      edit-2
      9 months ago

      T-SQL is turing complete. While the MS SQL server has limitations on OS level operations, if you allow yourself some leeway with CLR wrappers for the win32 API, there’s no reason I can think of you wouldn’t be able to get the database engine to be a webserver reacting to incoming requests on port 80, or drawing GUIs based off of table state.

      It’s be slow and terrible, but doable.

    • fmstrat@lemmy.nowsci.com
      link
      fedilink
      English
      arrow-up
      30
      ·
      9 months ago

      This doesn’t make sense to me. SPs and functions are in every major database. If I wrote a bash script that runs like a program, and sounds like a program, did I program it? Script it?

      And lots of systems have nested logic in the DB, optimization often leads to that to reduce overhead. Unless you’re being lazy with an ORM like prisma that can’t even join properly.

      Getting high performing queries is just as difficult as any other programming language, and should be treated as such. Even Lemmy’s huge performance increases to .18ish came from big PG optimizations.

      • mbp@lemmy.sdf.org
        link
        fedilink
        English
        arrow-up
        11
        ·
        9 months ago

        It seems to be about yelling at others that “you’re not a real programmer!!!” mixed with being so “technically correct” my eyes can no longer roll the same way they used to.

      • Random_user@lemmy.world
        link
        fedilink
        English
        arrow-up
        4
        ·
        9 months ago

        It’s mostly ignorance. People tend to underestimate or dismiss things they don’t completely understand.

      • rwhitisissle@lemmy.ml
        link
        fedilink
        arrow-up
        2
        arrow-down
        2
        ·
        9 months ago

        Admittedly, this discussion is more one of semantics than anything. It’s pretty clear I’m arguing that SQL is not a “General Purpose Language,” and that proficiency in that domain is what constitutes programming. Which, yeah, is arguably somewhat arbitrary. But my point is that, colloquially, someone who only works with SQL isn’t a programmer. Data Engineer, sure. DBA. Also, sure. Depends on what you do. Programmer? Not really. Not unless you (as in the person, not “it’s theoretically possible”) can use raw SQL to read in video data from a linux system device file and then encode it to mp4 and just nobody’s told me.

        • fmstrat@lemmy.nowsci.com
          link
          fedilink
          English
          arrow-up
          3
          ·
          9 months ago

          Do that in Javascript. Or HTML. Or CSS. Or by that logic is a web developer not a programmer? What about microcontroller programmers?

          I could easily write a full logic program in SQL where the API just feeds it data, which is the inverse of how you treat SQL. Admittedly that’s not as common, but it happens pretty frequently in areas of big data, like medical.

          I’ve hired Senior Software Engineers that were DBAs, and others that weren’t. They were a development team, all programmers in their own right.

      • AVincentInSpace@pawb.social
        link
        fedilink
        arrow-up
        2
        ·
        9 months ago

        LaTeX being called “programming” I can see, but I’ve never heard someone try to justify Markdown as programming. It’s just formalizing things people were already doing to format text in plain text files into approximately half of a standard.

      • rwhitisissle@lemmy.ml
        link
        fedilink
        arrow-up
        3
        arrow-down
        2
        ·
        9 months ago

        So is Tex. And, yet, I still don’t put it under the “programming languages I know” section on my resume. Probably because it’s not a programming language.

          • rwhitisissle@lemmy.ml
            link
            fedilink
            arrow-up
            2
            arrow-down
            2
            ·
            9 months ago

            It’s important to keep an up to date resume, even if you’re employed. That’s a little life pro tip for you kids out there, with your iphones and your tik toks and your Fortnite dances and your existential malaise brought about by encroaching climate disaster and advanced technoindustrial capitalism.

        • corship@feddit.de
          link
          fedilink
          arrow-up
          1
          ·
          edit-2
          9 months ago

          Where you put it is not my problem.

          The general census is that latex actually is an example of programming languages sharing semantics with non programming languages and not being intend as a programming language.

          since you linked to wikipedia:

          The domain of the language is also worth consideration. Markup languages like XML, HTML, or troff, which define structured data, are not usually considered programming languages.[12][13][14] Programming languages may, however, share the syntax with markup languages if a computational semantics is defined. XSLT, for example, is a Turing complete language entirely using XML syntax.[15][16][17] Moreover, LaTeX, which is mostly used for structuring documents, also contains a Turing complete subset.[18][19]

          Programming language

          Sometimes even non Turing complete languages are considered a programming language but Turing completeness usually is the criteria agreed upon:

          The majority of practical programming languages are Turing complete,[5] and all Turing complete languages can implement the same set of algorithms. ANSI/ISO SQL-92 and Charity are examples of languages that are not Turing complete, yet are often called programming languages.[6][7] However, some authors restrict the term “programming language” to Turing complete languages.[1][8]

    • dan@upvote.au
      link
      fedilink
      arrow-up
      22
      arrow-down
      1
      ·
      edit-2
      9 months ago

      You don’t “program” things with SQL

      Why not? It sounds like you haven’t written any OLAP queries :)

      I’ve written ETL data pipelines using a system similar to Apache Airflow, where most of the logic is in SQL (either Presto or Apache Spark) with small pieces of Python to glue things together. Queries that are thousands of lines long that take ~30 minutes to run and do all sorts of transformations to the data. They run once per day, overnight. I’d definitely call that programming.

      Most database systems support stored procedures, which are just like functions - you give them some input and they give you some output and/or perform some side effects.

      • BurnerPhone867@lemmy.ml
        link
        fedilink
        arrow-up
        4
        arrow-down
        2
        ·
        9 months ago

        thousands of lines long that take ~30 minutes

        Oh yea!!! Well I have 76 lines of code that takes up to 18 hours to run for 1 client!!!

        /s

        • dan@upvote.au
          link
          fedilink
          arrow-up
          2
          ·
          9 months ago

          Haha I only mentioned the run time to provide some context, since a lot of people have only ran OLTP queries that take less than a few seconds to run.

    • Heavybell@lemmy.world
      link
      fedilink
      arrow-up
      7
      arrow-down
      2
      ·
      9 months ago

      MS SQL Server has this thing called Replication. It’s a feature to keep tables in sync between databases, and even database servers. There’s merge replication (two way), snapshot replication (one way scheduled publishing), and transaction replication (one way live-ish publishing).

      And the logic is all implemented in T-SQL stored procedures.

      I fucking hate it.

    • XTornado@lemmy.ml
      link
      fedilink
      arrow-up
      4
      ·
      9 months ago

      Me losing my mind in 3000 lines of Oracle PL/SQL processing scripts in a Bank some time ago agrees with your last statement.

  • Cold_Brew_Enema@lemmy.world
    link
    fedilink
    arrow-up
    40
    ·
    9 months ago

    As a senior query writer, I use caps for begin and end and some other commands, but all caps makes my head hurt. It’s like the sql is screaming at me. I think it’s more important to have good looking queries with proper indentation.

    • BurnerPhone867@lemmy.ml
      link
      fedilink
      arrow-up
      4
      ·
      9 months ago

      So much this. Caps for all the things? Hell no! Been a query guy for 19 years now.

      I don’t even use it for Begin and End. and only shift for camelCase names.

  • AlexWIWA@lemmy.ml
    link
    fedilink
    English
    arrow-up
    23
    arrow-down
    1
    ·
    9 months ago

    Writing SQL on Mac sucks so bad. I hate their caps lock time out

    • dan@upvote.au
      link
      fedilink
      arrow-up
      17
      ·
      9 months ago

      My favourite MySQL error is “MySQL server has gone away”, like MySQL decides “nah I don’t want to run this” and runs off.

      (it happens when no data is sent over the connection for a while, like if the query is taking too long to run and times out)

  • VantaBrandon@lemmy.world
    link
    fedilink
    arrow-up
    20
    ·
    9 months ago

    I know a Perl programmer who exclusively writes all SQL in pure lowercase with atypical indentation.

    He’s also a die-hard InfoWars follower, apparently his go to source, so… maybe there is a connection there.

  • Rev. Layle@lemm.ee
    link
    fedilink
    English
    arrow-up
    11
    ·
    9 months ago

    I never use all caps when I write SQL, not sure why people insist on it so much

    • dan@upvote.au
      link
      fedilink
      arrow-up
      8
      ·
      edit-2
      9 months ago

      I write it in lowercase but then the auto-formatter we use at work converts it to uppercase when I save the file.

      I love auto formatters. Prettier (the initial version for JS) really popularized the concept. If the coding style is automated where possible (things like tabs vs spaces, tab width, line wrap at 80/100/120 characters, where to put line breaks in long statements, etc), it ensures the entire codebase is consistent, and I can jump between different code bases with different coding styles without having to think too much about it.

    • dylanTheDeveloper@lemmy.world
      link
      fedilink
      arrow-up
      2
      ·
      edit-2
      9 months ago

      I was taught it when working with Oracle SQL (their certification was not cheap and the exam also not easy)

      Personally do what’s easiest if it’s your own database

    • alci@jlai.lu
      link
      fedilink
      arrow-up
      2
      ·
      9 months ago

      Was an Oracle thing. Postgresql is rather on the lowercase side of things…

      • Rev. Layle@lemm.ee
        link
        fedilink
        English
        arrow-up
        3
        ·
        9 months ago

        I dunno, I don’t need it to be important, I need it to work and be readable and, to me, all caps is unreadable

  • Album@lemmy.ca
    link
    fedilink
    arrow-up
    11
    ·
    9 months ago

    If you guys think that’s cool you should see what I can do with a double negative.

        • systemglitch@lemmy.world
          link
          fedilink
          arrow-up
          4
          ·
          9 months ago

          Yes and no. Correct English does not use a double negative, but when it is used it can either reinforce or convey the opposite… which is why we don’t use double negatives, it’s confusing and language should be clear and precise when used properly.

        • Album@lemmy.ca
          link
          fedilink
          arrow-up
          2
          ·
          9 months ago

          In english a double negative is a positive, and is syntactically incorrect.

          • Makanar56@sh.itjust.works
            link
            fedilink
            arrow-up
            1
            ·
            9 months ago

            What if someone says “no no” or “no dont”. That conveys a negative response despite being two negatives. Granted these aren’t proper English or full sentences, but it’s something you might say

            • treesquid@lemmy.world
              link
              fedilink
              arrow-up
              1
              ·
              9 months ago

              “No, don’t [do that]” is not a double negative, it’s just two independent negatives in a row, they don’t affect each other. A double negative would be like “don’t not do that” which will mean “do not fail to do that” to most people.

  • tentaclius@lemm.ee
    link
    fedilink
    arrow-up
    10
    ·
    9 months ago

    Nobody knows (/s) but you don’t have to write keywords in upper case (for most DB’s default settings anyway).