### Run Development Server

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/README.md

Command to start the local development server for the documentation website. Access the site at http://localhost:3333/docs/introduction.

```sh
npm run dev
```

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

### Clone and Install Dependencies

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/README.md

Commands to clone the documentation boilerplate repository using degit and install the necessary npm dependencies.

```sh
npx degit dimerapp/docs-boilerplate <my-website>
```

```sh
cd <my-website>\nnpm i
```

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

### Setup Runner Hooks for Database Migration

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/testing/introduction.md

Use `testUtils.db().migrate()` in runner setup to apply migrations once for the test run and ensure a cleanup function is returned to reset the schema on exit.

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

export const runnerHooks: Required<Pick<Config, 'setup' | 'teardown'>> = {
  setup: [
    () => testUtils.db().migrate(),
  ],
  teardown: [],
}
```

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

### Example Seeder Filename Structure

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/testing/seeders.md

Prefix seeder filenames with a counter to ensure alphabetical sorting produces the desired execution order.

```text
database/seeders/
  01_category_seeder.ts
  02_user_seeder.ts
  03_post_seeder.ts
```

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

### Configure SQLite Connection

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Set up a SQLite database connection using the `better-sqlite3` client. This example specifies the database file path and enables `useNullAsDefault` for compatibility.

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

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

### Create a New Model Instance

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/introduction.md

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
// title: app/controllers/users_controller.ts
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'),
    })
  }
}
```

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

### afterFetch Hook Example

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/hooks.md

Fires after a multi-row query returns. Receives an array of model instances. This example demonstrates caching fetched users.

```typescript
import { afterFetch } from '@adonisjs/lucid/orm'
import { UsersSchema } from '#database/schema'

export default class User extends UsersSchema {
  @afterFetch()
  static warmCache(users: User[]) {
    for (const user of users) {
      cache.set(`user:${user.id}`, user, '1 hour')
    }
  }
}
```

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

### Nested Preloading Example

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/serializing_models.md

Demonstrates how to preload nested relationships in a query. The transformer at each level should then handle the corresponding preloaded relation.

```typescript
const users = await User
  .query()
  .preload('posts', (postsQuery) => {
    postsQuery.preload('comments', (commentsQuery) => {
      commentsQuery.preload('author')
    })
  })

return serialize(UserTransformer.transform(users))
```

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

### beforePaginate Hook Example

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/hooks.md

Fires before a pagination query is executed. Receives a tuple of the count query builder and the main query builder. Mutate both to keep counts and results in sync. This example filters out deleted users.

```typescript
import { beforePaginate } from '@adonisjs/lucid/orm'
import type { ModelQueryBuilderContract } from '@adonisjs/lucid/types/model'
import { UsersSchema } from '#database/schema'

export default class User extends UsersSchema {
  @beforePaginate()
  static filterDeleted([countQuery, query]: [
    ModelQueryBuilderContract<typeof User>,
    ModelQueryBuilderContract<typeof User>,
  ]) {
    countQuery.whereNull('deleted_at')
    query.whereNull('deleted_at')
  }
}
```

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

### Create a new table

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/schema_builder.md

Creates a new table named 'posts' with 'id', 'title', 'body', and 'timestamps' columns. This is a basic table creation example.

```typescript
this.schema.createTable('posts', (table) => {
  table.increments('id')
  table.string('title').notNullable()
  table.text('body')
  table.timestamps(true, true)
})
```

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

### Generated Schema Class Example

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/schema_classes.md

This is an example of a schema class generated by Lucid for the 'posts' table. It extends BaseModel, defines the table name, lists all columns, and uses decorators for column definitions, including primary keys and timestamps. Note that column names are converted to camelCase.

```typescript
import { DateTime } from 'luxon'
import { BaseModel, column } from '@adonisjs/lucid/orm'

export class PostsSchema extends BaseModel {
  static table = 'posts'

  static $columns = ['id', 'title', 'content', 'createdAt', 'updatedAt'] as const
  $columns = PostsSchema.$columns

  @column({ isPrimary: true })
  declare id: number

  @column()
  declare title: string

  @column()
  declare content: string

  @column.dateTime({ autoCreate: true })
  declare createdAt: DateTime | null

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  declare updatedAt: DateTime | null
}
```

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

