How to use Knex.js in a TypeScript project

20190417131115_test-setup.ts

import { Knex } from 'knex'

export async function up(knex: Knex): Promise<any> {
  await knex.schema.createTable('test_setup', (table: Knex.TableBuilder) => {
    table.integer('foobar');
  });
}

export async function down(knex: Knex): Promise<any> {
  await knex.schema.dropTable('test_setup');
}

knexfile.ts

import { Knex } from "knex";

const config: Knex.Config = {
  client: "pg",
  connection: {
    connectionString: process.env.DATABASE_URL,
    timezone: "utc",
  },
  pool: {
    min: 2,
    max: 10,
  },
  migrations: {
    tableName: "knex_migrations",
    directory: "migrations",
  },
};

export default config;

package.json

{
  "name": "my-app",
  "version": "0.0.1",
  "description": "",
  "main": "src/server.js",
  "private": true,
  "scripts": {
    "knex:migrate:make": "knex --knexfile src/database/knexfile.ts migrate:make -x ts",
    "knex:migrate:latest": "knex --knexfile src/database/knexfile.ts migrate:latest",
    "knex:migrate:rollback": "knex --knexfile src/database/knexfile.ts migrate:rollback"
  },
  "dependencies": {
    "knex": "^0.95.6",
    "pg": "^8.6.0",
    "ts-node": "^9.1.1",
    "typescript": "^4.2.4"
  }
}

usage.md

Create migration files

This creates a .ts file in migrations directory

npm run knex:migrate:make -- some-migration-name

Run migrations

npm run knex:migrate:latest

Rollback

npm run knex:migrate:rollback

Knex TypeScript issues

• knexfile.ts requires require('ts-node/register'); to work.
• ES6/ES2015 module syntax does not work in knexfile or in any files that it require()s

knex/knex#3003

knex/knex#2998

Hi, thanks for that gist. It helped me create my migration using typescript. I would like to suggest some improvements.

1. Create a more complete migration with different fields
2. Create foreign key
3. Create indexes
4. Put a field as the primary key of the table

Well, I did in the project that I am currently working on, so I am posting here for all who needs it.

export const up = async (knex: Knex): Promise<void> => {
  await knex.schema.createTable("test_setup", (table: Knex.TableBuilder) => {
    table.uuid("id").primary();
    table.uuid("external_id").references("id").inTable("externalTable").index("test_setup_id1");
    table.string("string_key_one").index("test_setup_id2");
    table.timestamp("created_at");
    table.timestamp("last_updated_at");
  });
};

Thank you for this; was trying to setup knex with TS from the official docs and was getting failures until the knexfile.ts you provided. Works great now 👍

I've this problem to use your soluction

rest-api-typescript@1.0.0 knex:migrate:make /home/rafael/Projects/exemples/rest-api-typescript
knex --knexfile src/database/knexfile.ts -x ts migrate:make "users"

internal/bootstrap/switches/does_own_process_state.js:115
rawMethods.chdir(directory);
^

Error: ENOENT: no such file or directory, chdir '/home/rafael/Projects/exemples/rest-api-typescript' -> '/home/rafael/Projects/exemples/rest-api-typescript/src/database'
at process.wrappedChdir [as chdir] (internal/bootstrap/switches/does_own_process_state.js:115:14)
at initKnex (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/knex/bin/cli.js:61:13)
at Command. (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/knex/bin/cli.js:167:24)
at Command.listener (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/commander/index.js:370:29)
at Command.emit (events.js:321:20)
at Command.parseArgs (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/commander/index.js:892:12)
at Command.parse (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/commander/index.js:642:21)
at Liftoff.invoke (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/knex/bin/cli.js:353:13)
at Liftoff.execute (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/liftoff/index.js:201:12)
at module.exports (/home/rafael/Projects/exemples/rest-api-typescript/node_modules/flagged-respawn/index.js:51:3) {
errno: -2,
code: 'ENOENT',
syscall: 'chdir',
path: '/home/rafael/Projects/exemples/rest-api-typescript',
dest: '/home/rafael/Projects/exemples/rest-api-typescript/src/database'
}
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! rest-api-typescript@1.0.0 knex:migrate:make: knex --knexfile src/database/knexfile.ts -x ts migrate:make "users"
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the rest-api-typescript@1.0.0 knex:migrate:make script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR! /home/rafael/.npm/_logs/2020-01-30T19_03_08_678Z-debug.log

@rafalogan The example npm scripts expect that you keep your knexfile.ts under src/database/knexfile.ts. Please check the directory structure of your project that it matches.

FYI doesn't work in knex v0.21.1. for command, knex --knexfile ./knexfile.ts -x ts migrate:make I get, "unknown option '-x'". No matter what knex does not seem to be passing through ts-node because I get

import dotenv from 'dotenv';
^^^^^^

SyntaxError: Cannot use import statement outside a module

That import statement is in my knex.ts

I am having this error:

import { __awaiter } from "tslib";
^^^^^^

SyntaxError: Cannot use import statement outside a module

@sahina For me this was fixed by changing a property in my tsconfig. "module" was set to "esnext" and I changed it to "commonjs" and then it worked.

For anyone encountering the "unknown option '-x'" issue, i solved this by doing the following in the package.json scripts

"knex": "./node_modules/.bin/knex --knexfile src/my/path/to/database/knexfile.ts"

Now i can use it like so:
npm run knex migrate:make some_migration

@acbd-ca try:
import 'dotenv/config'

Hey @tukkajukka, how would you import the knexfile config into your ts src files to actually use the config? Also, how do you avoid placing DB creds into GitHub with this? Should knexfile fetch from envvars?

@HamishWHC environment variables is the way to go with any credentials, e.g. process.env.DATABASE_URL reads database credentials from env vars.

Did you try importing the Knexfile with require() or ES6 import?

If you still have the "-x" issue, explanation is simple, there's a mistake in the gist.
Instead of doing "knex -x ts migrate:make migration_name" you need to do "knex migrate:make -x ts migration_name".

My script works like this:

// package.json script:
"knex:migrate:make": "knex --knexfile src/knexfile.ts migrate:make -x ts",

// in terminal:
npm run knex:migrate:make migration_name

@grumd thanks, updated.

In the migration file I'm getting this error Cannot use namespace Knex as a type

In the migration file I'm getting this error Cannot use namespace Knex as a type

Knex typings changed. Try the following:

import { Knex } from 'knex'

In the migration file I'm getting this error Cannot use namespace Knex as a type

Knex typings changed. Try the following:

import { Knex } from 'knex'

Thanks, that works.

@jengel3 @Hoxtygen thanks, updated the gist.

Now you can specify to knex generate ts files on knexfile.ts.

const config: Knex.Config = {
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'postgres'
  },
  migrations: {
    extension: 'ts', //define work typescript to migrations
    directory: 'src/knex/migrations',
    tableName: 'migrations_history',
  },
  seeds: {
    extension: 'ts', //define work typescript to seeds
    directory: 'src/knex/seeds',
  },
};

This makes it easier than always to specifying the -x ts flag.
In case you're interested, I set up this example project: https://github.com/jfollmann/knex-migrations-ts

Thank you for sharing this note.

[SOLVED]: npm install ts-node
and then try to run knex migrations

[SOLVED]: npm install ts-node
and then try to run knex migrations