10 ms·

Andl, a relational language that is not SQL, is coming to Postgres

272 points by pjungwir 10y ago

pjungwir 10y ago
Thought I'd submit this after leaving a comment about it here: https://news.ycombinator.com/item?id=11802900 https://news.ycombinator.com/item?id=11802900 I'm not involved in any way, but I heard about it from the author's activity on the Postgres mailing lists, and I'm very intrigued to see where the project goes. I thought other HNers might be interested too. I'm glad to see people are experimenting with ways to take the relational model beyond SQL.
yomly 10y ago
Interesting coincidence for me that this hits the front page of HN today as I recently read this article [0]. The author thinks we should move away using the database purely to persist the data, while handling all logic/validation in the server language (e.g. By using an ORM to map your server language to the database) was less robust over time. Instead, he suggests pushing knowledge of your data into your database as this has proven to be more time resilient and takes advantage of the power of these databases. I liked the argument (which is better expressed that my paraphrasing) but my largest reservation was that expressiveness is a big consideration for choosing some language, and in this case the alternative to using Ruby/JS/Python + ORM in your MVC framework is to use SQL functions, which are syntactically clunky/dated at the very least. It seems like Andl is looking to bridge this to some extent. [0] https://sivers.org/pg https://sivers.org/pg
- brightball 10y ago
  It's a very interesting approach. I've always liked that PG made a lot of this possible internally but leveraging thrift is definitely a broader stroke. I like it.
- andy_ppp 10y ago
  Yes, I too read that essay, but after listening to Derek Sivers on the Tim Ferriss podcast (highly recommend): http://fourhourworkweek.com/2015/12/14/derek-sivers-on-developing-confidence-finding-happiness-and-saying-no-to-millions/ http://fourhourworkweek.com/2015/12/14/derek-sivers-on-devel...
- pjungwir 10y ago
  Interesting connection! :-) As a web developer who has done mostly Rails for a long time, I default to ORMs. And it seems to me that the last 20 years of architecture theory boils down to "How do I put a layer in front of my database?" Once your schema becomes a public API, your app is frozen. But on the other hand, I believe in using all the features your database has to offer. I remember doing J2EE in the early oughts everyone spoke about being able to swap out one RDBMS for another, and I eventually decided that kind of lowest-common-denominator approach was not worth the cost in preset-day productivity. Plus building on an open source project is less risky than something with outrageous licensing fees, so why not use it? So my own Rails projects have real foreign keys, CHECK constraints, some SQL functions (even some in C), views, and big queries. If I can express an invariant I trust Postgres more than myself to enforce it. I've also had some projects lately where building a JSON response for the front-end involved hundreds of ActiveRecord objects, and dodging that by using the JSON-construction functions in pg 9.4+ is a huge performance win. You can even still use the ActiveRecord DSL to build the query, using your scopes and associations and Ruby code, but then do a `to_sql` and wrap the whole thing in Postgres JSON fuctions. I feel like there could almost be a gem for that. Anyway, sorry for rambling. :-) I agree that Andl could make it much more appealing for people to "put the logic in the database," and I feel like I have been moving in that direction for a long time. Maybe we will see an ActiveRecord Andl adapter?!
  - jacques_chester 10y ago
    ActiveRecord looks on databases as flat files with a funny accent. I don't see how changing the query language will shift the entire design paradigm.
    - pjungwir 10y ago
      Do you feel that way about all ORMs or just ActiveRecord? How would you like it to be different?
      - cwyers 10y ago
        From what I've seen of it, LINQ to SQL (or other ORMs hooking up to LINQ on the .NET ecosystem, like Entity Framework) can avoid the feel of what Jacques is talking about. LINQ lets you express queries in C# (or what have you) syntax but still exposes the "feel" of an SQL database, not just using the database as a persistent object store.
- cwyers 10y ago
  Interesting inversion of the usual argument to keep logic out of the DB in case you want to migrate from one RDBMS to another. I find Siver's formulation much more persuasive.
  - gumballhead 10y ago
    I agree. How often do you need to change your RDBMS? Hopefully never. And how often do you see people wanting to rewrite everything in a new language or framework? A little more often. I think many developers are just not as comfortable working with databases.
    - spriggan3 10y ago
      > I think many developers are just not as comfortable working with databases. It's because databases are seen as a liability. They are often seen as complicated to test,"un-agile", hard to manage... in fact DBA used to be a job itself. Now developers are asked to be developers and DBA, which leads to over engineered application layers and under engineered data layers, or the rise of things like MongoDB. In a world where people are trying to push Javascript everywhere, from top to bottom, SQL,schema,migrations, stored procedures, triggers are seen as a relic of the past when in fact, they are a guarantee of stability, robustness and integrity. Maybe it is SQL that should be pushed to upper layers instead ( LINQ,HQL,DQL,... ).
      - dahdum 10y ago
        "In a world where people are trying to push Javascript everywhere, from top to bottom, SQL,schema,migrations, stored procedures, triggers are seen as a relic of the past when in fact, they are a guarantee of stability, robustness and integrity." With PLV8 for Postgres you can write your stored procedures in JS, making your entire stack essentially the same language. I've run a couple of production apps like this and it's worked better than I expected. No DML is allowed, just executing of the stored procedures, which lets us refactor the underlying tables pretty easily.
      - voltagex_ 10y ago
