Type Incompatibility Between @effect/sql-drizzle and drizzle-orm 0.45.x

[km-mahbub]

What version of Effect is running?

3.19.12

What steps can reproduce the bug?

Description

When using @effect/sql-drizzle with drizzle-orm 0.45.x, there are type incompatibilities when passing table definitions to Drizzle query methods. The types returned by mysqlTable() don't match the expected types in the Effect SQL Drizzle wrapper, requiring type assertions (as any) as a workaround.

---

Environment

{
  "@effect/sql-drizzle": "0.47.0",
  "drizzle-orm": "0.45.1",
  "effect": "3.19.12",
  "@effect/sql": "0.48.6",
  "@effect/sql-mysql2": "0.49.1",
  "typescript": "5.6.2",
  "mysql2": "3.15.3"
}

---

Reproduction

1. Define a table using Drizzle ORM

import { int, mysqlTable, text } from "drizzle-orm/mysql-core";

const users = mysqlTable("users", {
  id: int().primaryKey().autoincrement(),
  name: text().notNull(),
  snakeCase: text("snake_case").notNull(),
});

2. Try to use it with Effect SQL Drizzle

import * as Mysql from "@effect/sql-drizzle/Mysql";
import { Effect } from "effect";

Effect.gen(function*() {
  const db = yield* Mysql.MysqlDrizzle;
  
  // ❌ Type error: Argument of type 'MySqlTableWithColumns<...>' is not assignable
  yield* db.insert(users).values({
    name: "Alice",
    snakeCase: "alice",
  });
  
  // ❌ Type error: Same issue with select
  const results = yield* db.select().from(users);
});

---

Quick Reproducible Test Example

import { SqlClient } from "@effect/sql";
import * as Mysql from "@effect/sql-drizzle/Mysql";
import { MysqlClient } from "@effect/sql-mysql2";
import { assert, describe, it } from "@effect/vitest";
import { int, mysqlTable, text } from "drizzle-orm/mysql-core";
import { Effect, Layer, Redacted } from "effect";

const users = mysqlTable("users", {
  id: int().primaryKey().autoincrement(),
  name: text().notNull(),
  snakeCase: text("snake_case").notNull(),
});

// Test database layer
const TestMysqlLive = MysqlClient.layer({
  database: "effect-app",
  host: "127.0.0.1",
  password: Redacted.make("aA123456"),
  port: 3306,
  username: "offline-user",
});

const TestDrizzleMysqlLive = Mysql.layer.pipe(Layer.provideMerge(TestMysqlLive));

describe.sequential("Mysql", () => {
  it.effect("drizzle operations", () =>
    Effect.gen(function*() {
      const sql = yield* SqlClient.SqlClient;
      const db = yield* Mysql.MysqlDrizzle;

      // Create the test table
      yield* sql`DROP TABLE IF EXISTS users`;
      yield* sql`CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name TEXT NOT NULL,
        snake_case TEXT NOT NULL
      )`;

       // ❌ Type error: Argument of type 'MySqlTableWithColumns<...>' is not assignable
      const returningId = yield* db.insert(users).values({
        name: "Alice",
        snakeCase: "alice",
      }).$returningId();

       // ❌ Type error: Argument of type 'MySqlTableWithColumns<...>' is not assignable
      const results = yield* db.select().from(users);

      // Verify results
      assert.deepStrictEqual(returningId, [{ id: 1 }]);
      assert.strictEqual(results.length, 1);
      assert.strictEqual(results[0]?.name, "Alice");
      assert.strictEqual(results[0]?.snakeCase, "alice");

      // Cleanup
      yield* sql`DROP TABLE users`;
    }).pipe(
      Effect.provide(TestDrizzleMysqlLive),
    ), { timeout: 60000 });
});

What is the expected behavior?

Type should match with drizzle orm

What do you see instead?

TypeScript Error

error TS2345: Argument of type 'MySqlTableWithColumns<{
  name: "users";
  schema: undefined;
  columns: { ... }
}>' is not assignable to parameter of type 'MySqlTable<TableConfig>'.
  Property '$columns' is protected but type 'MySqlTable<T>' is not a class derived from 'MySqlTable<T>'.

Additional information

Current Workaround

Type cast to any:

// ✅ Works but loses type safety
yield* db.insert(users as any).values({ name: "Alice", snakeCase: "alice" });
const results = yield* db.select().from(users as any);

[mikearnaldi]

Duplicate of #5108