### Configure Database Connection

Source: https://lucid.adonisjs.com/docs/schema-generation

Set up your database connection details in `config/database.ts`. This example shows a PostgreSQL connection using a DATABASE_URL environment variable.

```typescript
import env from '#start/env'

import { defineConfig } from '@adonisjs/lucid'



const dbConfig = defineConfig({

  connection: 'postgres',

  connections: {

    postgres: {

      client: 'pg',

      connection: env.get('DATABASE_URL'),

    },

  },

})



export default dbConfig

```

--------------------------------

### Create a User Table Migration

Source: https://lucid.adonisjs.com/docs/schema-builder

Example of a migration class that creates a 'users' table with an auto-incrementing ID, a unique and non-nullable email, and timestamps. This is a typical setup for a user table.

```typescript
import { BaseSchema } from '@adonisjs/lucid/schema'



export default class extends BaseSchema {

  protected tableName = 'users'



  async up() {

    this.schema.createTable(this.tableName, (table) => {

      table.increments('id')



      table.string('email').unique().notNullable()

      table.timestamps(true, true)

    })

  }



  async down() {

    this.schema.dropTable(this.tableName)

  }

}
```

--------------------------------

### Install Lucid with yarn

Source: https://lucid.adonisjs.com/docs/installation

Install the Lucid package using yarn. Use this command if your project manages dependencies with yarn.

```bash
yarn add @adonisjs/lucid
```

--------------------------------

### beforeSave Hook Example

Source: https://lucid.adonisjs.com/docs/model-hooks

A practical example of the `beforeSave` hook used for password hashing, demonstrating the check for changes using `$dirty`.

```typescript
import hash from '@adonisjs/core/services/hash'
import { beforeSave } from '@adonisjs/lucid/orm'
import { UsersSchema } from '#database/schema'

export default class User extends UsersSchema {
  @beforeSave()
  static async hashPassword(user: User) {
    if (user.$dirty.password) {
      user.password = await hash.make(user.password)
    }
  }
}
```

--------------------------------

### Create a Model Instance

Source: https://lucid.adonisjs.com/docs/models

Use `Model.create` to build an instance, persist it to the database, and return the created model. This example demonstrates creating a user with email and password.

```typescript
import type { HttpContext } from '@adonisjs/core/http'

import User from '#models/user'



export default class UsersController {
  async store({ request }: HttpContext) {
    return User.create({
      email: request.input('email'),
      password: request.input('password'),
    })
  }

}
```

--------------------------------

### Install Lucid with pnpm

Source: https://lucid.adonisjs.com/docs/installation

Install the Lucid package using pnpm. This command is for projects that utilize pnpm for package management.

```bash
pnpm add @adonisjs/lucid
```

--------------------------------

### Starter Schema Rules File Configuration

Source: https://lucid.adonisjs.com/docs/schema-classes

An example of a basic schema rules file used to customize Lucid's schema generation process, defining rules for types, columns, and tables.

```typescript
import { type SchemaRules } from '@adonisjs/lucid/types/schema_generator'



export default {

  types: {},

  columns: {},

  tables: {},

} satisfies SchemaRules
```

--------------------------------

### SQLite Database Configuration

Source: https://lucid.adonisjs.com/docs/installation

The `config/database.ts` file defines the database connection settings. This example shows the configuration for a SQLite database.

```typescript
import env from '#start/env'

import { defineConfig } from '@adonisjs/lucid'



const dbConfig = defineConfig({

  connection: 'sqlite',

  connections: {

    sqlite: {

      client: 'better-sqlite3',

      connection: {

        filename: env.get('DB_DATABASE', 'tmp/db.sqlite3'),

      },

      useNullAsDefault: true,

      migrations: {

        naturalSort: true,

        paths: ['database/migrations'],

      },

    },

  },

})



export default dbConfig
```

--------------------------------

### Get Database Query Builder Instance

Source: https://lucid.adonisjs.com/docs/select-query-builder

Get a builder instance through the `db` service. Use `db.query()` for a general builder or `db.from(table)` as a shortcut for a builder with a specific table selected.

```typescript
import db from '@adonisjs/lucid/services/db'



const query = db.query()              // builder without a table selected

const usersQuery = db.from('users')   // shortcut: builder with the table selected
```

--------------------------------

### Configure PostgreSQL with Connection String and SSL Options

Source: https://lucid.adonisjs.com/docs/configuration

This example shows how to use a connection string for PostgreSQL while also specifying SSL options, such as `rejectUnauthorized: false`. This is useful when merging connection string credentials with additional connection parameters.

```typescript
const dbConfig = defineConfig({

  connection: 'postgres',

  connections: {

    postgres: {

      client: 'pg',

      connection: {

        connectionString: env.get('DATABASE_URL'),

        ssl: { rejectUnauthorized: false },

      },

    },

  },

})

```

--------------------------------

### Create a Basic Query with `db.from`

Source: https://lucid.adonisjs.com/docs/database-service

Use `db.from()` to start a query against a specific table. Select columns using `.select()`. This is a common way to fetch data directly from a table.

```typescript
const posts = await db.from('posts').select('id', 'title')
```

--------------------------------

### Query Multiple Model Instances

Source: https://lucid.adonisjs.com/docs/models

Use `Model.query()` to get a typed query builder for the model. This example retrieves active users, ordered by creation date, and limits the results.

```typescript
const activeUsers = await User
  .query()
  .where('is_active', true)
  .orderBy('createdAt', 'desc')
  .limit(10)
```

--------------------------------

### Install Lucid with npm