- icebraining 10y ago
  In Postgres you were never really restricted to SQL, there's built-in support for Tcl, Perl and Python, and third-party support for others. https://www.postgresql.org/docs/9.5/static/xplang.html https://www.postgresql.org/docs/9.5/static/xplang.html
  - bdcravens 10y ago
    Article acknowledges this: "There are currently several language implementations for Postgres, including PL/SQL (similar to SQL/PSM), Python and Perl. The aim of this project is to add Andl to that list, but as more than ‘just another language’."
    - sk5t 10y ago
      FYI, the name PL/SQL traditionally refers to Oracle's thing... Postgres has PL/pgSQL.
      - cmdkeen 10y ago
        I did a double take at that. My heart skipped a beat thinking I could maybe suggest porting some old Oracle stuff over to Postgres. A quick Google shattered my dreams. Damn you article.
        tkyjonathan 10y ago
        You can actually. If you want more out of the box tools to migrate from Oracle, then look at EnterpriseDB.
- mamcx 10y ago
  Do things at the database was the point of the xbase family. And it work very well!
- kemiller 10y ago
  The trouble with this is that the database is the hardest part to scale. In a busy site the db may have its work cut out just serving data. This particular pendulum swings back and forth every decade or so. That said, plenty of sites will never have a scaling problem and andl looks interesting
  - ZenoArrow 10y ago
    > "The trouble with this is that the database is the hardest part to scale." Probably depends on the plan followed for data replication, as some approaches seem to make it easier to add new DB nodes as you scale. I've not tried it myself, but I think Apache Samza has some promising ideas in this area: https://www.youtube.com/watch?v=fU9hR3kiOK0 https://www.youtube.com/watch?v=fU9hR3kiOK0
  - jacques_chester 10y ago
    It's not databases that are hard to scale, it's consistent state. It's just that databases begin with the design assumption that you want state to be consistent.
    - naasking 10y ago
      This is a great point. I think the way forward is to unite these properties in an append-only schema that can be efficiently queried for a consistent state without requiring expensive transactions. Something like [1] works well enough for RDMSs, but still isn't ideal. [1] http://higherlogics.blogspot.ca/2015/10/versioning-domain-entities.html http://higherlogics.blogspot.ca/2015/10/versioning-domain-en...
      - zaphar 10y ago
        This is also not as easy as it sounds though when you go distributed. Databases already handle the non distributed case pretty well. It's when you need the performance/latency benefits of distribution that you begin to run into problems that an append only schema doesn't help with.
      - 10y ago
- naasking 10y ago
  > Instead, he suggests pushing knowledge of your data into your database as this has proven to be more time resilient and takes advantage of the power of these databases. Indeed, moving logic into a relational language is a great idea. If only SQL weren't such a poor pseudo-relational language.
  - bsg75 10y ago
    Has a better replacement for SQL been created?
    - jayshua 10y ago
      I don't know that one has ever been truly implemented, but it's certainly been discussed. Refer to just about any of Chris Date's work, primarily the third manifesto. I do recall reading about a database system that tries to implement the "true" relational model (mostly according to Date, with the exception of his type system), and has a language similar to Date's Tutorial D. I believe it was written in Haskell and showed up here not very long ago. But I can't find it now...
      - kccqzy 10y ago
        You might be looking for Project:M36. https://github.com/agentm/project-m36 https://github.com/agentm/project-m36
      - cwyers 10y ago
        I don't know if Tutorial D and its brethren are actually better than SQL in the ways that SQL is bothersome to most people. I don't think most people actually care that SQL isn't a pure implementation of the relational system (and it goes further than Codd's original proposal, Codd called for NULLs, Date and Darwen want to throw them out). I think stuff like enabling code modularity and code reuse and otherwise acting like language design has advanced since the 1970s is far more interesting to most people than getting rid of bags in favor of sets.
        10y ago
- mistermann 10y ago
  The problem with this theory is programming in some databases (MS SQL Server) is extremely unpleasant. For example, you can finally, in the year 2016, split strings natively in TSQL: https://www.brentozar.com/archive/2016/03/splitting-strings-sql-server-2016-rescue/ https://www.brentozar.com/archive/2016/03/splitting-strings-... Compare that to this SO question: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x http://stackoverflow.com/questions/2647/how-do-i-split-a-str... Note there are 32 answers on that question, many of which fail under various edge cases. Also notice that no one finds this situation absurd in the slightest (Stockholm Syndrome is alive and well in the MSSQL community apparently). This is just one example of the nonsense you have to put up with on SQL Server, there are many others. And while PG is getting yet another new language capability, it appears Microsoft is pulling SQLCLR support (in Azure, so far): https://www.brentozar.com/archive/2016/04/breaking-news-literally-sql-clr-support-removed-azure-sql-db/ https://www.brentozar.com/archive/2016/04/breaking-news-lite... ...which probably doesn't matter at the end of the day because almost no DBA would ever let you use it in production. While it would cause harm to my personal career, nothing would make me happier than to see Postgres crush MSSQL, Microsoft has earned nothing less with their absolute disdain for their users.
  - NDizzle 10y ago
    You have been able to split strings in t-sql forever. The link you are referring to splits a string by a delimiter and returns the result as a table. I've also used CLRs in production since SQL Server 2005. Not sure which circles you run in, but it's quite different than what I've seen. I also use Service Broker, so maybe I'm just an odd duck?!
    - mistermann 10y ago
      > You have been able to split strings in t-sql forever. The link you are referring to splits a string by a delimiter and returns the result as a table. Generally ones wants to either extract the Nth element from a delimited string, or convert a delimited string into a table, are you thinking of a different scenario? Somewhere on an official Microsoft page, there is something to the effect that SQLCLR is "not recommended", which gives your typical DBA a perfect excuse to say "no", with no interest in the particulars of why Microsoft might have written that warning.
