
Crane: An ORM for Common Lisp - eudox
http://eudoxia0.github.io/crane/
======
patrickmay
I'm currently using Postmodern
([http://marijnhaverbeke.nl/postmodern/](http://marijnhaverbeke.nl/postmodern/)).
How does Crane compare?

~~~
PuercoPop
Postmodern is built upon s-sql while Crane on sxql. Also Postmodern's DAO
doesn't strive to be an ORM.

~~~
eudox
Specifically, S-SQL only escapes SQL and does parametric statements
(Interpolation) only manually, while SxQL is safe by default: Every object in
an SQL query is passed in an injection-safe way.

~~~
patrickmay
That's a good non-functional feature. Does Crane offer any functional
capabilities over Postmodern?

------
rgrau
How does it compare to clos + elephant?

I have just used it (elephant) for really simple things, but they seemed to
work really well. Also with sql backends

~~~
eudox
From what I've seen, Elephant gives you an interface to some persistent data
structures then finds a way to back them with SQL or something else. This
doesn't have that kind of backend hiding, it's meant to be tightly integrated
with SQL.

------
ScottBurson
How does it compare to CLSQL? Looks mostly pretty similar at first glance,
though the automatic migrations look like a nice addition.

~~~
eudox
Some differences and similarities:

* 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.

[0] [https://github.com/AccelerationNet/clsql-
helper](https://github.com/AccelerationNet/clsql-helper)

[1] [https://github.com/fukamachi/cl-dbi](https://github.com/fukamachi/cl-dbi)

~~~
ScottBurson
CLSQL-Fluid [0][1] makes CLSQL thread-safe. (It also makes interesting reading
as a clever use of CLOS.)

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/](http://common-
lisp.net/project/clsql-fluid/) [1] [https://github.com/html/clsql-
fluid](https://github.com/html/clsql-fluid)

~~~
spacemanmatt
I don't think most people have a solid sense of what is acceptable safety for
dynamic SQL. The goal is to avoid injections, so you have to ensure that can't
happen. If you have a reliable way to quote/escape literals (values and
identifiers might have different syntax for this) you're good. It is risky,
like all programming, to take matters into your own hands, but so is using a
library you can't understand. Good luck.

~~~
ScottBurson
CLSQL does the escaping itself. The question is whether a library that does
prepared statements is reasonably expected to be safer than one that does
escaping.

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.

------
mantrax5
The project page makes claims that it doesn't fall into common ORM traps such
as recreating SQL syntax, locking you into a limited subset of it, yet... just
a few sections below it shows exactly that. I'm sorry, but building SQL out of
s-expressions doesn't count as "functional SQL". It's just crippled
declarative normal SQL, made from s-expressions.

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.

~~~
lucian1900
SQL libraries don't have to be ORMs or useless.

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.

~~~
mantrax5
> SQL libraries don't have to be ORMs or useless.

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.

~~~
mercurial
> 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).

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.

~~~
mantrax5
If we'll be talking about strawmen, the idea every SQL query is composed of
many concatenated string pieces is the biggest of them all.

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.

~~~
vertex-four
> You can't "statically syntax check" a dynamically built query.

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.

~~~
mantrax5
> "There should be no way to build and use an invalid query"

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!

~~~
mercurial
> Well the SQL server won't actually run an invalid query, so what's your
> point?

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.

------
huehue
The code is a joy to read.

This is not how I remember CL.

~~~
ska
Conversely, that is exactly how I remember CL. (e.g.
[http://weitz.de/](http://weitz.de/))

