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

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?

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! 👏👏👏

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”

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).

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.

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: {
        // ...
      }
    }
  }
});

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

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

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",
      }
    }),
  },
}));

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!

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 } } }
}});

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}
})

Is it possible to define relations in separate files / modules instead of a monolithic call to defineRelations ?

Maybe it is a naive question, but why do we have to define relations in the first place? Aren't they implicitly defined by foreign key relations in the schema? Most natural for me would be to have relations inferred from schema.

Is there a way to "count" using the new where syntax?

(Not sure if this discussion is monitored anymore)

But, what's the reason for supporting foo: { isNull: true } instead of foo: null in where conditions?