Source: https://lucid.adonisjs.com/docs/installation

Install the Lucid package using npm. This is the first step to integrating Lucid ORM into your AdonisJS project.

```bash
npm i @adonisjs/lucid
```

--------------------------------

### Setup Database Migrations for Tests

Source: https://lucid.adonisjs.com/docs/testing

Apply and roll back all migrations at the test runner level. This ensures the schema is ready for all tests and cleaned up upon completion.

```typescript
import testUtils from '@adonisjs/core/services/test_utils'



export const runnerHooks: Required<Pick<Config, 'setup' | 'teardown'>> = {

  setup: [

    () => testUtils.db().migrate(),

  ],

  teardown: [],

}

```

--------------------------------

### Fetch First or FirstOrFail User

Source: https://lucid.adonisjs.com/docs/crud-operations

Use `first` to get the first user record, or `firstOrFail` to get the first user or throw an error if none exist. Without constraints, `first` typically returns the record with the lowest primary key.

```typescript
const user = await User.first()          // User | null
```

```typescript
const user = await User.firstOrFail()    // User, or throws
```

--------------------------------

### Execute Raw SQL Query with Imports

Source: https://lucid.adonisjs.com/docs/raw-query-builder

Import the `db` service to execute raw SQL queries. This example demonstrates a query to aggregate order creation dates and counts.

```typescript
import db from '@adonisjs/lucid/services/db'



const result = await db.rawQuery(
  `select date_trunc('month', created_at) as month, count(*) as total

   from orders

   group by month

   order by month desc`
)
```

--------------------------------

### Run Migrations Against Specific Connections

Source: https://lucid.adonisjs.com/docs/migrations

When sharing a single migration directory across multiple connections (e.g., in a multi-tenant setup), use the `--connection=name` flag to specify which tenant's database the migrations should run against.

```bash
node ace migration:run --connection=tenant_a
node ace migration:run --connection=tenant_b
```

--------------------------------

### Preload and Count Posts

Source: https://lucid.adonisjs.com/docs/model-query-builder

Fetch users, eager-load their posts, and also get the count of their posts. `preload` and `withCount` are independent and can be called together.

```typescript
await User.query().preload('posts').withCount('posts')
```

--------------------------------

### Query and Persist Records

Source: https://lucid.adonisjs.com/docs/introduction

Perform database queries and persist records using Lucid models. This example demonstrates querying published posts, preloading the author, ordering by creation date, and publishing a specific post.

```typescript
const posts = await Post.query()

  .where('status', 'published')

  .preload('author')

  .orderBy('createdAt', 'desc')



const post = await Post.findOrFail(params.id)

await post.publish()
```

--------------------------------

### Configure MySQL Connection

Source: https://lucid.adonisjs.com/docs/configuration

Configure a MySQL connection using the 'mysql2' client. This setup requires environment variables for host, port, user, password, and database name.

```typescript
import env from '#start/env'

import { defineConfig } from '@adonisjs/lucid'



const dbConfig = defineConfig({

  connection: 'mysql',

  connections: {

    mysql: {

      client: 'mysql2',

      connection: {

        host: env.get('DB_HOST'),

        port: env.get('DB_PORT'),

        user: env.get('DB_USER'),

        password: env.get('DB_PASSWORD'),

        database: env.get('DB_DATABASE'),

      },

      migrations: {

        naturalSort: true,

        paths: ['database/migrations'],

      },

    },

  },

})



export default dbConfig

```

--------------------------------

### Create, Alter, Rename, and Drop Tables

Source: https://lucid.adonisjs.com/docs/migrations

Examples of common schema operations: creating a new table, altering an existing table by adding and dropping columns, renaming a table, and dropping a table.

```typescript
this.schema.createTable('posts', (table) => {

  table.increments('id')

  table.string('title').notNullable()

  table.timestamps(true, true)

})
```

```typescript
this.schema.alterTable('users', (table) => {

  table.string('first_name')

  table.string('last_name')

  table.dropColumn('name')

})
```

```typescript
this.schema.renameTable('user', 'users')
```

```typescript
this.schema.dropTable('users')
```

--------------------------------

### Inspect a query's execution plan

Source: https://lucid.adonisjs.com/docs/debugging

Extract a query's SQL and bindings using `.toSQL()`, then execute it with `db.rawQuery` prefixed by `EXPLAIN ANALYZE` to get the execution plan.

```typescript
import db from '@adonisjs/lucid/services/db'



export default class PostsService {
  async explainPublishedPosts() {
    const query = db
      .from('posts')
      .where('is_published', true)
      .orderBy('created_at', 'desc')
      .limit(10)
      .toSQL()



    const plan = await db.rawQuery(`EXPLAIN ANALYZE ${query.sql}`, query.bindings)

    return plan.rows

  }

}

```

--------------------------------

### Set Isolation Level for Manual Transaction

Source: https://lucid.adonisjs.com/docs/transactions

This example demonstrates how to initialize a manual transaction with a specific isolation level by passing the options object directly to `db.transaction()`.

```typescript
const trx = await db.transaction({ isolationLevel: 'serializable' })
```

--------------------------------

### Query Database Directly with `db` Service

Source: https://lucid.adonisjs.com/docs/installation

Use the `db` service for one-off queries, reports, or scripts when the full model machinery is not needed. This example shows inserting and selecting data.

