llamanodes / web3-proxy

Fast loadbalancing and caching proxy for Ethereum or chains with similar JSON-RPC methods
https://llamanodes.com
GNU General Public License v3.0
154 stars 34 forks source link

database id column consistency #121

Open BlinkyStitt opened 1 year ago

BlinkyStitt commented 1 year ago

Current status

Some places we use "bigint unsigned" and others we use "int".

Option 1: Short term fix

  1. Use "bigint unsigned" for all the id columns. Keep exposing the database ids in the frontend.

Option 2: Medium term fix

  1. Use "bigint unsigned" for all the id columns
  2. Add a "uuid" column with an index to all the tables. We won't use it for joins (that can still use id), but we will use it from the frontend so that we don't expose database ids publicly.

Long term fix

  1. Use ULID or UUID columns for all the primary columns.

In the past, I had issues with getting sea-orm to use UUID primary key columns (since they can't be autoincrement and defaults were awkward). So I just gave up and used ints to unblock.

I think it's fine for us to generate a ULID in web3-proxy instead of relying on the database to default. But that doesn't help us with all the existing columns. This will likely require a multi-step migration:

  1. Add nullable uuid column and change code to set the uuid for new data
  2. Generate ULIDs for all the rows and set them
  3. remove nullable
  4. add indexes for uuid to mirror the indexes with id
  5. drop indexes that use any id
  6. drop the id columns
BlinkyStitt commented 1 year ago

re UUIDs: https://www.sea-ql.org/SeaORM/docs/next/generate-entity/entity-structure/#active-model-behavior

The official docs give an example of creating the UUID in an ActiveModel hook. I think we will want these hooks for created_at/updated_at. We could make our ULID at the same time.

BlinkyStitt commented 1 year ago

I noticed that .big_unsigned()(u64) is not the same as .big_int().unsigned()` (u32), so be careful of that too