Relational API v2

[AlexBlokh]

We've launched Drizzle Relational Queries exactly a year ago and it's time to ship a fundamental upgrade. We've gathered a massive amount of valuable feedback from the community and going to address every issue, so if you have anything you're missing with Drizzle Queries - feel free to comment below!

Things we want to address:

• relations declaration
• query API with updated filters API and [nested relations filters](Add nested filters for relational queries #1152)
• maintain legacy api for smooth migration
• improved documentation
• UPD: support for Views

Relations declaration

Current implementation of Drizzle Relations has several major flaws - naming, imports, many to many relations, where clause and aggregation fields.

Naming and imports

Our community’ve split into 2 groups - ones that don’t care and others who demand us to reduce the needs for imports whenever possible. We've been using drizzle in production projects and never paid attention or considered amount of imports an issue, we’re on first camp, but as of now we do consider imports to be a decent part of the DX. We took that into consideration while designing API v2.

As of naming - we've originally sticked to fields and references and thought it'd be a good idea to stick to close-to-sql model here, but it turned out to be confusing for both the community and for us too! We've investigated implementations across different libraries and decided to land on one, many, from, to and through for junction tables, they does seem to be the most natural out of all for both newcomers and for developers who're going to migrate to Drizzle.

import { relations } from 'drizzle-orm';
import * as schema from './schema';

export default relations(schema, (r) => ({
  posts: {
    author: r.one({
      from: r.users.id,
      to: r.posts.authorId,
    })
  }
})

New API will let us support aggregation fields in schema declaration

export default relations(schema, (r) => ({
  pets: {
    owner: r.one({
      from: r.pets.ownerId,
      to: r.users.id,
    }),
    count: r.count(), // <---
  },
}));

Where clauses

export default relations(schema, (r) => ({
  users: {
    pikachuPets: r.many({
      from: r.pets.ownerId,
      to: r.users.id,
      where: {
	      petName: "pikachu",
      }
    }),
  },
}));

As of V2 - relations declaration will be defined in one file and relations initialisation function will now require schema and will provide the best possible autocomplete experience with no need to import anything else from other files

With this small restriction we can perfectly solve junction table declaration challenge:

export default relations(schema, (r) => ({
  pets: {
    ownersM2M: r.many({
      from: r.pets.ownerId.through(r.petsToOwners.ownerId),
      to: r.users.id.through(r.petsToOwners.petId),
    }),
  },
  users: {
    petsM2M: r.many({
      from: r.users.id.through(r.petsToOwners.ownerId),
      to: r.pets.ownerId.through(r.petsToOwners.petId),
    }),
  },
}));

and for composite keys

export default relations(schema, (r) => ({
  pets: {
    ownersM2MComposite: r.many({
      from: [
        r.pets.id1.through(r.petsToOwners.petId1),
        r.pets.id2.through(r.petsToOwners.petId2),
      ],
      to: [
        r.users.id1.through(r.petsToOwners.ownerId1),
        r.users.id2.through(r.petsToOwners.ownerId2),
      ],
    }),
  },
  users: {
    petsM2MComposite: r.many({
      from: [
        r.users.id1.through(r.petsToOwners.ownerId1),
        r.users.id2.through(r.petsToOwners.ownerId2),
      ],
      to: [
        r.pets.id1.through(r.petsToOwners.petId1),
        r.pets.id2.through(r.petsToOwners.petId2),
      ],
    }),
  },
}));

Filters API

While Drizzle was originally built as a SQL-like ORM, we quickly got to the point that we need to have best of both(sql and relational) worlds. We’ve introduced relational APIs with the least compromises possible. Based on the community feedback and by using our own library for a while on commercial production grade projects we do came to the conclusion to introduce object based filters for relational queries, it just feels natural

// old version
db.query.pets.findMany({
  where: (pets, { eq }) => eq(pets.ownerId, 1),
});

// new version
db.query.pets.findMany({
  where: { ownerId: 1 }
});

multiple filters are by default combined with AND operator

db.query.pets.findMany({
  where: { ownerId: 1, name: "pikachu" }
});

// translates to
// select ... from pets where ownerId = 1 and name = 'pikachu'

you will be able to combine filters with OR and NOT operator

db.query.pets.findMany({
  where: { NOT: { userId: 1 } }
})

db.query.pets.findMany({
  where: { OR: [{ ownerId: 1 }, { name: "pickachu" }] }
});

// translates to
// select ... from pets where ownerId = 1 or name = 'pikachu'

scalar types will have either scalar value filter or advanced object filter

where: {
  ownerId: {
	  eq: ...,
    gt: ...,
    lte: ...,
    not: ...,
    
    or: [1,2,3]
    or: [{ gt: 1 }, { lt: 3}]
    or: [1, { lt: 3}]
    // etc...
  }
}

this API will let us easily introduce relational filters while keeping the option to declare raw sql filters for whatever cases we do not support on launch day

db.query.pets.findMany({
  where: { owner: { id: 10 } }
});

db.query.pets.findMany({
  where: {
    RAW: (pets, { sql }) => sql`lower(${pets.petName}) like 'pikachu'`,
  }
});

Interoperability and Docs

As of now Queries section of the docs is a zero to hero of Relational Queries and we will split it into two detailed sections on how to declare relations and how to query

As of interop - we will add a legacy drizzle._query operator to establish a smoother transition on existing projects with the ability to either stay on legacy API or gradually migrate

[driwand]

Great news! was looking forward to the nest filter for a long time. There is one issue I faced using the current query and it's related to this issue#1378 hoping to see it fixed!

[firtoz]

Looking great!

One comment:

db.query.pets.findMany({
  where: { ownerId: 1, name: "pikachu" }
});

db.query.pets.findMany({
  where: { NOT: { userId: 1 } }
})

db.query.pets.findMany({
  where: { OR: [{ ownerId: 1 }, { name: "pickachu" }] }
});

These feel too similar, would it be better or worse to use something like a dollar prefix or some other indicator that highlights that "NOT / OR mean something special here and it's not a field"

I'm new to Drizzle (loving it so far) so I don't know much about the history or the ongoing conversations - if a dollar prefix or some other "this is a special keyword" indicator was considered already and decided against, fair enough

[sillvva]

I agree. I prefer a $ prefix over screaming case.

db.query.pets.findMany({
  where: { ownerId: 1, name: "pikachu" }
});

db.query.pets.findMany({
  where: { $not: { userId: 1 } }
})

db.query.pets.findMany({
  where: { $or: [{ ownerId: 1 }, { name: "pickachu" }] }
});

[AlexBlokh]

we've discussed this internally during planning and specifically landed on NOT and OR

[AlexBlokh]

$ syntax in drizzle is currently represents a non-sql / runtime parts of the API and does not align with query filters

[B33fb0n3]

I would love to see a better select functionality. I see this:

// old
await db.query.test.findMany({
   columns: {
      field1: true,
      field2: true,
      field3: true
      ...
   }
})

// maybe future
await db.query.test.findMany({
   columns: ["field1", "field2", "field3"] // or something similar
})

Is it just me, or is the composite keys pattern way more complicated than it's available right now?

[AlexBlokh]

current syntax is concise and in line with the rest of the API

[alexcroox]

@AlexBlokh the syntax for relationship query columns is different to non relationship query columns which can be frustrating maintaining 2 seperate column configs for repeated queries.

For example relationship query columns are: { fieldName: boolean }
Non relationship query columns are: { fieldName: schemaField }

Which means I need 2 versions of a common "selectUserSensitiveColumns" object

[AlexBlokh]

you shouldn't be mixing query and select APIs and we don't design them this way, we intentionally separate Query + CRUD everywhere we can so that developers get on board of a new modern paradigm

[alexcroox]

OK another example, when I do an INSERT (which I have to use CRUD for) and I want the same column config for returning(selectUserSensitiveColumns)

[dBianchii]

I usually avoid leaving unnecessary comments that don't add nothing of value to the discussion.

However, just this once....

LETS GOOOO!!! 🎉🎉🎉🚀

[IsaiahByDayah]

This looks great! The only thing that stands out to me is it looks like the from and to fields for relations doesn’t seem to care which table is “from” and which is “to”? In the examples you’ve included, there are relations where the table the relation is scoped to is sometimes from and sometimes to. Is this correct? Do the current fields and references behave the same way and I just never noticed?

example of what I mean (on mobile so excuse size):

besides that I think this is looking great! 👏👏👏

[AlexBlokh]

yeah, ma bad, it will care on the types lvl just like it does now

[14gasher]

This does introduce a vulnerability. Clients may be able to manipulate queries from endpoints.

Sequelize uses symbols to protect against this kind of vulnerability:

‘’’
where: {
[notSymbol]: [{name: “pikachu”}, {name: “jigglypuff”}],
}
‘’’

finally, this prevents the case (rare, but possible) that there is a conflict between column names and “special keywords”

[AlexBlokh]

we will take that into consideration

[rmtmckenzie]

This might not be in the scope of this redesign, but if you're working on the API anyways I'd like to throw in a suggestion.

In my database, I use relations with a primary key that I treat as an implementation detail and have a separate public ID that is used externally (very handy for migrating database providers which have different key requirements). I'm not sure it'd work at all with the new syntax, but rather than doing extra database calls to get the public id -> internal, it'd be awesome to be able to do something like this:

const columns = [ ... ]; 
const withMap = { ... }; 

db.query.purchaseFeedbacks.findFirst({
  columns,
  with: withMap,
  where: and(eq(receipts.publicId, receiptPublicId), eq(users.publicId, userPublicId)),
  joins: [
    { table: receipts, on: eq(purchaseFeedbacks.receiptId, receipts.id), type: 'inner' },
    { table: users, on: eq(receipts.userId, users.id), type: 'inner' },
  ],
});

It's super useful to be able to use the query for this rather than the more sql-like syntax as the columns & withMap are parsed from a graphql request - this same operation would take at least a few hundred lines of code if I had to write it all out more explicitly.

The idea of what I've done above is that tables which are specified in the joins are not read from directly but rather used exclusively for the where clause as any data being read is specified in the columns/with. There may be a more elegant way to do this, but it's what I came up with.

I've been using a very basic implementation of this in a fork (https://github.com/rmtmckenzie/drizzle-orm/tree/query-joins-v2) but it's definitely not production ready and only implemented for mysql and postgres. It seems like your proposed changes will make this less possible, but if this sparks any ideas I'd appreciate it being considered. (and feel free to lift any code in that branch if it's at all helpful).

[rmtmckenzie]

I can paste in the relevant part of my schema if it would help make sense of that

[AlexBlokh]

you should submit that as a separate discussion, I suppose

[JakeGinnivan]

Love the relations changes, the relations API currently is pretty hard to get right when first starting. The new filters API conversion from Prisma projects far easier, my concern with the new filters API is the TypeScript performance on a larger project.

Prisma has a codegen step to create the types which would help the approach scale due to less computed types. The current drizzle approach is great from a TypeScript performance point of view, it also it super easy to extract helpers and compose bits of queries together which getting the types right to store parts of queries in variables is pretty hard with the object syntax.

It could be good to have an opt out which ensures the types are not included at all for these relations in the case of build performance issues? Unsure if just having those types could impact build performance or you have to use the new syntax to be impacted.

At any rate, suggest checking the build perf ( of a large project before/after upgrading to a version with the filters API, then converting a number of queries across then checking build perf again. Once it's released it might be a lot of work to fix large perf issues without breaking changes.

[AlexBlokh]

if you don't use legacy ones - they don't compute

[JakeGinnivan]

I was more thinking for the new types. Even if you are using the old API, the types for the new query API will be in your type graph.

Just saying, this approach may struggle being inferred the whole way down and could end up needing a code gen step to get decent perf. Should just do some initial benchmarking to see the impact on compilation times before committing.

Could be good to decouple the various parts of this, so if the query api has build perf impacts, the improved relations can still be shipped.

[AlexBlokh]

we always have an ace in the sleeve for the types, we're friends with David Blass, who's a TS guru and author of ArkType

[AlexBlokh]

if there be any struggle with perf - we gonna fix them, mark my word!

[L-Mario564]

New API will let us support aggregation fields in schema declaration

export default relations(schema, (r) => ({
  pets: {
    owner: r.one({
      from: r.pets.ownerId,
      to: r.users.id,
    }),
    count: r.count(), // <---
  },
}));

What does r.count() do exactly here? How would I use the count property in a query?

Where clauses

export default relations(schema, (r) => ({
  users: {
    pikachuPets: r.many({
      from: r.pets.ownerId,
      to: r.users.id,
      where: {
	      petName: "pikachu",
      }
    }),
  },
}));

In the where clause example above, does that mean I can query the above relation like this?:

db.query.users.findFirst({
  columns: {
    // ...
  },
  with: {
    pikachuPets: {
      columns: {
        // ...
      }
    }
  }
});

[dankochetov]

The example for count() is misleading, it will be used in the query API, not in the declaration API.

In the where clause example above, does that mean I can query the above relation like this?:

yes

[kenn]

Great updates. Just a small nit:

As of interop - we will add a legacy drizzle._query operator to establish a smoother transition on existing projects with the ability to either stay on legacy API or gradually migrate

Wouldn't it make more sense to have it reverse with a config option? For instance:

// Start migrating

export default {
  query: 'legacy' // tentatively swap the assignment
} satisfies Config

db.query.pets.findMany // legacy
db._query.pets.findMany // v2

↓

// Almost finished transition to v2

export default {
  query: 'v2' // default
} satisfies Config

db._query.pets.findMany // legacy
db.query.pets.findMany // v2

[dankochetov]

drizzle-orm currently doesn't use the config file at all, it's for drizzle-kit only. Also, since we don't rely on codegen, it would be a relatively big change in the types to swap .query and ._query based on a config flag. So we've settled on a compromise which allows you to do find-and-replace db.query -> db._query across your codebase to postpone the migration to V2 (although the query API changes will be minimal compared to the declaration API, so a lot of queries will work with V2 without any changes).

[413n]

I don't know if this is already included in your changes, but will you include the possibility to have optional one() relations (#1066)?

[AlexBlokh]

yes, will cc: @dankochetov just to make sure

[413n]

Will the where clause be available also for the r.one relations?

export default relations(schema, (r) => ({
  users: {
    pikachuPets: r.many({ 
      from: r.pets.ownerId,
      to: r.users.id,
      where: {  // <--- This
        petName: "pikachu",
      }
    }),
  },
}));

[AlexBlokh]

yes, will cc: @dankochetov just to make sure

[ivstiv]

I am sure you have considered it seeing you have mentioned a "through" relation, but I want to point out the importance of polymorphic relations when designing the DX. These discussions have some good insights.

• [FEATURE]: Support Polymorphic Association #1051
• [FEATURE]: Customizable many relations #674

Exciting stuff!

[AndriiSherman]

Sure, it's included

[AndriiSherman]

This is an example from a discussion description

export default relations(schema, (r) => ({
  users: {
    pikachuPets: r.many({
      from: r.pets.ownerId,
      to: r.users.id,
      where: {
	  petName: "pikachu",
      }
    }),
  },
}));

[jakeleventhal]

One thing I would really hope to see in V2 that has been a pain point for me and has prevented me from using the V1 RQB is the lack of the ability to use where conditions that rely on related tables.

For example:
I have a table listings that has many listingVariants. I want to make a query on the listings table where all of its variants have isNotNull(listingVariants.productId).
The way I am currently accomplishing this is like so:

db
  .select({ id: listings.id })
  .from(listings)
  .leftJoin(listingVariants, eq(listingVariants.listingId, listings.id))
  .where(
    exists(db.select().from(listingVariants).where(and(eq(listingVariants.listingId, listings.id), isNotNull(listingVariants.productId))))
  )

This is annoying since it requires manually joining and using subqueries. This same problem gets even more complicated when my with statements require json_agg and I have to do all that logic myself rather than relying on the RQB. It would be much easier if I could mention related tables in where clauses nested inside with clauses. For example, I would want to be able to make the same query like so:

db.query.listings.findMany({ where: {
  ALL: { variants: { productId: { NOT: null } } }
}});

[AlexBlokh]

yes, that is included

[khuezy]

Sorry if this was discussed above but I skimmed through and didn't find anything. Would this new API allow to infer the type for the relation query?

eg:

const postRelations = relations(posts, ({ many }) => ({
  comments: many(comments)
})

// How do I create the types for this query?
db.query.posts.findMany({
   with: { comment: true}
})

[sillvva]

It would be nice if they had this InferQueryModel type helper built in

import * as schema from "$server/db/schema";
import type { BuildQueryResult, DBQueryConfig, ExtractTablesWithRelations } from "drizzle-orm";

type TSchema = ExtractTablesWithRelations<typeof schema>;
export type QueryConfig<TableName extends keyof TSchema> = DBQueryConfig<"one" | "many", boolean, TSchema, TSchema[TableName]>;
export type InferQueryModel<TableName extends keyof TSchema, QBConfig extends QueryConfig<TableName> = {}> = BuildQueryResult<
	TSchema,
	TSchema[TableName],
	QBConfig
>;

/**
 * Usage
 */
type Result = InferQueryModel<
  "logs",
  {
    columns: { id: true },
    with: {
      character: {
        columns: { id: true }
      }
    }
  }
>;

// type Result = { id: string; character: { id: string; } }

[khuezy]

I currently do this, but it's still not 100% right, eg:

type UnwrapPromise<T> = T extends Promise<infer U> ? U : T;

export type PostQuery = UnwrapPromise<ReturnType<typeof getPosts>>

export async function getPosts(id: string) {
   return db.query.posts.findMany({
      with: { comments: { comment: true }}
   })
}

This should work, but drizzle returns a weird type:

comments: (string & { comment: string}}

I'm not sure why the return type of the query has the string &... if it didn't include that then everything is good.

[sillvva]

You can also use Awaited<ReturnType<typeof getPosts>>. Awaited is a built-in helper

[khuezy]

type RemoveStringAnd<T> = T extends string & infer U ? U : { [K in keyof T]: RemoveStringAnd<T[K]> };

export type PostsQuery = RemoveStringAnd<Awaited<ReturnType<typeof getPosts>>>

This gets rid of the annoying (string & wrapper and returns the expected type.
Edit: but strings now become unknown 😢

This should work:

type RemoveStringAnd<T> = T extends string & infer U
  ? U extends object
  ? U
  : T
  : {
    [K in keyof T]: RemoveStringAnd<T[K]>;
  };

[khuezy]

Ok, I finally figured out why I was getting the weird typing, it's because my schema had the same name as my relation field. Looks like it concats the 2 types.

eg:
schema:

author: text('id')

relation:

author: one(author, {...

query:

with: {
   author: true
}

This results in (string & {what ever type the relation query is})

[isaacharrisholt]

Any hope of the relations API supporting views any time soon?

[dankochetov]

I'll try to add support for them in V2

[matjaeck]

A.B. in 2023/06:

Drizzle is a menace. It became so good other ORMs will have to get better now.

A.B. in 2024/05:

it's time to ship a fundamental upgrade

The other ORMs: 💀

[AlexBlokh]

What in particular is wrong in my statement?
What other ORMs are 💀 now?

[davidchalifoux]

What in particular is wrong in my statement?

What other ORMs are 💀 now?

I was confused by this comment at first as well. They're saying Drizzle is getting even better so other ORMs will be even further behind/worse.

[AlexBlokh]

Ah, I thought 💀 means other orms are laughing
My bad

[sillvva]

Would it be possible to declare the schema and relations in one file?

export const schema = {
  table1: pgTable(...),
  table2: pgTable(...),
  ...
}

export default relations(schema, ...)

And how would one import and use the relations in the db client? Something like this?

import schema from './schema'; // with relations
export const db = drizzle(connection, { schema });
// or
import relations, { schema } from './schema';
export const db = drizzle(connection, { schema, relations });

[AlexBlokh]

you can still put relations in the same file with scheme, but you will have to wrap all tables in schema object

import .... ;

export const users = pgTable("users", { ... });
export const pets = pgTable("pets", { ... });

export const scheme = { users, pets }

export const drizzleRelations = relations(scheme, (r) => { ... }); 

[zsilbi]

If only one relations object can be passed to the the drizzle constructor, is there a way to collect and combine multiple relations objects from different files?

We use a modular system where we have multiple schema files (could be for multiple database types) located in different packages that can work independently or together with other modules in an application.

Simplified folder structure:

├── apps
│   ├── my-app
│   │   ├── libsql/schema-for-my-app.ts
├── packages
│   ├── reusable-module-a
│   │   ├── libsql/schema-for-module-a.ts
│   │   ├── mysql/schema-for-module-a.ts
│   ├── reusable-module-b
│   │   ├── libsql/schema-for-module-b.ts

The modules rely on abstractions that are based on their own database schema, so we can just provide one database instance of the same dialect for all of them, or we can create seperate instances based on the requirements.

In schema-for-my-app.ts we import the schema files from the modules so we can generate migrations and apply them in one go.

// File: apps/my-app/libsql/schema-for-my-app.ts

export * from "@acme/reusable-module-a/libsql/schema";
export * from "@acme/reusable-module-b/libsql/schema";

[AlexBlokh]

you will then have 1 relations.ts file, import multiple schema parts there and combine into 1 inplace

export * as s1 from "@acme/reusable-module-a/libsql/schema";
export * as s2 from "@acme/reusable-module-b/libsql/schema";

export default relations({ ...s1, ...s2 }, (t) => { ... });

[zsilbi]

My question was more about combining the relations objects, not the tables.
If I understood correctly, s1 and s2 are not relations objects, they are a record of tables.

// File: libsql/schema-for-module-a.ts
export const s1 = { table_a1, table_a2 };
export default relations(s1, (r) => { ... });

// File: libsql/schema-for-module-b.ts
export const s2 = { table_b1, table_b2 };
export default relations(s2, (r) => { ... });

// File: libsql/schema-for-my-app.ts
import { s1, default as r1 } "@acme/reusable-module-a/libsql/schema";
import { s2, default as r2 } "@acme/reusable-module-b/libsql/schema";

export default relations({ ...s1, ...s2 }, (r) => { ... });
                                                  // ^ how can I use r1 and r2 to combine them here?

[dankochetov]

That was the answer - you can have schemas in multiple files, but you will always have a single relations object in one file, where you can use the full schema combined from multiple schema files.

[michalpuchmertl]

🔥🔥🔥

[johanneskares]

I added this as a separate request, but it would be epic, if the relations API would suport something like an AdHoc view as a source. This would enable perfect mixing and matching of the more low-level API and the query API. E.g. some data aggregation techniques, especially time-based windowing, etc., will make more sense in the traditional API. So table pre-processing can be done using all the SQL/Postgres magic and the the high-level querying, and joining of tables can be done using the Query API.

#2342

[danielsharvey]

Is there any consideration for composite updates based upon the relations structures?

ie taking the result from findFirst(), mutating and using to perform update?

[meirlamdan]

Maybe there is some way to combine the two methods query and the basic method of select
It's much simpler and cleaner than working with two different methods

[sillvva]

The query api uses JSON agg functions under the hood to return JSON data directly from the db. The select api is more of a 1:1 translation to SQL.