```typescript
import type { HttpContext } from '@adonisjs/core/http'

import db from '@adonisjs/lucid/services/db'



export default class PostsController {

  async store({ request }: HttpContext) {

    const [post] = await db

      .table('posts')

      .insert({

        title: request.input('title'),

        body: request.input('body'),

      })

      .returning(['id', 'title', 'body'])



    return post

  }



  async index() {

    return db

      .from('posts')

      .select('id', 'title', 'body')

      .orderBy('id', 'desc')

  }

}
```

--------------------------------

### Example of a Global Type Rule for Strings

Source: https://lucid.adonisjs.com/docs/schema-classes

Illustrates a global type rule for 'string' data types, specifying the TypeScript type and decorators. It handles 'text' as a special case.

```typescript
types: {
  string: (dataType) => ({
    tsType: dataType === 'text' ? 'string' : `string`,
    decorators: [{ name: '@column' }],
    imports: [],
  }),
}
```

--------------------------------

### Fresh Database with Custom Schema Path

Source: https://lucid.adonisjs.com/docs/schema-dumps

Use `migration:fresh` with the `--schema-path` option to drop all tables and then rebuild the database using the specified schema dump.

```bash
node ace migration:fresh --schema-path=./database/snapshots/schema.sql
```

--------------------------------

### Run Database Migrations

Source: https://lucid.adonisjs.com/docs/installation

Execute this command to apply all pending migration files, including the newly created 'posts' table migration, to your database.

```bash
node ace migration:run
```

--------------------------------

### Configure Lucid with SQLite

Source: https://lucid.adonisjs.com/docs/installation

Run the configure command to register Lucid with the framework and set up the SQLite database driver. The `--db=sqlite` flag specifies the database.

```bash
node ace configure @adonisjs/lucid --db=sqlite
```

--------------------------------

### Use SQL Window Functions with Lucid

Source: https://lucid.adonisjs.com/docs/introduction

Leverage SQL window functions like `rank()` directly within Lucid queries to perform complex aggregations and analyses on your data without post-processing in JavaScript. This example ranks users by points within each game.

```typescript
import db from '@adonisjs/lucid/services/db'



const rankings = await db

  .from('scores')

  .select('user_id', 'game_id', 'points')

  .select(

    db.raw(`rank() OVER (PARTITION BY game_id ORDER BY points DESC) as rank`)

  )
```

--------------------------------

### Create a new migration file

Source: https://lucid.adonisjs.com/docs/migrations

Use the `make:migration` command to generate a new migration file. Use `--create` for new tables or `--alter` for existing ones.

```bash
node ace make:migration users --create=users
```

--------------------------------

### Get Affected Rows Count After Delete

Source: https://lucid.adonisjs.com/docs/update-and-delete-queries

The `delete` method returns the number of rows affected (deleted) by the query.

```typescript
const removed = await db

  .from('expired_tokens')

  .where('expires_at', '<', new Date())

  .delete()



console.log(`Removed ${removed} expired tokens`)
```

--------------------------------

### Create Posts Migration

Source: https://lucid.adonisjs.com/docs/installation

Use the `make:migration` command to generate a new migration file for creating the 'posts' table. The `--create=posts` flag specifies the table name.

```bash
node ace make:migration posts --create=posts
```

--------------------------------

### Configure LibSQL Connection

Source: https://lucid.adonisjs.com/docs/configuration

Set up a LibSQL database connection using the 'libsql' client. Ensure the DB_DATABASE environment variable is set for the database file path.

```typescript
import env from '#start/env'

import { defineConfig } from '@adonisjs/lucid'



const dbConfig = defineConfig({

  connection: 'libsql',

  connections: {

    libsql: {

      client: 'libsql',

      connection: {

        filename: env.get('DB_DATABASE', 'tmp/db.sqlite3'),

      },

      useNullAsDefault: true,

      migrations: {

        naturalSort: true,

        paths: ['database/migrations'],

      },

    },

  },

})



export default dbConfig

```

--------------------------------

### Get Affected Rows Count After Update

Source: https://lucid.adonisjs.com/docs/update-and-delete-queries

The `update` method returns the number of rows affected by the query by default.

```typescript
const affected = await db

  .from('users')

  .where('is_active', false)

  .update({ status: 'archived' })



// affected === number of rows touched
```

--------------------------------

### Configure Database Seeders

Source: https://lucid.adonisjs.com/docs/configuration

Set up seeder configuration, including the paths to seeder files and natural sorting for file order. Defaults to `['database/seeders']` for paths.

```typescript
{

  client: 'pg',

  connection: env.get('DATABASE_URL'),

  seeders: {

    paths: ['database/seeders'],

    naturalSort: true,

  },

}
```

--------------------------------

### Register beforeFetch hook

Source: https://lucid.adonisjs.com/docs/model-hooks

Fires before any query that resolves to multiple rows. Use to filter results, for example, to implement soft deletes.

```typescript
import { beforeFetch } from '@adonisjs/lucid/orm'

import type { ModelQueryBuilderContract } from '@adonisjs/lucid/types/model'

import { UsersSchema } from '#database/schema'



export default class User extends UsersSchema {

  @beforeFetch()

  static filterDeleted(query: ModelQueryBuilderContract<typeof User>) {

    query.whereNull('deleted_at')

  }

}
```

--------------------------------

### Create UUID extension on older PostgreSQL

Source: https://lucid.adonisjs.com/docs/table-builder

For older PostgreSQL versions, install the `uuid-ossp` extension using `this.schema.raw` before using UUID columns.

```typescript
this.schema.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
```

--------------------------------

### Model Persistence Lifecycle

Source: https://lucid.adonisjs.com/docs/models

Demonstrates the lifecycle of a Lucid model instance from creation to deletion, showing how its state properties change.

