Skip to main content

MySQL / MariaDB

MySQL, MariaDB and Amazon Aurora MySQL are supported as TypeORM drivers.

Installation​

Either mysql or mysql2 are required to connect to a MySQL/MariaDB data source. Only mysql2 can connect to MySQL 8.0 or later and is recommended because it is still maintained. See more about mysql2.

npm install mysql

or:

npm install mysql2

Data Source Options​

See Data Source Options for the common data source options. You can use the data source types mysql, mariadb and aurora-mysql to connect to the respective databases.

  • connectorPackage - The database client, either mysql or mysql2. If the specified client cannot be loaded, it will fall back to the alternative. (Current default: mysql)

  • url - Connection url where the connection is performed. Please note that other data source options will override parameters set from url.

  • host - Database host.

  • port - Database host port. Default mysql port is 3306.

  • username - Database username.

  • password - Database password.

  • database - Database name.

  • charset and collation - The charset/collation for the connection. If an SQL-level charset is specified (like utf8mb4) then the default collation for that charset is used.

  • timezone - the timezone configured on the MySQL server. This is used to typecast server date/time values to JavaScript Date object and vice versa. This can be local, Z, or an offset in the form +HH:MM or -HH:MM. (Default: local)

  • connectTimeout - The milliseconds before a timeout occurs during the initial connection to the MySQL server. (Default: 10000)

  • acquireTimeout - The milliseconds before a timeout occurs during the initial connection to the MySQL server. It differs from connectTimeout as it governs the TCP connection timeout whereas connectTimeout does not. (default: 10000)

  • insecureAuth - Allow connecting to MySQL instances that ask for the old (insecure) authentication method. (Default: false)

  • supportBigNumbers - When dealing with big numbers (BIGINT and DECIMAL columns) in the database, you should enable this option (Default: true)

  • bigNumberStrings - Enabling both supportBigNumbers and bigNumberStrings forces big numbers (BIGINT and DECIMAL columns) to be always returned as JavaScript String objects (Default: true). Enabling supportBigNumbers but leaving bigNumberStrings disabled will return big numbers as String objects only when they cannot be accurately represented with JavaScript Number objects (which happens when they exceed the [-2^53, +2^53] range), otherwise they will be returned as Number objects. This option is ignored if supportBigNumbers is disabled.

  • dateStrings - Force date types (TIMESTAMP, DATETIME, DATE) to be returned as strings rather than inflated into JavaScript Date objects. Can be true/false or an array of type names to keep as strings. (Default: false)

  • debug - Prints protocol details to stdout. Can be true/false or an array of packet type names that should be printed. (Default: false)

  • trace - Generates stack traces on Error to include call site of library entrance ("long stack traces"). Slight performance penalty for most calls. (Default: true)

  • multipleStatements - Allow multiple mysql statements per query. Be careful with this, it could increase the scope of SQL injection attacks. (Default: false)

  • legacySpatialSupport - Use legacy spatial functions like GeomFromText and AsText which have been replaced by the standard-compliant ST_GeomFromText or ST_AsText in MySQL 8.0. (Current default: true)

  • flags - List of connection flags to use other than the default ones. It is also possible to blacklist default ones. For more information, check Connection Flags.

  • ssl - object with SSL parameters or a string containing the name of the SSL profile. See SSL options.

  • enableQueryTimeout - If a value is specified for maxQueryExecutionTime, in addition to generating a warning log when a query exceeds this time limit, the specified maxQueryExecutionTime value is also used as the timeout for the query. For more information, check mysql timeouts.

Additional options can be added to the extra object and will be passed directly to the client library. See more in the mysql connection options or the mysql2 documentation.

Column Types​

bit, int, integer, tinyint, smallint, mediumint, bigint, float, double, double precision, dec, decimal, numeric, fixed, bool, boolean, date, datetime, timestamp, time, year, char, nchar, national char, varchar, nvarchar, national varchar, text, tinytext, mediumtext, blob, longtext, tinyblob, mediumblob, longblob, enum, set, json, binary, varbinary, geometry, point, linestring, polygon, multipoint, multilinestring, multipolygon, geometrycollection, uuid, inet4, inet6

Note: uuid, inet4, and inet6 are only available for MariaDB and for the respective versions that made them available.

enum column type​

See enum column type.

set column type​

set column type is supported by mariadb and mysql. There are various possible column definitions:

Using TypeScript enums:

export enum UserRole {
    ADMIN = "admin",
    EDITOR = "editor",
    GHOST = "ghost",
}

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column({
        type: "set",
        enum: UserRole,
        default: [UserRole.GHOST, UserRole.EDITOR],
    })
    roles: UserRole[]
}

Using an array with set values:

export type UserRoleType = "admin" | "editor" | "ghost"

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column({
        type: "set",
        enum: ["admin", "editor", "ghost"],
        default: ["ghost", "editor"],
    })
    roles: UserRoleType[]
}