Change DB Client to a more dynamic alternative Kysely

[Adammatthiesen]

Body

Moved to Stage 2: #8

Summary

I propose we move to a new db client that could offer us a unified client that will work properly across different db dialects

Background & Motivation

Currently we rely on AstroDB and drizzle-orm, which in their current configuration has some minor issues, but even once it technically supports multiple dialects there would be the issue of slight difference in the resulting drizzle client between dialects. This will make it difficult to properly support AstroDB once user's will be able to swap out their driver (since each table would need a different schema based on the current dialect).

Kysely on the other-hand, has support for all the same db dialects but with a single unified interface to work with that supports many different dialects with easy switching during initialization. Kysely also has a fairly similar interface but without all the extra helper imports needed to build a db query.

Goals

• Introduce a new @withstudiocms scoped package that will include:
  • Kysely interfaces
  • shared public DB types and helpers
  • Migration and schema utilities
  • Utilize Effect for basic operations
• Implement CLI to manage migrations/db initialization in StudioCMS
  • Pull migrations and schemas from shared package
• Implement DB Client and replace drizzle-orm in StudioCMS SDK and CLI
  • Utilize utils & types from shared package
  • Will allow us to overhaul the current SDK and replace it with a even more dynamic system

Bonus

• We could support more than just sqlite/libsql quickly
• If this goes, we would be able to skip a bunch of steps towards leaving beta versioning.

Additional info

• Kysely docs: https://kysely.dev/docs/getting-started
• Example integration with Astro: https://github.com/Adammatthiesen/kysely-astro

[Adammatthiesen]

Early stages of this project have been in progress here: withstudiocms/studiocms#963

[Adammatthiesen]

Moved to Stage 2: #8

[Adammatthiesen]

Moved to Stage 3: #9