```javascript
const user = new User()                  // local, not persisted, not dirty

user.email = 'virk@adonisjs.com'         // dirty: { email }

user.password = 'secret'                 // dirty: { email, password }



await user.save()                        // INSERT runs; now persisted, dirty cleared



user.email = 'new@adonisjs.com'          // dirty again: { email }
console.log(user.$original.email)        // 'virk@adonisjs.com'
console.log(user.$attributes.email)      // 'new@adonisjs.com'



await user.save()                        // UPDATE runs; only email is sent



await user.delete()                      // DELETE runs; isDeleted is set
```

--------------------------------

### Scaffold and Run Migrations for a Specific Connection

Source: https://lucid.adonisjs.com/docs/migrations

Use the `--connection=name` flag with `make:migration` or `migration:run` to scaffold or execute migrations against a particular database connection.

```bash
node ace make:migration --connection=products
node ace migration:run --connection=products
```

--------------------------------

### Custom JSON Stringification with prepare

Source: https://lucid.adonisjs.com/docs/schema-classes

Use the `prepare` option to serialize or normalize values before they are persisted to the database. This example stringifies an object into JSON.

```typescript
@column({

  prepare: (value) => typeof value === 'object' ? JSON.stringify(value) : value,

})

declare preferences: Record<string, unknown>
```

--------------------------------

### Run Database Seeders

Source: https://lucid.adonisjs.com/docs/commands

Execute all seeders in the database/seeders directory. Use --files to specify a subset or --interactive for manual selection.

```bash
node ace db:seed
```

```bash
node ace db:seed --interactive
```

```bash
node ace db:seed --files=./database/seeders/user_seeder.ts
```

--------------------------------

### Bootstrap Database with Custom Schema Path

Source: https://lucid.adonisjs.com/docs/schema-dumps

Use the `--schema-path` option with `migration:run` to specify a custom location for the schema dump file.

```bash
node ace migration:run --schema-path=./database/snapshots/schema.sql
```

--------------------------------

### Generate SQL and Bindings with toSQL

Source: https://lucid.adonisjs.com/docs/select-query-builder

Use `toSQL` to get the SQL query and its bindings without executing it. This is useful for logging or composing with `db.rawQuery`.

```typescript
const { sql, bindings } = db.from('users').where('is_active', true).toSQL()
```

--------------------------------

### Count all rows in a table

Source: https://lucid.adonisjs.com/docs/select-query-builder

Use the `count` aggregate method to get the total number of rows. The alias 'total' controls the output key.

```javascript
const result = await db.from('orders').count('* as total')

console.log(result[0].total)
```

--------------------------------

### Build a recursive CTE

Source: https://lucid.adonisjs.com/docs/select-query-builder

Use `withRecursive` to construct recursive CTEs for hierarchical data traversal. This example sums account amounts down a parent/child hierarchy.

```javascript
db
  .query()
  .withRecursive('tree', (query) => {
    query
      .from('accounts')
      .select('amount', 'id')
      .where('id', 1)
      .union((subquery) => {
        subquery
          .from('accounts as a')
          .select('a.amount', 'a.id')
          .innerJoin('tree', 'tree.id', 'a.parent_id')
      })
  })
  .sum('amount as total')
  .from('tree')
```

--------------------------------

### Lifecycle Order: Creating a New Row

Source: https://lucid.adonisjs.com/docs/model-hooks

Illustrates the sequence of hook executions when a new row is created in the database.

```text
beforeCreate → beforeSave → INSERT → afterCreate → afterSave
```

--------------------------------

### Target Schema with withSchema

Source: https://lucid.adonisjs.com/docs/schema-builder

Use `withSchema` to specify a non-default schema for subsequent DDL operations. This example creates a table within the 'analytics' schema.

```typescript
this.schema
  .withSchema('analytics')
  .createTable('events', (table) => {
    table.increments('id')
    table.string('name').notNullable()
    table.timestamp('occurred_at', { useTz: true })
  })
```

--------------------------------

### Extend Generated Schema in Model

Source: https://lucid.adonisjs.com/docs/schema-generation

Define your Lucid model by extending the auto-generated schema class. This example shows a `User` model extending `UsersSchema`.

```typescript
import { UsersSchema } from '#database/schema'



export default class User extends UsersSchema {}

```

--------------------------------

### Query Models Dynamically

Source: https://lucid.adonisjs.com/docs/database-service

Use `db.modelQuery` when a model class is received dynamically, for example, in infrastructure code. Prefer `Model.query()` for standard application logic.

```typescript
import db from '@adonisjs/lucid/services/db'

import type { LucidModel } from '@adonisjs/lucid/types/model'



export default class ModelSearchService {

  search(model: LucidModel, term: string) {

    return db

      .modelQuery(model)

      .whereLike('title', `%${term}%`)

      .limit(20)

  }

}
```

--------------------------------

### Fresh and Status Migrations

Source: https://lucid.adonisjs.com/docs/migrations

Drops all tables and re-runs migrations from scratch with 'fresh'. Lists the status of all migrations, indicating whether they are completed, pending, or in error.

```bash
node ace migration:fresh
```

```bash
node ace migration:fresh --seed
```

```bash
node ace migration:status
```

--------------------------------

### Retrieve the First Matching Row with first

Source: https://lucid.adonisjs.com/docs/select-query-builder

Use the `first()` method to get the first row that matches the query criteria, or `null` if no rows are found.

```typescript
const user = await db.from('users').where('email', email).first()
```

--------------------------------

### Select distinct rows based on a column (PostgreSQL)

