Autoincrement keys using DataMapper with legacy PostgreSQL db

I have an existing PostgreSQL database that I want to access with Ruby DataMapper. I have created my models according to the existing database schemata, but primary keys are not auto incremented.

My models look like this:

class Company
  include DataMapper::Resource

  property :id, Serial
  property :name, String
end

Defining the id as Serial has no effect: when I create an instance I get an DataObjects::IntegrityError because of a violated not-null constraint.

The database is shared with a PHP/Doctrine application, so I cannot cha开发者_C百科nge the schema. The PHP application uses a sequence to create the primary keys like this:

SELECT NEXTVAL('"production"."sequence_id_seq"') 
INSERT INTO "production"."companies" ("name", "id") VALUES ('Acme Inc.', 40711)

The table looks like this:

CREATE TABLE production.companies
(
  id bigint NOT NULL,
  "name" character varying(255) NOT NULL,
  CONSTRAINT companies_pkey PRIMARY KEY (id),
)
WITH (
  OIDS=FALSE
);

This is the equivalent Doctrine schema definition:

CompanyOrm:
  tableName: companies
  columns:
    id:
      type: integer(8)
      unsigned: 1
      primary: true
      sequence: sequence_id
    name:
      type: string(255)
      notnull: true

How can I recreate that behavior with DataMapper? Or what other (better) solutions exist to create auto incrementing keys in this situation?

---

You can use the nextval() function in an expression to fetch and use the next sequence value as the id:

INSERT INTO "production"."companies" ("name", "id") 
VALUES ('Acme Inc.', nextval('production.sequence_id_seq') )

I don't know enough about DataMapper to say whether or not it's possible to cause a function to be used in an INSERT, but maybe there's some way to do it by defining your own Datamapper::Property (instead of Serial)?

Alternatively, you could alter the table to make the row default to using the sequence, which shouldn't break compatibility with anything that manually uses the same sequence:

ALTER TABLE production.companies 
  ALTER COLUMN id SET DEFAULT nextval('production.sequence_id_seq');