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

It appears from the error message that Drizzle is unable to identify the serial type. This is so because Drizzle is made to work with different database engines, while the serial type is exclusive to PostgreSQL. Use the integer() type rather than the serial() type to solve the issue, as  integer() is a common SQL type that is supported by all database engines.

Try this code, here I used integer() type rather than the serial() type:

export const likes = pgTable("likes", {
  postId: text("post_id").notNull().primaryKey(),
  authorId: integer("author_id"),
});

export const posts = pgTable("post", {
  id: integer("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: integer("id").notNull().primaryKey(),
  username: text("username").unique(),
  name: text("name"),
  email: text("email").notNull(),
  emailVerified: timestamp("emailVerified", { mode: "date" }),
  image: text("image"),
});

Additionally, unlike serial type, the integer() type does not support auto-incrementing, so try using this SQL query to allow auto-incrementing for posts table's id column:

CREATE SEQUENCE post_id_seq;
ALTER TABLE posts ALTER COLUMN id SET DEFAULT nextval('post_id_seq');

Hope it works :)