Inspects a mysql database and generates Typescript types for each table. Useful when you are using knex
or raw mysql clients instead of an ORM.
Table names in the database must be in snake_case and will be converted to PascalCase for type names.
The tool can be used with the javascript API or as a CLI with npx
.
Create a file to configure and run the generator:
src/db/updateTypes.js
import { generateMysqlTypes } from 'mysql-types-generator';
const dbConfig = {
host: 'localhost',
port: 3306,
user: 'myuser',
password: 'mypassword',
database: 'mydatabase',
ssl: {
rejectUnauthorized: true
}
};
// OR
const dbConfig = {
uri: 'mysql://myuser:mypassword@localhost:3306/mydatabase',
database: 'mydatabase',
ssl: {
rejectUnauthorized: true
}
};
generateMysqlTypes({
db: dbConfig,
output: {
// Specify only one of the following 2 options:
dir: 'src/db/types',
file: 'src/db/types.ts'
},
suffix: 'PO',
ignoreTables: [
'my_table_a',
'my_table_b',
],
overrides: [
{
tableName: 'my_table',
columnName: 'my_actual_tinyint_column',
columnType: 'int',
},
{
tableName: 'my_table',
columnName: 'my_column',
columnType: 'enum',
enumString: `enum('a','b','c')`
}
],
tinyintIsBoolean: true,
})
db
: Required - the database connection and credentialsssl
is optional. Defaults to themysql2
default.- if
uri
is specified, the host, port, username and password options are ignored.
output
: Required - you should define one of the following 2 options:dir
: Each type will be output in a separate file in this directory, along with anindex.ts
. WARNING: This directory will be emptied and overwritten if it already exists.file
: Each type will be output into this single file. WARNING: This file will be overwritten if it already exists.- If both
dir
andfile
are provided,file
will take precedence.
suffix
: Optional - a string appended to the PascalCase Type name (PO
in the example refers toPersistence Object
but you should use whatever convention you wish)ignoreTables
: Optional - a list of tables to ignore; types won't be generated for theseoverrides
: Optional - a list of columns where the column type in the database is ignored and the specifiedcolumnType
is used insteadcolumnType
can be any of themysql
column types, e.g.'varchar'
,'json'
, etc. Check the filesrc/getColumnDataType.ts
in this repo for a list- if
columnType
='enum'
, you should specifyenumString
- if
enumString
: Optional unlesscolumnType
='enum'
. Specify the enum options, for exampleenum('a','b','c')
will become'a' | 'b' | 'c'
tinyintIsBoolean
: Optional. Controls iftinyint(1)
should be converted to boolean or kept as number. Default isfalse
true
: convert to booleanfalse
: keep as number (default)
Run this file after running your database migrations. For example with knex
:
package.json
(...)
"scripts": {
"migrate:dev": "npm run build && npx knex migrate:latest && node src/db/updateTypes.js"
}
(...)
You can use env-cmd to load environment variables from a .env
file before running: env-cmd node src/db/updateTypes.js
NOTE: node v18.3 or greater is required to use the CLI as it uses parseArgs
from node:util
under the hood
npx mysql-types-generator [options] --outFile [output file] [database name]
npx mysql-types-generator [options] --outDir [output directory] [database name]
// example
npx mysql-types-generator -h localhost -P 3306 -u myuser -p mypassword --outFile ./src/db/types.ts mydatabase
Most options from the Javascript API are available, run npx mysql-types-generator --help
for details
SET
data type is treated as a simple string becauseknex
returns a comma-delimited string in queries. You need to manually split it by comma if you want to convert it to an array or javascriptSet<>
type.
2.0.0
- Changed mapping of
decimal
column to string instead of number - Added
includeTables
option (optional) to specify a list of tables to generate types for - CLI: Added the
ignoreTables
andincludeTables
options - CLI: Output to
STDOUT
if no--outDir
or--outFile
option is provided - JS API: Added
stream
option to ouput to awriteStream
- Changed mapping of
1.0.8
- Bugfixes: node version check, MariaDB support, ES2020 target
1.0.3
- Updated
README.md
to indicate that node v18.3 or greater is required to use the CLI
- Updated
1.0.2
- Added feature: Specify connection using a
uri
(e.g.mysql://user:password@host:port/database
) - Added feature: Specify SSL options (see mysql2 SslOptions)
- Added feature: Specify connection using a
1.0.1
- Added feature:
tinyintIsBoolean
config option in CLI
- Added feature:
1.0.0
- Added feature: CLI / usage with
npx
- Added feature:
tinyintIsBoolean
config option in JS API - Added feature: mysql column comments are now added as comments in the output files
- Breaking Change: Changed how the output file / directory is defined in the config
- Migrated from tslint to eslint
- Added feature: CLI / usage with
0.0.12
- Fixed typos in
README.md
- Fixed typos in
0.0.11
- Bugfix:
overrides
config option wasn't working properly - Added feature:
output
can now be a path to a single file instead of a directory - Added feature: output files now contain a warning comment at the top to indicate that the file was auto-generated and will be overwritten
- Bugfix: