How to get generated Id after inserting row in Cassandra

Question

I am trying to insert insert some rows sharing a row-id and decided to stick with time-based uuids. All documentation i could find explained how to create such a row:

INSERT INTO users (id, name) VALUES (now(), 'Florian')

I'm using DataStax's cassandra-driver for Node.js to execute my queries (where insertUser is a string containing the query from above):

var r = await client.execute(insertUser)
console.dir(r.rows)

The result looks like this:

ResultSet {
  info:
   { queriedHost: '127.0.0.1:9042',
     triedHosts: { '127.0.0.1:9042': null },
     speculativeExecutions: 0,
     achievedConsistency: 10,
     traceId: undefined,
     warnings: undefined,
     customPayload: undefined,
     isSchemaInAgreement: true },
  rows: undefined,
  rowLength: undefined,
  columns: null,
  pageState: null,
  nextPage: undefined }

---

As we can see there is no id in the result which i could use to create dependent rows.

Is there a Cassandra-idiomatic way to create multiple rows depending on the same id without generating the id local?

Answer 1

You should provide it in your query parameter instead of relying on the CQL now() function (which returns an UUID v1).

const cassandra = require('cassandra-driver');
const Uuid = cassandra.types.Uuid;

// ...
const query = 'INSERT INTO users (id, name) VALUES (?, ?)';
const id = Uuid.random();
const options = { prepare: true, isIdempotent: true };
const result = await client.execute(query, [ id, 'Florian' ], options);

The added benefit of generating the id from the client side is that it makes your query idempotent.

The DataStax driver has a rich type system, you can check out the CQL types to JavaScript types representation in this table: https://docs.datastax.com/en/developer/nodejs-driver/latest/features/datatypes/

Answer 2

This is a question that's related to your application query path. Generally in the Cassandra data model you have a top down approach from how the user gets to one piece of information to the next, from one query to the next.

Your users table, after it's created, will need to be queried by that "id" column. If you are not sure about what you are setting it, how are you going to get it back?

Cassandra is a NoSQL database. It doesn't mean it's not-relational. It has relationships that you can enforce. If you don't generate your ID, or don't have it from before the only way to access that data later is to use a scan, which is not recommended.

Another approach would be to maybe do a MD5 of the "Florian" string. That MD5 string would be deterministic.

var input_name = "Florian";
var input_id = md5hash(input_name);

// interpolate the variables into a valid CQL using the values
var cql = "INSERT INTO users (id, name) VALUES ('"+input_id+"', '"+input_name+"');";

You could probably do that cleaner, but you get the picture.