- rubber_duck 10y ago
  It sounds nice but from my experience version control, testing frameworks, debuggers and similar tools for managing code really don't play well with "let's put logic in to database".
- cmdkeen 10y ago
  I agree to an extent and in my current project have been making heavy use of PL/SQL to handle a lot of data logic having come off a project that tried to use Entity Framework to achieve the same thing. PL/SQL, for all its problems, does make it pretty straightforward to write a data API - AddX, AddYToX, AssociateXAndZ. It provides a nice abstraction over whatever table structure you may have and also makes it very clear, especially in the case of Table per Hierarchy type structures, what fields are required in what cases. The key thing is to limit yourself to what the language is good at - reading and writing data. You shouldn't need to do things like string manipulation, date conversions etc - that is what the calling code is responsible for doing. For me it makes sense to associate the data with the logic specifically relating to it as frontends can come and go without the underlying structure changing.
- stonewhite 10y ago
  While I do agree with you on ORM clunkyness, I cannot shake the outage-paranoia caused by untestable code that is deployed in a non-straightforward manner which makes it hard to rollback. Also, error logging or alerting tooling around it is very miniscule. I'd be keeping ORM's just to feel more secure and be more robust on the testing side.
- alex_duf 10y ago
  It a matter of finding the right balance. You want scalability? you have to drop your constraints and external keys in your DB. In fact don't use a DB with a schema. You want safe data? Use a good old relational DB with well designed schema and you're good to go.
- vbezhenar 10y ago
  I don't really like the idea of using DB-specific language. First of all, current "DB" languages and tooling are terrible and there's place for beautiful language with good SQL support embedded in PostgreSQL. But even with that theoretically good language, it's better to stay separate from your database, as you are (I hope) staying separate from OS you are using or from IDE you are using. Divide program (and development processes) to a clearly distinguishable parts with clearly defined interfaces and avoid tying too much with something. It might be difficult, but it's definitely possible to swap database if a program accesses it mostly using standard SQL. And it's impossible to swap (or sometimes even upgrade) database, if a program uses its proprietary internal mechanism to work. I know a project which uses Oracle 9. Customer wants to migrate to other version for last 5 years, but he can't, too much hidden obstacles, so he even have to support old hardware (because Oracle 9 doesn't run on newer Windows and old Windows doesn't run on newer hardware). And it's because 50% of a huge system was written using Oracle PL/SQL and it's just not realistic to rewrite it.
dominotw 10y ago
>Andl code can execute on any platform for which there is an implementation (in memory database, Sqlite or Postgres), providing identical results. sounds like .net LINQ
- bbcbasic 10y ago
  The syntax reminds me of LINQ.
tathougies 10y ago
> Andl code can execute on any platform for which there is an implementation (in memory database, Sqlite or Postgres), providing identical results. Sounds like SQL.
- lugg 10y ago
  Heh, thats my same remark when someone tries to describe the point of an ORM to me. Further to that point, if you ignore the ORM issue there really isn't much going for this Andl thing. I'm hesitant to dump too much on it as I don't fully understand the implications of what the article is talking about just yet. I think its a good start but I'm not sold on moving my logic there yet. I like my storage to be somewhat unknowledgeable to the rest of the world, it only needs to know what my data is and where it is. Just keep in mind, I understand fully that just because I find my current situation all fine and dandy that doesn't mean there isn't a better way of doing things. I just don't see it with this andl thing yet.
armamut 10y ago
SQL was here before I was born and I think will still be here long time. Like C. Thanks to Edgar F. Codd (not Larry Ellison :) ) By the way, I'll look at Andl. Even if I don't like the ORM idea.
- ibejoeb 10y ago
  Why the shot at Ellison? He embraced Codd's idea and did a hell of a job applying it, making it the de facto standard, making it commercially viable, and advancing it.
  - chubot 10y ago
    I'd like to hear a little more of Ellison's story. Jobs and Gates both have very well known stories, which intersect and which include partners Wozniak and Paul Allen. I don't know much about Ellison's story. He certainly has had an unusually long and successful career. I think he had a partner but I can't remember his name now. Did Ellison really have a visionary connection with computer science? (e.g. along the lines of Gates' "put a computer on every desk") It could be true, but almost nobody thinks of him that way. Certainly relational databases have had an enormous impact on computing and society. Did he ever make any technical contributions, the way Gates and Jobs did? (note: I'm not getting into an argument about Jobs' tech chops :) ) The core technology originated with IBM, and I think IBM had just as much of a role in commercializing it as Oracle, but I don't know much beyond that. I think there is a meme that Oracle was more of a ruthless competitor, skilled in acquiring other successful companies whose names we don't remember anymore. But perhaps they didn't really move things forward on a technological level the way that Apple and yes even Microsoft did.
    - jasonm23 10y ago
      Very true re: IBM, they had standardized SQL long before Oracle was "the name" in Db tech.
    - jasonm23 10y ago
      Very true re: IBM, they had standardized SQL long before Oracle was "the name" in Db tech.