### Controller with Direct Database Queries

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

Example controller demonstrating how to perform database operations directly using the `db` service without models. This is useful for one-off queries or scripts.

```ts
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')
  }
}
```

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

### Configure Database Connection Pooling

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Define database connection settings, including client, connection URL, and pool configurations. The `afterCreate` callback is useful for per-connection setup.

```typescript
// title: config/database.ts
import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'

const dbConfig = defineConfig({
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      connection: env.get('DATABASE_URL'),
      pool: {
        min: 2,
        max: 10,
        acquireTimeoutMillis: 60_000,
        afterCreate: (conn, done) => {
          conn.query(`SET timezone='UTC';`, (err: Error) => done(err, conn))
        },
      },
    },
  },
})

export default dbConfig

```

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

### Database Configuration File

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

The `config/database.ts` file is generated based on the selected driver. This example shows the default SQLite configuration.

```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'],
      },
    },
  },
})
```

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

### Install Lucid with npm, yarn, or pnpm

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

Use your preferred package manager to add the Lucid package to your project dependencies.

```sh
npm i @adonisjs/lucid
```

```sh
yarn add @adonisjs/lucid
```

```sh
pnpm add @adonisjs/lucid
```

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

### Configure PostgreSQL with Connection String

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Use a database URL for PostgreSQL connections, which is convenient when credentials are provided as a single string. This example shows setting the connection string directly in the configuration.

```typescript
// title: config/database.ts
import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'

const dbConfig = defineConfig({
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      connection: env.get('DATABASE_URL'),
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})
```

```dotenv
// title: .env
DATABASE_URL=postgres://username:password@localhost:5432/database_name
```

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

### Define Table Schema in Migration

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

Describe the columns for your new table within the generated migration file. This example creates a 'posts' table with 'id', 'title', 'body', and timestamps.

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

export default class extends BaseSchema {
  protected tableName = 'posts'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.string('title').notNullable()
      table.text('body').nullable()
      table.timestamp('created_at')
      table.timestamp('updated_at')
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}
```

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

### Excerpt of a Generated Schema Class

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/introduction.md

This is an example of a schema class generated by Lucid from the database schema. It defines table name, columns, and their types.

```typescript
// title: database/schema.ts (excerpt)
import { BaseModel, column } from '@adonisjs/lucid/orm'
import { DateTime } from 'luxon'

export class UsersSchema extends BaseModel {
  static table = 'users'

  @column({ isPrimary: true })
  declare id: number

  @column()
  declare email: string

  @column({ serializeAs: null })
  declare password: string

  @column.dateTime({ autoCreate: true })
  declare createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  declare updatedAt: DateTime
}
```

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

### Listen for Query Events with Reporter Data

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/insert.md

Example of listening to the `db:query` event and accessing custom reporter data attached to the query.

```typescript
// title: start/events.ts
import emitter from '@adonisjs/core/services/emitter'

emitter.on('db:query', (query) => {
  console.log(query.userId, query.source)
})
```

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

### Get Database Query Builder Instance

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

Instantiate a database query builder using the `db` service. Use `db.query()` for a builder without a table or `db.from(table)` as a shortcut to pre-select the table.

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

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

### Select Raw SQL Fragment as Column

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

Include arbitrary SQL fragments as columns using the `db.raw` method. This example counts the number of posts for each user.

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

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

### Configure Database Replicas

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Define read and write replica configurations within the `connections` object in `config/database.ts`. This setup is supported for MySQL, PostgreSQL, and MSSQL. Ensure database replication is handled externally.

```typescript
// title: config/database.ts
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
```

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

### Generated database/schema.ts Example

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/schema_generation.md

An excerpt from the generated `database/schema.ts` file, illustrating how database tables and columns are mapped to Lucid schema classes and model properties. Manual edits to this file are lost on regeneration.

```typescript
// title: database/schema.ts (excerpt)
import { BaseModel, column } from '@adonisjs/lucid/orm'
import { DateTime } from 'luxon'