Source: https://lucid.adonisjs.com/docs/select-query-builder

Use `distinctOn` with `orderBy` to get the first row for each distinct value in a column. This is a PostgreSQL-specific feature.

```javascript
db
  .from('events')
  .distinctOn('user_id')
  .orderBy('user_id')
  .orderBy('occurred_at', 'desc')
```

--------------------------------

### Inspect Query with toSQL

Source: https://lucid.adonisjs.com/docs/raw-query-builder

Use the .toSQL() method to get a { sql, bindings } object describing the query without executing it. This is useful for logging or debugging.

```typescript
const { sql, bindings } = db
  .rawQuery('select * from users where id = ?', [1])
  .toSQL()
```

--------------------------------

### Find, Update, and Save a Model Instance

Source: https://lucid.adonisjs.com/docs/models

Demonstrates how to find a user by ID, update their email, and save the changes. Ensure the User model and database connection are set up.

```typescript
import User from '#models/user'



const user = await User.findOrFail(1)

user.email = 'virk@adonisjs.com'

await user.save()
```

--------------------------------

### Create User and Profile with Manual Transaction

Source: https://lucid.adonisjs.com/docs/transactions

This snippet illustrates a manual transaction for creating a user and their profile. It requires explicit calls to `trx.commit()` on success or `trx.rollback()` on failure, ensuring proper transaction management across potential errors.

```typescript
import db from '@adonisjs/lucid/services/db'



export default class UsersService {

  async createWithProfile(email: string, fullName: string) {

    const trx = await db.transaction()



    try {

      const [user] = await trx

        .table('users')

        .insert({ email })

        .returning(['id', 'email'])



      await trx.table('profiles').insert({ user_id: user.id, full_name: fullName })

      await trx.commit()



      return user

    } catch (error) {

      await trx.rollback()

      throw error

    }

  }



}
```

--------------------------------

### Truncate Table

Source: https://lucid.adonisjs.com/docs/crud-operations

Empties the underlying table. Use this in test setup and teardown to reset to a clean state. Can optionally cascade truncate related tables.

```APIDOC
## DELETE /api/tables/:tableName/truncate

### Description
Empties the underlying table. Use it in test setup and teardown to reset to a clean state. Pass `true` to cascade-truncate related tables on dialects that support it (PostgreSQL, MSSQL).

### Method
DELETE

### Endpoint
/api/tables/:tableName/truncate

### Query Parameters
- **cascade** (boolean) - Optional - If true, cascade-truncate related tables.

### Request Example (No Cascade)
```http
DELETE /api/tables/users/truncate
```

### Request Example (Cascade)
```http
DELETE /api/tables/users/truncate?cascade=true
```

### Response
#### Success Response (200)
- **message** (string) - Confirmation message that the table has been truncated.

#### Response Example
```json
{
  "message": "Table 'users' truncated successfully."
}
```

### Notes
Truncate bypasses model hooks and does not fire `beforeDelete` or `afterDelete`. It is generally faster than `DELETE FROM users` because most databases reset the auto-increment counter and skip per-row logging, but the two are not equivalent in every dialect.
```

--------------------------------

### Run pending database migrations

Source: https://lucid.adonisjs.com/docs/commands

Applies all pending migrations in order and regenerates the `database/schema.ts` file. Use the `--force` flag to allow execution in production environments.

```bash
node ace migration:run
```

```bash
❯ migrated database/migrations/1720000000000_create_posts_table
❯ migrated database/migrations/1720000000001_create_comments_table

Migrated in 42 ms
```

--------------------------------

### Embed Raw SQL in WHERE Clause

Source: https://lucid.adonisjs.com/docs/raw-query-builder

Embed raw SQL fragments in `WHERE` clauses. This example filters posts where the view count is greater than the average view count.

```typescript
await db
  .from('posts')
  .where('view_count', '>', db.raw('(select avg(view_count) from posts)'))
```

--------------------------------

### Create Main Seeder

Source: https://lucid.adonisjs.com/docs/seeders

Use the 'ace make:seeder' command to create a main seeder file for orchestrating the execution of other seeders. This is useful for complex or conditional seeding logic.

```bash
node ace make:seeder main/index
```

```typescript
// CREATE: database/seeders/main/index_seeder.ts
```

--------------------------------

### Interpolate Query with toQuery

Source: https://lucid.adonisjs.com/docs/select-query-builder

Use `toQuery` to get the query as a single interpolated string with bindings substituted. Prefer `toSQL` for logs to avoid quoting issues.

```typescript
const sql = db.from('users').where('is_active', true).toQuery()
```

--------------------------------

### Build and save a user instance

Source: https://lucid.adonisjs.com/docs/crud-operations

Instantiate a model, assign properties, and then call `save()` to persist the record. The first `save` call performs an INSERT, and subsequent calls perform an UPDATE.

```typescript
const user = new User()
user.email = 'virk@adonisjs.com'
user.password = 'secret'
await user.save()
```

--------------------------------

### Interpolate Query with toQuery

Source: https://lucid.adonisjs.com/docs/insert-query-builder

Use `.toQuery()` to get the query as a single interpolated string with bindings substituted. Prefer `.toSQL()` for logging to avoid quoting issues.

```typescript
const sql = db.table('users').insert({ email: 'virk@adonisjs.com' }).toQuery()
```

--------------------------------

### Create Lucid Models

Source: https://lucid.adonisjs.com/docs/schema-generation

Generate models for the tables you intend to interact with using the `make:model` Ace command. Models should extend the corresponding generated schema classes.