pella 10y ago
any news about commercial use ? "LICENCE ======= This version of Andl is free for any kind of experimental use, especially helping to make it better. For now, the licence does not grant rights for distribution or commercial use. That will have to wait until I can choose the right licence, which depends a lot on who might want to use it." https://github.com/davidandl/Andl https://github.com/davidandl/Andl
- peteretep 10y ago
  > That will have to wait until I can choose the right > licence, which depends a lot on who might want to > use it Anybody who introduces this in to a project needs firing.
  - tobltobs 10y ago
    Firing from his own project?
    - peteretep 10y ago
      Who is 'his'? If you introduce this software in to any project, regardless of your gender, you should be fired, as a direct consequence of the insane license.
      - tobltobs 10y ago
        That is of course correct. I did not even consider the possibility that someone would do this.
    - dangoor 10y ago
      I believe GP meant anyone who uses andl in their own separate project, not andl itself
- tacone 10y ago
  > For now, the licence does not grant rights for distribution or commercial use. Thank you very much, goodbye, have a nice day.
deleted 10y ago
[deleted]
webjprgm 10y ago
Also sounds similar to M (aka MUMPS), see InterSystem's Cache (commercial) or GT.M (open source). It is an old programming language with a persistent key-value store that is basically a NoSQL database with programming logic combined. From what I've heard finance and health care software often uses this. It similarly puts the business logic in the database.
cwyers 10y ago
I am not sure I understand what this is. The documentation spends a fair amount of time referencing The Third Manifesto... is it a D? If so, how does it interact with valid Postgres tables that, say, have NULL values?
koolba 10y ago
From the article: > The language handler plandl is a DLL written in C, which calls a C++ function, which in turn uses COM to start up the CLR runtime and load the Andl Postgres entry point. Similar capabilities can be provided for Mono. It's hard enough getting someone to install a non-core contrib module. This sounds like it'd be impossible to get running.
- MichaelGG 10y ago
  On Windows/CLR, that whole bit is auto-magic (IJW - "It Just Works") interop. It's no harder than just having the DLL around. For Mono there's a bit of work involved to load it up but apart from that it's smooth enough. As far as the end-user is concerned, they should just need to install mono and they're good. Source: I wrote a similar module for FreeSWITCH which has lots of users both on Windows/CLR and on Mono.
Pxtl 10y ago
Good. SQL is getting very long in the tooth, but every "fix" throws the baby out with the bathwater.
rubyfan 10y ago
What's the compelling argument for why we all need this? I'm unable to distill this from the article.
bsg75 10y ago
Is this limited to Windows? The source does not seem to define system requirements, or I am missing it.
andersonmvd 10y ago
"(...) written in C, which calls a C++ function (...)", well, memory bugs ahead. That would be great if they could move to Rust or another similar language.
joe_the_user 10y ago
It seems Andl is an implementation of the Language that Date and Darwin referred to as D in their Third Manifesto.[1] [1] https://en.wikipedia.org/wiki/D_%28data_language_specification%29 https://en.wikipedia.org/wiki/D_%28data_language_specificati...
dmichulke 10y ago
I'm sorry I don't really understand what Andl could be used for, and wikipedia in English and German doesn't contain anything (ddg has as first hit "paraphilic infantilism"?!), so I will ask here. Is Andl only for stored procedures? If so why would it be better than say python? If I were to choose, I'd prefer a lispier SQL with support for the basic things like map, filter, reduce, ... Is there something alike? If not, why? UPDATE: Nevermind, I just found the other links (quite small on top of the headline), so most things are explained. However, the article suggests people use python or perl for queries instead of SQL. Did anyone see this in production (apart from Stored Procedures)?
- tkyjonathan 10y ago
  Sounds like you would really enjoy Clojure's YeSQL.
JohnDoe365 10y ago
No example how it looks like? > The language handler plandl is a DLL written in C, which calls a C++ function, which in turn uses COM to start up the CLR runtime and load the Andl Postgres entry point. Similar capabilities can be provided for Mono Seems like a lot of boilerplate
tkyjonathan 10y ago
To be honest, I'd rather just use YeSQL (or any SQL template engine) to keep my SQL separate from my code, keep everything looking clean and have no impedance mismatch.
kragen 10y ago
I hate SQL, so I was excited to see this, but there are two things that rule this out to me: 1. It's not open-source; the license on https://github.com/davidandl/Andl https://github.com/davidandl/Andl specifically forbids distribution and commercial use. 2. It only runs in Microsoft Windows. Datalog https://en.wikipedia.org/wiki/Datalog https://en.wikipedia.org/wiki/Datalog is much more interesting as an alternative query language — it has better abstraction capabilities than SQL, and it's much less verbose, but as far as I know there's no implementation of Datalog in Postgres.

