Defining Model for CockroachDB

[vsumit89]

Your Question

I am using cockroach db with gorm.

type Base struct {
    ID          uint            `gorm:"primary_key" json:"id"`
    CreatedAt   time.Time       `json:"created_at"`
    UpdatedAt   time.Time       `json:"updated_at"`
    DeletedAt   *gorm.DeletedAt `sql:"index" json:"deleted_at" swaggertype:"primitive,string"`
    CreatedByID uint            `gorm:"column:created_by_id" json:"created_by_id"`
    UpdatedByID uint            `gorm:"column:updated_by_id" json:"updated_by_id"`
}

while using this model, when I create an instance the id of the object in the database start from some random 18-digit number. I want it to start from 1 and then autoincrementally, what should I do.

Expected answer

[alfiankan]

I encountered the same problem as yours initially, and I have solved mine by creating a sequence on the cockroachdb.

when i read the docs i found https://www.cockroachlabs.com/docs/stable/create-sequence.html

so far i know cockroachdb use random number to increase speed. because when we use incremental database (autoincrement) need to look at the last number of id then add one by one.

First I create a sequence. you can use SQL like this.

CREATE TEMP SEQUENCE base_table_seq MINVALUE 1 MAXVALUE 9000000000 INCREMENT 1 START 1

you can use any other min or max value. make it sure INCREMENT 1 START 1 will auto incrementing sequence by adding +1

then you can create table and use sequence as your primary key auto increment like this

CREATE TABLE  base_table (
    id INT DEFAULT nextval('base_table_seq'),
    another_column STRING
);

and GORM model like this (also work for migration):

type BaseTable struct {
    ID int `json:"id" gorm:"column:id;default:nextval('base_table_seq');primaryKey"`
    another_column string `json:"title" gorm:"column:another_column"`
}

i hope this will helps

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days

[southwolf]

@alfiankan Sorry for digging this old issue, but did the default:nextval('base_table_seq'); actually work by itself without the DDL setting default value if ID?

I'm trying to use it in PostgreSQL on an existing table when migrating a Java app to Go, in which the default value of ID is not set. The Sequence Generator was set in application level like this

@SequenceGenerator(name="my_generator", sequenceName = "my_seq")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_generator")

But when I try to create a token

type Token struct {
    TokenId            int64 `gorm:"column:token_id;default:nextval('my_seq');primaryKey"`
}

It does not generate TokenId automatically, but reports ERROR: null value in column "token_id" of relation "token" violates not-null constraint