[Feature] Database access

[endel]

Context & Description

Databases are such an important piece of any game/application.

Providing a "standard" way to use and consume databases would make it easier to not only re-use and share tiny bits of features between different projects but also provide a building block for database-related features the framework could offer out-of-the-box.

The objective

The objective here is to select a safe module we can build on top of.

Every developer has their favorite SQL builder/ORM (or detests it deeply 😂), and the one selected is (hopefully?) going to be used for years to come.

This new feature will inevitably spark a number of database-related issues and requests that may only be solvable by the original library we are building upon could fix. (There have been some sustainability issues with big ORM projects like Vincit/objection.js#2335, typeorm/typeorm#3267)

Use cases

• User authentication
• Persisting room state between sessions
• "Async games" where other players don't necessarily need to be online
• Tournament-style games
• Leaderboards
• (...more stuff!)

Proposal

Features I would love to have:

• ORM-like API
• Be frictionless during development: provide automatic migration when inserting data into the database (could be disabled in production)

So far I'm leaning towards kysely.
I'm curious to hear your thoughts!

---

(Linked issues: #425, #177)

[dsarfati]

I like the idea of having an abstraction layer around saving Colyseus specific where that we could plug in different DB options depending on needs, but making a general DB API feels like focusing on the wrong thing. I would rather have more features or stability than an API that is already covered by 100s-1000s of different projects.

Out of your list of examples, having a way to simply configure which DB provider saves game state would be amazing. This way you aren't choosing a DB for everyone, the community could build different state persistent plugins to support whichever DB tech best fits the needs of their game. This also would also simplify the lives for developers since they could deploy a single DB tech stack and not need to deploy a different DB just to support a feature of Colyseus.

I also think that having different auth provider plugins would be a big win instead of needing to roll your own implementation each time. Especially with OAuth workflows being so well documented and supported it might help developers quickly add auth to their games.

[SuperLanceur]

Is the goal to integrate this feature directly into Colyseus, or as a package like @colyseus/monitor and @colyseus/loadtest ? I agree that some kind of DB interface would be useful, but I feel like it would be better as an optional package if it has to be opinionated.

[davidhernandeze]

I would suggest to try with a no SQL solution. That will allow users to iterate quickly on their data models without the need to write painful migrations. Also I can imagine the queries involved in multiplayer games are simple and don't require complicated SQL features.

[endel]

I like the idea of having an abstraction layer around saving Colyseus specific where that we could plug in different DB options depending on needs, but making a general DB API feels like focusing on the wrong thing. I would rather have more features or stability than an API that is already covered by 100s-1000s of different projects.

Designing and maintaining a general-purpose database API is not the objective indeed! I'm thinking of creating a thin layer on top of a good module such as kysely, and let the module do the hard work, while allowing the user to choose his preferred database (though, Kysely is only for SQL databases. NoSQL couldn't be an option there.)

@koskimas shared his views on building an ORM in the past. (@koskimas is the author of Objection.js and now the author of Kysely)

I would suggest to try with a no SQL solution. That will allow users to iterate quickly on their data models without the need to write painful migrations.

Most ORMs out there only support MongoDB (when they do). I have been observing on Twitter and other places a switch back from NoSQL to relational databases again. The tradeoffs of NoSQL don't seem to justify anymore, but it's a matter of preference of course.

I believe it is possible to provide a NoSQL experience using a relational database.

---

To summarize my thoughts, with a thin layer on top of Kysely in mind, I think what would need to be built would be:

• A "Model" class that does need migrations
• A "Collection" class with optional migrations
• A CLI tool for managing migrations
• A CLI tool for seeding/populating the database with test data

[jog1t]

@endel have you seen https://github.com/drizzle-team/drizzle-orm? It's a cool ORM on top of Zod. Zod could be handy in declaring schemas for rooms in the future 👀

[endel]

Hi @jog1t, sorry forgot to give feedback about Drizzle, my concern with it is about sustainability, the project started last year, and building a full ORM is no easy feat. I'm afraid they may stop its development at any time.

Even though Drizzle has almost twice as many stars as Kysely on GitHub, it doesn't seem to have as much adoption so far:

---

EDIT: I see that their GitHub Sponsors have quite a few people supporting it, including @oyed who is a member here. I'll give Drizzle a try as well!

[endel]

(@jog1t edited my comment! 👀 )

[oyed]

I can vouch for Drizzle, and the team behind it. In fact I built a (pretty shoddy) Colyseus Driver that uses my Drizzle schema for rooms :)