272 points by pjungwir 10y ago

Thought I'd submit this after leaving a comment about it here: https://news.ycombinator.com/item?id=11802900 https://news.ycombinator.com/item?id=11802900 I'm not involved in any way, but I heard about it from the author's activity on the Postgres mailing lists, and I'm very intrigued to see where the project goes. I thought other HNers might be interested too. I'm glad to see people are experimenting with ways to take the relational model beyond SQL.

yomly 10y ago

Interesting coincidence for me that this hits the front page of HN today as I recently read this article [0]. The author thinks we should move away using the database purely to persist the data, while handling all logic/validation in the server language (e.g. By using an ORM to map your server language to the database) was less robust over time. Instead, he suggests pushing knowledge of your data into your database as this has proven to be more time resilient and takes advantage of the power of these databases. I liked the argument (which is better expressed that my paraphrasing) but my largest reservation was that expressiveness is a big consideration for choosing some language, and in this case the alternative to using Ruby/JS/Python + ORM in your MVC framework is to use SQL functions, which are syntactically clunky/dated at the very least. It seems like Andl is looking to bridge this to some extent. [0] https://sivers.org/pg https://sivers.org/pg

brightball 10y ago
It's a very interesting approach. I've always liked that PG made a lot of this possible internally but leveraging thrift is definitely a broader stroke. I like it.
andy_ppp 10y ago
Yes, I too read that essay, but after listening to Derek Sivers on the Tim Ferriss podcast (highly recommend): http://fourhourworkweek.com/2015/12/14/derek-sivers-on-developing-confidence-finding-happiness-and-saying-no-to-millions/ http://fourhourworkweek.com/2015/12/14/derek-sivers-on-devel...
pjungwir 10y ago
Interesting connection! :-) As a web developer who has done mostly Rails for a long time, I default to ORMs. And it seems to me that the last 20 years of architecture theory boils down to "How do I put a layer in front of my database?" Once your schema becomes a public API, your app is frozen. But on the other hand, I believe in using all the features your database has to offer. I remember doing J2EE in the early oughts everyone spoke about being able to swap out one RDBMS for another, and I eventually decided that kind of lowest-common-denominator approach was not worth the cost in preset-day productivity. Plus building on an open source project is less risky than something with outrageous licensing fees, so why not use it? So my own Rails projects have real foreign keys, CHECK constraints, some SQL functions (even some in C), views, and big queries. If I can express an invariant I trust Postgres more than myself to enforce it. I've also had some projects lately where building a JSON response for the front-end involved hundreds of ActiveRecord objects, and dodging that by using the JSON-construction functions in pg 9.4+ is a huge performance win. You can even still use the ActiveRecord DSL to build the query, using your scopes and associations and Ruby code, but then do a `to_sql` and wrap the whole thing in Postgres JSON fuctions. I feel like there could almost be a gem for that. Anyway, sorry for rambling. :-) I agree that Andl could make it much more appealing for people to "put the logic in the database," and I feel like I have been moving in that direction for a long time. Maybe we will see an ActiveRecord Andl adapter?!
- jacques_chester 10y ago
  ActiveRecord looks on databases as flat files with a funny accent. I don't see how changing the query language will shift the entire design paradigm.
  - pjungwir 10y ago
    Do you feel that way about all ORMs or just ActiveRecord? How would you like it to be different?
    - cwyers 10y ago
      From what I've seen of it, LINQ to SQL (or other ORMs hooking up to LINQ on the .NET ecosystem, like Entity Framework) can avoid the feel of what Jacques is talking about. LINQ lets you express queries in C# (or what have you) syntax but still exposes the "feel" of an SQL database, not just using the database as a persistent object store.
cwyers 10y ago
Interesting inversion of the usual argument to keep logic out of the DB in case you want to migrate from one RDBMS to another. I find Siver's formulation much more persuasive.
- gumballhead 10y ago
  I agree. How often do you need to change your RDBMS? Hopefully never. And how often do you see people wanting to rewrite everything in a new language or framework? A little more often. I think many developers are just not as comfortable working with databases.
  - spriggan3 10y ago
    > I think many developers are just not as comfortable working with databases. It's because databases are seen as a liability. They are often seen as complicated to test,"un-agile", hard to manage... in fact DBA used to be a job itself. Now developers are asked to be developers and DBA, which leads to over engineered application layers and under engineered data layers, or the rise of things like MongoDB. In a world where people are trying to push Javascript everywhere, from top to bottom, SQL,schema,migrations, stored procedures, triggers are seen as a relic of the past when in fact, they are a guarantee of stability, robustness and integrity. Maybe it is SQL that should be pushed to upper layers instead ( LINQ,HQL,DQL,... ).
    - dahdum 10y ago
      "In a world where people are trying to push Javascript everywhere, from top to bottom, SQL,schema,migrations, stored procedures, triggers are seen as a relic of the past when in fact, they are a guarantee of stability, robustness and integrity." With PLV8 for Postgres you can write your stored procedures in JS, making your entire stack essentially the same language. I've run a couple of production apps like this and it's worked better than I expected. No DML is allowed, just executing of the stored procedures, which lets us refactor the underlying tables pretty easily.
    - voltagex_ 10y ago
