Pluggable database backends for Astro DB

[Fryuni]

Body

Summary

This proposal defines an interface (DatabaseBackend) allowing third-party integrations to provide alternative database backends for Astro DB. This decouples Astro DB's schema management, migration tooling, and ORM integration from its current default LibSQL implementation, enabling users to leverage various database providers while benefiting from the Astro DB developer experience.

Background & Motivation

Astro DB currently integrates tightly with LibSQL. While LibSQL provides a good default experience, its primary hosted offering comes from a single provider (Turso). Users wishing to use other managed database services (e.g., Neon, PlanetScale, Supabase, provider-specific offerings on Vercel/Netlify) or self-hosted databases other than LibSQL cannot leverage Astro DB's streamlined schema management and ORM features. They are forced to either use Turso, self-host LibSQL (which can be complex), or forgo Astro DB entirely.

Since the shutdown of Astro Studio (Astro's own hosted LibSQL service), Astro DB only supports open database connections. This presents a natural opportunity to fulfill a common community request: allowing users to bring their own database backend.

Decoupling the backend will:

• Allow users to integrate Astro DB with database providers they already use or prefer.
• Enable the use of specialized databases (e.g., vector, graph) within the Astro DB framework.
• Foster a richer ecosystem of database integrations for Astro.
• Provide a clear path forward after the Astro Studio shutdown, empowering the community to extend Astro DB's capabilities.

Goals

• Define a stable DatabaseBackend interface that third-party integrations can implement to provide database support for Astro DB.
• Ensure existing Astro DB projects using the default LibSQL backend continue to work without modification. Astro DB will ship with a default LibSQL adapter implementing the new interface.
• Allow Astro DB's core features (schema definition in db/config.ts, astro:db CLI commands for migrations, ORM generation via astro:db/runtime) to function correctly with any compliant backend adapter.
• Enable users to easily configure their desired database backend adapter via the astro.config.mjs file.

Non-Goals

• This RFC does not aim to provide implementations for specific database adapters (e.g., Postgres, MySQL). These would be separate community or official integrations.
• This proposal will not introduce changes to the existing Astro DB schema definition syntax or configuration (db/config.ts, db/seed.ts).
• No new ORM features will be added. The generated ORM client's capabilities will depend on the underlying technology chosen by the backend adapter (the default LibSQL adapter uses Drizzle ORM).
• Standardizing implementation details like connection pooling strategies, internal schema representations, or backend-specific configuration options across all adapters. Adapters are free to manage these aspects as appropriate for their specific database.

Example

User Configuration (astro.config.mjs)

Users would select and configure their desired backend within the astroDB integration options:

import { defineConfig } from "astro/config";
import astroDb from "astro:db";
// Assuming 'astro-db-adapter-postgres' provides a PostgresBackend class/factory
import { postgresBackend } from "astro-db-adapter-postgres";

export default defineConfig({
  integrations: [
    astroDb({
      // Pass an instance or configuration for the desired backend adapter
      backend: postgresBackend({
        // Backend-specific configuration options
        connectionString: process.env.POSTGRES_URL,
        // ... other options like pooling settings, etc.
      }),
    }),
  ],
});

Implementation Details / Open Questions

A core part of this RFC is defining the DatabaseBackend interface. I have considered two approaches as viable so far.

The names of method and types are entirely up for discussion.

Option 1: Simplified Interface

The adapter is responsible for the high-level logic of applying schema differences.

export interface DatabaseBackend {
  /**
   * Applies the given schema difference to the database.
   * Should handle creation, alteration, and dropping of tables based on the diff.
   *
   * If possible, implementations should apply all changes to the schema and save the new snapshot in a single atomic unit.
   */
  applySchema(options: {
    /**
     * Whether data loss is allowed when applying the schema difference.
     * If false, the operation should fail if any part of the diff would result in data loss.
     */
    allowsDataLoss: boolean;
    /** Tables to be created. */
    newTables: Record<string, ResolvedDBTable>;
    /** Tables to be altered, with their old and new definitions. */
    changedTables: Record<
      string,
      { oldTable: ResolvedDBTable; newTable: ResolvedDBTable }
    >;
    /** Tables to be dropped. */
    droppedTables: Record<string, ResolvedDBTable>;
    /**
     * The complete target schema snapshot.
     * This should be saved as the new latest snapshot if the migration succeeds.
     */
    snapshot: DBSnapshot;
  }): Promise<void>;

  /**
   * Detects whether the specified table changes can be applied without data loss.
   * Used by Astro DB to prompt the user before applying potentially destructive changes.
   */
  canAlterWithoutDataLoss(
    changedTables: Record<
      string,
      { oldTable: ResolvedDBTable; newTable: ResolvedDBTable }
    >,
  ): boolean;

  /**
   * Retrieves the latest schema snapshot stored in the database's snapshot table.
   * Returns null if no snapshot is found.
   */
  getLatestSnapshot(): Promise<DBSnapshot | null>;

  /**
   * Generates the source code for the `astro:db/runtime` virtual module.
   * This module exports the configured ORM client (`db`) and table schemas.
   * @param target Indicates whether the runtime is for local development/build or a remote deployment environment.
   */
  getDbExportModule(target: "local" | "remote"): string;

  /**
   * Generates the TypeScript declaration file (`.d.ts`) contents for the `astro:db/runtime` module.
   */
  getTypeDeclarations(): string;
}

• Pros: Less complex interface definition. Allows backends more flexibility and potential for database-specific optimizations in how schema changes are applied.
• Cons: Duplicates schema diffing logic across backend implementations. Higher burden on adapter authors. Might lead to inconsistencies in how different adapters interpret schema diffs.

Option 2: Granular Operations Interface

Astro DB retains the high-level logic for determining when to create, alter, or drop tables, and the adapter provides factories for generating the specific operations (e.g., SQL statements, API calls) for its database.

/**
 * Represents a database backend adapter.
 * @template Op - A type representing a single, backend-specific operation (e.g., a SQL string, an API call configuration).
 */
export interface DatabaseBackend<Op = unknown> {
  /**
   * Creates operations to drop the specified table if it exists.
   * Should ignore data loss risk.
   */
  getDropTableIfExistsOps(tableName: string): Op[];

  /**
   * Creates operations to create a new table according to the provided definition.
   */
  getCreateTableOps(tableName: string, table: ResolvedDBTable): Op[];

  /**
   * Creates operations to add, remove, or modify indexes on an existing table.
   */
  getChangeIndexOps(options: {
    tableName: string;
    oldIndexes?: ResolvedIndexes;
    newIndexes?: ResolvedIndexes;
  }): Op[];

  /**
   * Creates operations to completely recreate a table, potentially dropping it first.
   * Used for complex changes that cannot be handled by `getAlterTableOps`.
   */
  getRecreateTableOps(options: {
    tableName: string;
    newTable: ResolvedDBTable;
    addedColumns: Record<string, DBColumn>; // Columns present in newTable but not oldTable
    /** Whether the operation is allowed to proceed even if it causes data loss (e.g., dropping the table). */
    allowsDataLoss: boolean;
    migrateHiddenPrimaryKey: boolean;
  }): Op[];

  /**
   * Creates operations to modify an existing table by adding and/or dropping columns.
   * Assumes these alterations are safe based on prior checks (`canAlterTable...`).
   */
  getAlterTableOps(options: {
    tableName: string;
    added: Record<string, DBColumn>;
    dropped: Record<string, DBColumn>;
  }): Op[];

  /**
   * Checks if a column definition can be safely added to an existing table without requiring a full table recreate.
   */
  canAlterTableAddColumn(column: DBColumn): boolean;

  /**
   * Checks if a column can be safely dropped from an existing table without requiring a full table recreate.
   */
  canAlterTableDropColumn(column: DBColumn): boolean;

  /**
   * Creates operations to set up the internal table used by Astro DB to store schema snapshots.
   * Should be idempotent.
   */
  getCreateSnapshotRegistryOps(): Op[];

  /**
   * Creates operations to record a new schema snapshot in the database.
   */
  getStoreSnapshotOps(version: string, snapshot: DBSnapshot): Op[];

  /**
   * Retrieves the latest schema snapshot stored in the database's snapshot table.
   * Returns null if no snapshot is found.
   */
  getLatestSnapshot(): Promise<DBSnapshot | null>;

  /**
   * Executes a list of backend-specific operations, ideally atomically (if the backend supports transactions).
   * @param target Indicates the environment (local or remote) where operations should be executed.
   * @param ops An array of operations generated by other methods of this interface.
   */
  executeOps(target: "local" | "remote", ops: Op[]): Promise<void>;

  /**
   * Generates the source code for the `astro:db/runtime` virtual module.
   * This module exports the configured ORM client (`db`) and table schemas.
   * @param target Indicates whether the runtime is for local development/build or a remote deployment environment.
   */
  getDbExportModule(target: "local" | "remote"): string;

  /**
   * Generates the TypeScript declaration file (`.d.ts`) contents for the `astro:db/runtime` module.
   */
  getTypeDeclarations(): string;
}

• Pros: Centralizes schema migration logic within Astro DB, reducing duplication. Potentially easier for adapter authors as they focus on atomic operations. Clearer separation of concerns. Likely more consistent migration behavior across different backends.
• Cons: More complex interface definition. Might be less flexible for backends with non-standard migration approaches or highly optimized bulk operations. Requires Astro DB to correctly orchestrate operations for all potential backend types.

[Adammatthiesen]

No new ORM features will be added. The generated ORM client's capabilities will depend on the underlying technology chosen by the backend adapter (the default LibSQL adapter uses Drizzle ORM).

Is this implying support for swapping out the ORM? this seems dangerous for projects such as StudioCMS 😅 where we rely HEAVILY on the specific layout of the drizzle-orm client for operations in our SDK tools that we constructed.

Ensure existing Astro DB projects using the default LibSQL backend continue to work without modification. Astro DB will ship with a default LibSQL adapter implementing the new interface.

Is it also part of the goal to allow hot-swapping the database backend while maintaining functionality using the same functions without tweaking? (obviously talking about the more basic select, insert, batch type functions.

[Adammatthiesen]

Interesting... from my perspective, allowing backends to change the ORM layer feels like a leaky abstraction. ORMs are heavily opinionated and individuals have strong preferences, for sure, but I don't the benefit of a swappable DB layer if user-land code has to be rewritten when changing backends. Exposing full control of the ORM layer would render Astro DB useless as a primitive that external packages can build on top of, right?

This is my fear as well... that would make Astro DB more-less just a schema management/migration tool, and from a new user perspective could also make it seem more difficult to get into, since now they would need to be aware of what ORM is being used for each of their back-end adapters, for instance, lets say someone has a Astro DB adapter with another orm interface but try to use it with for example StudioCMS which built an SDK that wraps the current Astro DB included drizzle-orm methods, now if they don't know their adapter has a non drizzle interface (possibly due to lack of docs on that adapter), it could quickly become more of a pain... 😅

Astro DB Guides would also be interesting... all varying based on the writer of the article and the adapter they are choosing to use.

I agree that anything that means there'd be major differences in the userland code would lose a lot of the value. If we were to require the use of Drizzle, would that give us anything for free?

Drizzle ORM currently has a pretty good amount of database types that they do support so being limited to the use of drizzle would not be a horrible thing IMO. (MongoDB does not have support from drizzle so this would be a downside)

[Fryuni]

I agree that anything that means there'd be major differences in the userland code would lose a lot of the value. If we were to require the use of Drizzle, would that give us anything for free?

Drizzle ORM currently has a pretty good amount of database types that they do support so being limited to the use of drizzle would not be a horrible thing IMO. (MongoDB does not have support from drizzle so this would be a downside)

Not really. Drizzle already uses different types for each individual target database. It doesn't have a "Drizzle table" that you can pass to a "Drizzle database" it has "SQLite table" that you can pass to one of the "SQLite-like databases", and "Postgres table" that you can pass to the "Postgres database". The generated types will be different using Drizzle as well.

A user or integration code using db.select.distinctOn from Drizzle will not work and even fail during type check when using a backend that is not Postgres, for example.

Drizzle as an ORM is already non-portable, and it is so to allow for backing features that exist only on some databases to be available in order to not use only the lowest common feature set in terms of performance and semantic queries. Other ORMs have the lowest common feature set approach and prevent the users from using more optimal solutions in exchange for being far more portable.

The final feel of the user is the same for most things, but not everything, it is something called db that takes something called <table name> and provides a fluid API that somewhat resembles the structure of an SQL query. But the types involved are all different, to the point that if some library code (like StudioCMS) exported a function returning any of that it would have SqliteXXX or LibsqlXXX in the inferred name and would not work on any other Drizzle driver.

Since we already chose Drizzle that is not perfectly compatible between database backends as our ORM and it will require generating code and types differently between them, I thought it would be a very artificial limitation to not allow other mappings for things that don't have a Drizzle driver.

but I don't the benefit of a swappable DB layer if user-land code has to be rewritten when changing backends. Exposing full control of the ORM layer would render Astro DB useless as a primitive that external packages can build on top of, right?

We'd probably (not surely, as discussed) only provide Drizzle backends as first party. I think it is quite ok for a library to state that it requires "a Drizzle backend" just like like "requires Astro DB v0.99+" or anything of the like. And if someone wants to make a backend for Firebase or Neo4j or DGraph, more power to them. That would still allow the user to have the same data types and structure, and allows libraries to be written with the requirements "Astro DB with a Firebase backend" or "Astro DB with a Gremlin backend" (for graph DBs).

This is my fear as well... that would make Astro DB more-less just a schema management/migration tool, and from a new user perspective could also make it seem more difficult to get into, since now they would need to be aware of what ORM is being used for each of their back-end adapters, for instance, lets say someone has a Astro DB adapter with another orm interface but try to use it with for example StudioCMS which built an SDK that wraps the current Astro DB included drizzle-orm methods, now if they don't know their adapter has a non drizzle interface (possibly due to lack of docs on that adapter), it could quickly become more of a pain... 😅

I don't see how you could pick an adapter and not notice that it is using a different ORM unless it is the same ORM or you are not using your DB at all.

To reach the use case you said the user would have to:

1. Install a separate database backend
2. Read their docs for how to configure it
3. Change their own code to use the different ORM, clearly being distinct from the default first-party offering and our docs
4. Then want to install StudioCMS on the same project
5. Somehow not know that they used a different ORM after having migrated to it

The ORM change is not a minor side-effect the user wouldn't notice, it is a very conscious and active action by the user.

[ascorbic]

I'm less concerned about typings than the DX. Drizzle has shared docs for almost all parts of its API, and even if there are differences in some parts, the API is similar enough that people will feel comfortable. If we're returning db clients with completely different APIs I think it will get very confusing to people.

[Fryuni]

If we're returning db clients with completely different APIs I think it will get very confusing to people.

I agree, but my stance in that is that we always return the same (ish) API by only providing Drizzle. Preventing everyone else to implement and use something different is extra work for an artificial limitation.

Especially so when we'll already need to have per-backend code and type generation due to Drizzle not abstracting different backing DBs.

[OliverSpeir]

How does swapping the ORM reduce the value the integration offers? In my opinion swapping the ORM is very valuable if we allow swapping the DB

[florian-lefebvre]

Ideally I think it would be great if things like db urls could use loadEnv() outside of astro's context, and astro:env in an astro context. Not sure if that'd be something part of Astro DB impl or db adapters

[Fryuni]

Would be up to the implementation of the DB backend. Astro DB outside of the backend probably won't need any env var

[florian-lefebvre]

So at runtime, using astro:env would happen eg. in the module referenced by getDbExportModule

[Fryuni]

Yes

[florian-lefebvre]

Related: #1147

[OliverSpeir]

Astro DB will ship with a default LibSQL adapter implementing the new interface.

I wonder if it would make sense for it to ship with sqlite local file only by default as now there is no connection to turso

[Fryuni]

There is practically no code change to open a local file vs connecting to Turso, it is all LibSQL