```bash
node ace make:model User
node ace make:model Post

```

--------------------------------

### Generate SQL and Bindings with toSQL

Source: https://lucid.adonisjs.com/docs/insert-query-builder

Use `.toSQL()` to get the SQL query and its bindings without executing it. Call `.toNative()` on the result to see dialect-specific formatting.

```typescript
const { sql, bindings } = db
  .table('users')
  .insert({ email: 'virk@adonisjs.com' })
  .toSQL()

const native = db
  .table('users')
  .insert({ email: 'virk@adonisjs.com' })
  .toSQL()
  .toNative()
```

--------------------------------

### Create View

Source: https://lucid.adonisjs.com/docs/schema-builder

Use `createView` to define a new view. The callback receives a view builder, and `as(query)` defines the underlying SQL query.

```typescript
this.schema.createView('active_users', (view) => {
  view.columns(['id', 'email'])
  view.as(this.knex().select('id', 'email').from('users').where('is_active', true))
})
```

--------------------------------

### Fresh database migrations

Source: https://lucid.adonisjs.com/docs/commands

The `migration:fresh` command drops all tables and re-runs migrations from scratch. It's similar to `migration:refresh` but skips the rollback phase by dropping tables directly. Use `--seed` to run seeders afterward.

```bash
node ace migration:fresh
```

```bash
node ace migration:fresh --seed
```

--------------------------------

### Declare HasManyThrough Relationship

Source: https://lucid.adonisjs.com/docs/has-many-through

Declare a basic hasManyThrough relationship between Country, Post, and User models. This setup allows a country to have many posts through its associated users.

```typescript
import { hasManyThrough } from '@adonisjs/lucid/orm'

import type { HasManyThrough } from '@adonisjs/lucid/types/relations'

import { CountriesSchema } from '#database/schema'

import Post from '#models/post'

import User from '#models/user'



export default class Country extends CountriesSchema {

  @hasManyThrough([() => Post, () => User])

  declare posts: HasManyThrough<typeof Post>

}
```

--------------------------------

### Execute Basic Insert Query

Source: https://lucid.adonisjs.com/docs/insert-query-builder

Insert queries are Promises and can be executed using `await`. This is the fundamental way to send insert statements to the database.

```typescript
await db.table('users').insert({ email })
```

--------------------------------

### Set Isolation Level for Managed Transaction

Source: https://lucid.adonisjs.com/docs/transactions

This example shows how to set a specific isolation level for a managed transaction by passing an options object as the second argument to `db.transaction`.

```typescript
await db.transaction(

  async (trx) => {

    // queries using trx

  },

  { isolationLevel: 'serializable' }

)
```

--------------------------------

### Configure PostgreSQL Read/Write Replicas in Lucid

Source: https://lucid.adonisjs.com/docs/configuration

Define read and write replica connections for a PostgreSQL database in the Lucid configuration file. Ensure database replication is configured externally.

```typescript
import env from '#start/env'

import { defineConfig } from '@adonisjs/lucid'



const dbConfig = defineConfig({

  connection: 'postgres',

  connections: {

    postgres: {

      client: 'pg',

      connection: {

        user: env.get('DB_USER'),

        password: env.get('DB_PASSWORD'),

        database: env.get('DB_DATABASE'),

      },

      replicas: {

        read: {

          connection: [

            { host: env.get('DB_READ_HOST_1'), port: env.get('DB_PORT') },

            { host: env.get('DB_READ_HOST_2'), port: env.get('DB_PORT') },

          ],

        },

        write: {

          connection: {

            host: env.get('DB_WRITE_HOST'),

            port: env.get('DB_PORT'),

          },

        },

      },

      migrations: {

        naturalSort: true,

        paths: ['database/migrations'],

      },

    },

  },

})



export default dbConfig
```

--------------------------------

### Conditional Migration with `hasColumn`

Source: https://lucid.adonisjs.com/docs/schema-builder

Example migration that checks for the existence of a 'name' column in the 'users' table before renaming it to 'full_name'. This handles cases where the column might already have been renamed.

```typescript
async up() {

  const hasOldName = await this.schema.hasColumn('users', 'name')

  if (!hasOldName) {

    return

  }



  this.schema.alterTable('users', (table) => {

    table.renameColumn('name', 'full_name')

  })

}
```

--------------------------------

### Configure Shared Connection Options

Source: https://lucid.adonisjs.com/docs/configuration

Configure shared connection options for your database client, including enabling async stack traces in development environments.

```typescript
{

  client: 'pg',

  connection: env.get('DATABASE_URL'),

  asyncStackTraces: app.inDev,

}
```

--------------------------------

### Raw SQL in WHERE Clause with Positional Binding

Source: https://lucid.adonisjs.com/docs/raw-query-builder

Embed raw SQL fragments in `WHERE` clauses using positional bindings. This example filters users by a case-insensitive email comparison.

```typescript
db.from('users').whereRaw('LOWER(email) = ?', [email.toLowerCase()])
```

--------------------------------

### Generate Database Schema

Source: https://lucid.adonisjs.com/docs/schema-generation

Run the `schema:generate` Ace command to introspect your existing database and create the `database/schema.ts` file.

```bash
node ace schema:generate

```

--------------------------------

### Load Custom Count of Published Posts

Source: https://lucid.adonisjs.com/docs/has-many

Override the default alias for `withCount` by providing a callback function. This example counts only published posts and aliases the count as 'publishedPostsCount'.

```typescript
const users = await User
  .query()
  .withCount('posts', (query) => {
    query.where('is_published', true).as('publishedPostsCount')
  })
```

