Support for Google Cloud Spanner

[NOT-HAL9000]

[bweis]

https://cloud.google.com/spanner/docs/postgresql-interface

Hopefully interfacing with Spanner via their psql interface should be pretty simple?

[NOT-HAL9000]

There is some progress being made for connecting Spanner to Prisma. The learnings may be applicable here. prisma/prisma#717

[mailaneel]

I got a patch for migrations to work with spanner, for anyone who is interesting in using.

version: drizzle-orm@0.29.3
Issues:

1. Schemas are not supported with spanner
2. names starting with __ for tables is not supported
3. Serial for primary key is not supported
4. Transactions in DDL is not supported

diff --git a/node_modules/drizzle-orm/pg-core/dialect.js b/node_modules/drizzle-orm/pg-core/dialect.js
index 16455fd..bac53fd 100644
--- a/node_modules/drizzle-orm/pg-core/dialect.js
+++ b/node_modules/drizzle-orm/pg-core/dialect.js
@@ -26,30 +26,27 @@ class PgDialect {
   static [entityKind] = "PgDialect";
   async migrate(migrations, session) {
     const migrationTableCreate = sql`
-			CREATE TABLE IF NOT EXISTS "drizzle"."__drizzle_migrations" (
-				id SERIAL PRIMARY KEY,
+			CREATE TABLE IF NOT EXISTS "drizzle_migrations" (
+				id varchar(36) PRIMARY KEY DEFAULT spanner.generate_uuid(),
 				hash text NOT NULL,
 				created_at bigint
 			)
 		`;
-    await session.execute(sql`CREATE SCHEMA IF NOT EXISTS "drizzle"`);
     await session.execute(migrationTableCreate);
     const dbMigrations = await session.all(
-      sql`select id, hash, created_at from "drizzle"."__drizzle_migrations" order by created_at desc limit 1`
+      sql`select id, hash, created_at from "drizzle_migrations" order by created_at desc limit 1`
     );
     const lastDbMigration = dbMigrations[0];
-    await session.transaction(async (tx) => {
-      for await (const migration of migrations) {
-        if (!lastDbMigration || Number(lastDbMigration.created_at) < migration.folderMillis) {
-          for (const stmt of migration.sql) {
-            await tx.execute(sql.raw(stmt));
-          }
-          await tx.execute(
-            sql`insert into "drizzle"."__drizzle_migrations" ("hash", "created_at") values(${migration.hash}, ${migration.folderMillis})`
-          );
+    for await (const migration of migrations) {
+      if (!lastDbMigration || Number(lastDbMigration.created_at) < migration.folderMillis) {
+        for (const stmt of migration.sql) {
+          await session.execute(sql.raw(stmt));
         }
+        await session.execute(
+          sql`insert into "drizzle_migrations" ("hash", "created_at") values(${migration.hash}, ${migration.folderMillis})`
+        );
       }
-    });
+    }
   }
   escapeName(name) {
     return `"${name}"`;

[mailaneel]

Adding couple more issues found when using composite primary key and foreign keys

Issue: Constraint name is not supported for primary key

Constraint name is generated when using composite primary key, this not supported by spanner

Example:

Table

export const transactionTags = pgTable(
  'transaction_tags',
  {
    transactionId: varchar('transaction_id')
      .notNull()
      .references(() => transactions.id),
    tagId: varchar('tag_id')
      .notNull()
      .references(() => tags.id),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.transactionId, table.tagId] }),
  }),
);

Generated (Does not work for spanner)

CREATE TABLE IF NOT EXISTS "transaction_tags" (
	"transaction_id" varchar NOT NULL,
	"tag_id" varchar NOT NULL,
	CONSTRAINT "transaction_tags_transaction_id_tag_id_pk" PRIMARY KEY("transaction_id","tag_id")
);

Updated (Works for spanner)

CREATE TABLE IF NOT EXISTS "transaction_tags" (
	"transaction_id" varchar NOT NULL,
	"tag_id" varchar NOT NULL,
-	CONSTRAINT "transaction_tags_transaction_id_tag_id_pk" PRIMARY KEY("transaction_id","tag_id")
+   PRIMARY KEY("transaction_id","tag_id")
);

Issue: DO $$ BEGIN not supported

DO $$ BEGIN is generated when using foreign keys or indexes(TBC), this not supported by spanner

Example:

Table

export const transactionTags = pgTable(
  'transaction_tags',
  {
    transactionId: varchar('transaction_id')
      .notNull()
      .references(() => transactions.id),
    tagId: varchar('tag_id')
      .notNull()
      .references(() => tags.id),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.transactionId, table.tagId] }),
  }),
);

Generated (Does not work for spanner)

DO $$ BEGIN
 ALTER TABLE "transaction_tags" ADD CONSTRAINT "transaction_tags_transaction_id_transactions_id_fk" FOREIGN KEY ("transaction_id") REFERENCES "public"."transactions"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "transaction_tags" ADD CONSTRAINT "transaction_tags_tag_id_tags_id_fk" FOREIGN KEY ("tag_id") REFERENCES "public"."tags"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Updated (Works for spanner)

ALTER TABLE "transaction_tags" ADD CONSTRAINT "transaction_tags_transaction_id_transactions_id_fk" FOREIGN KEY ("transaction_id") REFERENCES "public"."transactions"("id") ON DELETE no action ON UPDATE no action;

ALTER TABLE "transaction_tags" ADD CONSTRAINT "transaction_tags_tag_id_tags_id_fk" FOREIGN KEY ("tag_id") REFERENCES "public"."tags"("id") ON DELETE no action ON UPDATE no action;

[Flux159]

After trying to use drizzle's postgres dialect w/ Spanner's postgres compatibility layer, I decided to get Gemini to write a drizzle inspired ORM that uses Google SQL for spanner compatibility instead of postgres dialect - https://github.com/Flux159/spanner-orm/

There were a few issues beyond what was mentioned by @mailaneel above - specifically the postgres compatibility for Spanner requires you to run pgadapter https://cloud.google.com/spanner/docs/pgadapter - a java server that translates your postgres calls for Spanner. However, standard postgres functions like ilike were not available through the compatibility later. In addition, newer features like Spanner's Graph language are not available when you choose the Postgres dialect for creating a database in Spanner. Instead of fighting the compatibility later, decided it was better to use Google SQL as the dialect (spanner-orm still supports postgres / pglite but just translates everything differently).

Chose not to make a dialect for drizzle because I couldn't find any docs on how to add a new dialect & didn't know if an AI model would be able to do it without extensive documentation of drizzle (Gemini was able to relatively easily make a new ORM though).

[jvliwanag]

Adding a few notes after my attempt.

1. On pk constraint naming - as long as the primary key constraint name follows the format: pk_<table_name>, the pgadapter layer allows it. Noted in liquibase notes.
2. Schemas are now supported on pgadapter. Not sure if it's a recent addition.
3. Drizzle now allows renaming migrations tables. Simpler workaround with spanner not supporting the default migration tables.