Monday, February 21, 2011

Clojure clj-record library and Postgresql

I came across a problem when using the clj-record library with Postgresql. When creating a new record in a table, a java.lang.IllegalArgumentException is thrown, despite the fact that the record was successfully created. The "create" function inserts and retrieves the inserted record, so it looked like the problem happened when fetching the newly inserted record. By looking at the source code for the "create" function, I found out that the "insert" function called by "create" calls "id-query-for", which is a multimethod. For postgresql, the function is :

(defmethod id-query-for "postgresql" [_ table-name]
  (str "SELECT currval(pg_get_serial_sequence('" table-name "','id'))"))

Calling Postgresql's pg_get_serial_sequence means that the id column must be called "id", and that it must be "serial". My table layout looked like that :
CREATE TABLE users
(
  id integer NOT NULL DEFAULT nextval('seq_users'::regclass),
  "login" character varying(10),
  first_name character varying(20),
  last_name character varying(20),
  "password" character varying(10),
  email_address character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (id)
)
The column id was properly named "id", but its type was not serial, which was the problem. pg_get_serial_sequence returned null, so the record could not be fetched. I changed my table layout to look like this :

CREATE TABLE users
(
  id serial NOT NULL,
  "login" character varying(10),
  first_name character varying(20),
  last_name character varying(20),
  "password" character varying(10),
  email_address character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (id)
)

This worked as expected. I don't quite like the fact that the column id is fixed to "id", nor the logic to fetch the inserted record...

2 comments:

  1. Yeah, that seems like a pretty bad kludge for clj-record to use.

    It's one thing for you to choose to attach an attribute called "id", and have it assigned via SERIAL. Quite reasonable to choose to do.

    But mandating the naming convention, for all tables, seems Rather Wrong. It's wrong, for instance, for relations that associate between two objects.

    For instance, if you have users, and security roles, both might legitimately have IDs.

    But the *association*, indicating which users are associated with which roles, might be properly described via an SQL definition like

    create table user_role (
    user_id integer references users(id),
    role_id integer references roles(id),
    primary key(user_id, role_id)
    );

    Throwing in an extra ID would be silly. And notice that if you've got a table referencing multiple IDs from other tables, you cannot have two "id" columns. Which points at the thought that perhaps it's not a totally smart move to require that the attribute be called "id"...

    ReplyDelete
  2. Associations work a bit differently actually. The library lets you define the name of the foreign key, so it can be something different from "id". Details are here.

    ReplyDelete