Knex.js Integration

In this tutorial, we’ll show you how to connect your TypeScript application to a SQLite Cloud database using the popular SQL builder, Knex.js.

Prerequisites

Node.js and npm installed on your system
A SQLite Cloud account (you can sign up for a free account here)

How to connect

Create a Knex.js instance that uses the SQLite Cloud JavaScript driver to connect to your database.

import 'dotenv/config'
import { knex } from 'knex'

const Client_SQLite3 = require('knex/lib/dialects/sqlite3')

// client will have sqlite3 dialect, but will use sqlitecloud-js driver
class Client_Libsql extends Client_SQLite3 {
  _driver() {
    return require('@sqlitecloud/drivers')
  }
}

// Create a Knex.js instance with the custom SQLite3 client
const db = knex({
    client: Client_Libsql as any,
    connection: {
        filename: process.env.DATABASE_URL as string
    }
})

Basic Usage

In this example, we will use the sample datasets that come pre-loaded with SQLite Cloud.

Initialize a new Node project:

npm init -y

Install the required dependencies:

npm install @sqlitecloud/drivers knex dotenv --save

Install the necessary development dependencies:

npm install @types/node nodemon ts-node typescript --save-dev

Create a .env file in the root of your project and add your SQLite Cloud connection string:

DATABASE_URL="sqlitecloud://{USER}:{PASSWORD}@{HOST}.sqlite.cloud:8860"

Replace {USER}, {PASSWORD}, and {HOST} with your actual SQLite Cloud credentials and server hostname.

Create a tsconfig.json file to configure your TypeScript compiler:

tsc --init

Create a new file called example.ts and add the following code:

import 'dotenv/config'
import { knex } from 'knex'

const Client_SQLite3 = require('knex/lib/dialects/sqlite3')

class Client_Libsql extends Client_SQLite3 {
  _driver() {
    return require('@sqlitecloud/drivers')
  }
}

console.assert(process.env.DATABASE_URL, 'Define DATABASE_URL environment variable')

const db = knex({
  client: Client_Libsql as any,
  connection: {
    filename: process.env.DATABASE_URL as string
  }
})

db.raw('USE DATABASE chinook.sqlite; SELECT * FROM customers')
  .then(result => {
    console.log(`Connected to database via knex and received ${result.length} rows`)
    console.log(JSON.stringify(result, null, 2))
    db.destroy()
  })
  .catch(err => {
    console.error(err)
    db.destroy()
  })

Update your package.json file to include a script for running the example:

{
  "scripts": {
    "dev": "nodemon --exec ts-node example.ts"
  }
}

Start the development server:

npm run dev

This will run the example.ts file using ts-node and will automatically restart the server when you make changes to your code.

Observe the output in the console, which should display the customer data fetched from the SQLite Cloud database.

 [
  {
    "CustomerId": 1,
    "FirstName": "Luís",
    "LastName": "Gonçalves",
    "Company": "Embraer - Empresa Brasileira de Aeronáutica S.A.",
    "Address": "Av. Brigadeiro Faria Lima, 2170",
    "City": "São José dos Campos",
    "State": "SP",
    "Country": "Brazil",
    "PostalCode": "12227-000",
    "Phone": "+55 (12) 3923-5555",
    "Fax": "+55 (12) 3923-5566",
    "Email": "luisg@embraer.com.br",
    "SupportRepId": 3
  },
 ]

And that’s it! You’ve successfully connected your TypeScript application to a SQLite Cloud database using Knex.js.