Crane: An ORM for Common Lisp
eudoxia0.github.io
eudoxia0.github.io
I have just used it (elephant) for really simple things, but they seemed to work really well. Also with sql backends
* CLSQL was not built for a web environment (A man can dream) and is not thread-safe. The clsql-helper[0] project was created to address this, among other things.
* CLSQL supports more databases. While CLSQL has its own bindings for every database (Like SQLAlchemy does, I think), Crane uses cl-dbi[1], which right now only supports the big three (Postgres, MySQL, SQLite3).
* Crane was originally conceived as a set of macros to reduce the verbosity of SQL, but I later decided it would be wiser to make it stand on its own.
* CLSQL's manual is what Crane's manual aspires to be. It's probably the most well-documented Common Lisp project.
* CLSQL only escapes strings, it doesn't do prepared statements, which is a huge security flaw.
How is string escaping a huge security flaw? Certainly, you rely on the escaping being done correctly, which may be harder to get right than prepared statements, but assuming it's correct, it's just as secure, no?
[0] http://common-lisp.net/project/clsql-fluid/ [1] https://github.com/html/clsql-fluid
I haven't audited CLSQL's escaping myself, however. And I concede that using prepared statements takes one possible vulnerability off the table, which is nice. I was just wondering whether 'eudox had any specific reason to think that CLSQL's escaping was flawed.
There's also the usual for ORMs display of superficial understanding of transactions with a "with-transactions" global switch, as if transactions are something you can just sprinkle on top of your finished product like an exotic condiment, and get greater goodness. This is not how SQL works.
Even the "fine-grained" transactions interface doesn't expose common-place and crucial features such as transaction isolation levels, store points, selecting for update and lock in share mode. If you're reading this and thinking "this guy's just listing arbitrary niche features no one uses", then you have no business using or, god forbid, writing ORMs, because the first time you get serious traffic hitting your database you'll end up with inconsistent data state, and have no clue how it happened (blame MySQL? Sure, blame MySQL).
SQL ORMs feel oddly anachronistic in 2014. People have largely moved to persisting objects with databases built for persisting objects, leaving SQL databases to be used where they work best - for relational data queries, without quirky layers in between caller, query and results.
Plus... and I realize maybe it's just my bias against ORMs, but "an ORM for Common Lisp" reads a bit like "we ported AIDS to heaven". Just because you could do it, didn't mean you should've.
SQLAlchemy has a core SQL layer that lets you compose SQL better than by bashing strings together and it handles the transaction lifecycle correctly for you through its unit of work pattern (the session).
While all I know of Crane is this page, it would appear to follow the same design, there its "filter" shortcut is built on top of the lower layer, which is also available to use directly.
Essentially this. To be perfectly clear: Crane doesn't share SQLAlchemy's data mapper/unit of work pattern, it is much closer to regular active record. However, it still provides good flexibility by using SxQL and not pretending SQL doesn't exist.
It's true, they don't have to be useless.
The problem is that people who end up writing most of those libs are:
1. People who have very superficial knowledge about range of features, semantics, and algorithms behind SQL.
2. People who are motivated by an irrational yucky feeling about code in strings (as if their Lisp/Python/Java/etc. source isn't just code in a string itself).
3. People who are typically very averse towards learning new technologies. They are blissfully unaware of all the ways they can persist their objects without "mapping" them. They know in depth and favor one application language, and they've stored data in SQL a few times, so let's do everything in SQL, because learning is hard, but writing abstractions is cool. It feels like an achievement.
I won't claim anything about people who write ORMs, but wanting to avoid a mess of concatenated strings in your code, which will blow up unexpectedly because your forgot a ',' in an if somewhere is a sane attitude to have. SQL strings are unsafe and compose very badly.
As for claiming that "all code is string anyway", this is a strawman. Sure, "all code is string", except it can be statically checked for basic syntax errors.
You both talk about the perils of dynamically building queries, yet proclaim the benefits of static syntax checks. You can't "statically syntax check" a dynamically built query. Even with ORMs. So pick one. Either you're building it dynamically, or if it's static there won't be "string pieces" to worry about.
Most of queries are defined statically: a query with several anonymous (?) or named (:foobar) parameters to insert literals. So none of those scary string concatenation problems even occur.
Regarding stray commas... please: listOfIdents.join(','). It's not rocket science. Plus, I'm not necessarily advocating you use string functions to build queries, there are many small helpers to be had when you need to build a complex query, but the point is to know when to stop, and not try to completely hide the resulting SQL from the user of such libraries.
It's one thing to use a query builder for those rare complicated dynamic queries, but an entirely different thing to lock down and hide the connection and have your hands at the mercy of an abstraction layer for your every query.
If you can't control when and how your database gets called, if you can't control select locking, and transaction isolation levels, if the vast majority of its functions and syntax is out of reach, you can only do the most basic of CRUD operations on SQL.
It's just ugly that we have a complex piece of machinery like a modern SQL database, with all those advanced querying and MVCC features packed in for people to use them in order to build fast and safe applications, and watch them use it like a little more than a file system.
It's like buying yourself a million dollar supercomputer so you can play Solitaire on it.
I'm usually careful about using such words as "never" and "always". However, I have yet to encounter a non-trivial application built on top of raw SQL where this doesn't come and bite you. Of course you can't statically check your ORM-built query, but just like with static typing, you can still avoid many problems this way.
> Regarding stray commas... please: listOfIdents.join(','). It's not rocket science. Plus, I'm not necessarily advocating you use string functions to build queries, there are many small helpers to be had when you need to build a complex query, but the point is to know when to stop, and not try to completely hide the resulting SQL from the user of such libraries.
That's just one example. I've done complex dynamic queries with joins and aliases, and frankly, that's ugly with an ORM. Without it, it's an abomination. And I'll point that most (all?) ORMs let you use raw SQL.
> If you can't control when and how your database gets called, if you can't control select locking, and transaction isolation levels, if the vast majority of its functions and syntax is out of reach, you can only do the most basic of CRUD operations on SQL.
I'm not the biggest ORM advocate there is, but even I have to admit that persisting a complex object graph using an ORM is way easier than without it. The problems arise (usually) when you combine ORMs with naive developers attempting to get a lot of data out of the database.
I think you dismiss too easily the advantages you get from a good ORM (not that there are many of them), but I agree they're full of performance pitfalls as well (lazy collections being the most prominent one).
In a language with any sort of decent static type system, you most assuredly can. There should be no way to build and use an invalid query, the same as you can use the type system to enforce any other constraints.
> It's one thing to use a query builder for those rare complicated dynamic queries, but an entirely different thing to lock down and hide the connection and have your hands at the mercy of an abstraction layer for your every query.
Almost any SQL I've written has needed to be composable, for the same reasons that any other code should be composable. I need to be able to get <X> complex set of data from the database, given an identifier which could be an ID, or an URL slug, or any number of other things. I may also need to get that either for one row or for many.
It's nice to be able to split that up into the bit of the query that selects the data, and the bit of the query that chooses which row(s) to select it from, without writing out the SQL twice or developing "stringly-typed" code. It's a standard across most of the software development world that when dealing with source code (which SQL is), you almost always work with ASTs.
The frameworks I use for this do allow me complete access to the database, including all the things you've mentioned, and the ability to extend my queries with arbitrary syntax.
Well the SQL server won't actually run an invalid query, so what's your point?
I'm not afraid of invalid queries. I'm rather afraid of valid queries like these:
"DELETE FROM Table"
Because depending on how abstract your ORM is, it's far easier to run something you really don't want to run on your SQL server. But at least it'll be valid, hey!
To avoid runtime errors, I suppose.
As for "DELETE FROM table", I would say it's not any likelier to happen with an ORM than without. I haven't experienced issues of this sort, at least. The problem is more to build efficient, complex SELECT queries without getting a bad surprise performance-wise once in production.
I don't see anywhere on the page where it is critical of recreating SQL syntax. I think the trap the SQL stuff is trying to avoid is doing everything through OOP method calls — something like Django's filter method, which the page explicitly calls out.
Even if it was critical of recreating SQL, the point of an s-expression syntax is that it is still SQL, rather than a naïve reinvention that ends up missing important features.
This is not how I remember CL.