Idea: Use drizzle-orm and expose `select()` API on the client

[gmaclennan]

drizzle-orm is a lightweight wrapper around better-sqlite that provides a typesafe interface, friendly API, and manages migrations / table creation SQL.

Using drizzle-orm internally might help us write type-safe code. We should be able to generate drizzle sql schema declarations from the JSONSchema in @mapeo/schema more easily than trying to write SQL CREATE TABLE statements ourselves. We don't need to expose drizzle to the client for this.

There could also be an advantage to exposing some of the drizzle API to the client, namely db.select(). This would provide a more powerful interface for the client for reading data, vs. the existing proposed datatype API. It would be an alternative for the getByDocId() and getMany() methods. We would not be able to expose the db.insert() method from drizzle, because writing data in Mapeo needs to do much more than just read SQL tables, and for getByVersionId(), we are not reading from SQL tables because those only contain the most recent version.

Whether it is worth doing the latter depends on whether this would provide a better interface for the front-end team? One clear advantage I can think of is being able to write more complex queries without needing to wait for the backend to add indexes and extra methods to support the query, and it avoids us needing to write our own query API.

To implement db.select() on the client we would not be able to use rpc-reflector, because it is a chained method which doesn't really work over RPC (because each step in the chain creates an object that needs to be garbage collected, but the server does not know when the client no longer references each step - see https://github.com/digidem/rpc-reflector/pull/7). However we could implement db.select() on the client using the drizzle custom proxy driver, which serializes the result of something like db.select().from(users).where(and(eq(users.id, 42), eq(users.name, 'Dan'))).all(); to an object that can be passed over RPC with a raw sql statement that can be executed on the server.

[achou11]

generally open to this idea! re: client - especially for the near future, the flexibility can be pretty powerful and can even inform what methods the client should expose if we decide to move away from it (i.e. go back to what's currently spec'd).

will also note that I'd like to be cautious about adding another dependency, especially one that dictates how this api gets shaped. more of a general concern and maybe not applicable in this case, but something to point out as I've personally been forming the opinion that deps are Evil™️ 😄

[gmaclennan]

Yes, agreed about the addition of more deps. I see this as a complimentary API, rather than a replacement for the current API idea. e.g. we would still have mapeo.observation.getByDocId(), but we have a top-level mapeo.select() for mode powerful queries. The two methods getByDocId() and getMany() would be duplicates of using select() (could switch to using select() internally if we want), but the other methods would not use anything Drizzly.

[achou11]

ah got it! then definitely open to exploring this 👍