How to transition from postgress sql to mysql/mariadb ?

[ishanabdul3]

Can you please provide some steps or config change for using mysql/mariadb in typeorm

[NarHakobyan]

Hi @ishanabdul3, just edit configurations in ormconfig.ts and api-config.service.ts files. and recreate all migrations.

[SaadBazaz]

This doesn't work (fully). The default migrations (Create tables of users, posts etc) are in PostgresSQL.

[NarHakobyan]

you must delete and recreate all migrations.

[SaadBazaz]

Yep! Did that and it worked. Too bad I didn't know that before and tried to rewrite all the migration SQL by hand 🤣 It even worked.
Newbie.

In the process I also converted all the ids to incremental ids, instead of UUID. Oof.

[NarHakobyan]

Use Typeorm's migration generator instead 😆

[SaadBazaz]

Just a hint for newbs like me:
(assuming you have installed mysql in your system and it is running on port 3306)

1. Change the DB in TypeORM to MySQL. You can do that by heading over to the file ormconfig.ts.

...
export const dataSource = new DataSource({
  type: 'mysql', // <-- Just write mysql here
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  namingStrategy: new SnakeNamingStrategy(),
  subscribers: [UserSubscriber],
  entities: [
    'src/modules/**/*.entity{.ts,.js}',
    'src/modules/**/*.view-entity{.ts,.js}',
  ],
  migrations: ['src/database/migrations/*{.ts,.js}'],
});

2. Delete all the files in migrations folder (src/database/migrations)
3. Run the following commands to regenerate the migrations:

yarn typeorm migration:generate ./src/database/migrations/MySQLMigrations

[NarHakobyan]

@SaadBazaz you have done great work, it would be nice if you can open PR and add this into the docs 💪

[SaadBazaz]

@SaadBazaz you have done great work, it would be nice if you can open PR and add this into the docs 💪

My pleasure. I'll do it in a bit.

[SaadBazaz]

Done! #306

[SaadBazaz]

I also wrote this docker-compose file for mysql + NestJS Awesome Boilerplate. Works for me.

version: "3"
services:
  app:
    env_file:
      - .env
    container_name: awesome_nest_boilerplate
    restart: always
    build: .
    ports:
      - "$PORT:$PORT"
    links:
      - mysql
    environment:
      DB_HOST: mysql
  mysql:
    image: mysql
    container_name: mysql
    restart: always
    environment:
      MYSQL_DATABASE: 'nest_boilerplate'
      # So you don't have to use root, but you can if you like
      MYSQL_USER: 'user'
      # You can use whatever password you like
      MYSQL_PASSWORD: 'password'
      # Password for root access
      MYSQL_ROOT_PASSWORD: 'password'
      MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
    ports:
      # <Port exposed> : < MySQL Port running inside container>
      - '3306:3306'
    expose:
      # Opens port 3306 on the container
      - '3306'
      # Where our data will be persisted
    volumes:
      - mysql:/data/mysql
      # - ./init-data.sh:/docker-entrypoint-initdb.d/init-data.sh
    env_file:
      - .env
  # pgadmin:
  #   container_name: pgadmin
  #   image: dpage/pgadmin4
  #   ports:
  #     - "8080:80"
  #   volumes:
  #     - /data/pgadmin:/root/.pgadmin
  #   env_file:
  #     - .env
  #   links:
  #     - postgres

volumes:
  mysql:
    driver: local