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

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

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

Select

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

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

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

See Also

Architecture

Overall architecture overview

Database Setup

Production database configuration

Testing

Writing database tests