Handling auto inc columns in Oracle via slick

Question

I am using slick 2.1.0. Oracle doesn't have a notion of auto increment attribute for a column, so how can I manage an insert via slick using a sequence. e.g. I have a table & sequence as follows :

    CREATE TABLE USER
    (    "USER_ID" NUMBER NOT NULL ENABLE,
         "NAME" VARCHAR2(100) NOT NULL ENABLE,
         "ADDRESS" VARCHAR2(1000) NOT NULL ENABLE
    );
    CREATE SEQUENCE USER_ID_SEQ  MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 2;

How can I use this sequence to set my USER_ID?
Also setting autoIncLastAsOption = true in Slicks's SourceCodeGenerator doesnt seem to help. My IDs are still not an Option[].

Rohan Rohan · Accepted Answer · 2014-12-07T19:41:43

Here are some of the options suggested by Typesafe Developer:

If you don’t mind letting Slick manage the DDL, you can use O.AutoInc with OracleDriver. It will automatically create a backing sequence for the generated identity values. The way this works is by installing a trigger that automatically populates the ID from the sequence. Here’s the code that Slick generates for an AutoInc column on Oracle:

create sequence $seq start with 1 increment by 1;
create or replace trigger $trg before insert on $tab referencing new as new for each row when (new.$col is null) begin select $seq.nextval into :new.$col from sys.dual;
end;
where $seq, $trg, $col and $tab are the names of the sequence, trigger, identity column and table.
There is no special code being run during an actual insert operation. So if you already have a database schema with an identity sequence, you can manually create a trigger as shown above and mark the column as O.AutoInc in Slick to get the standard handling for auto-incrementing columns.
If you want a solution without a trigger, you could you insertExpr for inserting in Slick. This allows computed expressions, like using Slick’s own sequence API (which is supported by OracleDriver), but unlike a normal insert you do not get all features, convenience and performance (e.g. batch inserts and pre-compiled inserts). The downside is that this can’t be precompiled (but compiling a simple expression of a few scalar values should be relatively cheap) and you can’t just insert a mapped cased class that way without some extra mapping boilerplate.
Another option would be to first get a new id (or even multiple ids for a batch insert) from the sequence with one query, put them into the data transfer objects, and then insert those normally with the ids in place. This requires one extra query per batch (for first fetching the ids) but you can easily use mapped objects and precompile everything.

Handling auto inc columns in Oracle via slick

1 Answers