[agmass]

If this comes out it'll finally give me the motivation to make some sort of account system i've been procrastinating

[dsarfati]

One of the best examples of a high level abstract to solve basic persistence is Orleans. They offer a persistence layer that can be called from code with simple methods such as SaveState(). This allows you to plug in different storage providers (AWS, GCP, Azure, Mongo...) and not change your code, if you need to use some specific features of a specific storage provider you can simply use the DB client instead of the batteries included SaveState().

[vitalyrotari]

Hey @endel take a look also to sqlx-ts ;)

[hunkydoryrepair]

IMO, this is out of scope. I'd be less likely to choose Colyseus from the beginning if it bound us to a particular database library, or limited the choices there.

The one area I think it might help to consider how databases are used is in the Schema area.
Using a NoSQL db, the Schema classes often have to be converted into plain javascript objects, so we end up doing this a lot:

JSON.parse(JSON.stringify(schemaobject))

Not only for database, but we also do this a lot on client to get a "regular" object we can serialize.
It might be useful to get the Schema class to have a `toObject, that works like toJSON, but just gives a regular object and not stringified, and we could skip all the excess JSON parsing to get back to a regular object. However, this is a pretty minor inconvenience.

[endel]

Thank you for the input, everybody!

I've had some progress with this idea recently and shared a private @colyseus/database repository with you all, it is still quite early, but the idea is there - feedback is greatly appreciated!

Just to be clear, this lib is supposed to be optional, if you'd like to use a different solution that's absolutely fine! This is meant to speed up development and have a "default" solution for anyone who doesn't want to make that decision themselves.

No CLI has been implemented yet, this is a very good reference of what would be nice to have IMO: https://github.com/acro5piano/kysely-migration-cli (kinda Laravel / Rails style)

If you'd like to contribute please let me know, I can give you write access 🙏

[endel]

Having some progress with the migration CLI (npx migration <command>)

• Consider the end-user would have its database configuration file located by default at ./src/config/database.ts
• When interacting with the migrations CLI, the ./src/config/database.ts would be loaded by default. A different path could be provided via --db ./path/to/config.ts
• End-user would run npx migration make create_leaderboard_table to scaffold the up/down Kysely migration file

The commands planned to be implemented on the initial release are:

$ npx migration --help
Usage: migration [options] [command]

Commands:
  make [options] <name>  Create a new migration file
  migrate [options]      Run database migrations.
  rollback [options]     Rollback the latest migration.
  status [options]       Run database migrations.
  help [command]         display help for command

---

How npx migration migrate would look like:

$ npx migration migrate --help
Usage: migration migrate [options]

Run database migrations.

Options:
  --db <path>    Path of database config file. (default: "./src/config/db")
  --step <n>     Migrate only a number of steps
  --refresh <n>  Rollback all migrations and re-run
  --pretend      See the SQL statements that will be executed by the migrations without actually running them
  --force        To force the commands to run without a prompt
  -h, --help     display help for command

[fliptrail]

Is it possible for me to get read-only access to @colyseus/database? I wanted to experiment with it.

[endel]

Hi @fliptrail, sure, just gave you read access, please feel free to give feedback and/or provide suggestions here!

[endel]

Thanks for the input and interest of everybody in this thread. The landscape of TypeScript database tooling is evolving fast, and as @dsarfati pointed out, this is starting to feel like investing time in the wrong thing.

I will pause development on @colyseus/database for now to focus on other things - let's evaluate next year if it makes sense to revisit it. I'd love to ship Colyseus 1.0 with a database solution out of the box, so new users don't feel overwhelmed by taking that decision themselves.

---

The @colyseus/auth module released recently is database-agnostic. If we're to implement other modules like this for tournaments, leaderboards, etc, they should probably follow the same philosophy.

I've added a new page to the docs: "Database & Persistance" listing some recommended tools for database access. Feel free to contribute to that page if you'd like to! :)

[Shaunmax]

in the mean time can we use Firebase Database or Firestore with Colyseus ?