Feature request for `NOCACHE` support for Oracle sequences

[code-ape]

Environment

Knex version: 0.95.3 Database + version: Oracle 18c OS: Ubuntu

Tagging @atiertant for Oracle support issue.

Feature discussion / request

Need

I recently stumbled across the fun fact that Oracle "caches" sequences where an insert operation reserves a default range of 20. Thus if you insert 3 values with an auto incrementing primary key then they get id's 1, 2, and 3. But the next insert gets id 21. I'd like to have some way of disabling this, so that we can have more a consistent experience across using multiple databases with Knex.

Suggestion

I would think offering this as an option for increments would be an easy additional configuration option. To fully support the feature it could be a non-negative integer with 0 disabling cache.

knex.schema.createTable('users', function (table) {
  table.increments('id', { cache: 0 });
});

Resulting SQL:

CREATE TABLE users (
  id LONG GENERATED BY DEFAULT AS IDENTITY (
      -- sequence options
      START WITH 1
      INCREMENT BY 1 
      NOCYCLE 
      NOCACHE
  ),
  PRIMARY KEY (id)
);

Additional thoughts

I know that this is a database specific feature, which I would normally want to do in knex.raw(...) but for something like this doing a raw statement would require custom writing the entire sequence and part of the table creation which seems to break the paradigm of Knex.

[kibertoad]

@code-ape What kind of SQL would you expect to be generated when this parameter is passed?

[code-ape]

@kibertoad I would think something like this (also added to issue description above). I'm not an oracle guru so feel free to suggest improvements if something is missing.

CREATE TABLE users (
  id LONG GENERATED BY DEFAULT AS IDENTITY (
      -- sequence options
      START WITH 1
      INCREMENT BY 1 
      NOCYCLE 
      NOCACHE
  ),
  PRIMARY KEY (id)
);

[i18nsite]

tidb CREATE TABLE t(a int AUTO_INCREMENT key) AUTO_ID_CACHE 1;

https://docs.pingcap.com/tidb/stable/auto-increment

how to use in DB.schema.createTable