How to get the unique_rowid() primary key from sql_exec?

Runtime Framework
,
1

After add row in CockroachDB through nk.sql_exec, How to get the unique_rowid() primary key?

My CockroachDB table:

CREATE TABLE public.test (
id INT8 NOT NULL,
user_id UUID NOT NULL,
CONSTRAINT test_pk PRIMARY KEY (id ASC),
FAMILY “primary” (id)
);
ALTER TABLE public.test ADD id int8 NOT NULL DEFAULT unique_rowid();

My Lua code:

local query = [[INSERT INTO test (user_id)
VALUES ($1);]]
local parameters = {json.user_id, json.name, json.description}
local status, error = pcall(nk.sql_exec, query, parameters)

How to get db generated column id?

2

Welcome @jason88.

Before jumping to the solution, can you elaborate on your usecase? What’s the motivation behind creating a custom table?

Note that your question is more related to the database usage and that CockroachDB’s forum is the proper place to ask it.

3

Thank you for replying.
I want to make an inventory system. It is a complex system and I think it is better to create a custom table.
I use lua “nk.sql_exec” to insert the table. It is a nakama function. I look at the “runtime_lua_nakama.go” source code and it only returns success code. If I use GO module to insert table, can I get the generated column id?

P.s. What is the advantage & disadvantage of creating a custom table? In which case I should create a custom table?

4

Regarding the id question, the nk.sql_exec is a Nakama function but notice that you pass a SQL query to it (INSERT INTO test (user_id) VALUES ($1);) that defines the output of the operation. For you to obtain the id or any piece of data, databases support a RETURING clausule for inserts. Please check this documentation for more information INSERT | CockroachDB Docs.

Using custom tables and queries has the disadvantage that you will be on your own since it’s logic outside of the general Nakama domain. This path isn’t recommended, unless Nakama is lacking the functionality you require.