> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/go-gitea/gitea/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Layer

> Database architecture, ORM usage, and migration system

## Overview

Gitea uses XORM as its ORM (Object-Relational Mapping) layer, providing database-agnostic access to PostgreSQL, MySQL, SQLite, and MSSQL.

## Supported Databases

<CardGroup cols={2}>
  <Card title="PostgreSQL" icon="database">
    **Recommended** for production

    * Version 12+
    * Best performance
    * Full feature support
  </Card>

  <Card title="MySQL / MariaDB" icon="database">
    Production-ready alternative

    * MySQL 8.0+
    * MariaDB 10.4+
    * utf8mb4 charset required
  </Card>

  <Card title="SQLite" icon="database">
    Development and small deployments

    * Version 3.35+
    * Single-file database
    * No separate server needed
  </Card>

  <Card title="MSSQL" icon="database">
    Enterprise environments

    * SQL Server 2012+
    * Windows-native integration
  </Card>
</CardGroup>

## XORM Basics

### Database Engine

Access the database through the engine:

```go theme={null}
import "code.gitea.io/gitea/models/db"

// Get database engine
engine := db.GetEngine(ctx)

// Execute queries
var users []User
err := engine.Where("is_active = ?", true).Find(&users)
```

### Common Operations

<Tabs>
  <Tab title="Insert">
    ```go theme={null}
    user := &User{
        Name:     "john",
        Email:    "john@example.com",
        IsActive: true,
    }

    // Insert single record
    _, err := db.GetEngine(ctx).Insert(user)
    // user.ID is now populated

    // Insert multiple records
    users := []*User{user1, user2, user3}
    _, err := db.GetEngine(ctx).Insert(&users)
    ```
  </Tab>

  <Tab title="Select">
    ```go theme={null}
    // Get single record by ID
    user := new(User)
    has, err := db.GetEngine(ctx).ID(123).Get(user)

    // Get with conditions
    has, err := db.GetEngine(ctx).
        Where("name = ?", "john").
        Get(user)

    // Get multiple records
    var users []User
    err := db.GetEngine(ctx).
        Where("is_active = ?", true).
        Limit(10).
        Find(&users)
    ```
  </Tab>

  <Tab title="Update">
    ```go theme={null}
    // Update specific fields
    _, err := db.GetEngine(ctx).
        ID(user.ID).
        Cols("name", "email").
        Update(&user)

    // Update all fields
    _, err := db.GetEngine(ctx).
        ID(user.ID).
        AllCols().
        Update(&user)

    // Update with conditions
    _, err := db.GetEngine(ctx).
        Where("created_unix < ?", oldTime).
        Update(&User{IsActive: false})
    ```
  </Tab>

  <Tab title="Delete">
    ```go theme={null}
    // Delete by ID
    _, err := db.GetEngine(ctx).ID(user.ID).Delete(&User{})

    // Delete with conditions
    _, err := db.GetEngine(ctx).
        Where("is_active = ? AND created_unix < ?", false, oldTime).
        Delete(&User{})
    ```
  </Tab>
</Tabs>

## Model Definition

### Basic Model

```go theme={null}
package user

import (
    "code.gitea.io/gitea/models/db"
    "code.gitea.io/gitea/modules/timeutil"
)

type User struct {
    ID          int64              `xorm:"pk autoincr"`
    Name        string             `xorm:"UNIQUE NOT NULL"`
    Email       string             `xorm:"UNIQUE NOT NULL"`
    Password    string             `xorm:"NOT NULL"`
    IsActive    bool               `xorm:"NOT NULL DEFAULT true"`
    IsAdmin     bool               `xorm:"NOT NULL DEFAULT false"
    CreatedUnix timeutil.TimeStamp `xorm:"created"`
    UpdatedUnix timeutil.TimeStamp `xorm:"updated"`
}

func init() {
    db.RegisterModel(new(User))
}
```

### XORM Tags

Common XORM struct tags:

| Tag        | Description         | Example               |
| ---------- | ------------------- | --------------------- |
| `pk`       | Primary key         | `xorm:"pk"`           |
| `autoincr` | Auto-increment      | `xorm:"autoincr"`     |
| `UNIQUE`   | Unique constraint   | `xorm:"UNIQUE"`       |
| `NOT NULL` | Not null constraint | `xorm:"NOT NULL"`     |
| `DEFAULT`  | Default value       | `xorm:"DEFAULT true"` |
| `INDEX`    | Create index        | `xorm:"INDEX"`        |
| `created`  | Auto-set on insert  | `xorm:"created"`      |
| `updated`  | Auto-set on update  | `xorm:"updated"`      |
| `deleted`  | Soft delete         | `xorm:"deleted"`      |
| `-`        | Ignore field        | `xorm:"-"`            |

### Indexes

```go theme={null}
// Single column index
type Repository struct {
    ID      int64  `xorm:"pk autoincr"`
    OwnerID int64  `xorm:"INDEX"`  // Creates index on owner_id
    Name    string `xorm:"INDEX"`  // Creates index on name
}

// Composite index (in TableIndices method)
func (repo *Repository) TableIndices() []*schemas.Index {
    return []*schemas.Index{
        schemas.NewIndex("owner_name", schemas.IndexType).
            AddColumn("owner_id", "name"),
    }
}
```

## Query Builders

### Find Options Pattern

Gitea uses a Find Options pattern for complex queries:

```go theme={null}
// Define options struct
type FindRepoOptions struct {
    db.ListOptions
    OwnerID  int64
    Private  bool
    IsFork   bool
    OrderBy  string
}

