7 ms·
Treating SQL like code
- tacone 5y agoDoes anybody know a good SQL formatter and linter?
- rbanffy 5y agoDoes anyone treat SQL as anything other than code?
- piyh 5y agoYes, because way more people write SQL than write a "proper" programming language.
- thenaturalist 5y agoYes, it does exist. In the domain of "Drag & Drop" ETL tools (looking at you, Matillion, Talend, Alteryx etc.), I've seen horrible setups. No version control, no centralized overview of queries, dependencies, code linting or auto-completion etc. Normally these tools are chosen by unexperienced, rather non-technical data or business people, to whom the initial ease of development is highly appealing. Of course compelxity quickly spirals out of control as LoC across all kinds of different transformation steps increase.
- cm2187 5y agoWhat I don't get is that most of these tools have their own steep learning curve. You have massive menus with dozens of actions, and your workflow look like complex electrical diagrams. It will put off the least technical people as well as SQL. So in the end you end up with really motivated, kind-of-geeky business users who might as well learn SQL and perhaps a little bit of python for the same effort. I would love to get every office users to do more with their computer than copy-pasting all day, but I came to belive that the problem is mentality, not tooling.
- fragmede 5y agoIt might be hard to believe, but statisticians and data scientists can be really good at their job while being bad at traditional programming that would lend themselves to learning Python and SQL. I've seen some do transforms on data that are beyond my abilities, while also having difficulty writing functions. If the abstractions in their tools make more sense to them, who are we to question them? Learning SQL is a non-trivial investment, and while it may seem like being an, eg, expert user of Alteryx (and nothing else) may be a career limiting move, there are also tons of programmers who only know Java, don't want to learn anything else, and still have really good careers. I'm sure there's the same for an expert Alteryx user.
- cm2187 5y agoI agree but you are starting with a user who is already an Alteryx expert. I am starting with users who are expert at nothing and are wasting time with manual work. What I say is that those users will either learn nothing, or if they do, they might as well learn SQL rather than learn to use a complicated, no-code software like Alteryx for the same effort than SQL.
- hermitcrab 5y agoSQL seems pretty straightforward to people who done a fair bit of coding. Totally opaque (possibly terrifyingly so) to people with no coding experience. There is a reason why no-code data wrangling tools are a multi-billion dollar industry.
- usrbinbash 5y agoAm I missing something here? SQL is code. So what else would I treat it like?
- nsxwolf 5y agoThey mean version control here. I've worked at places where the schemas and stored procedures were not tracked anywhere - only the latest version existed in the database itself. Change control was a disaster. I've worked at places that threw scripts into a folder somewhere in a project, so that it would at least have history. Again, change control was a disaster. If you're not using a change control tool of some sort specifically for databases, and using it and only it to manage all changes, it's going to be a disaster.
- usrbinbash 5y agoOf course, but this also applies to all code. I still remember the time when it was deemed normal, or at least common, to email around diffs and patches in a dev team, or upload everything to the "code server" in little directories with iso-timestamps as names. Not taking care of code properly, aka. in a VCS, is a disaster, no matter if its C, SQL or utility bash-scripts.
- deleted 5y ago[deleted]
- davismwfl 5y agoI've consulted at so many places that just wrote stored procedures/functions and put them in the DB but never put those in source control and managed them like code. Can't tell you how many times they'd split the SQL box onto two separate machines to support different clients and growth and then forget to update one copy of procs on one of the machines causing all sorts of hell. I found it a lot in small to medium sized business which were transitioning from the couple of people who wrote all their software to a team needing to do things properly and consistently. But I also found it at 50 person dev teams, which was weird and the worst. I've generally always managed it as code, just easier that way.
- mettamage 5y agoIt happened with me as well. My previous employer created stored procedures in a MSSQL database and called it a day.
- MikeTV 5y agoMy experience also. This is particularly bad in the SQL Server space, since Microsoft essentially dropped source control integration in SQL Server Management Studio 2016 and later (there's an official workaround[0], but it's not well received). Most likely they're happy to offload version control to add-ins. There are a few, but most of them are way out of budget for small businesses or small IT departments. Which means version control usually doesn't happen. This has been such a problem for me with clients that I started a side project[1] to try to help fill the gap. If anything it's shown me that the problem is more widespread than I originally thought. [0] https://cloudblogs.microsoft.com/sqlserver/2016/11/21/source-control-in-sql-server-management-studio-ssms/ https://cloudblogs.microsoft.com/sqlserver/2016/11/21/source... [1] https://www.versionsql.com/ https://www.versionsql.com/
- fatnoah 5y agoIn the past, I used the SQL project feature of Visual Studio to great success to manage the DB. Everything except data up/down migrations was well supported, and it enabled us to keep our SQL in source control. I never even knew there was direct integration with SQL server.
- mypastself 5y agoSo they push SQL scripts to a repository? Seems like a very basic and common approach. How do shops that don’t use it track database changes? Can’t remember the last time I was involved in a (non-legacy) project that did things differently.
- NovemberWhiskey 5y agoNot sure if article saying something too deep for me to comprehend or too trivial not to be obvious to me.
- thaumasiotes 5y agoIt's the second one.
- davidivadavid 5y ago"Because it is code?"
- sokoloff 5y agoExactly. “Why Google treats Javascript like code and you should too” would seem equally plausible as an article.
- newlisp 5y agoYes, it was that bad of an article.
- castlecrasher2 5y agoIt's obvious to me and I imagine most programmers, too. However, as a data engineer I've been at some companies that treat SQL differently and seen bad practices that I believe the article is addressing, like putting SQL statements as stored procedures or materialized views instead of in git.
- xupybd 5y agoI didn't understand the point about stored procedures. Why not have stored procedures? Yes, you treat it like code any changes to the stored procedure are scripted, stored in git and deployed with a data migration tool. But store procedures give you a single place to call SQL. If there is a procedure that does a standard calculation used by 5 reports then put it in a function or a stored procedure and have every report call that procedure / function.
- 5y ago
- kcbanner 5y ago"SQL is a query language. Why would Google treat it like code?" Because...it's a programming language, and therefore...it is code?
- tablespoon 5y ago> Because...it's a programming language, and therefore...it is code? It's also literally the classic example of a "fourth-generation programming language."
- FpUser 5y agoLast time I checked the sky is blue, and yes I do keep SQL in git
- leoh 5y agoGoogle and many other companies that use Looker do something else, too, which is that they store models in source control as opposed to SQL. Models describe the schema of the database, and things like calculations, measures, rollups — which are reusable and composable. https://docs.looker.com/data-modeling/learning-lookml/what-is-lookml https://docs.looker.com/data-modeling/learning-lookml/what-i...
- kristjansson 5y agoUsing models from your ORM of choice instead of the DDL they generate isn't that unreasonable. Suggesting that one should (or then could only?) use 'a simple API .. or UX' to actually query the database seems pretty objectionable though.
- corrius 5y agoIt locks you down to that ORM. If you are the owner of the ORM such as Google with Looker that is fine, otherwise it doesn't sounds like a good idea to me
- forinti 5y agoWe've built in-house a tool to move database objects (everything except tables) from development to testing to production. It even lets you pick a function or procedure from a package and only send that. At each step, the code gets commited to a git repository.
- zffr 5y agoI wish this article also covered how google manages sql like code. In my projects I have sometimes struggled with keeping my table definitions, constraints, functions, etc in sync with the SQL in my git repo
- krono 5y agoSearch for SQL or database as code and you'll find quite a few established SAAS offerings in this realm, with varying approaches and quite decent docs as well. Worth looking up I'd say.
- piyh 5y agoHaving done it the wrong way many times and a better way a few times, I can tell you that an ORM with migrations is a huge improvement.
- KronisLV 5y agoYep, something like Flyway or Liquibase in the Java world has always made a lot of sense, though you also should be able to explicitly say when you do/don't want to run said migrations (e.g. run different profiles). Of course, recently i've also found Dbmate (https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate) which is a lightweight and language-agnostic tool that supports a variety of DBMSes (PostgreSQL, MySQL/MariaDB, SQLite and more recently even ClickHouse) and can be used for running SQL migration scripts regardless of what technologies that particular app uses (Java, .NET, Node, PHP and so on). Now, setting up the CI might be a little bit more difficult, but having one tool across all of the services in your architecture can be worth it!
- tianzhou 5y agoBytebase (https://github.com/bytebase/bytebase https://github.com/bytebase/bytebase) is another option. It has bulit-in integration with GitLab to achieve so called database-as-code (disclaimer: I am the author of it)
- evanelias 5y ago
- kristjansson 5y agoIt seems obvious, but one would be surprised how often the argument has to be made. Though the real argument is that Tableau Workbooks, Alteryx ETL-graph-things, and the whole panoply of opaque propriety formats that contain, in their heart of hearts, a bunch of SQL are also themselves "code" and deserve the same testing, source control, code review, and deployment practices as the rest.
- hbarka 5y agoThe esteemed Google monorepo. Piper is a beautiful thing. Trunk-based development with rapid CI/CD. Everyone with access is essentially a knowledge worker and can create DAG-like data pipelines with their SQL code. There is no data team—they are all data team. They also have access to SQL macros and functions that have a global scope and evolves over time as more and more people build. Couples with a high performing database called F1, this is the secret to their success.
- he0001 5y agoI think that many people forget that version control is part of your delivery pipeline, not just a way to “store and move code”. I think there are possibilities to use the version control tools to much more than just that. GitOps touches that but I believe there’s a lot more to it. I think there’s some of it in what you describe here.
- mountainriver 5y agoYes! VCS hasn’t been fully utilized yet. All pipelines need versioning and most things are a pipeline. This is an exciting area of exploration
- dijit 5y agoI’ve been looking for something like piper, is there only perforce?
- hbarka 5y agodbt + dagster + GitHub
- fragmede 5y agoMicrosoft chose to extend git for their internal source control, and Facebook went all-in on Mercurial+extensions. Google also has a Mercurial+extensions interface to their monorepo. The sheer size of their monorepos mean that tools that don't work for them will work elsewhere. In particular, Git has made improvements recently working with giant (as opposed to merely bit or still-too-humongous-to-manage) repos so it's now workable after massaging the config. The other alternative to Perforce is SVN. There have been some improvements with it that make it more workable in today's git/"decentralized" world. Not knowing your particular use case it's hard to say if that's at all appropriate.
- tragomaskhalos 5y agoNot strictly germane to the article, but as code SQL has things precisely backwards IMHO. Consider: - DML is declarative; actually, though, I prefer to think of my query (or update) in terms of a pipeline splicing tuples together, filtering etc, and I'd be happier if I could write it as such, i.e. in a more imperative style (to express intent I mean - the planner should still be free to do all sorts of clever shenanigans, as now) - DDL, otoh, is imperative: "add this index", "remove this column". But here I want a declarative style! i.e. "I would like my table to look like this - make it so". A lot of the aggravation with database maintenance comes from having to manage a stack of non-idempotent DDL operations in order to wrestle the db into the required shape.
- glogla 5y agoI think it's a bit more nuanced - while I would say SELECT is declarative, UPDATE or INSERT or DELETE feel pretty imperative to me. Not only are they literally orders, but only DELETE is idempotent. But for analytics systems, you can get close to having declarative, idempotent DDL by doing CREATE OR REPLACE VIEW ... SELECT for derived data. Some systems even allow transactional and this idempotent DROP TABLE IF EXISTS; CREATE TABLE ... AS SELECT. This doesn't in any way help with indexes, with tables that are primary storage and you can't just drop and recreate them and so on, so there definitely is something missing.
- Wicher 5y agofrom TFA: Spend the time to migrate scripts from local locations to global locations, eliminate views, materialized views, and stored procedures. Wow. So things will be better if we don't use the modularization you can get through views? I've just finished up a tool* to make it easy to keep code for SQL views in VCS, but I must have been thoroughly misguided ;-) *) https://pypi.org/project/DBSamizdat/ https://pypi.org/project/DBSamizdat/
- xupybd 5y agoYeah I'm with you on this one. I think they keep these in code so that they can deploy over multiple databases. But I think this should be seen as a schema change. If you make a change to a view, materialized view or procedure you need to store that change in git and deploy it out to all instances via a migration tool.
- ilovedata1 5y agoAuthor of the article here, if anyone has any questions (or just continue roasting away; that’s good too) I wrote the article because many companies treat SQL like excel files. It gets emailed around, unknowingly forked and modified, saved on desktops or shared file storage, and nobody knows how to fix anything. For everyone who said “SQL is code is this news” - I agree with you. It is code. And it shouldn’t be news. And I hope all companies implement version control with SQL. I see Java version controlled at a significantly higher rate than SQL. That’s all. Good day.