= Schema Modifications with Sequel

In addition to its support for getting data from databases, Sequel has good support for modifying the schema of databases.  Unfortunately, the documentation from this is spread out in various places in the Sequel RDoc.

The recommended way to set up schema modifications in Sequel is through migrations. Migrations are modifications to the schema that handle modifying the schema in two directions, up and down, with down reversing the changes made by up. Migrations are stored in sequentially numbered files inside a directory (see the Sequel::Migrator documentation for file naming format details).  The migrations can be run on the database using the sequel command line tool (the -m and -M switches).

The format of the individual migration files themselves is explained in the Sequel::Migration documentation.  Each migration file contains a single migration class.  The migration class acts a proxy for the related database (given on the command line if the sequel command line tool is used, or by the db argument to Sequel::Migration#apply if the API is used). The methods that can be used inside Sequel::Migration#up or Sequel::Migration#down are just Sequel::Database instance methods, such as create_table, drop_table, and alter_table.  Most database methods that alter the schema take regular arguments, but create_table and alter_table take a block.  The methods you can use inside the create_table block are documented in Sequel::Schema::Generator, and the methods you can use inside the alter_table block are documented in Sequel::Schema::AlterTableGenerator.

Migrations are not required, you can just call the schema modification methods directly on the database object.  This is often done in test code and examples.  However, it is recommended that you use the migration framework unless the database schema will not be changing in the future, as it provides a way to easily handle modifications to existing database schema.

Sequel has the ability to have database independent migrations using ruby classes as types.  When you use a ruby class as a type, Sequel translates it to the most comparable type in the database you are using.  Here's an example using all supported types:

    DB.create_table(:cats) do
      primary_key :id, :type=>Integer # integer
      String :a                       # varchar(255)
      column :b, File                 # blob
      Fixnum :c                       # integer
      foreign_key :d, :other_table, :type=>Bignum # bigint
      Float :e                        # double precision
      BigDecimal :f                   # numeric
      Date :g                         # date
      DateTime :h                     # timestamp
      Time :i                         # timestamp
      Numeric :j                      # numeric
      TrueClass :k                    # boolean
      FalseClass :l                   # boolean
    end

Basically, if you use one of the ruby classes above, it will translate into a database specific type.  If you use a lowercase method, symbol, or string to specify the type, Sequel won't attempt to translate it.

Note that if you use a ruby class, you shouldn't use a :size option.  Using a ruby class means that you want Sequel to pick the database type to use.  If you want to specify the size, you are no longer in database independent territory, and you need to specify the type as well, using a lowercase method, symbol, or string.