Drizzle ORM integration

[PeterZainzinger]

Hi,

I am trying to integrate electric-sql with drizzle-orm, I already got a basic proof-of-concept working without much effort. My approach was:

• Follow the steps to get a nextjs running and copy some code over from web-wa-sqlite.
• Define a simple comments table on the server side with

import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const tableComments = sqliteTable('comments', {
  id: text('id').primaryKey(),
  text: text('text'),
});

.

• Generate migration for this table with drizzle cli, add

ALTER TABLE "comments" ENABLE ELECTRIC;

and apply these migrations to postgres proxy.

• Create a typescript client, these has typescript erros as I have no prisma client configured, disabled type checking and eslint there.
• define a sqlite table:

import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const tableComments = sqliteTable('comments', {
  id: text('id').primaryKey(),
  text: text('text'),
});

in schema.ts

• use sqlite proxy driver of drizzle orm to create a drizzle db builder:

import { drizzle } from 'drizzle-orm/sqlite-proxy';

import * as schema from './schema';
import { ElectricClient } from 'electric-sql/client/model';

export const drizzleBuilder = (electric: ElectricClient<any>['db']) => {
  return drizzle(
    async (sql, params, method) => {
      const rows = await electric.raw({
        sql: sql,
        args: params,
      });
      return {
        rows: rows,
      };
    },
    {
      schema: {
        ...schema,
      },
      logger: true,
    },
  );
};

.

• Use this in client code like this:

const { db } = useElectric()!;
const drizzleDb = useMemo(() => drizzleBuilder(db), [db]);

• live queries work like this, at least the non relational ones like this

 const selectQuery = useMemo(
    () => drizzleDb.select().from(tableComments).toSQL(),
    [db],
  );
  const { results } = useLiveQuery(
    db.liveRaw({
      sql: selectQuery.sql,
      args: selectQuery.params as any,
    }),
  );

The trick there is to use toSQL() method. I read a bit the drizzle source code, for more complex queries something like mapRelationalRow has to be used.

• Non live queries are even simpler, for example:

   await drizzleDb.insert(tableComments).values({
      id: genUUID(),
      text: 'drizzle comment',
    });

Everything basically works, there are two main pain points:

1. the code generation depends on prisma and the outputs have a lot of invalid typescript code. As far as I can see the code generated for drizzle could be a lot simpler, zod schemas and types could all be derived/inferred from the table definitions there.
2. When doing an insert via a raw sql statement, the live query takes about 2 seconds to update.

Does somebody have input for these?

In general I would really like to see drizzle orm support because I think its a really elegant., powerfull and also lightweight library that could be leveraged without crazy effort from electric-sql.

Peter

[PeterZainzinger]

figured out a solution to the second pain point, there is satellite.notifier.potentiallyChanged();

[kevin-dp]

Glad you found it! By calling potentiallyChanged you are telling the Electric client that something may have changed in the local SQLite database. As a result, the Electric client will check the oplog for changes, take a snapshot of the changes, and inform the Electric sync service. Electric also periodically takes a snapshot of the oplog in case you don't call potentiallyChanged, which is the behavior you were observing.

[kevin-dp]

Thanks a lot for your insights on how you got Drizzle to work with Electric SQL.
It definitely feels like there is a way to add support for the Drizzle ORM in Electric such that our users don't need to manually do the steps you described here.

Regarding your first question, our current workflow relies on Prisma to generate an Electric client. The typings of our Electric client re-use the typings from the generated Prisma client. Normally, when using a Prisma version we support you should not get type errors in the generated client. As to Zod, we use Zod for runtime validation of user input.

A trickier part of integrating Drizzle with Electric will be data type support. For example, Electric supports booleans, dates, etc. Booleans are true booleans in Postgres but are numbers (0 or 1) in the local SQLite database. Therefore, our Electric client converts the user's booleans into 0 or 1 and when reading does the opposite conversion. Not sure how this would work when integrating Drizzle because our client only does this conversion when using our Prisma-inspired API but not when using raw queries.

[PeterZainzinger]

@kevin-dp

Regarding your first question, our current workflow relies on Prisma to generate an Electric client. The typings of our Electric client re-use the typings from the generated Prisma client. Normally, when using a Prisma version we support you should not get type errors in the generated client.

Doesn't relying on prisma mean there is no way (without editing generated code or ignoring typ errors) to use electric-sql without exactly the nodejs + prisma stack which excludes most setups.

I also think that the generated prisma schema are unnecessarily huge (thousands of lines of code for two tables with two fields). Is it really necessary to have these validators? Why not rely on typechecking and let the user do potential validation. Validation can be circumvented by using raw sql anyway.

Drizzle would also allow to derive insert and select schemas with drizzle-zod.

A trickier part of integrating Drizzle with Electric will be data type support. For example, Electric supports booleans, dates, etc. Booleans are true booleans in Postgres but are numbers (0 or 1) in the local SQLite database. Therefore, our Electric client converts the user's booleans into 0 or 1 and when reading does the opposite conversion. Not sure how this would work when integrating Drizzle because our client only does this conversion when using our Prisma-inspired API but not when using raw queries.

I think thats relatively straight forward, one could just define custom drizzle datatypes and use these in the generated code

[samwillis]

Hey @PeterZainzinger,

Doesn't relying on prisma mean there is no way (without editing generated code or ignoring typ errors) to use electric-sql without exactly the nodejs + prisma stack which excludes most setups.

Since version 0.8 it's possible for a project using Electric to depend on a different version of Prisma to the one Electric uses to generate the client.

Making it easier to use other ORMs and query builders is something we plan to work on. We have a few ideas how to do this, including moving the type validation to a lower layer (maybe into the database adaptor), but we have no timeframe for this right now.

[PeterZainzinger]

Hi @samwillis

I am questioning if there is even a point doing any validation on the orm layer. For me thats an orthogonal concern, I have not seen any other orm doing this. Imho electic-sql should focus on providing really good raw sql support and not to try to reinvent the wheel on the orm side.

Sql check contraints on the other hand would be really usefull, but I understand that translating postgres check contraints to sqlite contraints check is non trivial.

[PeterZainzinger]

Hi,

I collected all the things about drizzle + electric in a repo: https://github.com/PeterZainzinger/electric-drizzle . It contains its own code generator, which is bit limited.

It should demonstrate some ideas how to use electric with external query libraries. Feel free to give feedback

[octalpixel]

Hey @PeterZainzinger and @kevin-dp curious if https://github.com/farreldarian/prisma-generator-drizzle from @farreldarian would help address some issues that were mentioned in this thread

[KyleAMathews]

👋 we've been working the last month on a rebuild of the Electric server over at a temporary repo https://github.com/electric-sql/electric-next/

You can read more about why we made the decision at https://next.electric-sql.com/about

We're really excited about all the new possibilities the new server brings and we hope you'll check it out soon and give us your feedback.

We're now moving the temporary repo back here. As part of that migration we're closing all the old issues, PRs, and discussions. We really appreciate you taking the time to start this discussion! If it's still relevant in the new system, please reopen.