icebraining 10y ago
In Postgres you were never really restricted to SQL, there's built-in support for Tcl, Perl and Python, and third-party support for others. https://www.postgresql.org/docs/9.5/static/xplang.html https://www.postgresql.org/docs/9.5/static/xplang.html
- bdcravens 10y ago
  Article acknowledges this: "There are currently several language implementations for Postgres, including PL/SQL (similar to SQL/PSM), Python and Perl. The aim of this project is to add Andl to that list, but as more than ‘just another language’."
  - sk5t 10y ago
    FYI, the name PL/SQL traditionally refers to Oracle's thing... Postgres has PL/pgSQL.
    - cmdkeen 10y ago
      I did a double take at that. My heart skipped a beat thinking I could maybe suggest porting some old Oracle stuff over to Postgres. A quick Google shattered my dreams. Damn you article.
      - tkyjonathan 10y ago
        You can actually. If you want more out of the box tools to migrate from Oracle, then look at EnterpriseDB.
mamcx 10y ago
Do things at the database was the point of the xbase family. And it work very well!
kemiller 10y ago
The trouble with this is that the database is the hardest part to scale. In a busy site the db may have its work cut out just serving data. This particular pendulum swings back and forth every decade or so. That said, plenty of sites will never have a scaling problem and andl looks interesting
- ZenoArrow 10y ago
  > "The trouble with this is that the database is the hardest part to scale." Probably depends on the plan followed for data replication, as some approaches seem to make it easier to add new DB nodes as you scale. I've not tried it myself, but I think Apache Samza has some promising ideas in this area: https://www.youtube.com/watch?v=fU9hR3kiOK0 https://www.youtube.com/watch?v=fU9hR3kiOK0
- jacques_chester 10y ago
  It's not databases that are hard to scale, it's consistent state. It's just that databases begin with the design assumption that you want state to be consistent.
  - naasking 10y ago
    This is a great point. I think the way forward is to unite these properties in an append-only schema that can be efficiently queried for a consistent state without requiring expensive transactions. Something like [1] works well enough for RDMSs, but still isn't ideal. [1] http://higherlogics.blogspot.ca/2015/10/versioning-domain-entities.html http://higherlogics.blogspot.ca/2015/10/versioning-domain-en...
    - zaphar 10y ago
      This is also not as easy as it sounds though when you go distributed. Databases already handle the non distributed case pretty well. It's when you need the performance/latency benefits of distribution that you begin to run into problems that an append only schema doesn't help with.
    - 10y ago
naasking 10y ago
> Instead, he suggests pushing knowledge of your data into your database as this has proven to be more time resilient and takes advantage of the power of these databases. Indeed, moving logic into a relational language is a great idea. If only SQL weren't such a poor pseudo-relational language.
- bsg75 10y ago
  Has a better replacement for SQL been created?
  - jayshua 10y ago
    I don't know that one has ever been truly implemented, but it's certainly been discussed. Refer to just about any of Chris Date's work, primarily the third manifesto. I do recall reading about a database system that tries to implement the "true" relational model (mostly according to Date, with the exception of his type system), and has a language similar to Date's Tutorial D. I believe it was written in Haskell and showed up here not very long ago. But I can't find it now...
    - kccqzy 10y ago
      You might be looking for Project:M36. https://github.com/agentm/project-m36 https://github.com/agentm/project-m36
    - cwyers 10y ago
      I don't know if Tutorial D and its brethren are actually better than SQL in the ways that SQL is bothersome to most people. I don't think most people actually care that SQL isn't a pure implementation of the relational system (and it goes further than Codd's original proposal, Codd called for NULLs, Date and Darwen want to throw them out). I think stuff like enabling code modularity and code reuse and otherwise acting like language design has advanced since the 1970s is far more interesting to most people than getting rid of bags in favor of sets.
      - 10y ago
mistermann 10y ago
The problem with this theory is programming in some databases (MS SQL Server) is extremely unpleasant. For example, you can finally, in the year 2016, split strings natively in TSQL: https://www.brentozar.com/archive/2016/03/splitting-strings-sql-server-2016-rescue/ https://www.brentozar.com/archive/2016/03/splitting-strings-... Compare that to this SO question: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x http://stackoverflow.com/questions/2647/how-do-i-split-a-str... Note there are 32 answers on that question, many of which fail under various edge cases. Also notice that no one finds this situation absurd in the slightest (Stockholm Syndrome is alive and well in the MSSQL community apparently). This is just one example of the nonsense you have to put up with on SQL Server, there are many others. And while PG is getting yet another new language capability, it appears Microsoft is pulling SQLCLR support (in Azure, so far): https://www.brentozar.com/archive/2016/04/breaking-news-literally-sql-clr-support-removed-azure-sql-db/ https://www.brentozar.com/archive/2016/04/breaking-news-lite... ...which probably doesn't matter at the end of the day because almost no DBA would ever let you use it in production. While it would cause harm to my personal career, nothing would make me happier than to see Postgres crush MSSQL, Microsoft has earned nothing less with their absolute disdain for their users.
- NDizzle 10y ago
  You have been able to split strings in t-sql forever. The link you are referring to splits a string by a delimiter and returns the result as a table. I've also used CLRs in production since SQL Server 2005. Not sure which circles you run in, but it's quite different than what I've seen. I also use Service Broker, so maybe I'm just an odd duck?!
  - mistermann 10y ago
    > You have been able to split strings in t-sql forever. The link you are referring to splits a string by a delimiter and returns the result as a table. Generally ones wants to either extract the Nth element from a delimited string, or convert a delimited string into a table, are you thinking of a different scenario? Somewhere on an official Microsoft page, there is something to the effect that SQLCLR is "not recommended", which gives your typical DBA a perfect excuse to say "no", with no interest in the particulars of why Microsoft might have written that warning.
