# Using Knex.js with Encore

Encore.ts supports integrating [Knex.js](http://knexjs.org/), 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:

```typescript
// 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](/docs/ts/primitives/databases#database-migrations) in SQL format.

Example migration file to create the `site` table:

```sql
-- 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.

<GitHubLink 
    href="https://github.com/encoredev/examples/tree/main/ts/knex" 
    desc="Example implementation showing how to use Knex ORM with Encore.ts" />