Custom Fields with custom data types

[thanaParis]

While reviewing the documentation for creating custom Fields, I didn't see any mention of how to go about changing the underlying database type.

I have a Postgres database with some unusual column types, such as name (see table 8.5) that I'm trying to make a Payload schema for. I also am interested in Point support for Postgres. Solving either of these problems, as far as I can tell, will require modifications to the interpretation of the 'type' field. Aka:

      {
        name: 'issuerName',
        type: 'text', // <- this field
        label: 'Issuer Name',
      }

Is there any documentation on custom Field type options? What would you say the level of effort would be for adding a custom Field type option?

[flowingkhaos]

I encountered the exact same issue here. I am using a postgres adapter to use supabase as DB, and i am working with AI technology to create a RAG system.

  {
  import { CollectionConfig } from 'payload'
  import type { Field } from 'payload'
  

    export const Articles: CollectionConfig = {
     slug: 'articles',
        fields: [
        {
          name: 'embedding',
          type: 'json', // <- i need to change this to a vector type
          required: true,
         },
        ]  as Field[],
  }

In this case, in order to store embeddings in the database articles table, the field type in the payload Article.ts collection file AND the column type in the DB table NEED to match. However as we all know there is a limited selection of types native to payloads framework:

array
blocks
checkbox
json
code
collapsible
date
email
group
number
point
radio
relationship
rich-text
select
tabs
text
textarea
ui
upload

so just like my collegue here i ask does anyone know how to create custom field types?

[DmacMcgreg]

Bumping this. Has anyone figured out how to do this?

[flowingkhaos]

hey man. i have been working on this issue for a while now. Using payload to manipulate and view data types that aren't supported locally can be messy and hard if your not an experienced dev with a concise goal and approach to your project. It's hard because, most of the time, you are scaffolding payload over an existing structure.. Thankfully, Payload offers you two approaches to deal with data.

Everything Done and Sync. For You, Versus Manual data manipulation

1. You can either use payload out of the box, and have the API ovewrite your DB tables and automatically sync everything from schemas to types etc. This is why payload is very attractive, but the problems start to reveal themselves when you want to manipulate data that is created outside of that environment and also when you want to manipulate unsupported data types and formats.

2. In that case Payload offers you the option to use an ORM and along with their different DB adapters to manually generate and migrate schemas and finally synchronise tables to have a uniform type and schema interface in your application. That solution works so well because you can manage data that was created outside of payload, and inside of it as well equally, and the local API will work as well on any view. But you loose the apeal of having payload handle absolutely everything for your, and you most likely need to learn ORMs.

i didn't want to learn ORMs and i was forced to because of this situation, and i have to admit, i regret not learning those sooner, so in my humble opinion you should learn ORMs if you haven't already. That will really get you out of a pinch, when you need to manipulate objects throughout complex structures, while maintaining their type safety...

So to sum up, if your application structure is complex, and you need special data types like embeddings for example, you should create them outside of payload using an ORM like drizzle and create collections using the dbName property to match the table in the DB and make sure there are no naming conflict between the tables created from drizzle or prisma versus the ones created in payload. For instance, you can't have a postgres schema named "users" using drizzle, and have a payload collection called "users", while expecting to find two different tables, that will create a conflict, since payload doesn't know what table you are reffering to. so for example, what i do is i create an "Admin" collection using payload for the admin dashboard, then i create a "Users" schema in my application using drizzle for accessing the application front-end. the i migrate and sync everything using payload and drizzle CLI commands.

It sounds complicated but in reality it is so easy once you get it, the docs aren't bad, they're just lacking a bit. Read those docs to fully understand whats going on under the hood:

database overview
postgres adapter
collections

Here is some extra info to give you direction:

your app might look like this:

• /src/app: Next.js application routes and pages
• /src/collections: Payload CMS collection definitions
• /src/components: React components for UI elements
• /src/lib/db: Database schema, migrations, and queries

this is what a collection looks like when created inside of payload:

import type { CollectionConfig } from 'payload';
import { isAdmin, isAdminFieldLevel } from '@/access/isAdmin';
import { isAdminOrSelf } from '@/access/isAdminOrSelf';

export const Admins: CollectionConfig = {
  slug: 'admins',
  admin: {
    useAsTitle: 'username',
  },
  fields: [
    {
      type: 'row',
      fields: [
        {
          name: 'username',
          type: 'text',
          required: true,
        },
        // Email added by default
      ],
    },
  ],
};

this is what a collection looks like when the table was created outside of payload:

// collections/User.ts
import { CollectionConfig } from 'payload';

export const Users: CollectionConfig = {
  slug: 'users',
  admin: {
    useAsTitle: 'email',
  },
  fields: [
    {
      name: 'id',
      type: 'text',
      admin: {
        readOnly: true,
      },
    },
    {
      name: 'email',
      type: 'email',
      required: true,
    },
    {
      name: 'password',
      type: 'text',
      admin: {
        readOnly: true,
        position: 'sidebar',
        description: 'Password is stored securely and cannot be edited here',
      },
    },
  ],
  // Set custom database name to match existing schema
  dbName: 'User',
};

here is what a config file looks like when using this hybrid approach with tables coming from both inside and outside of payload:

// storage-adapter-import-placeholder
import { postgresAdapter } from '@payloadcms/db-postgres'
import { payloadCloudPlugin } from '@payloadcms/payload-cloud'
import { lexicalEditor } from '@payloadcms/richtext-lexical'
import path from 'path'
import { buildConfig } from 'payload'
import { fileURLToPath } from 'url'
import sharp from 'sharp'

//?? Import your collection configs
import { Admins } from './collections/Admin'; // i import regular collections created by payload as we usually would
import { Media } from './collections/Media';
import { 
  Users,
} from './collections'; // i put external tables in an index.ts file in the collections folder so i can identify them quickly

//?? Import your schema from the existing drizzle schema
import { 
  user, 
} from '@/lib/db/schema';

const filename = fileURLToPath(import.meta.url)
const dirname = path.dirname(filename)

export default buildConfig({
  admin: {
    user: Admins.slug,
    importMap: {
      baseDir: path.resolve(dirname),
    },
  },
  email: brevoAdapter(),
  collections: [
    // tables created from payload
    Admins, 
    Media,
    // external tables created from any ORM
    Users,
  ],
  editor: lexicalEditor(),
  secret: (process.env.PAYLOAD_SECRET as string) ?? '',
  typescript: {
    outputFile: path.resolve(dirname, 'payload-types.ts'),
  },
  db: postgresAdapter({
    pool: {
      connectionString: (process.env.DATABASE_URI as string) || '',
    },
    push: false, // Important! We don't want to modify our existing schema that were created outside of payload using drizzle or prisma
    beforeSchemaInit: [
      ({ schema, adapter }) => {
        return {
          ...schema,
          tables: {
            ...schema.tables,
            // Add your existing drizzle tables to the schema
            User: user,
          },
        };
      },
    ],
  }),
  sharp,
  plugins: [
    payloadCloudPlugin(),
    // storage-adapter-placeholder
  ],
})

this is what a drizzle schema looks like in src/lib/db/schema.ts:

import type { InferSelectModel } from 'drizzle-orm';
import {
  pgTable,
  varchar,
  timestamp,
  json,
  uuid,
  text,
  primaryKey,
  foreignKey,
  boolean,
} from 'drizzle-orm/pg-core';

export const user = pgTable('User', {
  id: uuid('id').primaryKey().notNull().defaultRandom(),
  email: varchar('email', { length: 64 }).notNull(),
  password: varchar('password', { length: 64 }),
});

export type User = InferSelectModel<typeof user>;

CLI commands:

• pnpm db:generate: Generate Drizzle schema
• pnpm db:migrate: Run migrations
• pnpm generate:types: Generate Payload types
• pnpm generate:importmap: Generate import map

I really hope that could help. Cheers.

[DmacMcgreg]

Thanks for the detailed writeup, very much appreciated!

[DanRibbens]

Thanks for bumping this thread @DmacMcgreg!

@thanaParis your recommendation is using beforeSchemaInit, but I would say you should do it afterSchemaInit so that you can extend the schema from what payload does for you already if you only want to override one column. This iss documented here https://payloadcms.com/docs/database/postgres#afterschemainit and this is a more specific example for you:

// payload.config.ts
import { vector } from 'drizzle-orm/pg-core'

// rest of the config...
  db: postgresAdapter({
	  afterSchemaInit:[
	  ({ schema, extendTable }) => {
	    extendTable({
	      table: schema.tables.places,
	      columns: {
	        embedding: vector('embedding', { dimensions: 1536 }),
	      },
	    });
	    return schema;
	  },
	],
	  pool: {
	    connectionString: process.env.POSTGRES_URL,
	  },
	}),

[flowingkhaos]

Waw, that solves most use cases around that issue if not qll. Can't believe i just couldn't read right. Thanks

[DmacMcgreg]

Thanks @DanRibbens this is pretty much exactly how I got this working. Cheers!

[DmacMcgreg]

@DanRibbens just to be clear, this wont actually add the column for you as payload normally does, correct? I had to add it manually, and then it just seems to accept it as a fact afterwards.

[DanRibbens]

Right, I'm going off the config shared above and overriding the embeddings column in afterSchemaInit

[DmacMcgreg]

But what I mean is that you have to manually install vectors in the database as an extension, then add the column yourself through an SQL query. The postgresAdapter wont add this for you, is that correct?

[itzaks]

Does anyone have some insight on how to provide similar results for mongo?