rubber_duck 10y ago
It sounds nice but from my experience version control, testing frameworks, debuggers and similar tools for managing code really don't play well with "let's put logic in to database".
cmdkeen 10y ago
I agree to an extent and in my current project have been making heavy use of PL/SQL to handle a lot of data logic having come off a project that tried to use Entity Framework to achieve the same thing. PL/SQL, for all its problems, does make it pretty straightforward to write a data API - AddX, AddYToX, AssociateXAndZ. It provides a nice abstraction over whatever table structure you may have and also makes it very clear, especially in the case of Table per Hierarchy type structures, what fields are required in what cases. The key thing is to limit yourself to what the language is good at - reading and writing data. You shouldn't need to do things like string manipulation, date conversions etc - that is what the calling code is responsible for doing. For me it makes sense to associate the data with the logic specifically relating to it as frontends can come and go without the underlying structure changing.
stonewhite 10y ago
While I do agree with you on ORM clunkyness, I cannot shake the outage-paranoia caused by untestable code that is deployed in a non-straightforward manner which makes it hard to rollback. Also, error logging or alerting tooling around it is very miniscule. I'd be keeping ORM's just to feel more secure and be more robust on the testing side.
alex_duf 10y ago
It a matter of finding the right balance. You want scalability? you have to drop your constraints and external keys in your DB. In fact don't use a DB with a schema. You want safe data? Use a good old relational DB with well designed schema and you're good to go.
vbezhenar 10y ago
I don't really like the idea of using DB-specific language. First of all, current "DB" languages and tooling are terrible and there's place for beautiful language with good SQL support embedded in PostgreSQL. But even with that theoretically good language, it's better to stay separate from your database, as you are (I hope) staying separate from OS you are using or from IDE you are using. Divide program (and development processes) to a clearly distinguishable parts with clearly defined interfaces and avoid tying too much with something. It might be difficult, but it's definitely possible to swap database if a program accesses it mostly using standard SQL. And it's impossible to swap (or sometimes even upgrade) database, if a program uses its proprietary internal mechanism to work. I know a project which uses Oracle 9. Customer wants to migrate to other version for last 5 years, but he can't, too much hidden obstacles, so he even have to support old hardware (because Oracle 9 doesn't run on newer Windows and old Windows doesn't run on newer hardware). And it's because 50% of a huge system was written using Oracle PL/SQL and it's just not realistic to rewrite it.

dominotw 10y ago

>Andl code can execute on any platform for which there is an implementation (in memory database, Sqlite or Postgres), providing identical results. sounds like .net LINQ

bbcbasic 10y ago
The syntax reminds me of LINQ.

tathougies 10y ago

> Andl code can execute on any platform for which there is an implementation (in memory database, Sqlite or Postgres), providing identical results. Sounds like SQL.

lugg 10y ago
Heh, thats my same remark when someone tries to describe the point of an ORM to me. Further to that point, if you ignore the ORM issue there really isn't much going for this Andl thing. I'm hesitant to dump too much on it as I don't fully understand the implications of what the article is talking about just yet. I think its a good start but I'm not sold on moving my logic there yet. I like my storage to be somewhat unknowledgeable to the rest of the world, it only needs to know what my data is and where it is. Just keep in mind, I understand fully that just because I find my current situation all fine and dandy that doesn't mean there isn't a better way of doing things. I just don't see it with this andl thing yet.

armamut 10y ago

SQL was here before I was born and I think will still be here long time. Like C. Thanks to Edgar F. Codd (not Larry Ellison :) ) By the way, I'll look at Andl. Even if I don't like the ORM idea.

ibejoeb 10y ago
Why the shot at Ellison? He embraced Codd's idea and did a hell of a job applying it, making it the de facto standard, making it commercially viable, and advancing it.
- chubot 10y ago
  I'd like to hear a little more of Ellison's story. Jobs and Gates both have very well known stories, which intersect and which include partners Wozniak and Paul Allen. I don't know much about Ellison's story. He certainly has had an unusually long and successful career. I think he had a partner but I can't remember his name now. Did Ellison really have a visionary connection with computer science? (e.g. along the lines of Gates' "put a computer on every desk") It could be true, but almost nobody thinks of him that way. Certainly relational databases have had an enormous impact on computing and society. Did he ever make any technical contributions, the way Gates and Jobs did? (note: I'm not getting into an argument about Jobs' tech chops :) ) The core technology originated with IBM, and I think IBM had just as much of a role in commercializing it as Oracle, but I don't know much beyond that. I think there is a meme that Oracle was more of a ruthless competitor, skilled in acquiring other successful companies whose names we don't remember anymore. But perhaps they didn't really move things forward on a technological level the way that Apple and yes even Microsoft did.
  - jasonm23 10y ago
    Very true re: IBM, they had standardized SQL long before Oracle was "the name" in Db tech.
  - jasonm23 10y ago
    Very true re: IBM, they had standardized SQL long before Oracle was "the name" in Db tech.

