Change default "timestamps" column name

[mordechaim]

Hi,
I like to use the timestamp names created and updated instead of the default createdAt and updatedAt.

Is it possible to change it and still get the default update hook?

[romeerez]

Currently, it is not supported, because the logic will be checking if there is already a value for updatedAt key, and if not, it will add updatedAt to the update query.

I'll come up with something to make this configurable.

[romeerez]

Done, I published an update, now this it's possible to set custom names in a such way:

export class SomeTable extends BaseTable {
  readonly table = 'someTable';
  columns = this.setColumns((t) => ({
    created: t.timestamps().createdAt,
    updated: t.timestamps().updatedAt,
  }));
}

To they are named differently in the database use name():

export class SomeTable extends BaseTable {
  readonly table = 'someTable';
  columns = this.setColumns((t) => ({
    created: t.timestamps().createdAt.name('created_in_db'),
    updated: t.timestamps().updatedAt.name('updated_in_db'),
  }));
}

[mordechaim]

You're quick! Thanks

[mordechaim]

I don't know what has changed, but installing the latest version causes a typescript error with a simple migration file. Reinstalling 1.24.3 resolves this error.

import { change } from '../dbScript';

change(async (db) => {
  await db.createTable('account_user', (t) => ({
    id: t.identity().primaryKey(),
    name: t.text(0, 1000)
  }));
});

No overload matches this call.
  Overload 1 of 2, '(tableName: "account_user", fn?: ColumnsShapeCallback<{ timestamp: (precision?: number | undefined) => ParseColumn<DateBaseColumn<DefaultSchemaConfig>, unknown, Date>; ... 59 more ...; timestampsNoTZSnakeCase<T extends ColumnTypeBase<...>>(this: { ...; }): Timestamps<...>; }, ColumnsShape> | undefined): Promise<...>', gave the following error.
    Argument of type '(t: Omit<{ timestamp: (precision?: number | undefined) => ParseColumn<DateBaseColumn<DefaultSchemaConfig>, unknown, Date>; ... 59 more ...; timestampsNoTZSnakeCase<T extends ColumnTypeBase<...>>(this: { ...; }): Timestamps<...>; }, "string" | ... 1 more ... | "enum"> & { ...; } & { ...; }) => { ...; }' is not assignable to parameter of type 'ColumnsShapeCallback<{ timestamp: (precision?: number | undefined) => ParseColumn<DateBaseColumn<DefaultSchemaConfig>, unknown, Date>; schema: DefaultSchemaConfig; ... 58 more ...; timestampsNoTZSnakeCase<T extends ColumnTypeBase<...>>(this: { ...; }): Timestamps<...>; }, ColumnsShape>'.
      Type '{ id: PrimaryKeyColumn<ColumnWithDefault<IntegerColumn<DefaultSchemaConfig>, Expression<QueryColumn<unknown, CoreBaseOperators>>>>; name: TextColumn<...>; }' is not assignable to type 'ColumnsShape'.
        Property 'id' is incompatible with index signature.
          Type 'PrimaryKeyColumn<ColumnWithDefault<IntegerColumn<DefaultSchemaConfig>, Expression<QueryColumn<unknown, CoreBaseOperators>>>>' is not assignable to type 'ColumnType<ColumnTypeSchemaArg, unknown, any, BaseOperators, unknown, unknown, any, unknown, any>'.
            Types of property 'primaryKey' are incompatible.
              Type '<T extends import("c:/Users/morde/workspace/next-template/node_modules/orchid-orm/node_modules/orchid-core/dist/index").PickColumnBaseData>(this: T) => import("c:/Users/morde/workspace/next-template/node_modules/orchid-orm/node_modules/orchid-core/dist/index").PrimaryKeyColumn<T>' is not assignable to type '<T extends import("c:/Users/morde/workspace/next-template/node_modules/orchid-core/dist/index").PickColumnBaseData>(this: T) => import("c:/Users/morde/workspace/next-template/node_modules/orchid-core/dist/index").PrimaryKeyColumn<T>'.
                The 'this' types of each signature are incompatible.
                  Type 'T' is not assignable to type 'PickColumnBaseData'.
                    Type 'import("c:/Users/morde/workspace/next-template/node_modules/orchid-core/dist/index").PickColumnBaseData' is not assignable to type 'import("c:/Users/morde/workspace/next-template/node_modules/orchid-orm/node_modules/orchid-core/dist/index").PickColumnBaseData'.
                      Types of property 'data' are incompatible.
                        Property 'key' is missing in type 'import("c:/Users/morde/workspace/next-template/node_modules/orchid-core/dist/index").ColumnDataBase' but required in type 'import("c:/Users/morde/workspace/next-template/node_modules/orchid-orm/node_modules/orchid-core/dist/index").ColumnDataBase'.
  Overload 2 of 2, '(tableName: "account_user", options: TableOptions, fn?: ColumnsShapeCallback<{ timestamp: (precision?: number | undefined) => ParseColumn<DateBaseColumn<DefaultSchemaConfig>, unknown, Date>; ... 59 more ...; timestampsNoTZSnakeCase<T extends ColumnTypeBase<...>>(this: { ...; }): Timestamps<...>; }, ColumnsShape> | undefined): Promise<...>', gave the following error.
    Type '(t: Omit<{ timestamp: (precision?: number | undefined) => ParseColumn<DateBaseColumn<DefaultSchemaConfig>, unknown, Date>; ... 59 more ...; timestampsNoTZSnakeCase<T extends ColumnTypeBase<...>>(this: { ...; }): Timestamps<...>; }, "string" | ... 1 more ... | "enum"> & { ...; } & { ...; }) => { ...; }' has no properties in common with type 'TableOptions'.

