Thank you for taking the time to write this.

The following opinions are my own, and mine only.

I personally think that the core drizzle-orm package should stay dependency-free. That is one of the selling point that brought me to it.
The opt-in architecture of plugins like drizzle-zod, drizzle-kit, the driver I use, etc. is just top of the line. That means I'm in control of what I bring in my project, totally in control. That allows the users to decide if they want zod, valibot, typebox or their own validator library.

I think the approach we should follow is improve the experience of the plug-ins we have.
For example, what could be missing in drizzle-zod that made you suggest this feature?

I agree with you about keeping drizzle dependency-free, and I think that it can still be achieved with the API example I provided.
The .validation() functions could just take in any function. All they do is pass the column or row to whatever validator function the user passes. It could be totally up to the user to install Zod, Joi, or anything else.

What I miss from drizzle-zod is exactly this, the possibility of having "in-client" validations by default.
If you think about it, which better place for the users to describe their data in detail if not the schema.ts file? It would be awesome to describe your schema in very granular details in a single file. Drizzle could then split these validations between DB (for whatever is supported) and runtime (e.g. string lengths, email validations, etc...). All of it becomes invisible to the user that just interacts with the client.

Your clarification just changed my mind! I think this is a great idea!
A couple of questions though:

1. if the user passes validation to both the table and the columns, what should be the behavior, order of execution?
2. Should the user expect the validation to be run on insert, update or both?
3. I saw that you included: id: serial('id').primaryKey().validation(z.string().default(() => uuid())),. I believe that will create that key in the returned object. Should that key supersede the possible implemented .default() or .$default() values?
4. In general, should drizzle just run the validation passed and insert/update the returned object?
5. Something to note here is that z.string().default(() => uuid()) doesn't return a validation function but an object with a parse method. We should make sure we are clear on what we accept.

Having said this, I think we should convert this discussion to a feature request. I think it is a nice idea.

Hello again,

After further reflection on the proposed enhancements for Drizzle ORM, I've developed an additional perspective that might further expand our capabilities: replacing the .validation() method with a more versatile .use() middleware approach.

This concept of middleware aligns more closely with the pattern seen in many modern web frameworks. It opens up a plethora of possibilities, going beyond mere validation. Here's how it could look:

export const mySchemaUsers = mySchema.table('users', {
 id: serial('id').primaryKey().use(myIdMiddleware),
 name: text('name').use(myNameMiddleware),
});

Expanded Capabilities with Middleware:

1. Beyond Validation:
   While maintaining the ability to validate data, middleware can perform various other functions. For example, it can transform data before it's saved or after it's retrieved from the database.

2. Data Type Transformations:
   Middleware can be extremely useful for data type conversions, such as transforming BigInt to number, or vice versa, ensuring seamless handling of different data types.

3. JSON Field Handling:
   Another application could be the conversion and validation of JSON DB fields into actual JavaScript objects, streamlining the process of working with complex data structures.

4. Custom Business Logic:
   It can facilitate the implementation of custom business logic at the schema level, like adding timestamps, modifying data based on specific conditions, or implementing complex inter-field logic.

5. Plug-and-Play Extensions:
   This approach also allows for the easy integration of third-party libraries or custom functions as middleware, enhancing the ORM's flexibility and capability.

By conceptualizing these functions as middleware, we not only retain the initial idea of enhanced validation but also open the door to a more powerful and flexible data-handling paradigm within Drizzle ORM.

I'm curious to know what the community and maintainers think about this expanded idea. Does this middleware approach align with the goals and architecture of Drizzle ORM?

Looking forward to hearing your thoughts and suggestions!

The functionality that developers can extend their drizzle client like prisma client extension.
It would improve the developer experience way better.

Scenarios that I have faced:

• Hashing the password value before saving it into a database.
• Adding a new property to query results at runtime and type-level.
  e.g. to use casl ability it requires to __caslSubjectType__ property.
• Ability to implement soft delete feature like https://github.com/olivierwilkinson/prisma-soft-delete-middleware/blob/main/src/lib/createSoftDeleteMiddleware.ts

Sorry for spamming.

I've been thinking about this. There is a lot to consider. I think the middleware should be at the table level only to avoid any race condition/complications around the order of operations. To be able to make it useful enough, use should accept and object with the following properties:

const usersMiddleware = {
 beforeSelect: ({table: CurrentTable, selectedFields: SelectFields, where: SQL, logger: CustomLogger}) => ({ SelectedFields, where }),
 afterSelect: ({result: DependsOnTheSelect[], logger: CustomLogger }) => CustomReturn,
 beforeInsert: ({ table: CurrentTable, insertedFields: Partial<InsertedFields>, logger: CustomLogger }) => ({ insertedFields, returnedObj }),
 afterInsert: ({result: ResultTypeFromTheDriver, returnedObject: WhateverTheUserWants, logger: CustomLogger}) => unknown,
 beforeDelete: ({table: CurrentTable, where: SQL, logger: CustomLoger}) => boolean | ((db: Database) => unknown),
 afterDelete: ({result: ResultTypeFromTheDriver, logger: CustomLogger}) => unknown,
 beforeUpdate: ({ table: CurrentTable, insertedFields: InsertedFields, logger: CustomLogger }) => InsertedFields,
 afterUpdate: ({result: ResultTypeFromTheDriver, logger: CustomLogger}) => unknown,
}

Not sure if logger is a good fit here, or the user should have it in the scope. As you might see, the names are very descriptive and make the intent very easy to guess. This avoid the if-else nightmare of an event-like api like on, where you have to do something different depending on the type of event. Even though this is basically the same thing, you can just subscribe to whichever you like and make sure you'll get what you are suppose to get in the callback.