export class UsersSchema extends BaseModel {
  static table = 'users'

  @column({ isPrimary: true })
  declare id: number

  @column()
  declare email: string

  @column()
  declare password: string

  @column.dateTime({ autoCreate: true })
  declare createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  declare updatedAt: DateTime
}

export class PostsSchema extends BaseModel {
  static table = 'posts'

  @column({ isPrimary: true })
  declare id: number

  // ...
}
```

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

### Wrap Each Test in a Global Transaction

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/testing/introduction.md

Use `testUtils.db().wrapInGlobalTransaction()` as a `group.each.setup` hook to start a transaction before each test and roll it back afterward, providing fast isolation for Lucid-only interactions.

```typescript
test.group('Users', (group) => {
  group.each.setup(() => testUtils.db().wrapInGlobalTransaction())
})
```

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

### Combine Schema Rules for Types, Columns, and Tables

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/schema_classes.md

Rules compose from least specific to most specific. This example shows how to define global type rules, column-specific rules, and table-specific column rules, demonstrating how they can refine each other.

```typescript
// title: database/schema_rules.ts
export default {
  types: {
    json: {
      tsType: 'JSON<any>',
      decorators: [{ name: '@column' }],
      imports: [
        { source: '#types/db', typeImports: ['JSON'] },
      ],
    },
  },
  columns: {
    deleted_at: {
      tsType: 'DateTime | null',
      decorators: [{ name: '@column.dateTime' }],
      imports: [
        { source: 'luxon', namedImports: ['DateTime'] },
      ],
    },
  },
  tables: {
    posts: {
      columns: {
        metadata: {
          tsType: 'JSON<{ title?: string; description?: string; ogImage?: string }>',
          decorators: [{ name: '@column' }],
          imports: [
            { source: '#types/db', typeImports: ['JSON'] },
          ],
        },
      },
    },
  },
} satisfies SchemaRules
```

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

### Dynamically Registering a beforeSave Hook

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/hooks.md

Demonstrates registering a hook dynamically at runtime using `Model.before`. This example hashes a user's password before saving if the password has been modified.

```typescript
// title: providers/app_provider.ts
import User from '#models/user'
import hash from '@adonisjs/core/services/hash'

export default class AppProvider {
  async boot() {
    User.before('save', async (user) => {
      if (user.$dirty.password) {
        user.password = await hash.make(user.password)
      }
    })
  }
}
```

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

### Controller with Model CRUD Operations

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

Example controller demonstrating how to use Lucid models for creating and retrieving data. `Post.create()` inserts a row, and `Post.query()` returns a typed query builder.

```ts
import type { HttpContext } from '@adonisjs/core/http'
import Post from '#models/post'

export default class PostsController {
  async store({ request }: HttpContext) {
    return Post.create({
      title: request.input('title'),
      body: request.input('body'),
    })
  }

  async index() {
    return Post.query().orderBy('id', 'desc')
  }
}
```

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

### Query Entry Points: from, table, query, insertQuery

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/database_service.md

Demonstrates different entry points for creating query builders. `from` and `table` are shortcuts for table queries, while `query` and `insertQuery` allow for pre-selection of connection modes and type parameters without a predefined table.

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

export default class PostsController {
  async index() {
    return db
      .from('posts')
      .select('id', 'title')
      .orderBy('id', 'desc')
  }

  async store({ request, response }: HttpContext) {
    const [post] = await db
      .table('posts')
      .insert({ title: request.input('title') })
      .returning(['id', 'title'])

    return response.created(post)
  }
}
```

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

export default class PostsService {
  async listPublished() {
    return db
      .query<{ id: number; title: string }>({ mode: 'read' })
      .from('posts')
      .where('is_published', true)
      .select('id', 'title')
  }
}
```

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

### Define Default and Named Connections

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Configure the default database connection and register named connections within the `config/database.ts` file. This setup defines the client, connection details, and migration paths for a PostgreSQL database.

```typescript
import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'

