Drizzle filter

Build Prisma style JSON/Object filters to drizzle functional where clauses.

Initial build, so very simple, additional features such as custom sql template function and such may be added later.

Installation

npm install @emberworks/drizzle-filter

bun add @emberworks/drizzle-filter

Usage

// Ideally we have a schema first, particularly if you are using user input to build filters
// schemas.ts
import { z, ZodType } from 'zod';
import { mkSclFltrOpSchema } from '@emberworks/drizzle-filter';
//... user example
export const userFilterBaseSchema = z.object({
  id: mkSclFltrOpSchema(z.string()).optional(),
  email: mkSclFltrOpSchema(z.string()).optional(),
  firstName: mkSclFltrOpSchema(z.string()).optional(),
  lastName: mkSclFltrOpSchema(z.string()).optional(),
  phone: mkSclFltrOpSchema(z.string()).optional(),
  sub: mkSclFltrOpSchema(z.string()).optional(),
});

// Note there is also mkArrFltrOpSchema for array filters

export type UserFilterBase = z.infer<typeof userFilterBaseSchema>;

// You have to manually build the schema type for zod type annotation, TS is quite particular with circular
// types so it makes it hard to build a reliable generic type for this and even when we had one that worked as 
// soon as you try to use it anywhere useful you run into max depth TS errors which are pretty much unavoidable. 
export type UserFilter = UserFilterBase & {
  AND?: UserFilter[];
  OR?: UserFilter[];
  NOT?: UserFilter;
};
// Filters support recursive AND OR and NOT filters
export const userFilterSchema = userFilterBaseSchema.merge(
  z.object({
    AND: z.array(z.lazy(() => userFilterSchema)).optional(),
    OR: z.array(z.lazy(() => userFilterSchema)).optional(),
    NOT: z.lazy(() => userFilterSchema).optional(),
  }),
) as ZodType<UserFilter>;

// place-where-you-want-to-use.ts
import { db } from './drizzle';
import { users } from './schema/tables.ts';
import { parseQryFilterToWhere } from '@emberworks/drizzle-filter';

const someUserQuery = async (qryFilter: UserFilter) => {
  // Validate it if you built a schema, this could also (probably should) be done in at transport layer
  const parsedFilter = userFilterSchema.parse(qryFilter);
  //
  const where = parseQryFilterToWhere<UserFilter>(users, parsedFilter);
  const user = await db.query.users.findFirst({
    where,
  });
  return user;
};

// OR

const someUserQuery = async (qryFilter: UserFilter) => {
  // Validate it if you built a schema, this could also (probably should) be done in at transport layer
  const parsedFilter = userFilterSchema.parse(qryFilter);
  //
  const where = parseQryFilterToWhere<UserFilter>(users, parsedFilter);
  const users = await db.user.select({
    name: users.name,
  }).from('users').where(where);
  return users;
};