Using Sequelize and SERIAL keys

General
#1

Hello - I was totally and completely blown away when i did a simple cut/paste and had this fantatic sequelize example working.

This particular Sequelize example force-creates an id column like so:

SHOW COLUMNS FROM accounts;
+-----------+-----------+-------+----------------+-----------+
|   Field   |   Type    | Null  |    Default     |  Indices  |
+-----------+-----------+-------+----------------+-----------+
| id        | INT       | false | NULL           | {primary} |

However, from reading this, cockroach has a superior way of handling unique primary keys using SERIAL UUID.

Would it be possible do an insert without having to specify the “id” column? i would really like to take advantage of the SERIAL UUID feature.

I took an uneducated guess and tried the following sequelize types (but none of these feeble attempts worked)

  • Sequelize.unique_rowid
  • Sequelize.uniquerowid
  • Sequelize.UNIQUE_ROWID
  • Sequelize.UNIQUEROWID

A possible workaround to be to generate my own UUID, but hey, this is cockroachdb after all !

and a special thank you to Cuong Do for creating the sequelize interface.

EDIT: in other words, i am able to do the following using the CLI:

create table accounts (id SERIAL PRIMARY KEY, balance int, createdAt timestamp, updatedAt timestamp ) ;
insert into accounts (balance) values ( 1234 ) ; ## we can conveniently omit the id column.

Notice i did not specify the “id” column, the geniuses at cockroachdb generate a UUID for me. i would like to do the same using Sequelize.

#2

GOT IT! whoo-hoo!

  1. create table (notice primary key definition, the syntax is a bit unusual for Mariadb people, but this primary key definition is far simpler to understand)

    CREATE TABLE accounts ( id SERIAL PRIMARY KEY ,
    balance FLOAT ,
    createdAt TIMESTAMP ,
    updatedAt TIMESTAMP
    ) ;

  2. add autoIncrement=true to model and change force: false to sync:

    var Sequelize = require(‘sequelize-cockroachdb’);

    // Connect to CockroachDB through Sequelize.
    var sequelize = new Sequelize(‘bank’, ‘maxroach’, ‘’, {
    dialect: ‘postgres’,
    host: ‘35.184.141.246’,
    port: 26258,
    logging: false
    });

    // Define the Account model for the “accounts” table.
    var Account = sequelize.define(‘accounts’, {
    id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement : true }, // added autoIncrement true
    balance: { type: Sequelize.INTEGER }
    });

    // Create the “accounts” table.
    Account.sync({force: false}).then(function() { // change force: true to false
    // Insert two rows into the “accounts” table.
    return Account.bulkCreate([
    //{id: 1, balance: 1000},
    {balance: 1000},
    //{id: 11,balance: 250}
    {balance: 250}
    ]);
    }).then(function() {
    // Retrieve accounts.
    return Account.findAll();
    }).then(function(accounts) {
    // Print out the balances.

1 Like