TypeORM column type dependant on database

Question

I have simple entity

@Entity()
export class File {
    @PrimaryGeneratedColumn()
    id: number;
    @Column({type: "mediumblob"})
    data: Buffer;
}

Which I want to use on production with Mysql ("mediumblob" because I want to store 10MB files).

I also want to run integration tests but with sqlite but it supports only "blob".

Then I want test like that:

describe("CR on File and blobs", () => {
    it("should store arbitrary binary file", async (done) => {
        const conn = await createConnection({
            type: "sqlite",
            database: ":memory:",
            entities: ["src/models/file.ts"],
            synchronize: true
        });
        const fileRepo = await conn.getRepository(File);
        fileRepo.createQueryBuilder("file")
            .where("file.id == :id", {id: 1})
            .select([
                "file.data"
            ])
            .stream();
        done();
    });
});

When I run such code I get error like this DataTypeNotSupportedError: Data type "mediumblob" in "File.data" is not supported by "sqlite" database.

If I change column type to blob then for mysql I get following error when uploading 116kb file QueryFailedError: ER_DATA_TOO_LONG: Data too long for column 'data' at row 1

Is it somehow possible to generate some kind of logic/mapping to work arround incompatibility of mysql/sqlite so "blob" is used for sqlite while "mediumblob" is used for mysql?

Answer 1

We can create a simple decorator, @DbAwareColumn, on top of @Column. The new decorator corrects a column type based on environment. I expect you're using sqlite for the test environment

import { Column, ColumnOptions, ColumnType } from 'typeorm';

const mysqlSqliteTypeMapping: { [key: string]: ColumnType } = {
  'mediumtext': 'text',
  'timestamp': 'datetime',
  'mediumblob': 'blob'
};

export function resolveDbType(mySqlType: ColumnType): ColumnType {
  const isTestEnv = process.env.NODE_ENV === 'test';
  if (isTestEnv && mySqlType in mysqlSqliteTypeMapping) {
    return mysqlSqliteTypeMapping[mySqlType.toString()];
  }
  return mySqlType;
}

export function DbAwareColumn(columnOptions: ColumnOptions) {
  if (columnOptions.type) {
    columnOptions.type = resolveDbType(columnOptions.type);
  }
  return Column(columnOptions);
}

In entities, we can use it as

@Entity({name: 'document'})
export class Document {

  @DbAwareColumn({ name: 'body', type: 'mediumtext'})
  body: string;

  @DbAwareColumn({type: "mediumblob"})
  data: Buffer;

  @DbAwareColumn({type: "timestamp"})
  createdAt: Date;
}