CODEWITHSUNDEEP
gocassandracqlcqlshgocql
BentCoder
BentCoder

Reputation: 12740

Checking the row existence before insertion using Cassandra within a Go application

I am using gocql with my Go application and trying to solve the issue described below.

CREATE TABLE IF NOT EXISTS website.users (
    id            uuid,
    email_address text,
    first_name    text,   
    last_name     text,   
    created_at    timestamp,
    PRIMARY KEY (email_address)
);

This query is going to override matching record which is Cassandra's expected behaviour.

INSERT INTO users (id, email_address, first_name, last_name, created_at)
VALUES (?, ?, ?, ?, ?)

In order to prevent overriding the existing record we can use IF NOT EXISTS at the end of the query.

INSERT INTO users (id, email_address, first_name, last_name, created_at)
VALUES (?, ?, ?, ?, ?)
IF NOT EXISTS

However, there is no way for me to know if the query affected any rows in DB or not. Somehow I need to return something like "Record exists" message back to caller but it is currently not possible. If there was something specific with session.Query(...).Exec() it would be useful but there isn't as far as I know.

I was thinking to SELECT by email_address before proceeding with INSERT if there was no matching record but as you can guess this is not feasible because by the time I INSERTed a new record after SELECT, some other operation could have INSERTed a new record with same email address.

How do we handle such scenario?

Upvotes: 1

Views: 1033

Answers (2)

BentCoder
BentCoder

Reputation: 12740

The solution is to use ScanCAS and the test case example from the library is here.

NOTE:

  • Order of the fields in ScanCAS() should match cqlsh> DESCRIBE keyspace.users; output for the CREATE TABLE ... block.
  • If you don't care about the scanned fields, prefer MapScanCAS instead.
func (r Repository) Insert(ctx context.Context, user User) error {
    var (
        emailAddressCAS, firstNameCAS, idCAS, lastNameCAS string
        createdAtCAS                                      time.Time
    )

    query := `
INSERT INTO users (email_address, created_at, first_name, id, last_name)
VALUES (?, ?, ?, ?, ?) IF NOT EXISTS
`

    applied, err := r.session.Query(
        query,
        user.EmailAddress,
        user.CreatedAt,
        user.FirstName,
        user.LastName,
        user.CreateAt,
    ).
    WithContext(ctx).
    ScanCAS(&emailAddressCAS, &createdAtCAS, &firstNameCAS, &idCAS, &lastNameCAS)

    if err != nil {
        return err
    }
    if !applied {
        // Check CAS vars here if you want.
        return // your custom error implying a duplication
    }

    return nil
}

Upvotes: 2

Alex Ott
Alex Ott

Reputation: 87244

If you're using INSERT with IF NOT EXISTS, then in contrast to "normal" inserts that doesn't return anything, such query returns a single row result consisting of:

  • field with name [applied], and true value - if there was no record before, and new row was inserted
  • field with name [applied], and false value + all columns of existing row.

So you just need to get result of your insert query, and analyze it. See documentation for more details.

Upvotes: 2

Related Questions