const dbConfig = defineConfig({
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      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
```

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

### Get Insert Query Builder Instance

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/insert.md

Instantiate the insert query builder using the db service. Use db.insertQuery() for a builder without a table or db.table(table) 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
```

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

### Create and Boot Collections

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/README.md

Define new collections using the `Collection` class, specifying the database URL, renderer, and URL prefix. Ensure to call `collection.boot()`.

```typescript
// Docs
const docs = new Collection()
  .db(new URL('../content/docs/db.json', import.meta.url))
  .useRenderer(renderer)
  .urlPrefix('/docs')

await docs.boot()

// API reference
const apiReference = new Collection()
  .db(new URL('../content/api_reference/db.json', import.meta.url))
  .useRenderer(renderer)
  .urlPrefix('/api')

await apiReference.boot()

export const collections = [docs, apiReference]
```

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

### Get SQL and Bindings with `toSQL`

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

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

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

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

### Start Transaction from Model

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/transactions.md

Initiate a transaction directly from a model using `Model.transaction`. This is useful when the entire operation is scoped to a single model and its connection, especially if the model defines a custom `static 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()
})
```

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

### Get First Row or Throw Exception

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

Use `firstOrFail()` to get the first matching row. Throws a `RowNotFoundException` if no rows are found, which is useful for returning 404 responses.

```typescript
const user = await db.from('users').where('id', params.id).firstOrFail()
```

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

### Basic Table Creation with Columns

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/table_builder.md

Demonstrates creating a new table named 'posts' with common column types like increments, string, text, integer with foreign key, and timestamps.

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

export default class extends BaseSchema {
  async up() {
    this.schema.createTable('posts', (table) => {
      table.increments('id')
      table.string('title').notNullable()
      table.text('body')
      table.integer('user_id').unsigned().references('users.id').onDelete('CASCADE')
      table.timestamps(true, true)
    })
  }
}
```

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

### afterPaginate Hook Example

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/hooks.md

Fires after a pagination query has completed. Receives the ModelPaginator instance. Use it for inspection or logging. This example logs the number of users paginated and the current page.

```typescript
import { afterPaginate } from '@adonisjs/lucid/orm'
import type { ModelPaginatorContract } from '@adonisjs/lucid/types/model'
import { UsersSchema } from '#database/schema'

export default class User extends UsersSchema {
  @afterPaginate()
  static logUsage(paginator: ModelPaginatorContract<User>) {
    logger.debug({ count: paginator.all().length, page: paginator.currentPage }, 'users paginated')
  }
}
```

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

### Scoping Database Operations to a Connection

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/testing/introduction.md

Run setup, teardown, or assertion logic against a specific database connection by using the `db('connection_name')` helper. This is useful when managing multiple database connections.

```typescript
// Setup hooks on a specific connection
group.each.setup(() => testUtils.db('analytics').truncate())

// Transactional isolation on a specific connection
group.each.setup(() => testUtils.db('analytics').wrapInGlobalTransaction())

// Assertions on a specific connection
await db.connection('analytics').assertHas('events', { kind: 'signup' })
```

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

### Create a new migration file

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/introduction.md

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

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

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

### Get Interpolated SQL String with `toQuery`

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

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

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

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

### Run migration:fresh

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/commands.md

Drops all tables and re-runs migrations. Use --seed to also run seeders.

```sh
node ace migration:fresh
```

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

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

### Register new database connections at runtime

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/connection_manager.md

Use the `add` method to register a new database connection after the application has booted. This is useful for multi-tenant applications where each tenant requires a separate database. The `add` method is a no-op if a connection with the same name already exists.

```typescript
// title: app/services/tenant_connections.ts
import db from '@adonisjs/lucid/services/db'
import type { ConnectionConfig } from '@adonisjs/lucid/types/database'

export default class TenantConnections {
  register(tenantId: string, config: ConnectionConfig) {
    const name = `tenant:${tenantId}`

    if (!db.manager.has(name)) {
      db.manager.add(name, config)
    }

    return db.connection(name)
  }

  async forget(tenantId: string) {
    await db.manager.release(`tenant:${tenantId}`)
  }
}
```

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

### Starter Schema Rules File

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/schema_classes.md

Provides a basic structure for a schema rules file used to customize Lucid's schema generation. This file allows for defining rules for types, columns, and tables.

```typescript
// title: database/schema_rules.ts
import { type SchemaRules } from '@adonisjs/lucid/types/schema_generator'

export default {
  types: {},
  columns: {},
  tables: {},
} satisfies SchemaRules
```

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

### Column Types

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/table_builder.md

Reference for various column types supported by the Table Builder, including their definitions and usage examples.

```APIDOC
## Column Types

### increments

Auto-incrementing integer column. Marked as the primary key by default.

```ts
table.increments('id')
table.increments('id', { primaryKey: false }) // skip auto primary key
```

PostgreSQL uses `serial`; MySQL uses `int unsigned auto_increment`; Redshift uses `integer identity (1,1)`.

### bigIncrements

Auto-incrementing `bigint` column. Marked as the primary key by default.

```ts
table.bigIncrements('id')
```

### integer

Standard integer column.

```ts
table.integer('view_count')
```

### tinyint, smallint, mediumint, bigInteger

Smaller and larger integer types. `bigInteger` is `bigint` on PostgreSQL and MySQL; on dialects without a native bigint it falls back to a regular integer. `bigint` is an alias for `bigInteger`.

```ts
table.tinyint('flag_byte')
table.smallint('priority')
table.mediumint('view_bucket')   // MySQL-only
table.bigInteger('snowflake_id')
```

Bigint values are returned as strings in query results to avoid JavaScript precision loss.

### float

Floating-point column with optional precision (default 8) and scale (default 2).

```ts
table.float('rating')
table.float('price', 8, 2)
```

### double

Double-precision floating-point column. Same precision and scale arguments as `float`.

```ts
table.double('balance', 14, 4)
```

### decimal

Fixed-precision decimal column. Pass `null` as precision to allow arbitrary precision (PostgreSQL, SQLite, Oracle).

```ts
table.decimal('price')
table.decimal('price', 8, 2)
table.decimal('amount', null)   // arbitrary precision
```

### boolean

Boolean column. Many dialects represent booleans as `0`/`1` and return them as such.

```ts
table.boolean('is_published')
```

### string

Variable-length string column with optional length (defaults to 255).

```ts
table.string('title')
table.string('title', 100)
```

### text

Long-form text column. Pass `'mediumtext'` or `'longtext'` as the second argument on MySQL; ignored on other dialects.

```ts
table.text('body')
table.text('body', 'longtext')
```

### date

Date column (no time component).

```ts
table.date('dob')
```

### time

Time column (no date). MySQL accepts a precision option.

```ts
table.time('starts_at')
table.time('starts_at', { precision: 6 })
```

### dateTime

DateTime column with optional timezone and precision. `dateTime` and `datetime` are aliases.

```ts
table.dateTime('starts_at', { useTz: true })
table.dateTime('starts_at', { precision: 6 }).defaultTo(this.now(6))
```

`useTz: true` produces `timestamptz` on PostgreSQL and `DATETIME2` on MSSQL.

### timestamp

Timestamp column. Same options object as `dateTime`.

```ts
table.timestamp('created_at')
table.timestamp('created_at', { useTz: true })
table.timestamp('created_at', { precision: 6 })
```

### timestamps

Convenience method that adds `created_at` and `updated_at` columns. The signature is `timestamps(useTimestamps, defaultToNow)`.

```ts
table.timestamps()                // DATETIME columns, no defaults
table.timestamps(true)            // TIMESTAMP columns, no defaults
table.timestamps(true, true)      // TIMESTAMP columns, default CURRENT_TIMESTAMP
```

:::tip
For applications that need indexes, custom precision, or timezone-aware columns, prefer two `table.timestamp(...)` calls over `timestamps`. The `timestamps` shortcut returns void, so you cannot chain modifiers on the columns it creates.
:::

```

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

### Create a New Migration File

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

Use the `make:migration` command to scaffold a new migration. The `--create` flag specifies the table to be created.

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

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

### Executing Queries

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

Demonstrates how to execute a query and retrieve all matching rows.

```APIDOC
## Executing the query

The query builder is a `Promise`. You can `await` it directly, iterate the result, or use the dedicated execution helpers below.

```ts
const users = await db.from('users').where('is_active', true)
```
```

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

### Get Deleted Row Count - Lucid ORM

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/update_and_delete.md

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

```typescript
const removed = await db
  .from('expired_tokens')
  .where('expires_at', '<', new Date())
  .delete()

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

```

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

### Get Affected Row Count - Lucid ORM

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/update_and_delete.md

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 Website Links and Settings

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/README.md

Configure website links, file edit base URL, and copyright information in `content/config.json`.

```json
{
  "links": {
    "home": {
      "title": "Your project name",
      "href": "/"
    },
    "github": {
      "title": "Your project on Github",
      "href": "https://github.com/dimerapp"
    }
  },
  "fileEditBaseUrl": "https://github.com/dimerapp/docs-boilerplate/blob/develop",
  "copyright": "Your project legal name"
}
```

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

### Configure Lucid with a Database Driver

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

Run the Ace command to register Lucid with the framework and select a database driver. SQLite is recommended for local testing.

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

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

### Environment Variable for Database Path

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

The database file path is added to your `.env` file. This example shows the default path for SQLite.

```dotenv
DB_DATABASE=tmp/db.sqlite3
```

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

### Define Routes for Model Operations

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/installation.md

Wire up HTTP routes to the controller methods for handling POST and GET requests to the /posts endpoint.

```ts
import router from '@adonisjs/core/services/router'

const PostsController = () => import('#controllers/posts_controller')

router.get('/posts', [PostsController, 'index'])
router.post('/posts', [PostsController, 'store'])
```

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

### Configure Database Connection

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/schema_generation.md

Set up your database connection details in `config/database.ts`. Ensure the `DATABASE_URL` environment variable is correctly configured.

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

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

### Fetch all records from a table

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/crud_operations.md

Use the `all` method to retrieve every row from the model's table. The example shows the generated SQL query.

```typescript
const users = await User.all()
// SQL: SELECT * FROM "users" ORDER BY "id" DESC
```

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

### Inspect Query Execution Plan

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/debugging.md

Build a query, extract its SQL and bindings using `.toSQL()`, and then run it through `db.rawQuery` prefixed with `EXPLAIN ANALYZE` (or dialect-specific equivalent) to inspect the execution plan.

```typescript
// title: app/services/posts_service.ts
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
  }
}
```

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

### Get the First Matching Row

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

Use `first()` to retrieve only the first row that matches the query criteria. Returns `null` if no rows are found.

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

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

### Fresh Database with Schema Dump

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/schema_dumps.md

Use `migration:fresh` with `--schema-path` to drop all tables and then rebuild the database from the specified schema dump, instead of replaying all migrations.

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

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

### Interpolate Insert Query - toQuery

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/insert.md

Use `toQuery` to get the interpolated SQL string for an insert query. This is useful for debugging or manual inspection.

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

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

### Start Transactions

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/database_service.md

Use db.transaction for operations that must succeed or fail together. The callback is automatically committed on success and rolled back on error.

```typescript
// title: app/controllers/users_controller.ts
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)
  }
}
```

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

### Bootstrap Database with Schema Dump

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/schema_dumps.md

When the target database is empty, `migration:run` automatically loads a schema dump if one exists at the default or specified path. This replaces replaying all historical migrations.

```sh
node ace migration:run
```

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

### Bootstrap with Custom Schema Dump Path

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/schema_dumps.md

Specify a custom location for the schema dump file when bootstrapping an empty database using the `--schema-path` argument.

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

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

### Select from a Derived Table (Subquery)

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

Use a subquery as a derived table in the `from` clause. This example calculates the average of 'total' marks from a subquery aliased as 'totals'.

```typescript
db
  .from((subquery) => {
    subquery
      .from('user_exams')
      .sum('marks as total')
      .groupBy('user_id')
      .as('totals')
  })
  .avg('totals.total')