--------------------------------

### Load Custom Post Count with Alias

Source: https://lucid.adonisjs.com/docs/has-many-through

Override the default count alias and apply conditions to the count by providing a callback to `withCount`. This example counts only published posts.

```typescript
const countries = await Country
  .query()
  .withCount('posts', (query) => {
    query.where('is_published', true).as('publishedPostsCount')
  })
```

--------------------------------

### Working with Dates and Timestamps in Lucid Models

Source: https://lucid.adonisjs.com/docs/schema-classes

Demonstrates accessing and manipulating date/timestamp columns as Luxon DateTime instances within a Lucid model. Shows how to format dates and calculate differences.

```typescript
import { PostsSchema } from '#database/schema'



const post = await Post.findOrFail(params.id)



post.createdAt.toFormat('yyyy-MM-dd')



const daysSinceCreated = DateTime.now().diff(post.createdAt, 'days').days



if (post.publishedOn && post.publishedOn > DateTime.now()) {

  // scheduled for later

}
```

--------------------------------

### Query with Ordering and Insert with Returning

Source: https://lucid.adonisjs.com/docs/database-service

Demonstrates using `db.from()` with `.orderBy()` for sorting results and `db.table().insert().returning()` to insert data and retrieve the inserted rows.

```typescript
const posts = await db.from('posts').select('id', 'title').orderBy('id', 'desc')
```

```typescript
const [post] = await db.table('posts').insert({ title: request.input('title') }).returning(['id', 'title'])
```

--------------------------------

### Get Insert Query Builder Instance

Source: https://lucid.adonisjs.com/docs/insert-query-builder

Obtain an instance of the InsertQueryBuilder using the `db` service. Use `db.insertQuery()` for a builder without a table or `db.table(tableName)` as a shortcut.

```typescript
import db from '@adonisjs/lucid/services/db'



const query = db.insertQuery()  // builder without a table selected

const usersQuery = db.table('users')  // shortcut: builder with the table selected
```

--------------------------------

### Start Transaction from Model

Source: https://lucid.adonisjs.com/docs/transactions

Use `Model.transaction` for operations scoped to a single model, especially when the model overrides the `static connection`. This is equivalent to `db.transaction` for models on the default connection.

```typescript
import User from '#models/user'



await User.transaction(async (trx) => {

  const user = new User()

  user.email = 'virk@adonisjs.com'

  user.useTransaction(trx)

  await user.save()

})
```

--------------------------------

### Configure Database Migrations

Source: https://lucid.adonisjs.com/docs/configuration

Define migration settings like scan paths, table names, and transaction behavior. Use `naturalSort` for timestamp-prefixed filenames. `disableTransactions` should only be set to true for statements incompatible with transactions.

```typescript
{

  client: 'pg',

  connection: env.get('DATABASE_URL'),

  migrations: {

    naturalSort: true,

    paths: ['database/migrations'],

    tableName: 'adonis_schema',

    disableTransactions: false,

    disableRollbacksInProduction: true,

  },

}
```

--------------------------------

### Start Managed Database Transactions

Source: https://lucid.adonisjs.com/docs/database-service

Use `db.transaction` for operations that must succeed or fail together. The transaction commits on success and rolls back on error within the callback.

```typescript
import type { HttpContext } from '@adonisjs/core/http'

import db from '@adonisjs/lucid/services/db'



export default class UsersController {

  async store({ request, response }: HttpContext) {

    const user = await db.transaction(async (trx) => {

      const [createdUser] = await trx

        .table('users')

        .insert({ email: request.input('email') })

        .returning(['id', 'email'])



      await trx

        .table('profiles')

        .insert({

          user_id: createdUser.id,

          full_name: request.input('full_name'),

        })



      return createdUser

    })



    return response.created(user)

  }

}
```

--------------------------------

### Generate a New Factory

Source: https://lucid.adonisjs.com/docs/model-factories

Use the Ace command to generate a new factory file. This command creates the factory file in the `database/factories/` directory.

```bash
node ace make:factory User

// CREATE: database/factories/user_factory.ts
```

--------------------------------

### Dump Database Schema

Source: https://lucid.adonisjs.com/docs/commands

Write the current database schema to a SQL file and a JSON manifest. This is used to bootstrap a database without replaying all migrations. Optionally specify a custom path.

```bash
node ace schema:dump
```

```bash
node ace schema:dump --path=./database/schema.sql
```

--------------------------------

### Embed Raw SQL in ORDER BY Clause

Source: https://lucid.adonisjs.com/docs/raw-query-builder

Use `orderByRaw` to specify custom ordering using raw SQL. This example orders posts by the length of their titles in descending order.

```typescript
await db.from('posts').orderByRaw('LENGTH(title) DESC')
```

--------------------------------

### Find or Create a User

Source: https://lucid.adonisjs.com/docs/crud-operations

Use `firstOrCreate` to find a user by email or create a new one if it doesn't exist. The second argument provides additional values for creation.

```javascript
const user = await User.firstOrCreate(

  { email: 'virk@adonisjs.com' },  // search

  { password: 'secret' }           // additional values if creating

)
```

--------------------------------

### Create a new seeder file

Source: https://lucid.adonisjs.com/docs/commands

Generates a new seeder file in the `database/seeders` directory. This command is used to create files for populating your database with initial data.

```bash
node ace make:seeder User
```

--------------------------------

### Programmatically Pretty-Print Queries

Source: https://lucid.adonisjs.com/docs/debugging

