Nextcloud Syntax Database

nextcloud-syntax-database

Quick Reference

Column Types (OCP\DB\Types)

| Constant | SQL Type | Notes | |----------|----------|-------| | Types::BIGINT | BIGINT | ALWAYS use for id columns | | Types::INTEGER | INT | Standard integer | | Types::FLOAT | FLOAT | Floating point | | Types::BOOLEAN | BOOLEAN | Oracle: CANNOT be NOT NULL | | Types::STRING | VARCHAR | Max 4,000 chars (Oracle limit) | | Types::TEXT | TEXT/CLOB | Large text | | Types::BLOB | BLOB | Binary data | | Types::JSON | JSON/TEXT | JSON data | | Types::DATE | DATE | Date only | | Types::TIME | TIME | Time only | | Types::DATETIME | DATETIME | Date + time | | Types::DATETIME_TZ | DATETIMETZ | Date + time + timezone |

Database Constraints

| Constraint | Limit | Reason | |------------|-------|--------| | Table name | Max 23 characters | oc_ prefix + Oracle 30-char limit | | Column name | Max 30 characters | Oracle identifier limit | | Index name | Max 30 characters | Oracle identifier limit | | FK name | Max 30 characters | Oracle identifier limit | | String column | Max 4,000 characters | Oracle VARCHAR2 limit | | Primary key | REQUIRED on every table | Galera Cluster requirement | | Boolean NOT NULL | NOT allowed | Oracle does not support it | | NOT NULL string with empty default | NOT allowed | Oracle treats empty string as NULL |

Entity Type Mapping

| Property Type | addType() Value | Column Type | |---------------|-------------------|-------------| | ?int | 'integer' | INTEGER/BIGINT | | ?float | 'float' | FLOAT | | ?bool | 'boolean' | BOOLEAN | | ?string | (default) | STRING/TEXT | | ?\DateTime | 'datetime' | DATETIME | | ?array | 'json' | JSON |

QBMapper Methods

| Method | Returns | Throws | |--------|---------|--------| | findEntity($qb) | Single Entity | DoesNotExistException, MultipleObjectsReturnedException | | findEntities($qb) | Entity array | -- | | insert($entity) | Inserted Entity | -- | | update($entity) | Updated Entity | -- | | delete($entity) | Deleted Entity | -- | | insertOrUpdate($entity) | Entity | -- |

Migration Naming Convention

Format: Version{MajorMinor}Date{YYYYMMDDHHmmss}

| App Version | Migration Prefix | |-------------|-----------------| | 1.0.x | Version1000 | | 2.4.x | Version2004 | | 24.0.x | Version24000 |

Critical Warnings

NEVER modify an existing migration file -- create a new migration class instead. Nextcloud tracks which migrations have already executed and will NOT re-run modified migrations.

NEVER use raw SQL queries -- ALWAYS use the query builder for cross-database portability (MySQL, PostgreSQL, SQLite, Oracle).

NEVER create tables without a primary key -- Galera Cluster replication will fail silently.

NEVER exceed 23 characters for table names -- the oc_ prefix plus Oracle's 30-character limit will cause creation failures.

NEVER forget to close cursors on select queries -- call $result->closeCursor() after processing. The findEntity() and findEntities() methods handle this automatically.

NEVER use NOT NULL on boolean columns if Oracle support is required.

NEVER use NOT NULL with empty string defaults on string columns -- Oracle treats empty string as NULL, causing constraint violations.

ALWAYS include an auto-incremented id BIGINT column on every table.

ALWAYS use $qb->createNamedParameter() for all query values -- never concatenate user input into queries.

ALWAYS use the TTransactional trait for multi-step operations that must be atomic.

ALWAYS pass the table name without the oc_ prefix to QBMapper and query builder -- the prefix is added automatically.

---

Decision Tree: Choosing a Database Pattern

Need to store data?
├── Simple CRUD with single table?
│   └── Use Entity + QBMapper pattern
│       ├── Define Entity class (extends Entity)
│       ├── Define Mapper class (extends QBMapper)
│       └── Create migration for table schema
├── Complex queries with joins?
│   └── Use query builder directly via IDBConnection
│       ├── $db->getQueryBuilder() for building queries
│       └── Use expressions for WHERE clauses
├── Multiple operations that must succeed/fail together?
│   └── Use TTransactional trait
│       └── Wrap operations in $this->atomic(fn, $db)
└── Schema changes needed?
    ├── New table or column?
    │   └── Create new migration class in lib/Migration/
    ├── Data transformation?
    │   └── Use postSchemaChange() in migration
    └── Add index to large existing table?
        └── Use AddMissingIndicesEvent listener (non-blocking)