[mordechaim]

As I already have you here, is it possible to define default columns so it is present in every table automatically? Such as in the BaseTable (including inside migrations...)?

[mordechaim]

Another issue, when running the migration (with @ts-ignore to ignore the ts error above) I get the following error:

(20.6ms) CREATE TABLE "account_user" (
  "id" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" text NOT NULL,
  "created_at" timestamp with time zone NOT NULL DEFAULT '{"_sql":"now()"}',
  "updated_at" timestamp with time zone NOT NULL DEFAULT '{"_sql":"now()"}'
) Error: invalid input syntax for type timestamp with time zone: "{"_sql":"now()"}"
error: invalid input syntax for type timestamp with time zone: "{"_sql":"now()"}"

[romeerez]

The type error looks exactly like in the case when you update rake-db but not the ORM, or vice-versa.

I don't know how to resolve this in principle. Both libs depends on a "core" lib, when you update one but not another it results in two "core" versions installed simultaneously, and types are conflicting.

Another issue, when running the migration (with @ts-ignore to ignore the ts error above) I get the following error:

Ooops, fixing.

is it possible to define default columns so it is present in every table automatically?

Yes:

export const BaseTable = createBaseTable({
  columnTypes: (t) => ({
    defaultColumns: () => ({
      created: t.timestamps().createdAt,
      updated: t.timestamps().updatedAt,
    }),
  }),
});

export class SomeTable extends BaseTable {
  readonly table = 'someTable';
  columns = this.setColumns((t) => ({
    ...t.defaultColumns()
  }));
}

And it should be also available in migrations if you set baseTable in the migration config.

[romeerez]

Another issue, when running the migration (with @ts-ignore to ignore the ts error above) I get the following error:

Not broken, it's also because of incompatible ORM and rake-db versions.

They both depend on a "core" package, you updated one of them and there are two versions of "core".
Two "cores" export SQL class, rake-db checks if the default is instance of SQL class, and it's not an instance because that's a class from a different version of "core".

So update both "orchid-orm" and "rake-db" to the latest versions and it should become fine.

I'll try to make something with it, at least to detect this and throw an informative message.

[mordechaim]

Ok so now I updated rake-db, looking at the generated table definition I get a bunch of ts errors

import { BaseTable } from '../baseTable';

