typeorm#ConnectionOptions TypeScript Examples

static showTableStatus = async(connectOption: ConnectionOptions):Promise<MysqlTableStatus[]> => {
    return new Promise(async(resolve, reject) => {
      const name = connectOption.name;
      let tableStatuses:MysqlTableStatus[] = []
      try {
        await createConnections([connectOption])
        const manager = await getManager(name)
        tableStatuses = await manager.query("SHOW TABLE STATUS;");
      } catch(err) {
        await getConnection(name).close();
        reject(err);
        return;
      } finally {
        await getConnection(name).close();
      }
      resolve(tableStatuses);
    });
  };

export default function config(name?: string): ConnectionOptions {
  const conf = getDBConfig()
  return {
    name,
    type: 'postgres',
    host: conf.DB_HOST,
    port: conf.DB_PORT,
    username: conf.DB_USER,
    password: conf.DB_PASS,
    database: conf.DB_NAME,
    entities: [
      SubstrateEventEntity,
      SubstrateExtrinsicEntity,
      SubstrateBlockEntity,
    ],
    migrations: [`${migrationsDir}/v3/*.js`, `${migrationsDir}/v4/*.js`],
    cli: {
      migrationsDir: 'src/migrations/v3',
    },
    namingStrategy: new SnakeNamingStrategy(),
  }
}

async run(): Promise<void> {
        dotenv.config()

        let cfg: ConnectionOptions = {
            ...createOrmConfig(),
            subscribers: [],
            synchronize: false,
            migrationsRun: false,
            dropSchema: false,
            logging: ["query", "error", "schema"]
        }

        let connection = await createConnection(cfg)
        try {
            await connection.undoLastMigration({transaction: 'all'})
        } finally {
            await connection.close().catch(err => null)
        }
    }

async run(): Promise<void> {
        dotenv.config()

        let cfg: ConnectionOptions = {
            ...createOrmConfig(),
            subscribers: [],
            synchronize: false,
            migrationsRun: false,
            dropSchema: false,
            logging: ["query", "error", "schema"],
        }

        let connection = await createConnection(cfg)
        try {
            await connection.runMigrations({transaction: 'all'})
        } finally {
            await connection.close().catch(err => null)
        }
    }

export async function createConnection(): Promise<Connection | null> {
  if (!config.database.enable) {
    return null
  }

  let connection: Connection

  try {
    const options = Object.assign({}, config.database, {
      entities: [join(fs.resolveZenPath('entity'), `*${fs.resolveZenFileExtension()}`)],
    }) as ConnectionOptions

    connection = await typeormCreateConnection(options)
  } catch (e) {
    throw new Error(e)
  }

  return connection
}

config: ConnectionOptions = {
	type: process.env.TYPEORM_CONNECTION as "postgres",
	host: process.env.TYPEORM_HOST,
	port: Number(process.env.TYPEORM_PORT),
	username: process.env.TYPEORM_USERNAME,
	password: process.env.TYPEORM_PASSWORD,
	database: process.env.TYPEORM_DATABASE,
	entities: [...Entities],
	// ...(process.env.TYPEORM_SSL === "true" && {ssl: {
	// 	rejectUnauthorized: false,
	// }}),

	// We are using migrations, synchronize should be set to false.
	synchronize: false,
	migrationsRun: true,

	// Run migrations automatically,
	// you can disable this if you prefer running migration manually.
	// migrationsRun: true,
	logging: process.env.TYPEORM_LOGGING as LoggerOptions,

	// allow both start:prod and start:dev to use migrations
	// __dirname is either dist or src folder, meaning either
	// the compiled js in prod or the ts in dev
	migrations: [__dirname + '/database/migrations/**/*{.ts,.js}'],
	cli: {
		migrationsDir: 'src/database/migrations',
	},
}

connectionOptions: ConnectionOptions = {
  type: 'postgres',
  host: config.host || 'localhost',
  port: 5432,
  username: config.user || 'postgres',
  password: config.password || 'postgres',
  database: config.database || 'my_database',
  ssl: process.env.NODE_ENV === PROD_ENV
    ? { rejectUnauthorized: false }
    : false,
  entities: [
    join(__dirname, '../models/*{.ts,.js}'),
  ],
  // We are using migrations, synchronize should be set to false.
  synchronize: false,
  dropSchema: false,
  // Run migrations automatically,
  // you can disable this if you prefer running migration manually.
  migrationsRun: true,
  logging: ['warn', 'error'],
  logger: process.env.NODE_ENV === PROD_ENV ? 'file' : 'debug',
  migrations: [
    join(__dirname, 'migrations/*{.ts,.js}')
  ],
  cli: {
    migrationsDir: 'src/database/migrations'
  }
}