```

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

### Select Subquery as Column

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/select.md

Compute a value at query time by passing a subquery as a column. This example selects the most recent login IP address for each user.

```typescript
const users = await db
  .from('users')
  .select(
    db
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id')
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip')
  )
```

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

### Build and save a user instance

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/crud_operations.md

Create a model instance, assign attributes, and then persist it using the `save` method. The first `save` call performs an INSERT, while subsequent calls perform an UPDATE.

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

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

### Configure PostgreSQL Connection

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Set up a PostgreSQL connection using the `pg` client. This configuration includes standard connection fields and specific PostgreSQL options like `ssl`, `connectionString`, `searchPath`, and `returning`.

```typescript
// title: config/database.ts
import env from '#start/env'
import { defineConfig } from '@adonisjs/lucid'

const dbConfig = defineConfig({
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      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
```

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

### Disable Advisory Locks

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/introduction.md

Skip the advisory lock mechanism when running migrations, for example, against databases that do not support them or if a previous run left a stale lock.

```bash
node ace migration:run --disable-locks
```

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

### Controller Methods for List and Detail Views

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/serializing_models.md

Implements `index` and `show` controller methods. The `index` method fetches a paginated list of posts, preloading only the author, and serializes them using the base transformer. The `show` method fetches a single post with preloaded author and comments (including their authors), then serializes it using the `forDetailedView` variant.

```typescript
// title: app/controllers/posts_controller.ts
async index({ request, serialize }: HttpContext) {
  const page = request.input('page', 1)
  const posts = await Post
    .query()
    .preload('author')
    .orderBy('created_at', 'desc')
    .paginate(page, 20)

  return serialize(PostTransformer.paginate(posts.all(), posts.getMeta()))
}

async show({ params, serialize }: HttpContext) {
  const post = await Post
    .query()
    .where('id', params.id)
    .preload('author')
    .preload('comments', (q) => q.preload('author'))
    .firstOrFail()

  return serialize(
    PostTransformer.transform(post).useVariant('forDetailedView')
  )
}
```

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

### Returning Single Column from Insert

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/query_builders/insert.md

Retrieve only the 'id' of the newly inserted row in the 'posts' table. This is a concise way to get a specific generated value.

```typescript
// Single column
const [{ id }] = await db
  .table('posts')
  .returning('id')
  .insert({ title: 'Hello', body: 'World' })
```

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

### Basic Model Query with Preload and Count

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/query_builder.md

Demonstrates a basic model query using `query()`, filtering with `where()`, preloading a relationship with `preload()`, and counting a relationship with `withCount()`. Results are ordered by creation date.

```typescript
const posts = await Post
  .query()
  .where('is_published', true)
  .preload('author')
  .withCount('comments')
  .orderBy('created_at', 'desc')
```

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

### Enable UUID Extension on PostgreSQL

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/table_builder.md

On older PostgreSQL versions, ensure the `uuid-ossp` extension is installed before using `uuid` columns by executing a raw SQL command.

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

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

### Create Main Seeder Command

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/testing/seeders.md

Use the Ace CLI to generate a main seeder file, which will serve as an entry point for orchestrating other seeders.

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

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

### Apply Query Scopes with apply

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/query_builder.md

Use `apply` as an alias for `withScopes` for better readability when chaining multiple scopes. This example applies both `forUser` and `active` scopes.

```typescript
const teams = await Team
  .query()
  .apply((scopes) => {
    scopes.forUser(auth.user)
    scopes.active()
  })
```

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

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

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/introduction.md

Demonstrates basic schema operations for managing database tables. Use these methods to define your database structure.

```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')
```

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

### Configure LibSQL Connection

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Set up a LibSQL database connection using the `libsql` client. This configuration is identical to SQLite and is suitable for edge deployments with services like Turso.

```typescript
// title: config/database.ts
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
```

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

### Query a Named Database Connection

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/configuration.md

Access a specific named database connection using `db.connection('name')` in your application code. This example queries the 'analytics' connection.

```typescript
// title: app/services/reports_service.ts
import db from '@adonisjs/lucid/services/db'

export async function getSignupTotals() {
  return db
    .connection('analytics')
    .from('daily_signups')
    .select('date', 'total')
    .orderBy('date', 'desc')
}
```

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

### Main Seeder Implementation

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/testing/seeders.md

Implement the main seeder to import and invoke other seeders in the desired order. It respects the `environment` declaration of each seeder.

```ts
// title: database/seeders/main/index_seeder.ts
import app from '@adonisjs/core/services/app'
import { BaseSeeder } from '@adonisjs/lucid/seeders'

export default class IndexSeeder extends BaseSeeder {
  private async seed(SeederModule: { default: typeof BaseSeeder }) {
    const Seeder = SeederModule.default

    if (Seeder.environment && !Seeder.environment.includes(app.nodeEnvironment)) {
      return
    }

    await new Seeder(this.client).run()
  }

  async run() {
    await this.seed(await import('#database/seeders/category_seeder'))
    await this.seed(await import('#database/seeders/user_seeder'))
    await this.seed(await import('#database/seeders/post_seeder'))
  }
}
```

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

### Persisting Through Intermediate Relationship

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/relationships/has_many_through.md

HasManyThrough relationships are read-only. To create or modify related records, you must persist through the intermediate relationship (e.g., the User model in the Country-User-Post example).

```typescript
// Right: persist through the intermediate relationship
const user = await country.related('users').query().firstOrFail()
await user.related('posts').create({ title: 'Hello' })
```

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

### createView

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/schema_builder.md

Create a view from a callback that receives the view builder. Use the view builder's `as(query)` method to define the underlying query.

```APIDOC
## createView

Create a view from a callback that receives the view builder. Use the view builder's `as(query)` method to define the underlying query.

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

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

### Schema Class with Custom Primary Key

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/models/schema_classes.md

Example of a generated schema class where the primary key is explicitly defined using `@column({ isPrimary: true })` for a non-id column.

```typescript
// title: database/schema.ts (generated)
export class OauthStatesSchema extends BaseModel {
  static table = 'oauth_states'

  @column({ isPrimary: true })
  declare key: string

  @column()
  declare value: string

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  declare updatedAt: DateTime | null
}
```

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

### Run database seeders

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/guides/commands.md

Executes all seeder files in the database/seeders directory. Use --files to specify individual seeders or --interactive for a selection prompt.

```sh
node ace db:seed
```

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

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

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

### Define Github Link Slot in Header Component

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/README.md

Use the 'github' slot to define custom markup for the GitHub link in the header. This example includes the star count.

```edge
@component('docs::header', contentConfig)
  @slots('github')
    <span> Github (11K+ Stars) </span>
  @end
@end
```

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

### Configure Multiple Database Connections

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/introduction.md

Define separate migration directories for different database connections. This is useful when each database has distinct tables.

```typescript
// title: config/database.ts
{
  users: {
    client: 'pg',
    migrations: {
      paths: ['./database/users/migrations'],
    },
  },
  products: {
    client: 'pg',
    migrations: {
      paths: ['./database/products/migrations'],
    },
  },
}
```

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

### HasMany with onQuery Constraints

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/relationships/has_many.md

Apply default constraints to `hasMany` relationship queries using the `onQuery` option. This example ensures that only non-deleted posts are loaded and orders them by creation date.

```typescript
@hasMany(() => Post, {
  onQuery: (query) => query.whereNull('deleted_at').orderBy('created_at', 'desc'),
})
declare posts: HasMany<typeof Post>
```

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

### engine

Source: https://github.com/adonisjs/lucid.adonisjs.com/blob/22.x/content/docs/migrations/table_builder.md

Set the storage engine. MySQL only.

```APIDOC
## engine

### Description
Set the storage engine. MySQL only.

### Method
```ts
table.engine('InnoDB')
```
```