Using Knex.js with Encore

Encore.ts supports integrating Knex.js, a SQL query builder for Node.js. To use Knex.js with Encore.ts, start by creating an SQLDatabase instance and provide its connection string to Knex.js.

1. Setting Up the Database Connection

In site.ts, initialize the SQLDatabase and configure Knex.js:

// site.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import knex from "knex";

// Create SQLDatabase instance with migrations configuration
const SiteDB = new SQLDatabase("siteDB", {
  migrations: "./migrations",
});

// Initialize Knex with the database connection string
const orm = knex({
  client: "pg",
  connection: SiteDB.connectionString,
});

// Define the Site interface
export interface Site {
  id: number;
  url: string;
}

// Query builder for the "site" table
const Sites = () => orm<Site>("site");

// Example queries

// Query all sites
await Sites().select();

// Query a site by id
await Sites().where("id", id).first();

// Insert a new site
await Sites().insert({ url: params.url });

2. Creating Migrations

Currently, Encore does not support JavaScript migration files generated by knex migrate:make. Instead, you can create and maintain migration files in SQL format.

Example migration file to create the site table:

migrations/1_create_table.up.sql
CREATE TABLE site (
    id SERIAL PRIMARY KEY,
    url TEXT NOT NULL UNIQUE
);

3. Applying Migrations

Encore automatically applies migrations when you run your application. You do not need to run knex migrate:latest or similar commands manually.