TypeORM迁移中@CreateDateColumn时间戳未递增问题排查

我使用TypeORM和PostgreSQL作为数据库，在Post实体中通过@CreateDateColumn()自动生成createdAt列。执行npx typeorm migration:create -n FakePosts创建迁移，向数据库添加5条测试帖子，但所有帖子的createdAt时间戳均为同一值"2023-05-30T02:40:32.318Z"。请问这是预期行为吗？我哪里操作有误？如何配置TypeORM实现单次迁移中createdAt时间戳递增？

实体文件 /entity/Post.ts #

@ObjectType()
@Entity()
export class Post extends BaseEntity {
  @Field()
  @PrimaryGeneratedColumn()
  id!: number;

  @Field()
  @CreateDateColumn()
  createdAt: Date;

  @Field()
  @Column()
  title!: string;
}

迁移文件 /migration/1685412921219-FakePosts.ts #

import { MigrationInterface, QueryRunner } from "typeorm";

export class FakePosts1685414339712 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
    insert into post (title, text, "authorId") values ('American Madness', 'In sagittis dui vel nisl. Duis ac nibh. Fusce lacus purus, aliquet at, feugiat non, pretium quis, lectus.

    Suspendisse potenti. In eleifend quam a odio. In hac habitasse platea dictumst.
    
    Maecenas ut massa quis augue luctus tincidunt. Nulla mollis molestie lorem. Quisque ut erat.', 1);
    insert into post (title, text, "authorId") values ('It''s Pat', 'Etiam vel augue. Vestibulum rutrum rutrum neque. Aenean auctor gravida sem.', 1);
    insert into post (title, text, "authorId") values ('Wicked Little Things', 'Pellentesque at nulla. Suspendisse potenti. Cras in purus eu magna vulputate luctus.
    
    Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Vivamus vestibulum sagittis sapien. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.
    
    Etiam vel augue. Vestibulum rutrum rutrum neque. Aenean auctor gravida sem.', 1);
    insert into post (title, text, "authorId") values ('There''s Only One Sun', 'Proin interdum mauris non ligula pellentesque ultrices. Phasellus id sapien in sapien iaculis congue. Vivamus metus arcu, adipiscing molestie, hendrerit at, vulputate vitae, nisl.
    
    Aenean lectus. Pellentesque eget nunc. Donec quis orci eget orci vehicula condimentum.', 1);
    insert into post (title, text, "authorId") values ('Fairly Odd Movie: Grow Up, Timmy Turner!, A', 'In congue. Etiam justo. Etiam pretium iaculis justo.
    
    In hac habitasse platea dictumst. Etiam faucibus cursus urna. Ut tellus.
    
    Nulla ut erat id mauris vulputate elementum. Nullam varius. Nulla facilisi.', 1);
    
    `);
  }

  public async down(_: QueryRunner): Promise<void> {}
}

查询结果 #

{
  "data": {
    "posts": [
      {
        "id": 1,
        "createdAt": "2023-05-30T02:40:32.318Z",
        "title": "American Madness"
      },
      {
        "id": 2,
        "createdAt": "2023-05-30T02:40:32.318Z",
        "title": "It's Pat"
      },
      {
        "id": 3,
        "createdAt": "2023-05-30T02:40:32.318Z",
        "title": "Wicked Little Things"
      },
      {
        "id": 4,
        "createdAt": "2023-05-30T02:40:32.318Z",
        "title": "There's Only One Sun"
      },
      {
        "id": 5,
        "createdAt": "2023-05-30T02:40:32.318Z",
        "title": "Fairly Odd Movie: Grow Up, Timmy Turner!, A"
      }
    ]
  }
}

这是预期行为吗？ #

是，这是PostgreSQL原生SQL批量插入的特性导致的：

• TypeORM的@CreateDateColumn()会将数据库列的默认值设为now()，但PostgreSQL在单次SQL语句执行过程中，now()只会计算一次并返回同一个时间戳，所以批量插入的所有行都会共用这个值。
• 如果是通过TypeORM实体管理器（如repository.save()）插入数据，不会出现这个问题，因为TypeORM会为每个实体单独处理时间戳。

哪里操作有误？ #

你在迁移中使用了单条原生SQL语句批量插入，没有为createdAt指定值，依赖数据库默认的now()生成时间，而PostgreSQL的这个函数在单次SQL执行中只会返回一次结果，导致所有行的createdAt相同。

如何实现单次迁移中createdAt时间戳递增？ #

有三种可行方案：

方案1：拆分SQL语句，逐行执行 #

把原来的单条批量INSERT拆分成多个独立的queryRunner.query()调用，每次执行一条INSERT语句，这样每次执行时now()都会重新计算：

public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`insert into post (title, text, "authorId") values ('American Madness', 'In sagittis dui vel nisl...', 1);`);
  await queryRunner.query(`insert into post (title, text, "authorId") values ('It''s Pat', 'Etiam vel augue...', 1);`);
  await queryRunner.query(`insert into post (title, text, "authorId") values ('Wicked Little Things', 'Pellentesque at nulla...', 1);`);
  await queryRunner.query(`insert into post (title, text, "authorId") values ('There''s Only One Sun', 'Proin interdum mauris...', 1);`);
  await queryRunner.query(`insert into post (title, text, "authorId") values ('Fairly Odd Movie: Grow Up, Timmy Turner!, A', 'In congue. Etiam justo...', 1);`);
}

方案2：手动指定递增的时间戳 #

在INSERT语句中直接给createdAt赋值，基于now()递增固定时间间隔：

public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`
    insert into post (title, text, "authorId", "createdAt") values ('American Madness', '...', 1, now());
    insert into post (title, text, "authorId", "createdAt") values ('It''s Pat', '...', 1, now() + interval '1 second');
    insert into post (title, text, "authorId", "createdAt") values ('Wicked Little Things', '...', 1, now() + interval '2 seconds');
    insert into post (title, text, "authorId", "createdAt") values ('There''s Only One Sun', '...', 1, now() + interval '3 seconds');
    insert into post (title, text, "authorId", "createdAt") values ('Fairly Odd Movie: Grow Up, Timmy Turner!, A', '...', 1, now() + interval '4 seconds');
  `);
}

方案3：使用TypeORM实体管理器插入数据 #

避免原生SQL，直接用TypeORM的Repository API创建并保存实体，TypeORM会自动为每个实体生成独立的createdAt时间戳：

import { MigrationInterface, QueryRunner } from "typeorm";
import { Post } from "../entity/Post";

export class FakePosts1685414339712 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    const postRepo = queryRunner.manager.getRepository(Post);
    
    const postDataList = [
      { title: 'American Madness', text: 'In sagittis dui vel nisl...', authorId: 1 },
      { title: 'It\'s Pat', text: 'Etiam vel augue...', authorId: 1 },
      { title: 'Wicked Little Things', text: 'Pellentesque at nulla...', authorId: 1 },
      { title: 'There\'s Only One Sun', text: 'Proin interdum mauris...', authorId: 1 },
      { title: 'Fairly Odd Movie: Grow Up, Timmy Turner!, A', text: 'In congue. Etiam justo...', authorId: 1 },
    ];

    // 逐个保存确保时间戳独立，可选加微小延迟保证差异
    for (const data of postDataList) {
      await postRepo.save(data);
      await new Promise(resolve => setTimeout(resolve, 10));
    }
  }

  public async down(_: QueryRunner): Promise<void> {}
}

内容的提问来源于stack exchange，提问作者Zoe L