Knex.js

Knex.js plays a foundational role in AdonisJS as the underlying SQL query builder powering its ORM, Lucid. Here’s how it fits into the AdonisJS ecosystem:

🧱 What is Knex.js?

Knex.js is a flexible SQL query builder for Node.js that supports multiple databases including PostgreSQL, MySQL, SQLite, and MSSQL. It provides:

• A fluent API for building SQL queries
• Schema building tools for migrations
• Transaction support
• Connection pooling

More details are available on Knex.js official site.

🔗 How AdonisJS Uses Knex

AdonisJS integrates Knex through its Lucid ORM, which wraps Knex to provide:

• Active Record-style models
• Fluent query building
• Schema migrations
• Database seeding and factories

Lucid is built on top of Knex, meaning any advanced SQL features or raw queries supported by Knex can be used directly in AdonisJS when needed.

🛠️ Example Use Case: PostGIS with Knex

AdonisJS allows extending Knex with plugins like knex-postgis for geospatial queries. You can hook into the Knex instance used by Lucid to access PostGIS functions:

const knex = require('knex')
const knexPostgis = require('knex-postgis')

const db = knex({ client: 'postgres' })
const st = knexPostgis(db)

Since Lucid manages connections internally, you can adapt this setup to work within AdonisJS by accessing the underlying Knex instance.

🧪 Migrations and Schema

When writing migrations in AdonisJS, you’re using Knex’s schema builder under the hood. For example:

this.schema.createTable('users', (table) => {
  table.increments()
  table.string('username').notNullable()
  table.enu('role', ['admin', 'user']).defaultTo('user')
})

This uses Knex’s table.enu() and defaultTo() methods to define enum columns with default values.