pella 10y ago

any news about commercial use ? "LICENCE ======= This version of Andl is free for any kind of experimental use, especially helping to make it better. For now, the licence does not grant rights for distribution or commercial use. That will have to wait until I can choose the right licence, which depends a lot on who might want to use it." https://github.com/davidandl/Andl https://github.com/davidandl/Andl

peteretep 10y ago
> That will have to wait until I can choose the right > licence, which depends a lot on who might want to > use it Anybody who introduces this in to a project needs firing.
- tobltobs 10y ago
  Firing from his own project?
  - peteretep 10y ago
    Who is 'his'? If you introduce this software in to any project, regardless of your gender, you should be fired, as a direct consequence of the insane license.
    - tobltobs 10y ago
      That is of course correct. I did not even consider the possibility that someone would do this.
  - dangoor 10y ago
    I believe GP meant anyone who uses andl in their own separate project, not andl itself
tacone 10y ago
> For now, the licence does not grant rights for distribution or commercial use. Thank you very much, goodbye, have a nice day.

deleted 10y ago

[deleted]

webjprgm 10y ago

Also sounds similar to M (aka MUMPS), see InterSystem's Cache (commercial) or GT.M (open source). It is an old programming language with a persistent key-value store that is basically a NoSQL database with programming logic combined. From what I've heard finance and health care software often uses this. It similarly puts the business logic in the database.

cwyers 10y ago

I am not sure I understand what this is. The documentation spends a fair amount of time referencing The Third Manifesto... is it a D? If so, how does it interact with valid Postgres tables that, say, have NULL values?

koolba 10y ago

From the article: > The language handler plandl is a DLL written in C, which calls a C++ function, which in turn uses COM to start up the CLR runtime and load the Andl Postgres entry point. Similar capabilities can be provided for Mono. It's hard enough getting someone to install a non-core contrib module. This sounds like it'd be impossible to get running.

MichaelGG 10y ago
On Windows/CLR, that whole bit is auto-magic (IJW - "It Just Works") interop. It's no harder than just having the DLL around. For Mono there's a bit of work involved to load it up but apart from that it's smooth enough. As far as the end-user is concerned, they should just need to install mono and they're good. Source: I wrote a similar module for FreeSWITCH which has lots of users both on Windows/CLR and on Mono.

Pxtl 10y ago

Good. SQL is getting very long in the tooth, but every "fix" throws the baby out with the bathwater.

rubyfan 10y ago

What's the compelling argument for why we all need this? I'm unable to distill this from the article.

bsg75 10y ago

Is this limited to Windows? The source does not seem to define system requirements, or I am missing it.

andersonmvd 10y ago

"(...) written in C, which calls a C++ function (...)", well, memory bugs ahead. That would be great if they could move to Rust or another similar language.

joe_the_user 10y ago

It seems Andl is an implementation of the Language that Date and Darwin referred to as D in their Third Manifesto.[1] [1] https://en.wikipedia.org/wiki/D_%28data_language_specification%29 https://en.wikipedia.org/wiki/D_%28data_language_specificati...

dmichulke 10y ago

I'm sorry I don't really understand what Andl could be used for, and wikipedia in English and German doesn't contain anything (ddg has as first hit "paraphilic infantilism"?!), so I will ask here. Is Andl only for stored procedures? If so why would it be better than say python? If I were to choose, I'd prefer a lispier SQL with support for the basic things like map, filter, reduce, ... Is there something alike? If not, why? UPDATE: Nevermind, I just found the other links (quite small on top of the headline), so most things are explained. However, the article suggests people use python or perl for queries instead of SQL. Did anyone see this in production (apart from Stored Procedures)?

tkyjonathan 10y ago
Sounds like you would really enjoy Clojure's YeSQL.

JohnDoe365 10y ago

No example how it looks like? > The language handler plandl is a DLL written in C, which calls a C++ function, which in turn uses COM to start up the CLR runtime and load the Andl Postgres entry point. Similar capabilities can be provided for Mono Seems like a lot of boilerplate

tkyjonathan 10y ago

To be honest, I'd rather just use YeSQL (or any SQL template engine) to keep my SQL separate from my code, keep everything looking clean and have no impedance mismatch.

kragen 10y ago

I hate SQL, so I was excited to see this, but there are two things that rule this out to me: 1. It's not open-source; the license on https://github.com/davidandl/Andl https://github.com/davidandl/Andl specifically forbids distribution and commercial use. 2. It only runs in Microsoft Windows. Datalog https://en.wikipedia.org/wiki/Datalog https://en.wikipedia.org/wiki/Datalog is much more interesting as an alternative query language — it has better abstraction capabilities than SQL, and it's much less verbose, but as far as I know there's no implementation of Datalog in Postgres.