// ToDBOptions converts to XORM query
func (opts FindRepoOptions) ToDBOptions(ctx context.Context) db.FindOptions {
    return db.FindOptions{
        ListOptions: opts.ListOptions,
        OrderBy:     opts.OrderBy,
    }
}

// ToConds generates WHERE conditions
func (opts FindRepoOptions) ToConds() builder.Cond {
    cond := builder.NewCond()
    if opts.OwnerID > 0 {
        cond = cond.And(builder.Eq{"owner_id": opts.OwnerID})
    }
    if opts.Private {
        cond = cond.And(builder.Eq{"is_private": true})
    }
    return cond
}

// Usage
repos, err := db.Find[Repository](ctx, opts)
```

### Pagination

```go theme={null}
// List options for pagination
type ListOptions struct {
    Page     int  // 1-indexed
    PageSize int  // Items per page
}

// Apply pagination
func (opts ListOptions) GetSkipTake() (skip, take int) {
    opts.SetDefaultValues()
    return (opts.Page - 1) * opts.PageSize, opts.PageSize
}

// Usage
opts := db.ListOptions{Page: 2, PageSize: 20}
repos, err := db.Find[Repository](ctx, FindRepoOptions{
    ListOptions: opts,
    OwnerID:     userID,
})
```

## Transactions

### Basic Transaction

```go theme={null}
import "code.gitea.io/gitea/models/db"

func TransferRepository(ctx context.Context, repoID, newOwnerID int64) error {
    return db.WithTx(ctx, func(ctx context.Context) error {
        // Get repository
        repo, err := GetRepositoryByID(ctx, repoID)
        if err != nil {
            return err
        }
        
        // Update owner
        repo.OwnerID = newOwnerID
        if err := UpdateRepository(ctx, repo); err != nil {
            return err
        }
        
        // Update collaborators
        if err := UpdateCollaborators(ctx, repoID, newOwnerID); err != nil {
            return err
        }
        
        // All operations succeed or rollback
        return nil
    })
}
```

## Migrations

### Migration Files

Migrations are located in `models/migrations/`:

```
models/migrations/
├── v1_22/
│   ├── v289.go  # Migration #289
│   └── v290.go  # Migration #290
└── migrate.go   # Migration registry
```

### Creating a Migration

```go theme={null}
// models/migrations/v1_22/v291.go
package v1_22

import (
    "xorm.io/xorm"
)

func AddUserBioField(x *xorm.Engine) error {
    type User struct {
        Bio string `xorm:"TEXT"`
    }
    return x.Sync2(new(User))
}
```

### Register Migration

```go theme={null}
// models/migrations/migrations.go
func init() {
    NewMigration("Add user bio field", v1_22.AddUserBioField),
}
```

### Migration Best Practices

<AccordionGroup>
  <Accordion title="Test All Databases">
    Ensure migrations work on PostgreSQL, MySQL, SQLite, and MSSQL:

    ```bash theme={null}
    make test-sqlite
    make test-mysql
    make test-pgsql
    ```
  </Accordion>

  <Accordion title="Handle Data Migration">
    When changing schema, migrate existing data:

    ```go theme={null}
    func MigrateOldData(x *xorm.Engine) error {
        // 1. Add new column
        type Table struct {
            NewColumn string
        }
        if err := x.Sync2(new(Table)); err != nil {
            return err
        }
        
        // 2. Migrate data from old column
        _, err := x.Exec("UPDATE table SET new_column = old_column")
        return err
    }
    ```
  </Accordion>

  <Accordion title="Use Transactions">
    Wrap multi-step migrations in transactions when possible
  </Accordion>
</AccordionGroup>

## Database Utilities

### Count Records

```go theme={null}
count, err := db.GetEngine(ctx).
    Where("is_active = ?", true).
    Count(new(User))
```

### Check Existence

```go theme={null}
exists, err := db.GetEngine(ctx).
    Where("name = ?", "john").
    Exist(new(User))
```

### Batch Operations

```go theme={null}
// Batch insert
users := make([]*User, 100)
if _, err := db.GetEngine(ctx).Insert(&users); err != nil {
    return err
}

// Batch update
_, err := db.GetEngine(ctx).
    In("id", userIDs).
    Update(&User{IsActive: true})
```

## Performance Optimization

### Eager Loading

```go theme={null}
// Instead of N+1 queries
for _, issue := range issues {
    issue.Repo, _ = GetRepositoryByID(ctx, issue.RepoID)  // N queries
}

// Use bulk loading
repoIDs := make([]int64, len(issues))
for i, issue := range issues {
    repoIDs[i] = issue.RepoID
}
repos, _ := GetRepositoriesByIDs(ctx, repoIDs)  // 1 query
```

### Indexes

Add indexes for frequently queried columns:

```go theme={null}
type Issue struct {
    RepoID int64 `xorm:"INDEX"`  // Frequently filtered
    IsPull bool  `xorm:"INDEX"`  // Frequently filtered
}
```

## See Also

<CardGroup cols={2}>
  <Card title="Architecture" href="/development/architecture">
    Overall architecture overview
  </Card>

  <Card title="Database Setup" href="/admin/database">
    Production database configuration
  </Card>

  <Card title="Testing" href="/development/testing">
    Writing database tests
  </Card>
</CardGroup>