Use `db.prettyPrint(query)` within a custom listener in `start/events.ts` to format specific queries before they are logged or processed further. This example skips pretty-printing DDL statements.

```typescript
import emitter from '@adonisjs/core/services/emitter'
import db from '@adonisjs/lucid/services/db'


emitter.on('db:query', (query) => {
  if (query.ddl) {
    return
  }

  db.prettyPrint(query)
})
```

--------------------------------

### Find or Initialize a User

Source: https://lucid.adonisjs.com/docs/crud-operations

Use `firstOrNew` to find a user by email or create a new, unsaved instance if no match is found. This allows for further modifications before saving.

```javascript
const user = await User.firstOrNew(

  { email: 'virk@adonisjs.com' },

  { password: 'secret' }

)



if (user.$isNew) {

  user.source = 'invitation'

  await user.save()

}
```

--------------------------------

### Custom JSON Parsing with consume

Source: https://lucid.adonisjs.com/docs/schema-classes

Implement custom logic for transforming raw database values into model attributes using the `consume` option. This example parses a stringified JSON value.

```typescript
@column({

  consume: (value) => typeof value === 'string' ? JSON.parse(value) : value,

})

declare preferences: Record<string, unknown>
```

--------------------------------

### Run All Seeders

Source: https://lucid.adonisjs.com/docs/seeders

Execute all seeders located in the `database/seeders` directory using the `db:seed` Ace command.

```bash
node ace db:seed
```

--------------------------------

### Embed Raw SQL Fragment in Query Builder

Source: https://lucid.adonisjs.com/docs/raw-query-builder

Use `db.raw` to embed raw SQL fragments within a query builder. This example adds a subquery to count posts for each user.

```typescript
const users = await db
  .from('users')
  .select(
    'id',
    'email',
    db.raw('(select count(*) from posts where posts.user_id = users.id) as posts_count')
  )
```

--------------------------------

### Create a new model factory

Source: https://lucid.adonisjs.com/docs/commands

Scaffolds a new model factory file within the `database/factories` directory. This factory will be used to produce instances of the specified model.

```bash
node ace make:factory Post
```

--------------------------------

### Persist Through Intermediate Relationship

Source: https://lucid.adonisjs.com/docs/has-many-through

For `hasManyThrough` relationships, persistence must be done through the intermediate model. This example shows how to create a post by first fetching an associated user and then creating the post through that user.

```typescript
const user = await country.related('users').query().firstOrFail()
await user.related('posts').create({ title: 'Hello' })
```

--------------------------------

### migration:run

Source: https://lucid.adonisjs.com/docs/commands

Applies all pending database migrations in order and regenerates the database/schema.ts file. It can be configured to run on specific connections and includes options for production safety and dry runs.

```APIDOC
## Commands

### make:migration
Creates a new migration file inside `database/migrations` with a timestamped filename.

#### Arguments
- **name** (string) - Required - Name of the migration file. Lucid prefixes the file with a timestamp automatically.

#### Options
- **--connection, -c** (string) - Select the database connection the migration belongs to.
- **--folder** (string) - Select the migration directory when the connection has multiple migration paths configured.
- **--create** (string) - Scaffold a migration that creates the given table. This is the default action.
- **--alter** (string) - Scaffold a migration that alters the given table instead of creating one.
- **--contents-from** (string) - Use the contents of the given file as the generated migration source.
- **--force, -f** - Overwrite the file if it already exists.

### make:model
Creates a new Lucid model file inside `app/models`.

#### Arguments
- **name** (string) - Required - Name of the model class. Lucid converts it to snake_case for the filename.

#### Options
- **--migration** - Generate a matching migration file alongside the model.
- **--controller** - Generate a matching resourceful controller for the model.
- **--transformer** - Generate a matching transformer file for the model.
- **--factory** - Generate a matching factory file for the model.
- **--contents-from** (string) - Use the contents of the given file as the generated model source.
- **--force** - Overwrite the file if it already exists.

### make:factory
Creates a new model factory inside `database/factories`.

#### Arguments
- **model** (string) - Required - Name of the model the factory produces.

#### Options
- **--contents-from** (string) - Use the contents of the given file as the generated factory source.
- **--force, -f** - Overwrite the file if it already exists.

### make:seeder
Creates a new seeder file inside `database/seeders`.

#### Arguments
- **name** (string) - Required - Name of the seeder class. The filename is derived from this name.

#### Options
- **--contents-from** (string) - Use the contents of the given file as the generated seeder source.
- **--force, -f** - Overwrite the file if it already exists.

### migration:run
Applies every pending migration in order, records each one in the `adonis_schema` table, and regenerates `database/schema.ts` when finished.

#### Options
- **--connection, -c** (string) - Run migrations for the given connection instead of the default.
- **--force** - Explicitly allow the command to run in production.
- **--dry-run** - Print the SQL that would run without executing it.
- **--compact-output** - Emit a single-line summary instead of per-file progress lines.
- **--disable-locks** - Skip the advisory lock that serializes concurrent migration runs.
- **--schema-path** (string) - Load a schema dump file before running pending migrations.
- **--no-schema-generate** - Skip regenerating `database/schema.ts` after the migrations apply.
```

--------------------------------

### Load Max Creation Date of Posts

Source: https://lucid.adonisjs.com/docs/has-many

Use `withAggregate` to load custom aggregate values from a relationship. This example finds the maximum creation date of posts for each user and aliases it as 'lastPostAt'.

```typescript
const users = await User
  .query()
  .withAggregate('posts', (query) => {
    query.max('created_at').as('lastPostAt')
  })
```