How to use serial with drizzle orm postgres?

Question

I want to use the serial type in drizzle but I get this error on migrating. I cant find the reason for this and I am importing serial from drizzle-orm/pg-core.

error: type "serial" does not exist

This is my schema:

    export const likes = pgTable("likes", {
      postId: text("post_id").notNull().primaryKey(),
      authorId: integer("author_id"),
    });
    
    export const posts = pgTable("post", {
      id: serial("id").notNull().primaryKey(),
      code: text("content"),
      language: text("content"),
      likes: integer("likes"),
      authorId: integer("author_id")
        .notNull()
        .references(() => users.id),
    });
    
    export const users = pgTable("user", {
      id: serial("id").notNull().primaryKey(),
      username: text("username").unique(),
      name: text("name"),
      email: text("email").notNull(),
      emailVerified: timestamp("emailVerified", { mode: "date" }),
      image: text("image"),
    });
    

Answer 1

Postgres doesn't support automatically altering an integer field to a serial field, see here. While you can do the conversion manually, it's probably easier to continue using an integer field, or to drop the table and recreate it.

Answer 2

You're correctly trying to use drizzle's PostgreSQL column types - serial() or serial4() (just an alias).

Auto incrementing 4-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

import { pgTable, serial } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  serial: serial('serial'),
});

I've confirmed that it works by looking in my postgres database, which now contains incrementing values:

.