TypeORM: Doesn't create database table when running migrations using SQLITE

Question

I am currently facing an issue with using migrations with TypeORM and a sqlite3 database. I am looking to have as much parity as possible between local/testing/staging/production environments by using only environment variables at runtime that will be passed to docker in docker-compose files. I would like to avoid using the ormconfig.json file and synchronize methods.

For now, locally, I am trying to run migrations using a dev configuration file, which seems to be working, but the migrations table is somehow empty and the URL entity table is never created. The issue persists even when the database or dev.db file doesn't exist at all or is brand new using:

$ sqlite3 src/db/storage/dev.db

File structure:

backend
 ┣ env
 ┃ ┣ dev.env
 ┃ ┣ prod.env
 ┃ ┗ test.env
 ┣ scripts
 ┣ src
 ┃ ┣ db
 ┃ ┃ ┣ entity
 ┃ ┃ ┣ migration
 ┃ ┃ ┗ storage
 ┃ ┣ tests
 ┃ ┃ ┗ api.test.ts
 ┃ ┣ utils
 ┃ ┃ ┗ url.ts
 ┃ ┣ index.ts
 ┃ ┗ server.ts
 ┣ .babelrc
 ┣ .dockerignore
 ┣ .gitignore
 ┣ .prettierrc.json
 ┣ Dockerfile
 ┣ README.md
 ┣ jest.config.ts
 ┣ package.json
 ┣ tsconfig.json
 ┣ tsconfig.prod.json
 ┗ yarn.lock

My env file (dev.env):

TYPEORM_CONNECTION = better-sqlite3
TYPEORM_DATABASE = src/db/storage/dev.db
TYPEORM_ENTITIES = src/db/entity/*.ts 
TYPEORM_MIGRATIONS_DIR = src/db/migration/*.ts

TYPEORM_LOGGING = all

Package.json (scripts):

{
...
"scripts": {
   "typeorm": "ts-node --transpile-only ./node_modules/typeorm/cli.js",
   "typeorm:generate-migration": "yarn typeorm migration:generate",
   "typeorm:create-migration": "yarn typeorm migration:create",
   "typeorm:run-migration": "yarn typeorm migration:run",
   "typeorm:revert-migration": "yarn typeorm migration:revert",
   },
"dependencies": {
  ...
  "better-sqlite3": "^7.4.3",
  "reflect-metadata": "^0.1.13",
  "typeorm": "0.2.34"
  ...
}
...
}

My entity:

import {EntitySchema} from "typeorm"

export interface Url {
  id: number
  url: string
}

export const UrlEntity = new EntitySchema<Url>({
  name: "Url",
  columns: {
    id: {
      type: Number,
      primary: true,
      generated: true
    },
    url: {
      type: String
    }
  }
})

What I have tried:

• Generating the migration file

$ yarn typeorm:generate-migration -n CreateDatabase --config env/dev.env

Migration file content:

import {MigrationInterface, QueryRunner} from "typeorm";

export class CreateDatabase1627595908837 implements MigrationInterface {
    name = 'CreateDatabase1627595908837'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE TABLE "url" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "url" varchar NOT NULL)`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TABLE "url"`);
    }

}

• Running the migration which generates the migrations table (which somehow is empty) and doesn't create the url table

$ yarn typeorm:run-migration --config ./env/dev.env 

Output from the command:

$ query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'migrations'
$ query: CREATE TABLE "migrations" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "timestamp" bigint NOT NULL, "name" varchar NOT NULL)
query: SELECT * FROM "migrations" "migrations" ORDER BY "id" DESC
$ No migrations are pending
~``

Answer 1

I figured out the solution to this problem.

When using ormconfig.json and using the typeorm CLI we need to be explicit about the directories for subscribers, entities and migrations, same holds true when using environment variables. If these are not provided, or only one of them in my case, you will end up creating a migrations folder with the name "/path/*.ts", which is incorrect behaviour, even if it means it still creates the file, but then doesn't know where to lookup using the cli tool.

Before:

TYPEORM_CONNECTION = better-sqlite3
TYPEORM_DATABASE = src/db/storage/dev.db
TYPEORM_ENTITIES = src/db/entity/*.ts 
TYPEORM_MIGRATIONS_DIR = src/db/migration/*.ts
TYPEORM_LOGGING = all

After:

TYPEORM_CONNECTION = "better-sqlite3"
TYPEORM_DATABASE = src/db/storage/dev.db
TYPEORM_ENTITIES = src/db/entity/*.ts
TYPEORM_MIGRATIONS = src/db/migrations/*.ts
TYPEORM_ENTITIES_DIR = src/db/entity
TYPEORM_MIGRATIONS_DIR = src/db/migrations
TYPEORM_LOGGING = all

After running migrations, this was the output:

$ yarn typeorm migration:run --config ./env/dev.env

$ ts-node --transpile-only ./node_modules/typeorm/cli.js migration:run --config ./env/dev.env

query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'migrations'

query: CREATE TABLE "migrations" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "timestamp" bigint NOT NULL, "name" varchar NOT NULL)

query: SELECT * FROM "migrations" "migrations" ORDER BY "id" DESC

0 migrations are already loaded in the database.
1 migrations were found in the source code.
1 migrations are new migrations that needs to be executed.

query: BEGIN TRANSACTION

query: CREATE TABLE "url" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "url" varchar NOT NULL)

query: INSERT INTO "migrations"("timestamp", "name") VALUES (?, ?) -- PARAMETERS: [1627601766574,"CreateDatabase1627601766574"]

Migration CreateDatabase1627601766574 has been executed successfully.
query: COMMIT

I hope this helps other people having the same issue as I did.