sequel-sequence

Adds a useful interface for PostgreSQL and MariaDB SEQUENCE on Sequel migrations. This Gem includes functionality to meet the needs of MySQL and SQLite users as well.

Installation

gem install sequel-sequence

Or add the following line to your project's Gemfile:

gem 'sequel-sequence'

Usage with PostgreSQL and MariaDB

To create and delete a SEQUENCE, simply use the create_sequence and drop_sequence methods.

Sequel.migration do
  up do
    create_sequence :position, if_exists: false
  end

  down do
    drop_sequence :position, if_exists: true
  end
end

It would also be correct to write:

Sequel.migration do
  up do
    create_sequence :position
  end

  down do
    drop_sequence :position
  end
end

You can also specify the following optional parameters: if_exists – a condition of acceptability; start – an initial value; increment or step – step size to the next auto incrementing value:

create_sequence :position, increment: 2
create_sequence :position, step: 2
create_sequence :position, start: 100
create_sequence :position, if_exists: false

The increment and step parameters have the same meaning. By default their values are 1. The default value of start is 1 as well.

To define a column that has a sequence as its default value, use something like the following:

Sequel.migration do
  change do
    create_sequence :position_id, if_exists: false, start: 1000

    create_table(:things) do
      primary_key :id
      String :name, text: true

      # PostgreSQL uses bigint as the sequence's default type.
      Bignum :position

      Time :created_at, null: false
      Time :updated_at, null: false
    end

    set_column_default_nextval :things, :position, :position_id
  end
end

Before running the migration for your application, don't forget to invoke requires, for example like this:

require 'sequel'
require 'sequel-sequence'

migrate = -> (env, version) do
  ...
  Sequel::Migrator.apply(DB, 'db/migrations', version)
end

This gem also adds a few helpers to interact with SEQUENCEs.

DB = Sequel.connect('...')
# Advance sequence and return new value
DB.nextval("position")

# Return value most recently obtained with nextval for specified sequence, either
DB.currval("position")
# or
DB.lastval("position")
# Both options are acceptable in PostgreSQL and MySQL.

# Set a new sequence value. It must be greater than lastval or currval. Only PostgreSQL allows setting a lower value.
DB.setval("position", 1234)

Usage with SQLite and MySQL

The sequence functionality for SQLite or MySQL databases is implemented by registering tables in the database with a primary key of id and an additional integer field fiction.

CREATE TABLE `name_of_your_sequence_table`
(id integer primary key autoincrement, fiction integer);

You might utilize the last field fiction as a numeric label to collect statistics on the operation of the end-to-end counter "name_of_your_sequence_table".id within the application.

create_sequence :position, if_exists: false, start: 1000, numeric_label: 1

and

DB.nextval_with_label(:position, 1)

By default, fiction has a zero value. Moreover, it is assumed that you can use the history of sequence changes, for example, to collect statistics on the fiction field. However, in most cases, such statistics will not be necessary and you can program periodic cleaning of the SEQUENCE table using the method:

DB.delete_to_currval(:position)

Otherwise, the operation of this gem for SQLite and MySQL is similar to the ways of using Sequence in more advanced RDBMS. There is only one difference here, you won't be able to change the increment value from 1 to another using the increment or step parameter.

Known issues you may encounter

This solution does not allow you to simultaneously work with MySQL and MariaDB databases from one application. If such a need arises, move the data processing functionality to different microservices.
When you start with a new database in SQLite, you'll receive an error message - "SQLite3::SQLException: no such table: sqlite_sequence". sqlite_sequence table is not created, until you define at least one autoincrement and primary key column in your schema.

All methods defined in this gem can use either a String or a Symbol parameter to denote a SEQUENCE.

DB.nextval('position')

is equivalent to

DB.nextval(:position)

This solution allows you to specify advanced options for a SEQUENCE when creating it in PostgreSQL and MariaDB. For more information, check out the description at https://www.postgresql.org/docs/current/sql-createsequence.html and https://mariadb.com/kb/en/create-sequence.

Additional handy methods:

To discover a database information about SEQUENCEs you could take advantage of check_sequences and custom_sequence? methods.

custom_sequence?(:sequence_name) allows you to instantly find out the availability of the called SEQUENCE.
check_sequences provides complete information about known SEQUENCEs in the datebase. The output data depends on RDBMS.

To remove several sequences at once, you can use the method:

drop_sequence? can accept multiple arguments of SEQUENCEs and checks the IF EXISTS condition for each one.

To drop previous SEQUENCE and recreate the new one utilize the method:

create_sequence!.

Maintainer

Nikolai Bocharov

Contributors

https://github.com/oreol-group/sequel-sequence/contributors

Contributing

For more details about how to contribute, please read https://github.com/oreol-group/sequel-sequence/blob/master/CONTRIBUTING.md.

License

The gem is available as open source under the terms of the MIT License. A copy of the license can be found at https://github.com/oreol-group/sequel-sequence/blob/master/LICENSE.md.

Code of Conduct

Everyone interacting in the sequel-sequence project's codebases, issue trackers, chat rooms and mailing lists is expected to follow the code of conduct.