How to customize database table for nakama

Local Setup
1

Hi there,

Would you give me the better way how to customize database table? This is because I need to add a custom table for recording users’ logs. Currently, I use Lua modules to add business logic. But I think Lua is not a good way to create a custom table when using sql_exec method from Nakama. Or do I only use migrate way to create a custom table in Nakama?

Thanks

2

Can you tell me in more details what information you’d like to persist to the database, and at what point, and how you’d like to read this information back?

3

Hi Mofirouz,

Thank you for your prompt reply. I want to record information that clients receive data. This is because our clients need to replay all of their behaviors after they leave match. And I would like to use sql_query way to read this information back when I use Lua modules.

Thanks

4

@Kobe-Wang You can create custom database tables although we cannot help with the performance or maintenance of these tables.

I heavily recommend against changes to tables with the current Nakama schema. These tables are carefully crafted to take advantage of the design of the underlying storage engines (i.e. RocksDB, etc) inside Postgres and cockroachdb to store and retrieve data efficiently.

In general I’d recommend against new database tables unless they’re absolutely necessary. In your example wouldn’t it be easy to just create a storage collection called “match_moves” with each key as the match ID and store the replay the behaviours for the player on rejoin?

5

I’m an inexperienced user, so take my opinion with a big pinch of salt.

I plan to do something similar with my project in future - store some game data on a custom DB. Though I am thinking I will have another DB elsewhere, and write a module that uses the register_rpc function to communicate with it.

My other DB will be very different, like maybe a NoSQL like MongoDB or something, so I hadn’t even considered modifying the Nakama tables for my needs.

I think this is the relevant info in the docs.

But as I said I’m a newbie, so I may have misunderstood what is possible, and got too excited when I read “Server to server”.

6

@petergordon87 I’m curious what would you store in a separate database when Nakama already has a storage engine API for all sorts of different game data you’ll need to store.

https://heroiclabs.com/docs/storage-collections/

store some game data on a custom DB

Let me know what access patterns (k/v, filter, listings, sort, etc) you expect for the data and what read/write IOPS you’d want. Does the data you’re going to interface with already exist somewhere else? If so what’s the rough structure of a single row/object?

7

@novabyte Actually I already understood table about storage collection in Nakama, but I still need different extra columns to fulfill my requirement when I use storage collection to store the replay behaviors. To be more specific, I need extra four different INT type columns to store data when I use current storage table structure. Therefore, I want to create different table for fitting my business requirement and avoid modifying current storage table structure.

If I want to create new table to do this one, do you suggest that I should choose use Lua to create table or use migration files way to create table?

Thanks

8

@Kobe-Wang I’m not sure why the storage of “extra four different INT type columns” would require a custom table. A storage object with four integer fields would just look like:

{ "int1": 0, "int2": 0, "int3": 0, "int4": 0 }

You could key it on whatever you want (maybe match_id?) and store it in a collection called “match_history” or whatever you wanted to name it.

do you suggest that I should choose use Lua to create table or use migration files way to create table?

This depends a lot on how you want to maintain and integrate the server setup and development workflow with the operational process to run it on cloud somewhere.

If you’re completely set on the use of custom tables I’d suggest you at least keep the process simple for now and use a run_once function to execute what you need at server startup. i.e.

local nk = require("nakama")
nk.run_once(function(context)
    nk.logger_info("Server startup initiated")
    local parameters = {}
    nk.sql_exec([[
CREATE TABLE IF NOT EXISTS some_table (
    PRIMARY KEY (someint1, someint2, someint3, someint4),

    someint1 INT DEFAULT 0 NOT NULL,
    someint2 INT DEFAULT 0 NOT NULL,
    someint3 INT DEFAULT 0 NOT NULL,
    someint4 INT DEFAULT 0 NOT NULL
)
    ]], parameters)
    nk.logger_info("Server startup complete")
end)

You can always change/update this in the future and use a migration framework to manage your custom schema changes.

9

@novabyte I would like to separate different columns to store four INT type columns because I need to use these columns to query different condition and response to clients, such as different games, different rounds, and so on. Also, these clients will be able to replay according to the different data.

Thanks