config: ConnectionOptions = {
  type: 'postgres',
  host: configService.get('DB_HOST'),
  port: +configService.get<number>('DB_PORT'),
  username: configService.get('DB_USERNAME'),
  password: configService.get('DB_PASSWORD'),
  database: configService.get('DB_DATABASE'),
  namingStrategy: new SnakeNamingStrategy(),
  entities: ['src/modules/**/*{.entity,.index}{.ts,.js}'],
  migrations: ['src/migrations/*{.ts,.js}'],
  migrationsRun: true,
  subscribers: [
    UserSubscriber,
    UserAuthSubscriber,
    UserAuthForgottenPasswordSubscriber,
  ],
  synchronize: false,
  logging: true,
}

connectionOptions: ConnectionOptions = {
  cli: {
    entitiesDir: 'src/packages/database/models',
    migrationsDir: 'src/packages/database/migrations',
  },
  database: config.DB.NAME,
  entities: ['src/packages/database/models/*.ts'],
  host: config.DB.HOST,
  logging: false,
  migrations: ['src/packages/database/migrations/*.ts'],
  password: config.DB.PASSWORD,
  port: config.DB.PORT,
  synchronize: false,
  type: 'postgres',
  username: config.DB.USER,
}

async function run() {
  // Initialize Nest logger
  const logger = new Logger('ParseSondeData');
  // Extract command line arguments
  const { f: filePath, s: siteId, p: surveyPointId, t: sourceType } = argv;

  logger.log(
    `Script params: filePath: ${filePath}, siteId/surveyPointId: ${siteId}/${surveyPointId}, sourceType: ${sourceType}`,
  );

  // Initialize typeorm connection
  const config = configService.getTypeOrmConfig() as ConnectionOptions;
  const connection = await createConnection(config);

  logger.log('Uploading sonde data');
  await uploadTimeSeriesData(
    filePath,
    last(filePath.split('/')) || '',
    siteId,
    surveyPointId,
    sourceType as SourceType,
    // Fetch all needed repositories
    {
      siteRepository: connection.getRepository(Site),
      surveyPointRepository: connection.getRepository(SiteSurveyPoint),
      timeSeriesRepository: connection.getRepository(TimeSeries),
      sourcesRepository: connection.getRepository(Sources),
      dataUploadsRepository: connection.getRepository(DataUploads),
    },
  );

  logger.log('Finished uploading sonde data');
}

run = async () => {
  // Extract command line arguments
  const { s: siteIds } = argv;

  // Cast siteIds into a number array.
  const parsedSiteIds = siteIds.map(Number);

  const config = configService.getTypeOrmConfig() as ConnectionOptions;
  const connection = await createConnection(config);

  return addWindWaveData(parsedSiteIds, {
    siteRepository: connection.getRepository(Site),
    hindcastRepository: connection.getRepository(ForecastData),
  });
}

async function run() {
  // Extract command line arguments
  const { d: days, s: siteIds, t: task } = argv;

  // Cast siteIds into a number array. If none are given return empty array
  const parsedSiteIds = siteIds ? siteIds.map(Number) : [];

  // Initialize typeorm connection
  const config = configService.getTypeOrmConfig() as ConnectionOptions;
  const connection = await createConnection(config);

  // Fetch selected task fn
  const fn = getTaskFn(task);

  // Run selected task
  return fn(
    parsedSiteIds,
    days,
    connection,
    // Fetch all needed repositories
    {
      siteRepository: connection.getRepository(Site),
      sourceRepository: connection.getRepository(Sources),
      timeSeriesRepository: connection.getRepository(TimeSeries),
      exclusionDatesRepository: connection.getRepository(ExclusionDates),
    },
  );
}

/**
 * 데이터베이스 커넥션을 생성한다.
 */