Here is an annotated example of what this could allow:

const users = pgTable('users', {
 id: serial('id').primaryKey(),
 tenantId: bigint('tenant_id').notNull(),
 name: varchar('name', { length: 150 }).notNull(),
 password: varchar('password', { length: 300 }).notNull(),
 lastName: varchar('last_name', { length: 150 }).notNull(),
 fullName: varchar('full_name', { length: 300 }).notNull(),
 createdAt: timestamp('created_at').notNull().defaultNow(),
 updatedAt: timestamp('updated_at').notNull().defaultNow(),
 deletedAt: timestamp('deleted_at'),
 randomId: varchar('random_id').notNull()
}).$use({
 beforeSelect({table, where, selectedFields }) {
 // Here we make sure that if the query is db.select().from(users), the password won't come out.
 // But if we do db.select({ password: users.password }).from(users), it would work.
 if (!selectedFields) {
 const { password, rest: selectedFields } = getTableColumns(table);
 }
 // You could implement multi tenant applications like this
 return { selectedFields, where: and(where, eq(table.tenantId, getTenantId()), isNull(table.deletedAt)) };
 },
 beforeInsert({table, insertedFields, logger}) {
 // Here you can modify the data, validate. The sky is the limit.
 if (Array.isArray(insertedFields) {
 insertedFields = insertedFields.map((field) => userSchema.parse({
 ...field,
 randomId: generateRandomId(),
 fullName: `${field.name} ${field.lastName}`
 })
 } else {
 insertedFields.randomId = generateRandomId();
 insertedFields.fullName = `${insertedFields.name} ${insertedFields.lastName}`
 insertedFields = userSchema.parse(insertedFields);
 }
 // Here we can define a custom object to return after the insert:
 return {
 insertedFields,
 generatedRandomId: Array.isArray(insertedFields) ? insertedFields.map(field => field.randomId) : insertedFields.randomId
 }
 },
 afterInsert({ result, returnedObject, logger }) {
 logger("inserted random id(s): ", returnedObject.generatedRandomId)
 return { result, randomId: returnedObject.generatedRandomId }
 },
 beforeDelete({table, where }) {
 // Here we can do soft deletes:
 return async (db) => await db.update(users).set({ deletedAt: new Date() }).where(and(where, eq(table.tenantId, getTenantId()))
 }
}

This are just the examples I came up with and that I see people asking about.
Notice how this would basically deprecate $default() and the possible $onUpdateFn that's in the works.
Having $use defined this way would also help in separating the code that's supposed to be sql code vs the code that's supposed to only run in javascript.

Middleware is a must have in my opinion. It's actually preventing me from migrating a current project to drizzle. I think Sequelize offers a strong solution.

Some important aspects for me:

• Hooks at global/table level
• Ability to pass arguments when calling a query (ex. Passing in user detail to add detail to createdby column)

For anyone needing to plug into Drizzle's lifecycle, check this gist. It's not perfect but it's a good workaround until this is officially supported.

Is there an implementation plan? I really need this feature.

We currently wrap drizzle in a base repository https://gist.github.com/cayter/49d5c256a885d90c399ca6c1eca19f51 which it will trigger the before and after hooks. Note that we only made it working with postgres.

No update 😕

I like that Drizzle takes its time for this, because it's very easy to get this one wrong.
And we can actually implement it on our own with a repository or proxy pattern (see above the gists).
I prefer the proxy pattern because I don't want the user (the dev) to create a new language that wraps Drizzle.

On Django, we have the same issue: the core team is slower to add things, particularly when they're not really core. Which is a good thing.

But in Django there is also a great ecosystem of third party packages, in particular, middlewares. Then the Django core team eventually decides to integrate these packages into Django based on how successful they are.

The sad thing though, is that THIS feature WOULD allows a package ecosystem to thrive ! haha!

So it's a bit like a chicken and egg problem.

WE CAN implement a middleware/proxy pattern on top of the existing DSL, but we're missing the primary hooks to do so.

I tried to think about what's the best way to hook into this and I ended up with the same thing as the solution above: https://gist.github.com/ikupenov/10bc89d92d92eaba8cc5569013e04069 so I invite you to look into it.

So the drizzle team could give us functions built on top of this proxy pattern and "let us play with it". Because right now we're all building our own proxy system, which doesn't really feel right to me (it's also a lot of nasty work and type checks)

If these hooks are built properly, this might foster an ecosystem as big as Vite on the DB side of things (BTW, the api could be very similar to what Vite offers, this would facilitate adoption).

I think a good start is to JUST give the function that's called AFTER the proxy, and the function simply passes exactly the context in which drizzle was called (all the db.insert. etc, etc... plus their arguments).

No after_save, no after_update, just something very low level that could allow these higher level hooks to be built, this way it's easier to get it right for the Drizzle team.

Implementing this is safer than coming up with the perfect "vite-like" api. Which is hard to get right.

There are some improvements in the proxy solution. It’s now a lot more flexible and hooks can easily be added. Bear in mind that if you go with this approach you’ll need to maintain it and there are some breaking changes coming with V1. Shouldn’t be too bad but worth keeping in mind.

That’s the updated gist: https://gist.github.com/ikupenov/26f3775821c05f17b6f8b7a037fb2c7a.

Is there any way i can implement live query feature with sqlite using drizzle or maybe just get table name passed to the SQL query in drizzle so that I can trigger render when changes are made to that tables and queries associated with that table

Has there been any updates to this topic?
I heard mentions of a middleware implementation?

Hi,What's the progress?

Hi,What's the progress?