export class AccountUserTable extends BaseTable {
  readonly table = 'account_user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    created: t.timestamp().default(t.sql({ raw: 'now()' })),
    updated: t.timestamp().default(t.sql({ raw: 'now()' })).modifyQuery((q, column) => {
      var _a;
      if (!updatedAtInjector) {
        const key = column.data.key;
        const name = (_a = column.data.name) != null ? _a : key;
        const nowSql = new SimpleRawSQL(`"${name}" = ${now}`);
        const updatedAtRegex = makeRegexToFindInSql(`\\b${name}\\b"?\\s*=`);
        updatedAtInjector = (data) => {
          const alreadyUpdatesUpdatedAt = data.some((item) => {
            if (isRawSQL(item)) {
              updatedAtRegex.lastIndex = 0;
              return updatedAtRegex.test(
                typeof item._sql === "string" ? item._sql : item._sql[0].join("")
              );
            } else {
              return typeof item !== "function" && item[key];
            }
          });
          return alreadyUpdatesUpdatedAt ? void 0 : nowSql;
        };
      }
      pushOrNewArrayToObject(q.q, "updateData", updatedAtInjector);
    }),
    name: t.text(0, 1000),
  }));
}

[romeerez]

I'll fix the generator, for now just replace created and updated with

      created: t.timestamps().createdAt,
      updated: t.timestamps().updatedAt,

[mordechaim]

Ok, tested it, everything works wonderfully (except the codegen).

I sponsored your project to show a tiny token of appreciation.

[romeerez]

Thank you! I'm really appreciating that!

[mordechaim]

Some further testing and I found the following.

---

The following code will still generate the default created_at and updated_at columns:

export class UserTable extends BaseTable {
  readonly table = 'account_user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.text(0, 1000),
    created: t.timestamps().createdAt,
    updated: t.timestamps().updatedAt
  }));
}

The app code gets the correct name but the database still has the default column name.

While:

export class UserTable extends BaseTable {
  readonly table = 'account_user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.text(0, 1000),
    created: t.timestamps().createdAt.name('created'),
    updated: t.timestamps().updatedAt.name('updated')
  }));
}

does work correctly, but requires repeating the name even if it's identical with the definition key.

---

If I use default columns from base table, such as:

export const BaseTable = createBaseTable({
    snakeCase: true,

    columnTypes: (t) => ({
        ...t,
        timestamp: (precision?: number) => t.timestamp(precision).asDate(),
        defaults: () => ({
            id: t.identity().primaryKey(),
            created: t.timestamps().createdAt,
            updated: t.timestamps().updatedAt
        })
    })
})

And used as

export class UserTable extends BaseTable {
  readonly table = 'account_user';
  columns = this.setColumns((t) => ({
    ...t.defaults(),
    name: t.text(0, 1000)
  }));
}

The column names are now correct in the database even without providing the name, but interestingly, the timestamp type is now string not Date.

[romeerez]

I've fixed the code generator, now it should output nicely.

but requires repeating the name even if it's identical with the definition key.

Now that's not needed, a single name (the column key) is enough.

It's not straightforward to solve the issue with different type in your defaults, because the code inside defaults can't see the overridden timestamp nearby.

One option would be to duplicate asDate:

export const BaseTable = createBaseTable({
    snakeCase: true,
    columnTypes: (t) => ({
        ...t,
        timestamp: (precision?: number) => t.timestamp(precision).asDate(),
        defaults: () => ({
            id: t.identity().primaryKey(),
            created: t.timestamps().createdAt.asDate(),
            updated: t.timestamps().updatedAt.asDate()
        })
    })
})

Or, you can use this so this.timestamps() will have access to the overridden timestamp (I didn't test this but should work):

export const BaseTable = createBaseTable({
  snakeCase: true,
  columnTypes: (t) => ({
    ...t,
    // Parse timestamps to Date object.
    timestamp: (precision?: number) => t.timestamp(precision).asDate(),
    defaults() {
      return {
        id: t.identity().primaryKey(),
        created: this.timestamps().createdAt,
        updated: this.timestamps().updatedAt,
      };
    },
  }),
});

Or to somehow construct and reuse objects inside columnTypes function, but that would be cumbersome.