Database Layer

Overview

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

Supported Databases

PostgreSQL

Recommended for production

Version 12+
Best performance
Full feature support

MySQL / MariaDB

Production-ready alternative

MySQL 8.0+
MariaDB 10.4+
utf8mb4 charset required

SQLite

Development and small deployments

Version 3.35+
Single-file database
No separate server needed

MSSQL

Enterprise environments

SQL Server 2012+
Windows-native integration

XORM Basics

Database Engine

Access the database through the engine:

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

Insert
Select
Update
Delete

user := &User{
    Name:     "john",
    Email:    "[email protected]",
    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)

// 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)

// 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})

// 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{})

Model Definition

Basic Model

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

// 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:

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

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

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

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

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

Migration Best Practices

Test All Databases

Ensure migrations work on PostgreSQL, MySQL, SQLite, and MSSQL:

make test-sqlite
make test-mysql
make test-pgsql

Handle Data Migration

When changing schema, migrate existing data:

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
}

Use Transactions

Wrap multi-step migrations in transactions when possible

Database Utilities

Count Records

count, err := db.GetEngine(ctx).
    Where("is_active = ?", true).
    Count(new(User))

Check Existence

exists, err := db.GetEngine(ctx).
    Where("name = ?", "john").
    Exist(new(User))

Batch Operations

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

// 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:

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

Architecture

Overall architecture overview

Database Setup

Production database configuration

Testing

Writing database tests

Contributing

Architecture

Database Layer

Overview

Supported Databases

PostgreSQL

MySQL / MariaDB

SQLite

MSSQL

XORM Basics

Database Engine

Common Operations

Model Definition

Basic Model

XORM Tags

Indexes

Query Builders

Find Options Pattern

Transactions

Basic Transaction

Migrations

Migration Files

Creating a Migration

Register Migration

Migration Best Practices

Database Utilities

Count Records

Check Existence

Batch Operations

Performance Optimization

Eager Loading

Indexes

See Also

Architecture

Database Setup

Testing

Build docs developers (and LLMs) love

Contributing

Architecture

​Overview

​Supported Databases

PostgreSQL

MySQL / MariaDB

SQLite

MSSQL

​XORM Basics

​Database Engine

​Common Operations

​Model Definition

​Basic Model

​XORM Tags

​Indexes

​Query Builders

​Find Options Pattern

​Pagination

​Transactions

​Basic Transaction

​Migrations

​Migration Files

​Creating a Migration

​Register Migration

​Migration Best Practices

​Database Utilities

​Count Records

​Check Existence

​Batch Operations

​Performance Optimization

​Eager Loading

​Indexes

​See Also

Architecture

Database Setup

Testing

Build docs developers (and LLMs) love

Overview

Supported Databases

XORM Basics

Database Engine

Common Operations

Model Definition

Basic Model

XORM Tags

Indexes

Query Builders

Find Options Pattern

Pagination

Transactions

Basic Transaction

Migrations

Migration Files

Creating a Migration

Register Migration

Migration Best Practices

Database Utilities

Count Records

Check Existence

Batch Operations

Performance Optimization

Eager Loading

Indexes

See Also