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;
}

Answer 2

I run into a similar problem. I'm using Postgres and wanted to use SQLite for unit tests.

@Hung's answer was basically what I was looking for.

However, I am using JSON types alongside default values so I had to add some improvements there.

I'm posting it here to share with you and I hope it will be useful to somebody else.

const Env = {isTest: process.env.NODE_ENV === 'test'};

function resolveType(type: ColumnType): ColumnType {
  if (!Env.isTest) return type;

  if (type === 'timestamp') return 'datetime';
  if (type === 'mediumblob') return 'blob';
  if (type === 'mediumtext') return 'text';
  if (type === 'jsonb') return 'text';
  if (type === 'json') return 'text';
  if (type === 'enum') return 'text';
  if (type === 'uuid') return 'text';

  return type;
}

function resolveDefault(defaultValue: unknown): any {
  if (!Env.isTest) return defaultValue;

  const whitelist = ['string', 'number'];
  const type = typeof defaultValue;
  if (!whitelist.includes(type)) return JSON.stringify(defaultValue);

  return defaultValue;
}

export function EnvSpecificDecoratorValue(options: ColumnOptions) {
  if (options.type) options.type = resolveType(options.type);
  if (options.default) options.default = resolveDefault(options.default);

  return options;
}



import { ColumnOptions, Column as OriginalColumn } from 'typeorm';
import { UpdateDateColumn as OriginalUpdateDateColumn } from 'typeorm';
import { CreateDateColumn as OriginalCreateDateColumn } from 'typeorm';


export function Column(columnOptions: ColumnOptions) {
  return OriginalColumn(EnvSpecificDecoratorValue(columnOptions));
}

export function CreateDateColumn(columnOptions: ColumnOptions) {
  return OriginalCreateDateColumn(EnvSpecificDecoratorValue(columnOptions));
}

export function UpdateDateColumn(columnOptions: ColumnOptions) {
  return OriginalUpdateDateColumn(EnvSpecificDecoratorValue(columnOptions));
}

Now you can reference Column, CreateDateColumn, UpdateDateColumn as it was the original decorator but imported from your local file.