TypeScript is an open-source programming language that is a superset of JavaScript. It adds optional static typing and class-based object-oriented programming to the language, making it easier to write and maintain large-scale applications. Knex.js is a popular SQL query builder for Node.js that provides a fluent interface for building SQL queries. In this article, we will explore how to use TypeScript with Knex.js for SQL database management.
To get started, we need to set up a new TypeScript project and install the required dependencies: TypeScript, Knex.js, and the appropriate database driver (e.g. pg
for PostgreSQL).
$ mkdir my-project
$ cd my-project
$ npm init -y
$ npm install typescript knex pg
$ npx tsc --init
Next, we need to configure TypeScript to use ES6 module syntax and tell Knex.js to use TypeScript types:
// tsconfig.json
{
"compilerOptions": {
"module": "es6",
"outDir": "./dist",
"target": "es6"
}
}
// knexfile.ts
import { Config } from "knex";
import { development, production } from "./config";
const knexConfig: Record<string, Config> = {
development: {
client: "pg",
connection: development.url,
migrations: {
directory: "./src/migrations",
},
seeds: {
directory: "./src/seeds",
},
},
production: {
client: "pg",
connection: production.url,
migrations: {
directory: "./dist/migrations",
},
seeds: {
directory: "./dist/seeds",
},
},
};
export default knexConfig;
Now that we have set up our project, we can start defining our database models using TypeScript. We will use the Knex
interface provided by Knex.js to define the shape of our database tables.
// src/models/User.ts
import { Knex } from "knex";
interface User {
id: number;
username: string;
email: string;
created_at: Date;
updated_at: Date;
}
const createTable = (knex: Knex) =>
knex.schema.createTable("users", (table) => {
table.increments("id").primary();
table.string("username").notNullable();
table.string("email").notNullable().unique();
table.timestamps(true, true);
});
export { User, createTable };
In this example, we define a User
interface that represents the structure of our users
table. We also define a createTable
function that takes a Knex
instance and creates the users
table if it does not exist. This function can be used in our migration scripts to create the table.
// src/migrations/20220101000000_create_users.ts
import { Knex } from "knex";
import { createTable } from "../models/User";
export const up = async (knex: Knex): Promise<void> => {
await createTable(knex);
};
export const down = async (knex: Knex): Promise<void> => {
await knex.schema.dropTableIfExists("users");
};
Now that we have defined our database models, we can start querying the database using Knex.js. We can use the Knex
interface to construct SQL queries in a type-safe manner.
// src/repository/UserRepository.ts
import { Knex } from "knex";
import { User } from "../models/User";
class UserRepository {
constructor(private readonly knex: Knex) {}
async create(user: User): Promise<User> {
const [createdUser] = await this.knex("users")
.insert(user)
.returning("*");
return createdUser as User;
}
async findById(id: number): Promise<User | undefined> {
const [user] = await this.knex("users").where({ id });
return user as User | undefined;
}
async findByEmail(email: string): Promise<User | undefined> {
const [user] = await this.knex("users").where({ email });
return user as User | undefined;
}
async update(user: User): Promise<User> {
const [updatedUser] = await this.knex("users")
.where({ id: user.id })
.update(user)
.returning("*");
return updatedUser as User;
}
async delete(id: number): Promise<void> {
await this.knex("users").where({ id }).delete();
}
}
export { UserRepository };
In this example, we define a UserRepository
class that provides methods for creating, finding, updating, and deleting User
objects. We use the this.knex
instance to construct SQL queries that interact with the users
table.
In this article, we have explored how to use TypeScript with Knex.js for SQL database management. We have seen how to define database models using TypeScript interfaces and how to use the Knex
interface to construct type-safe SQL queries. We have also seen how to create a repository class to encapsulate database operations. By using TypeScript with Knex.js, we can write more maintainable and scalable database code.