[Feature] Map database fields from existing tables with field-level dbName option.

[samuelsogbesan]

Hi Team.

Feature Request / Proposed Solution

The ability to supply a dbName per field in a Collection. Payload will use this dbName during lookup, instead of using the name of the field in the collection.

export const Users: CollectionConfig = {
  slug: 'users',
  dbName: 'AspNetUsers',
  ...
  fields: [
    {
      name: 'email',
      type: 'email',
      dbName: 'EmailField' // Payload looks up EmailField instead
    }
  ],
}

Problem

We have an existing database that we want to partially expose to Payload via a series of Collections. This includes our User table, which has a different schema to the one that is expected by Payload.

The CollectionConfig.dbName option allows us to point Payload to the right database schema, but we are not able to map the fields. So we have some mismatches, for fields like id, password etc.

Our final goal is to allow login to the Payload platform, using the partners existing credentials.

[samuelsogbesan]

This is also an issue with fields that aren't defined in snake case. Payload automatically turns field names to snake case before performing the database query.

e.g.

export const Products: CollectionConfig = {
  slug: 'products',
  dbName: 'Products',
  admin: {
    useAsTitle: 'name',
  },
  access: {
    read: () => true,
    create: () => true,
    update: () => true,
    delete: () => true,
  },
  fields: [
    {
      name: 'fullName',
      type: 'text',
      required: true,
      label: 'Product Name',
      admin: {
        description: 'e.g., "Enter your full product name"',
      },
    }
 ]
}

Would execute in SQL as:

select "full_name" from "Products"

The quickest solution would be to let developers override the behaviour.

[r1tsuu]

I have a PR here that I'll need to continue #10293