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

Question

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?

BentCoder · Answer 1 · 2021-01-17T21:24:22.863

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
}

score 1 · Answer 2 · answered Jan 17 '21 at 18:05

1

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.

answered Jan 17 '21 at 18:05

Alex Ott

80,552
8
87
132

I am aware of all these but the current Go library won't help with that because it either returns an error or no error without any possibility to analyse anything coming from query. – BentCoder Jan 17 '21 at 18:30
patch the Go library to behave correctly? ;-) Because most of other solutions would have the same problem with race condition... Other thing to look is to check the specific error that is returned - I suspect that for insert failure there will be the separate error instance, that is different from the NoHostAvailable, or Timeout... – Alex Ott Jan 17 '21 at 19:20
Yes, you're right. gocql implements LWTs with `ScanCAS()` -- https://godoc.org/github.com/gocql/gocql#Query.ScanCAS. :) – Erick Ramirez Jan 17 '21 at 22:07

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

2 Answers2