auto-generated-primary-key

Sample - Auto-generated Primary Keys

This example shows how to use an IDENTITY column to generate the primary key of a model.

See https://cloud.google.com/spanner/docs/primary-key-default-value#identity-columns for more information about IDENTITY columns in Cloud Spanner.

Requirements

Using IDENTITY for generating primary key values in ActiveRecord has the following requirements:

1. You must use ActiveRecord version 7.1 or higher.
2. Your database configuration must include a default_sequence_kind value like this:

development:
  adapter: spanner
  emulator_host: localhost:9010
  project: test-project
  instance: test-instance
  database: testdb
  default_sequence_kind: BIT_REVERSED_POSITIVE
  pool: 5
  timeout: 5000
  schema_dump: false

Creating Tables with IDENTITY in ActiveRecord

You can create an IDENTITY column using migrations in ActiveRecord by using the :primary_key type for the column. Note that this only generates an IDENTITY column if you have included the option default_sequence_kind: BIT_REVERSED_POSITIVE as shown above.

Any default id column that is added by ActiveRecord will also use the :primary_key type and use an IDENTITY column.

# The default `id` column that is added by ActiveRecord will automatically use
# an IDENTITY column.
create_table :singers do |t|
  t.string :first_name
  t.string :last_name
end

# You can also explicitly create a primary key column with a different name. Use
# the :primary_key type to generate an IDENTITY column.
create_table :singers, id: false, primary_key: :singerid do |t|
  # Use the ':primary_key' data type to create an auto-generated primary key column.
  t.column :singerid, :primary_key, primary_key: true, null: false
  t.string :first_name
  t.string :last_name
end

Example Data Model

This example uses the following table schema:

CREATE TABLE `singers` (
    `singerid` INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
    `first_name` STRING(MAX),
    `last_name` STRING(MAX)
) PRIMARY KEY (`singerid`)

CREATE TABLE `albums` (
    `singerid` INT64 NOT NULL,
    `albumid` INT64 NOT NULL GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE),
    `title` STRING(MAX)
) PRIMARY KEY (`singerid`, `albumid`), INTERLEAVE IN PARENT `singers`

This schema can be created in ActiveRecord 7.1 and later as follows:

# Execute the entire migration as one DDL batch.
connection.ddl_batch do
  create_table :singers, id: false, primary_key: :singerid do |t|
    # Use the ':primary_key' data type to create an auto-generated primary key column.
    t.column :singerid, :primary_key, primary_key: true, null: false
    t.string :first_name
    t.string :last_name
  end

  create_table :albums, primary_key: [:singerid, :albumid], id: false do |t|
    # Interleave the `albums` table in the parent table `singers`.
    t.interleave_in :singers
    t.integer :singerid, null: false
    # Use the ':primary_key' data type to create an auto-generated primary key column.
    t.column :albumid, :primary_key, null: false
    t.string :title
  end
end

Auto-generated Primary Keys and Mutations

Using primary keys that are auto-generated by an IDENTITY column is not supported in combination with mutations, because Spanner must return the generated primary key value after a row was inserted using a THEN RETURN clause. This is not supported for mutations.

A workaround for this is to explicitly set a value for the primary key of a row before inserting it:

ActiveRecord::Base.transaction isolation: :buffered_mutations do
  # Assign the singer a random id value. This value will be included in the insert mutation.
  singer = Singer.create id: SecureRandom.uuid.gsub("-", "").hex & 0x7FFFFFFFFFFFFFFF,
                         first_name: "Melissa", last_name: "Garcia"
end

You can also instruct the Spanner ActiveRecord provider to automatically include a primary key value when you use mutations. The primary key value assignment can then be omitted from your code. Add use_client_side_id_for_mutations: true to your configuration for this:

development:
  adapter: spanner
  emulator_host: localhost:9010
  project: test-project
  instance: test-instance
  database: testdb
  default_sequence_kind: BIT_REVERSED_POSITIVE
  use_client_side_id_for_mutations: true
  pool: 5
  timeout: 5000
  schema_dump: false

Running the Sample

The sample will automatically start a Spanner Emulator in a docker container and execute the sample against that emulator. The emulator will automatically be stopped when the application finishes.

Run the application with the following commands:

export AR_VERSION="~> 7.1.2"
bundle install
bundle exec rake run