export async function createDatabaseConnection(): Promise<void> {
  try {
    const connectionOpts: ConnectionOptions = {
      type: "mysql",
      host: env.database.host,
      port: env.database.port,
      username: env.database.usename,
      password: env.database.password,
      database: env.database.name,
      synchronize: env.database.synchronize,
      logging: env.database.logging,
      entities: [__dirname + "/entities/*{.ts,.js}"],
    };

    useContainer(Container);
    await createConnection(connectionOpts);
  } catch (error) {
    throw error;
  }
}

static getColumns = async(connectOption: ConnectionOptions, table: string):Promise<MetaColumn[]> => {
    return new Promise(async(resolve, reject) => {
      const name = connectOption.name;
      let mysqlDescribeResults:MysqlDescribeResult[] = []
      let columns: MetaColumn[] = []
      try {
        await createConnections([connectOption])
        const manager = await getManager(name)
        mysqlDescribeResults = await manager.query(`DESCRIBE \`${table}\`;`);
        mysqlDescribeResults.forEach( result => {
          const metaCol = new MetaColumn();
          metaCol.originalColumnName = result.Field;
          metaCol.columnName = result.Field;
          const convertedType = MysqlHelper.convertType(result.Type);
          metaCol.type = convertedType.type;
          if(convertedType.size) metaCol.size = convertedType.size;
          columns.push(metaCol);
        })
      } catch(err) {
        await getConnection(name).close();
        reject(err);
        return;
      } finally {
        await getConnection(name).close();
      }
      resolve(columns);
    })
  }

static showTables = async(connectOption: ConnectionOptions) => {
    return new Promise(async(resolve, reject) => {
      const name = connectOption.name;
      let tables = []
      try {
        await createConnections([connectOption])
        const manager = await getManager(name)
        const results = await manager.query("SHOW tables;");
        
        
        results.forEach(row => {
          tables.push(row[`Tables_in_${connectOption.database}`]);
        });
      } catch(err) {
        await getConnection(name).close();
        reject(err);
        return;
      } finally {
        await getConnection(name).close();
      }
      resolve(tables);
    });
  };

defaultConnection: ConnectionOptions = {
  type: "mariadb",
  host: process.env.DESIGNER_META_DB_HOSTNAME || defaultConnectionInfo.host,
  port: Number(process.env.DESIGNER_META_DB_PORT) || defaultConnectionInfo.port,
  username: process.env.DESIGNER_META_DB_USERNAME || defaultConnectionInfo.username,
  password: process.env.DESIGNER_META_DB_PASSWORD || defaultConnectionInfo.password,
  database: process.env.DESIGNER_META_DB_NAME || defaultConnectionInfo.database,
  synchronize: true,
  logging: false,
  entities: [
    "src/entity/manager/*{.ts,.js}"
  ],
  migrations: [
    "src/migration/**/*.ts"
  ],
  subscribers: [
    "src/subscriber/**/*.ts"
  ],
  cli: {
    "entitiesDir": "src/entity/manager",
    "migrationsDir": "src/migration",
    "subscribersDir": "src/subscriber"
  }
}

connectDatabase = async (ormConfigOverride?: Partial<Writeable<ConnectionOptions & MysqlConnectionOptions>>,
    awaitCheck?: boolean) => {

    const env = loadEnv();
    await coreConnectDatabase({ ormConfigOverride, development: env.envMode === 'dev' });

    const args = yargs(process.argv.slice(2));
    const checking = checkData(args.init);
    if (awaitCheck) await checking;
}

connectDatabase = async () => {
    const connectionOptions: ConnectionOptions = {
        type: "sqlite",
        database: resolve(process.cwd(), 'db.sqlite3'),
        entityPrefix: 'crw_',
        synchronize: true,
        entities: [
            ...ORMEntities,
        ]
    }

    await createConnection(connectionOptions);
}

datasetConnection: ConnectionOptions =  {
  type: "mariadb",
  name: "dataset",
  host: process.env.DESIGNER_DATASET_HOSTNAME || datasetConnectionInfo.host,
  port: Number(process.env.DESIGNER_DATASET_PORT) || datasetConnectionInfo.port,
  username: process.env.DESIGNER_DATASET_USERNAME || datasetConnectionInfo.username,
  password: process.env.DESIGNER_DATASET_PASSWORD || datasetConnectionInfo.password,
  database: process.env.DESIGNER_DATASET_DATABASE || datasetConnectionInfo.database,
  synchronize: false,
  logging: true,
  entities: [
    "src/entity/dataset/*.ts"
  ]
}

