Skip to main content

Kysely

Prerequisites​

Make sure you have your database connection URL (that includes your authentication credentials) at hand! This will allow Kysely to connect to your development database.

The connection URL has to be available in the development environment via the key DATABASE_URL.

For example:

DATABASE_URL="postgresql://<user>:<password>@<address>:<port>/<db-name>?schema=<schema-name>"

Create a .env file in the root of your backend workspace and add the connection URL there:

DATABASE_URL="postgresql://votura:votura@localhost:5432/votura?schema=public"

Using the provided docker development database​

With the included docker-compose.yml file in the backend project, you can easily spin up a local development database using Docker Compose.

For that you will need:

  • Docker
  • Docker Compose

Starting the development database​

Navigate to the backend workspace root and run the following command to start the service in the background:

docker compose up -d
# or use the npm script "npm run postgres-up"

This will pull any required images, create containers and set up networking and volumes as defined in the docker-compose.yml in a detached manner. To verify your database container in running use the following command:

docker ps -a

Stopping the development database​

To stop the containers created by docker compose run:

docker compose down
# or use the npm script "npm run postgres-down"

Working with Kysely​

Kysely is a powerful TypeScript SQL query builder that provides type-safe database interactions with a clean and composable API. It supports PostgreSQL, MySQL, and SQLite. This section provides a minimal introduction to working with Kysely.

Defining your database types​

Unlike ORMs that rely on a separate schema file, Kysely leverages TypeScript's type system directly. You define your database table structures as TypeScript interfaces, which Kysely then uses to provide type-safety for your queries. Example of src/db/types/db.d.ts defining your database schema:

export interface UserTable {
  id: string;
  email: string;
  name: string | null;
  createdAt: Date;
}

export interface ProductTable {
  id: string;
  name: string;
  price: number;
}

// Define the overall database interface by mapping table names to their types
export interface Database {
  users: UserTable;
  products: ProductTable;
  // add other tables here
}

To generate a new file for our project just call npm run gen-types. This requires the database for which you want to generate the types to be running and for you to have a .env-File in /backend containing export DATABASE_URL="postgresql://votura:votura@localhost:5432/votura?schema=public" with the port you are actually using.

Database Migrations with Kysely​

Kysely itself is a query builder and does not come with a built-in migration system. Developers typically use a separate migration tool or build a custom solution using Kysely to manage schema changes. A common approach involves creating migration files (e.g., TypeScript files containing SQL or Kysely queries) and a runner script. This is also the approach we are taking in this project. This means migrations have to be written by hand.

The migrations in this project are in the src/db/migrations directory. Each migration file typically up and down functions to apply and revert schema changes. These functions will receive a Kysely instance to execute queries.

The runner is in the src/db/migrate.ts file. You can run it using npx tsx path/to/migrate.ts.

Seeding with Kysely​

Seeding involves populating your database with initial data (e.g., for development or testing). Similar to migrations, Kysely doesn't have a built-in seeding mechanism, but you can create a simple script that uses Kysely to insert data.

An example is in the src/db/seed.ts file. You can run it using npx tsx path/to/seed.ts.

Further readings​

This guide only explains the bare minimum to get started with the Kysely. Their documentation is a good starting point for further reading, I have to admit however that it is only alright. I recommend looking at their API docs to see what functions they support.