Horizontal & vertical scaling package (@typed-query-builder/scale)

[ClickerMonkey]

This package will introduce a layer that appears to be a single database but behind the scenes actually communicates with any number of database. Similar to how the pgsql/mssql packages have a createDatabase function this package will have one that returns a Database object.

Features:

• Horizontal & vertical scaling
• Horizontal scaling via sharding, meaning an algorithm can take a query parameter and tell you which databases you can look in to find that data.
• Vertical scaling via specifying read & write databases.
• Seamless use, if a query is analyzed and it reaches across databases it will run it on each one and then bring it together and process the results into a single result. Ideally all queries resolve to a single database, but sometimes that's unavailable. The package will communicate what databases will be required.
• The package can have a dynamic list of databases, so as the user scales up they don't need to restart their server with updated code.

Examples:

import { createScaleDatabase, WriteStrategy, ReadStrategy, DatabaseStrategy, Shard } from '@typed-query-builder/scale';
import { createDatabase } from '@typed-query-builder/pgsql';

// Vertical scaling with one write and multiple reads
const db = createScaleDatabase({
  databaseStrategy: DatabaseStrategy.DYNAMIC, // CACHED
  getDatabase: async (name: string) => createDatabase(/* connection */),
  databases: {}, // alternative to getDatabase, specify a fixed map of databases by their name

  writeStrategy: WriteStrategy.ALL, // ROUND_ROBIN, LEAST_USED, ALL, or a function
  getWrites: async (meta) => ['main_database'],
  writes: [], // alternative to getWrites, specify a fixed array of write databases
  writesByTable: {}, // map of which database to write to based on the table.

  readStrategy: ReadStrategy.ROUND_ROBIN, // LEAST_USED, or a function
  getReads: async(meta) => ['read_1', 'read_2', 'read_3'],
  reads: [], // alternative to getReads, specify a fixed array of read databases
  readsByTable: {}, map of which database to read from based on the table
});

// Horizontal scaling
const db = createScaleDatabase({
  databases: {
     shard_1: createDatabase(/* connection */),
     shard_2: createDatabase(/* connection */),
     shard_3: createDatabase(/* connection */),
   }, 
   // generates reading and writing options defined in the vertical scaling example. may need to rethink design.
  shard: {
    user: Shard.byRange({
       column: 'user_id',
       ranges: async () => ({
         shard_1: [1, 10000],
         shard_2: [10001, 20000],
         shard_3: [20000, 100000000],
       }),
       insert: async (props) => 'shard_3',
    }),
  },
});

[ClickerMonkey]

Thoughts:
Updates in one shard may require the record be moved to another.