constructor() {
        super();
        this.dbType = getStoreItem('dbInfo')?.dbType as ConnectionOptions['type']
            ?? getConnection().options.type;
    }

getSqlLike = (dbType: ConnectionOptions['type']) => {
    if (dbType === 'postgres') return 'ILIKE';
    return 'LIKE';
}

wrapInQuotes = (dbType: ConnectionOptions['type'], str: string) => {
    if (dbType === 'postgres') return `"${str}"`;
    return '`' + str + '`';
}

async run(): Promise<void> {
        dotenv.config()

        let {args} = await this.parse(CreateMigration)
        let name: string = args.name ? args.name : await CliUx.ux.prompt('Enter migration name', {
            required: true,
        })

        let cfg: ConnectionOptions = {
            ...createOrmConfig(),
            synchronize: false,
            migrationsRun: false,
            dropSchema: false,
            logging: false
        }

        let commands: SqlInMemory
        let connection = await createConnection(cfg)
        try {
            commands = await connection.driver.createSchemaBuilder().log()
        } finally {
            await connection.close().catch(err => null)
        }

        if (commands.upQueries.length == 0) {
            this.error('No changes in database schema were found - cannot generate a migration.')
        }

        let dir = new OutDir(assertNotNull(cfg.cli?.migrationsDir))
        let timestamp = Date.now()
        let out = dir.file(`${timestamp}-${name}.js`)
        out.block(`module.exports = class ${name}${timestamp}`, () => {
            out.line(`name = '${name}${timestamp}'`)
            out.line()
            out.block(`async up(db)`, () => {
                commands.upQueries.forEach(q => {
                    out.line(`await db.query${queryTuple(q)}`)
                })
            })
            out.line()
            out.block(`async down(db)`, () => {
                commands.downQueries.forEach(q => {
                    out.line(`await db.query${queryTuple(q)}`)
                })
            })
        })
        out.write()
    }

getMigrationsDirName = (dbType: ConnectionOptions['type']) => {
    if (dbType === 'sqlite') return 'migrations/sqlite';
    if (dbType === 'mysql' || dbType === 'mariadb') return 'migrations/mysql';
    if (dbType === 'postgres') return 'migrations/postgres';
}

constructor(
        private EntityClass: (new (...args: any[]) => EntityType & { id?: number }),
    ) {
        super();
        this.dbType = getStoreItem('dbInfo')?.dbType as ConnectionOptions['type']
            ?? getConnection().options.type;
    }

public dbType: ConnectionOptions['type'];

public dbType: ConnectionOptions['type'];

async function run() {
  // Initialize Nest logger
  const logger = new Logger('ParseHoboData');
  // Extract command line arguments
  const { p: rootPath, u: userEmail } = argv;

  logger.log(`Script params: rootPath: ${rootPath}, userEmail: ${userEmail}`);

  // Initialize typeorm connection
  const config = configService.getTypeOrmConfig() as ConnectionOptions;
  const connection = await createConnection(config);

  // Initialize google cloud service, to be used for media upload
  const googleCloudService = new GoogleCloudService(
    connection.getRepository(SurveyMedia),
  );

  logger.log('Uploading hobo data');
  const dbIdtTSiteId = await uploadHoboData(
    rootPath,
    userEmail,
    googleCloudService,
    // Fetch all needed repositories
    {
      siteRepository: connection.getRepository(Site),
      surveyPointRepository: connection.getRepository(SiteSurveyPoint),
      timeSeriesRepository: connection.getRepository(TimeSeries),
      userRepository: connection.getRepository(User),
      surveyRepository: connection.getRepository(Survey),
      surveyMediaRepository: connection.getRepository(SurveyMedia),
      sourcesRepository: connection.getRepository(Sources),
      regionRepository: connection.getRepository(Region),
      historicalMonthlyMeanRepository: connection.getRepository(
        HistoricalMonthlyMean,
      ),
      dataUploadsRepository: connection.getRepository(DataUploads),
    },
  );

  logger.log('Finished uploading hobo data');
  logger.log(dbIdtTSiteId);
}

getMigrationsDirName = (dbType: ConnectionOptions['type']) => {
    if (dbType === 'sqlite') return 'migrations/sqlite';
    if (dbType === 'mysql' || dbType === 'mariadb') return 'migrations/mysql';
    if (dbType === 'postgres') return 'migrations/postgres';
}