---

Essential Patterns

Pattern 1: Complete Migration (Create Table)

hasTable('myapp_items')) {
            $table = $schema->createTable('myapp_items');
            $table->addColumn('id', Types::BIGINT, [
                'autoincrement' => true,
                'notnull' => true,
            ]);
            $table->addColumn('user_id', Types::STRING, [
                'notnull' => true,
                'length' => 64,
            ]);
            $table->addColumn('title', Types::STRING, [
                'notnull' => true,
                'length' => 255,
            ]);
            $table->addColumn('content', Types::TEXT, [
                'notnull' => false,
                'default' => null,
            ]);
            $table->addColumn('created_at', Types::DATETIME, [
                'notnull' => true,
            ]);
            $table->setPrimaryKey(['id']);
            $table->addIndex(['user_id'], 'myapp_items_uid_idx');
        }

        return $schema;
    }
}

Pattern 2: Entity Definition

addType('category', 'integer');
        $this->addType('archived', 'boolean');
        $this->addType('createdAt', 'datetime');
    }
}

Property $userId auto-generates getUserId() / setUserId() and maps to column user_id.

Pattern 3: QBMapper with CRUD

db->getQueryBuilder();
        $qb->select('*')
            ->from($this->getTableName())
            ->where($qb->expr()->eq('id', $qb->createNamedParameter($id)))
            ->andWhere($qb->expr()->eq('user_id', $qb->createNamedParameter($userId)));
        return $this->findEntity($qb);
    }

    /**
     * @return Item[]
     */
    public function findAll(string $userId): array {
        $qb = $this->db->getQueryBuilder();
        $qb->select('*')
            ->from($this->getTableName())
            ->where($qb->expr()->eq('user_id', $qb->createNamedParameter($userId)));
        return $this->findEntities($qb);
    }
}

Pattern 4: Query Builder with Joins

$qb = $this->db->getQueryBuilder();
$qb->select('i.id', 'i.title', 'c.name AS category_name')
    ->from('myapp_items', 'i')
    ->join('i', 'myapp_categories', 'c',
        $qb->expr()->eq('i.category_id', 'c.id'))
    ->where($qb->expr()->eq('i.user_id', $qb->createNamedParameter($userId)))
    ->andWhere($qb->expr()->gte('i.created_at', $qb->createNamedParameter($since, IQueryBuilder::PARAM_DATE)))
    ->orderBy('i.created_at', 'DESC')
    ->setMaxResults(50);

$result = $qb->executeQuery();
$rows = $result->fetchAll();
$result->closeCursor();

Pattern 5: TTransactional for Atomic Operations

atomic(function () use ($fromUser, $toUser) {
            $items = $this->mapper->findAll($fromUser);
            foreach ($items as $item) {
                $item->setUserId($toUser);
                $this->mapper->update($item);
            }
        }, $this->db);
    }
}

Pattern 6: AddMissingIndicesEvent (Non-Blocking Index Creation)

addMissingIndex(
            'myapp_items',
            'myapp_items_title_idx',
            ['title']
        );
    }
}

Register in Application::register():

$context->registerEventListener(
    AddMissingIndicesEvent::class,
    AddMissingIndicesListener::class
);

---

Reference Links

• [references/methods.md](references/methods.md) -- Migration API, Entity API, QBMapper API, QueryBuilder API
• [references/examples.md](references/examples.md) -- Migration, entity, mapper, query builder patterns
• [references/anti-patterns.md](references/anti-patterns.md) -- Database mistakes, Oracle/Galera issues

Official Sources

• https://docs.nextcloud.com/server/latest/developermanual/diggingdeeper/database.html
• https://docs.nextcloud.com/server/latest/developer_manual/basics/storage/database.html
• https://docs.nextcloud.com/server/latest/developermanual/diggingdeeper/classloader.html

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: Impertio-Studio
• Source: Impertio-Studio/Nextcloud-Claude-Skill-Package
• License: MIT

Install and usage instructions live in the source repository linked above.