SQL syntax error when trying to create a new table using TypeORM

I'm doing the backend for a project using NodeJS. To manage the DB I'm using typeorm.

I've created several tables with no issues.

Now I'm trying to create a new table (or entity) that has a date column, and that column uses the decorator @CreateDateColumn() which should insert the current date when a new row is added, like this:

@Entity()
export class Order{
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    first_name: string
    
    @Column()
    last_name: string

    @Column()
    email: string

    @CreateDateColumn() //This one here
    created_at: string

    @OneToMany(() => OrderItem, orderItem => orderItem.order)
    order_items: OrderItem[]
}

The fact that I set the type as string is not the issue, as I have tried to make it Date with the same results.

When I save the file and typeorm tries to create the table, this is what I get back:

code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax near '(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (`id`)) ENGINE=InnoDB' at line 1",
  sql: 'CREATE TABLE `order` (`id` int NOT NULL AUTO_INCREMENT, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (`id`)) ENGINE=InnoDB'

When I comment the decorator out, the issue is gone, so it is that SQL part for sure.

After googling around someone mentioned that SQL versions could be an issue. The SQL version of the DB i'm using is Server version: 5.5.62-0ubuntu0.14.04.1

Any ideas?

Solution

The syntax error is because TypeORM is trying to define the column as created_at DATETIME(6) with microsecond precision.

This causes an error on MySQL 5.5, because the fractional datetime data type wasn't implemented until MySQL 5.6.

Read https://github.com/typeorm/typeorm/issues/609 for a discussion of the back-and-forth about supporting fractional datetimes for versions of MySQL that support it, while providing a workaround for older versions that don't support it.

You should be able to define the data type explicitly, as mentioned in that issue.

Honestly, you should just upgrade your version of MySQL to version 8.0. Version 5.6 expired in 2021, and also version 5.7 expired in 2023. See https://endoflife.date/mysql

You're still using version of MySQL 5.5 from 2010, so it's no surprise that modern features are not supported, and it's not wrong for TypeORM to assume you're using a more recent version.