@mikro-orm/sql - npm Package Compare versions

Comparing version 7.0.0-dev.231 to 7.0.0-dev.232

dialects/mysql/MySqlSchemaHelper.d.ts

@@ -24,2 +24,11 @@ import { type Dictionary, type Type } from '@mikro-orm/core';
     getCreateIndexSQL(tableName: string, index: IndexDef, partialExpression?: boolean): string;
+    /**
+     * Build the column list for a MySQL index, with MySQL-specific handling for collation.
+     * MySQL requires collation to be specified as an expression: (column_name COLLATE collation_name)
+     */
+    protected getIndexColumns(index: IndexDef): string;
+    /**
+     * Append MySQL-specific index suffixes like INVISIBLE.
+     */
+    protected appendMySqlIndexSuffix(sql: string, index: IndexDef): string;
     getAllColumns(connection: AbstractSqlConnection, tables: Table[]): Promise<Dictionary<Column[]>>;
@@ -26,0 +35,0 @@ getAllChecks(connection: AbstractSqlConnection, tables: Table[]): Promise<Dictionary<CheckDef[]>>;

dialects/mysql/MySqlSchemaHelper.js

@@ -75,3 +75,3 @@ import { EnumType, StringType, TextType } from '@mikro-orm/core';
     async getAllIndexes(connection, tables) {
-        const sql = `select table_name as table_name, nullif(table_schema, schema()) as schema_name, index_name as index_name, non_unique as non_unique, column_name as column_name /*!80013 , expression as expression */
+        const sql = `select table_name as table_name, nullif(table_schema, schema()) as schema_name, index_name as index_name, non_unique as non_unique, column_name as column_name, index_type as index_type, sub_part as sub_part, collation as sort_order /*!80013 , expression as expression, is_visible as is_visible */
         from information_schema.statistics where table_schema = database()
@@ -91,2 +91,22 @@ and table_name in (${tables.map(t => this.platform.quoteValue(t.table_name)).join(', ')})
             };
+            // Capture column options (prefix length, sort order)
+            if (index.sub_part != null || index.sort_order === 'D') {
+                indexDef.columns = [{
+                        name: index.column_name,
+                        ...(index.sub_part != null && { length: index.sub_part }),
+                        ...(index.sort_order === 'D' && { sort: 'DESC' }),
+                    }];
+            }
+            // Capture index type for fulltext and spatial indexes
+            if (index.index_type === 'FULLTEXT') {
+                indexDef.type = 'fulltext';
+            }
+            else if (index.index_type === 'SPATIAL') {
+                /* v8 ignore next */
+                indexDef.type = 'spatial';
+            }
+            // Capture invisible flag (MySQL 8.0.13+)
+            if (index.is_visible === 'NO') {
+                indexDef.invisible = true;
+            }
             if (!index.column_name || index.expression?.match(/ where /i)) {
@@ -111,5 +131,6 @@ indexDef.expression = index.expression; // required for the `getCreateIndexSQL()` call
         const keyName = this.quote(index.keyName);
-        const sql = `alter table ${tableName} add ${index.unique ? 'unique' : 'index'} ${keyName} `;
+        let sql = `alter table ${tableName} add ${index.unique ? 'unique' : 'index'} ${keyName} `;
         if (index.expression && partialExpression) {
-            return `${sql}(${index.expression})`;
+            sql += `(${index.expression})`;
+            return this.appendMySqlIndexSuffix(sql, index);
         }
@@ -119,7 +140,55 @@ // JSON columns can have unique index but not unique constraint, and we need to distinguish those, so we can properly drop them
             const columns = this.platform.getJsonIndexDefinition(index);
-            const sql = `alter table ${tableName} add ${index.unique ? 'unique ' : ''}index ${keyName} `;
-            return `${sql}(${columns.join(', ')})`;
+            sql = `alter table ${tableName} add ${index.unique ? 'unique ' : ''}index ${keyName} `;
+            sql += `(${columns.join(', ')})`;
+            return this.appendMySqlIndexSuffix(sql, index);
         }
-        return `${sql}(${index.columnNames.map(c => this.quote(c)).join(', ')})`;
+        // Build column list with advanced options
+        const columns = this.getIndexColumns(index);
+        sql += `(${columns})`;
+        return this.appendMySqlIndexSuffix(sql, index);
     }
+    /**
+     * Build the column list for a MySQL index, with MySQL-specific handling for collation.
+     * MySQL requires collation to be specified as an expression: (column_name COLLATE collation_name)
+     */
+    getIndexColumns(index) {
+        if (index.columns?.length) {
+            return index.columns.map(col => {
+                const quotedName = this.quote(col.name);
+                // MySQL supports collation via expression: (column_name COLLATE collation_name)
+                // When collation is specified, wrap in parentheses as an expression
+                if (col.collation) {
+                    let expr = col.length ? `${quotedName}(${col.length})` : quotedName;
+                    expr = `(${expr} collate ${col.collation})`;
+                    // Sort order comes after the expression
+                    if (col.sort) {
+                        expr += ` ${col.sort}`;
+                    }
+                    return expr;
+                }
+                // Standard column definition without collation
+                let colDef = quotedName;
+                // MySQL supports prefix length
+                if (col.length) {
+                    colDef += `(${col.length})`;
+                }
+                // MySQL supports sort order
+                if (col.sort) {
+                    colDef += ` ${col.sort}`;
+                }
+                return colDef;
+            }).join(', ');
+        }
+        return index.columnNames.map(c => this.quote(c)).join(', ');
+    }
+    /**
+     * Append MySQL-specific index suffixes like INVISIBLE.
+     */
+    appendMySqlIndexSuffix(sql, index) {
+        // MySQL 8.0+ supports INVISIBLE indexes
+        if (index.invisible) {
+            sql += ' invisible';
+        }
+        return sql;
+    }
     async getAllColumns(connection, tables) {
@@ -126,0 +195,0 @@ const sql = `select table_name as table_name,

dialects/postgresql/PostgreSqlSchemaHelper.d.ts

@@ -32,2 +32,16 @@ import { type Dictionary } from '@mikro-orm/core';
     getAllIndexes(connection: AbstractSqlConnection, tables: Table[]): Promise<Dictionary<IndexDef[]>>;
+    /**
+     * Parses column definitions from the full CREATE INDEX expression.
+     * Since pg_get_indexdef(oid, col_num, true) doesn't include sort modifiers,
+     * we extract them from the full expression instead.
+     *
+     * We use columnDefs (from individual pg_get_indexdef calls) as the source
+     * of column names, and find their modifiers in the expression.
+     */
+    private parseIndexColumnsFromExpression;
+    /**
+     * Extracts the content inside parentheses starting at the given position.
+     * Handles nested parentheses correctly.
+     */
+    private extractParenthesizedContent;
     getAllColumns(connection: AbstractSqlConnection, tablesBySchemas: Map<string | undefined, Table[]>, nativeEnums?: Dictionary<{
@@ -66,2 +80,10 @@ name: string;
     dropIndex(table: string, index: IndexDef, oldIndexName?: string): string;
+    /**
+     * Build the column list for a PostgreSQL index.
+     */
+    protected getIndexColumns(index: IndexDef): string;
+    /**
+     * PostgreSQL-specific index options like fill factor.
+     */
+    protected getCreateIndexSuffix(index: IndexDef): string;
     private getIndexesSQL;
@@ -68,0 +90,0 @@ private getChecksSQL;

dialects/postgresql/PostgreSqlSchemaHelper.js

@@ -131,5 +131,17 @@ import { DeferMode, EnumType, Type, Utils } from '@mikro-orm/core';
             const key = this.getTableKey(index);
+            // Extract INCLUDE columns from expression first, to filter them from key columns
+            const includeMatch = index.expression?.match(/include\s*\(([^)]+)\)/i);
+            const includeColumns = includeMatch
+                ? includeMatch[1].split(',').map((col) => unquote(col.trim()))
+                : [];
+            // Filter out INCLUDE columns from the column definitions to get only key columns
+            const keyColumnDefs = index.index_def.filter((col) => !includeColumns.includes(unquote(col)));
+            // Parse sort order and NULLS ordering from the full expression
+            // pg_get_indexdef individual columns don't include sort modifiers, so we parse from full expression
+            const columns = this.parseIndexColumnsFromExpression(index.expression, keyColumnDefs, unquote);
+            const columnNames = columns.map(col => col.name);
+            const hasAdvancedColumnOptions = columns.some(col => col.sort || col.nulls || col.collation);
             const indexDef = {
-                columnNames: index.index_def.map((name) => unquote(name)),
-                composite: index.index_def.length > 1,
+                columnNames,
+                composite: columnNames.length > 1,
                 // JSON columns can have unique index but not unique constraint, and we need to distinguish those, so we can properly drop them
@@ -141,2 +153,6 @@ constraint: index.contype === 'u',
             };
+            // Add columns array if there are advanced options
+            if (hasAdvancedColumnOptions) {
+                indexDef.columns = columns;
+            }
             if (index.condeferrable) {
@@ -151,2 +167,17 @@ indexDef.deferMode = index.condeferred ? DeferMode.INITIALLY_DEFERRED : DeferMode.INITIALLY_IMMEDIATE;
             }
+            // Extract fillFactor from reloptions
+            if (index.reloptions) {
+                const fillFactorMatch = index.reloptions.find((opt) => opt.startsWith('fillfactor='));
+                if (fillFactorMatch) {
+                    indexDef.fillFactor = parseInt(fillFactorMatch.split('=')[1], 10);
+                }
+            }
+            // Add INCLUDE columns (already extracted above)
+            if (includeColumns.length > 0) {
+                indexDef.include = includeColumns;
+            }
+            // Add index type if not btree (the default)
+            if (index.index_type && index.index_type !== 'btree') {
+                indexDef.type = index.index_type;
+            }
             ret[key] ??= [];
@@ -157,2 +188,67 @@ ret[key].push(indexDef);
     }
+    /**
+     * Parses column definitions from the full CREATE INDEX expression.
+     * Since pg_get_indexdef(oid, col_num, true) doesn't include sort modifiers,
+     * we extract them from the full expression instead.
+     *
+     * We use columnDefs (from individual pg_get_indexdef calls) as the source
+     * of column names, and find their modifiers in the expression.
+     */
+    parseIndexColumnsFromExpression(expression, columnDefs, unquote) {
+        // Extract just the column list from the expression (between first parens after USING)
+        // Pattern: ... USING method (...columns...) [INCLUDE (...)] [WHERE ...]
+        // Note: pg_get_indexdef always returns a valid expression with USING clause
+        const usingMatch = expression.match(/using\s+\w+\s*\(/i);
+        const startIdx = usingMatch.index + usingMatch[0].length - 1; // Position of opening (
+        const columnsStr = this.extractParenthesizedContent(expression, startIdx);
+        // Use the column names from columnDefs and find their modifiers in the expression
+        return columnDefs.map(colDef => {
+            const name = unquote(colDef);
+            const result = { name };
+            // Find this column in the expression and extract modifiers
+            // Create a pattern that matches the column name (quoted or unquoted) followed by modifiers
+            const escapedName = name.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
+            const colPattern = new RegExp(`"?${escapedName}"?\\s*([^,)]*?)(?:,|$)`, 'i');
+            const colMatch = columnsStr.match(colPattern);
+            if (colMatch) {
+                const modifiers = colMatch[1];
+                // Extract sort order (PostgreSQL omits ASC in output as it's the default)
+                if (/\bdesc\b/i.test(modifiers)) {
+                    result.sort = 'DESC';
+                }
+                // Extract NULLS ordering
+                const nullsMatch = modifiers.match(/nulls\s+(first|last)/i);
+                if (nullsMatch) {
+                    result.nulls = nullsMatch[1].toUpperCase();
+                }
+                // Extract collation
+                const collateMatch = modifiers.match(/collate\s+"?([^"\s,)]+)"?/i);
+                if (collateMatch) {
+                    result.collation = collateMatch[1];
+                }
+            }
+            return result;
+        });
+    }
+    /**
+     * Extracts the content inside parentheses starting at the given position.
+     * Handles nested parentheses correctly.
+     */
+    extractParenthesizedContent(str, startIdx) {
+        let depth = 0;
+        const start = startIdx + 1;
+        for (let i = startIdx; i < str.length; i++) {
+            if (str[i] === '(') {
+                depth++;
+            }
+            else if (str[i] === ')') {
+                depth--;
+                if (depth === 0) {
+                    return str.slice(start, i);
+                }
+            }
+        }
+        /* v8 ignore next - pg_get_indexdef always returns balanced parentheses */
+        return '';
+    }
     async getAllColumns(connection, tablesBySchemas, nativeEnums) {
@@ -493,3 +589,3 @@ const sql = `select table_schema as schema_name, table_name, column_name,
         const value = to.comment ? this.platform.quoteValue(to.comment) : 'null';
-        return `comment on column ${name}."${to.name}" is ${value}`;
+        return `comment on column ${name}.${this.quote(to.name)} is ${value}`;
     }
@@ -530,3 +626,3 @@ alterTableComment(table, comment) {
     getDatabaseNotExistsError(dbName) {
-        return `database "${dbName}" does not exist`;
+        return `database ${this.quote(dbName)} does not exist`;
     }
@@ -553,2 +649,39 @@ getManagementDbName() {
     }
+    /**
+     * Build the column list for a PostgreSQL index.
+     */
+    getIndexColumns(index) {
+        if (index.columns?.length) {
+            return index.columns.map(col => {
+                let colDef = this.quote(col.name);
+                // PostgreSQL supports collation with double quotes
+                if (col.collation) {
+                    colDef += ` collate ${this.quote(col.collation)}`;
+                }
+                // PostgreSQL supports sort order
+                if (col.sort) {
+                    colDef += ` ${col.sort}`;
+                }
+                // PostgreSQL supports NULLS FIRST/LAST
+                if (col.nulls) {
+                    colDef += ` nulls ${col.nulls}`;
+                }
+                return colDef;
+            }).join(', ');
+        }
+        return index.columnNames.map(c => this.quote(c)).join(', ');
+    }
+    /**
+     * PostgreSQL-specific index options like fill factor.
+     */
+    getCreateIndexSuffix(index) {
+        const withOptions = [];
+        if (index.fillFactor != null) {
+            withOptions.push(`fillfactor = ${index.fillFactor}`);
+        }
+        if (withOptions.length > 0) {
+            return ` with (${withOptions.join(', ')})`;
+        }
+        return super.getCreateIndexSuffix(index);
+    }
     getIndexesSQL(tables) {
@@ -563,6 +696,9 @@ return `select indrelid::regclass as table_name, ns.nspname as schema_name, relname as constraint_name, idx.indisunique as unique, idx.indisprimary as primary, contype, condeferrable, condeferred,
       c.condeferrable as deferrable,
-      c.condeferred as initially_deferred
+      c.condeferred as initially_deferred,
+      i.reloptions,
+      am.amname as index_type
       from pg_index idx
       join pg_class as i on i.oid = idx.indexrelid
       join pg_namespace as ns on i.relnamespace = ns.oid
+      join pg_am as am on am.oid = i.relam
       left join pg_constraint as c on c.conname = i.relname
@@ -569,0 +705,0 @@ where indrelid in (${tables.map(t => `${this.platform.quoteValue(`${this.quote(t.schema_name)}.${this.quote(t.table_name)}`)}::regclass`).join(', ')})

dialects/sqlite/SqliteSchemaHelper.js

@@ -214,3 +214,4 @@ import { Utils } from '@mikro-orm/core';
         }
-        return `${sqlPrefix} (${index.columnNames.map(c => this.quote(c)).join(', ')})`;
+        // Use getIndexColumns to support advanced options like sort order and collation
+        return `${sqlPrefix} (${this.getIndexColumns(index)})`;
     }
@@ -217,0 +218,0 @@ parseTableDefinition(sql, cols) {

package.json

 {
   "name": "@mikro-orm/sql",
-  "version": "7.0.0-dev.231",
+  "version": "7.0.0-dev.232",
   "description": "TypeScript ORM for Node.js based on Data Mapper, Unit of Work and Identity Map patterns. Supports MongoDB, MySQL, PostgreSQL and SQLite databases as well as usage with vanilla JavaScript.",
@@ -59,4 +59,4 @@ "type": "module",
   "peerDependencies": {
-    "@mikro-orm/core": "7.0.0-dev.231"
+    "@mikro-orm/core": "7.0.0-dev.232"
   }
 }

schema/DatabaseTable.d.ts

@@ -65,2 +65,14 @@ import { type Configuration, type DeferMode, type Dictionary, type EntityMetadata, type EntityProperty, type IndexCallback, type NamingStrategy } from '@mikro-orm/core';
         options?: Dictionary;
+        columns?: {
+            name: string;
+            sort?: 'ASC' | 'DESC' | 'asc' | 'desc';
+            nulls?: 'FIRST' | 'LAST' | 'first' | 'last';
+            length?: number;
+            collation?: string;
+        }[];
+        include?: string | string[];
+        fillFactor?: number;
+        invisible?: boolean;
+        disabled?: boolean;
+        clustered?: boolean;
     }, type: 'index' | 'unique' | 'primary'): void;
@@ -67,0 +79,0 @@ addCheck(check: CheckDef): void;

schema/DatabaseTable.js

@@ -170,3 +170,6 @@ import { DecimalType, EntitySchema, RawQueryFragment, ReferenceKind, t, Type, UnknownType, Utils, } from '@mikro-orm/core';
             && !(index.columnNames.some(col => !col) && !index.expression));
+        // Helper to map column name to property name
+        const columnToPropertyName = (colName) => this.getPropertyName(namingStrategy, colName);
         for (const index of potentiallyUnmappedIndexes) {
+            // Build the index/unique options object with advanced options
             const ret = {
@@ -176,4 +179,28 @@ name: index.keyName,
                 expression: index.expression,
+                // Advanced index options - convert column names to property names
+                columns: index.columns?.map(col => ({
+                    ...col,
+                    name: columnToPropertyName(col.name),
+                })),
+                include: index.include?.map(colName => columnToPropertyName(colName)),
+                fillFactor: index.fillFactor,
+                disabled: index.disabled,
             };
-            const isTrivial = !index.deferMode && !index.expression;
+            // Index-only options (not valid for Unique)
+            if (!index.unique) {
+                if (index.type) {
+                    // Convert index type - IndexDef.type can be string or object, IndexOptions.type is just string
+                    ret.type = typeof index.type === 'string' ? index.type : index.type.indexType;
+                }
+                if (index.invisible) {
+                    ret.invisible = index.invisible;
+                }
+                if (index.clustered) {
+                    ret.clustered = index.clustered;
+                }
+            }
+            // An index is trivial if it has no special options that require entity-level declaration
+            const hasAdvancedOptions = index.columns?.length || index.include?.length || index.fillFactor ||
+                index.type || index.invisible || index.disabled || index.clustered;
+            const isTrivial = !index.deferMode && !index.expression && !hasAdvancedOptions;
             if (isTrivial) {
@@ -729,2 +756,6 @@ // Index is for FK. Map to the FK prop and move on.
     addIndex(meta, index, type) {
+        // If columns are specified but properties are not, derive properties from column names
+        if (index.columns?.length && !index.expression && (!index.properties || Utils.asArray(index.properties).length === 0)) {
+            index = { ...index, properties: index.columns.map(c => c.name) };
+        }
         const properties = Utils.unique(Utils.flatten(Utils.asArray(index.properties).map(prop => {
@@ -761,2 +792,33 @@ const parts = prop.split('.');
         const name = this.getIndexName(index.name, properties, type);
+        // Process include columns (map property names to field names)
+        const includeColumns = index.include ? Utils.unique(Utils.flatten(Utils.asArray(index.include).map(prop => {
+            if (meta.properties[prop]) {
+                return meta.properties[prop].fieldNames;
+            }
+            /* v8 ignore next */
+            return [prop];
+        }))) : undefined;
+        // Process columns with advanced options (map property names to field names)
+        const columns = index.columns?.map(col => {
+            const fieldName = meta.properties[col.name]?.fieldNames[0] ?? col.name;
+            return {
+                name: fieldName,
+                sort: col.sort?.toUpperCase(),
+                nulls: col.nulls?.toUpperCase(),
+                length: col.length,
+                collation: col.collation,
+            };
+        });
+        // Validate that column options reference fields in the index properties
+        if (columns?.length && properties.length > 0) {
+            for (const col of columns) {
+                if (!properties.includes(col.name)) {
+                    throw new Error(`Index '${name}' on entity '${meta.className}': column option references field '${col.name}' which is not in the index properties`);
+                }
+            }
+        }
+        // Validate fillFactor range
+        if (index.fillFactor != null && (index.fillFactor < 0 || index.fillFactor > 100)) {
+            throw new Error(`fillFactor must be between 0 and 100, got ${index.fillFactor} for index '${name}' on entity '${meta.className}'`);
+        }
         this.indexes.push({
@@ -774,2 +836,8 @@ keyName: name,
             deferMode: index.deferMode,
+            columns,
+            include: includeColumns,
+            fillFactor: index.fillFactor,
+            invisible: index.invisible,
+            disabled: index.disabled,
+            clustered: index.clustered,
         });
@@ -776,0 +844,0 @@ }

schema/SchemaComparator.d.ts

@@ -53,2 +53,10 @@ import { type Dictionary } from '@mikro-orm/core';
     isIndexFulfilledBy(index1: IndexDef, index2: IndexDef): boolean;
+    /**
+     * Compare advanced column options between two indexes.
+     */
+    private compareIndexColumns;
+    /**
+     * Compare two arrays for equality (order matters).
+     */
+    private compareArrays;
     diffExpression(expr1: string, expr2: string): boolean;
@@ -55,0 +63,0 @@ parseJsonDefault(defaultValue?: string | null): Dictionary | string | null;

schema/SchemaComparator.js

@@ -520,2 +520,26 @@ import { ArrayType, BooleanType, DateTimeType, inspect, JsonType, parseJsonSafe, Utils, } from '@mikro-orm/core';
         }
+        // Compare advanced column options (sort order, nulls, length, collation)
+        if (!this.compareIndexColumns(index1, index2)) {
+            return false;
+        }
+        // Compare INCLUDE columns for covering indexes
+        if (!this.compareArrays(index1.include, index2.include)) {
+            return false;
+        }
+        // Compare fill factor
+        if (index1.fillFactor !== index2.fillFactor) {
+            return false;
+        }
+        // Compare invisible flag
+        if (!!index1.invisible !== !!index2.invisible) {
+            return false;
+        }
+        // Compare disabled flag
+        if (!!index1.disabled !== !!index2.disabled) {
+            return false;
+        }
+        // Compare clustered flag
+        if (!!index1.clustered !== !!index2.clustered) {
+            return false;
+        }
         if (!index1.unique && !index1.primary) {
@@ -533,2 +557,54 @@ // this is a special case: If the current key is neither primary or unique, any unique or
     }
+    /**
+     * Compare advanced column options between two indexes.
+     */
+    compareIndexColumns(index1, index2) {
+        const cols1 = index1.columns ?? [];
+        const cols2 = index2.columns ?? [];
+        // If neither has column options, they match
+        if (cols1.length === 0 && cols2.length === 0) {
+            return true;
+        }
+        // If only one has column options, they don't match
+        if (cols1.length !== cols2.length) {
+            return false;
+        }
+        // Compare each column's options
+        // Note: We don't check c1.name !== c2.name because the indexes already have matching columnNames
+        // and the columns array is derived from those same column names
+        for (let i = 0; i < cols1.length; i++) {
+            const c1 = cols1[i];
+            const c2 = cols2[i];
+            const sort1 = c1.sort?.toUpperCase() ?? 'ASC';
+            const sort2 = c2.sort?.toUpperCase() ?? 'ASC';
+            if (sort1 !== sort2) {
+                return false;
+            }
+            const defaultNulls = (s) => s === 'DESC' ? 'FIRST' : 'LAST';
+            const nulls1 = c1.nulls?.toUpperCase() ?? defaultNulls(sort1);
+            const nulls2 = c2.nulls?.toUpperCase() ?? defaultNulls(sort2);
+            if (nulls1 !== nulls2) {
+                return false;
+            }
+            if (c1.length !== c2.length) {
+                return false;
+            }
+            if (c1.collation !== c2.collation) {
+                return false;
+            }
+        }
+        return true;
+    }
+    /**
+     * Compare two arrays for equality (order matters).
+     */
+    compareArrays(arr1, arr2) {
+        if (!arr1 && !arr2) {
+            return true;
+        }
+        if (!arr1 || !arr2 || arr1.length !== arr2.length) {
+            return false;
+        }
+        return arr1.every((val, i) => val === arr2[i]);
+    }
     diffExpression(expr1, expr2) {
@@ -535,0 +611,0 @@ // expressions like check constraints might be normalized by the driver,

schema/SchemaHelper.d.ts

@@ -30,2 +30,11 @@ import { type Connection, type Dictionary, RawQueryFragment } from '@mikro-orm/core';
     getCreateIndexSQL(tableName: string, index: IndexDef): string;
+    /**
+     * Hook for adding driver-specific index options (e.g., fill factor for PostgreSQL).
+     */
+    protected getCreateIndexSuffix(_index: IndexDef): string;
+    /**
+     * Build the column list for an index, supporting advanced options like sort order, nulls ordering, and collation.
+     * Note: Prefix length is only supported by MySQL/MariaDB which override this method.
+     */
+    protected getIndexColumns(index: IndexDef): string;
     getDropIndexSQL(tableName: string, index: IndexDef): string;
@@ -32,0 +41,0 @@ getRenameIndexSQL(tableName: string, index: IndexDef, oldIndexName: string): string[];

schema/SchemaHelper.js

@@ -90,17 +90,58 @@ import { RawQueryFragment, Utils } from '@mikro-orm/core';
         }
+        if (index.fillFactor != null && (index.fillFactor < 0 || index.fillFactor > 100)) {
+            throw new Error(`fillFactor must be between 0 and 100, got ${index.fillFactor} for index '${index.keyName}'`);
+        }
         tableName = this.quote(tableName);
         const keyName = this.quote(index.keyName);
         const defer = index.deferMode ? ` deferrable initially ${index.deferMode}` : '';
-        let sql = `create ${index.unique ? 'unique ' : ''}index ${keyName} on ${tableName} `;
+        let sql = `create ${index.unique ? 'unique ' : ''}index ${keyName} on ${tableName}`;
         if (index.unique && index.constraint) {
-            sql = `alter table ${tableName} add constraint ${keyName} unique `;
+            sql = `alter table ${tableName} add constraint ${keyName} unique`;
         }
         if (index.columnNames.some(column => column.includes('.'))) {
             // JSON columns can have unique index but not unique constraint, and we need to distinguish those, so we can properly drop them
-            const sql = `create ${index.unique ? 'unique ' : ''}index ${keyName} on ${tableName} `;
+            sql = `create ${index.unique ? 'unique ' : ''}index ${keyName} on ${tableName}`;
             const columns = this.platform.getJsonIndexDefinition(index);
-            return `${sql}(${columns.join(', ')})${defer}`;
+            return `${sql} (${columns.join(', ')})${this.getCreateIndexSuffix(index)}${defer}`;
         }
-        return `${sql}(${index.columnNames.map(c => this.quote(c)).join(', ')})${defer}`;
+        // Build column list with advanced options
+        const columns = this.getIndexColumns(index);
+        sql += ` (${columns})`;
+        // Add INCLUDE clause for covering indexes (PostgreSQL, MSSQL)
+        if (index.include?.length) {
+            sql += ` include (${index.include.map(c => this.quote(c)).join(', ')})`;
+        }
+        return sql + this.getCreateIndexSuffix(index) + defer;
     }
+    /**
+     * Hook for adding driver-specific index options (e.g., fill factor for PostgreSQL).
+     */
+    getCreateIndexSuffix(_index) {
+        return '';
+    }
+    /**
+     * Build the column list for an index, supporting advanced options like sort order, nulls ordering, and collation.
+     * Note: Prefix length is only supported by MySQL/MariaDB which override this method.
+     */
+    getIndexColumns(index) {
+        if (index.columns?.length) {
+            return index.columns.map(col => {
+                let colDef = this.quote(col.name);
+                // Collation comes after column name (SQLite syntax: column COLLATE name)
+                if (col.collation) {
+                    colDef += ` collate ${col.collation}`;
+                }
+                // Sort order
+                if (col.sort) {
+                    colDef += ` ${col.sort}`;
+                }
+                // NULLS ordering (PostgreSQL)
+                if (col.nulls) {
+                    colDef += ` nulls ${col.nulls}`;
+                }
+                return colDef;
+            }).join(', ');
+        }
+        return index.columnNames.map(c => this.quote(c)).join(', ');
+    }
     getDropIndexSQL(tableName, index) {
@@ -302,4 +343,16 @@ return `drop index ${this.quote(index.keyName)}`;
             if (map[index.keyName]) {
-                map[index.keyName].composite = true;
-                map[index.keyName].columnNames.push(index.columnNames[0]);
+                if (index.columnNames.length > 0) {
+                    map[index.keyName].composite = true;
+                    map[index.keyName].columnNames.push(index.columnNames[0]);
+                }
+                // Merge columns array for advanced column options (sort, length, collation, etc.)
+                if (index.columns?.length) {
+                    map[index.keyName].columns ??= [];
+                    map[index.keyName].columns.push(index.columns[0]);
+                }
+                // Merge INCLUDE columns
+                if (index.include?.length) {
+                    map[index.keyName].include ??= [];
+                    map[index.keyName].include.push(index.include[0]);
+                }
             }
@@ -306,0 +359,0 @@ else {

typings.d.ts

 import type { Generated, Kysely } from 'kysely';
-import type { CheckCallback, DeferMode, Dictionary, EntityName, EntityProperty, EntitySchemaWithMeta, FilterQuery, GroupOperator, Opt, Primary, PrimaryProperty, QueryFlag, QueryOrderMap, RawQueryFragment, Type } from '@mikro-orm/core';
+import type { CheckCallback, DeferMode, Dictionary, EntityName, EntityProperty, EntitySchemaWithMeta, FilterQuery, GroupOperator, IndexColumnOptions, Opt, Primary, PrimaryProperty, QueryFlag, QueryOrderMap, RawQueryFragment, Type } from '@mikro-orm/core';
 import type { JoinType, QueryType } from './query/enums.js';
@@ -81,2 +81,25 @@ import type { DatabaseSchema } from './schema/DatabaseSchema.js';
     deferMode?: DeferMode | `${DeferMode}`;
+    /**
+     * Advanced column options for the index.
+     * When specified, these options override the simple columnNames for index generation.
+     */
+    columns?: IndexColumnOptions[];
+    /**
+     * Columns to include in the index but not as part of the key (PostgreSQL, MSSQL).
+     */
+    include?: string[];
+    /** Fill factor for the index as a percentage 0-100 (PostgreSQL, MSSQL). */
+    fillFactor?: number;
+    /**
+     * Whether the index is invisible/hidden from the query optimizer (MySQL 8+, MariaDB 10.6+, MongoDB).
+     */
+    invisible?: boolean;
+    /**
+     * Whether the index is disabled (MSSQL only).
+     */
+    disabled?: boolean;
+    /**
+     * Whether the index should be clustered (MariaDB, MSSQL).
+     */
+    clustered?: boolean;
 }
@@ -83,0 +106,0 @@ export interface CheckDef<T = unknown> {

New alerts

Network access

Supply chain risk

This module accesses the network.

Found 1 instance in 1 package

Long strings

Supply chain risk

Contains long string literals, which may be a sign of obfuscated or packed code.

Found 1 instance in 1 package

URL strings

Supply chain risk

Package contains fragments of external URLs or IP addresses, which the package may be accessing at runtime.

Found 1 instance in 1 package

Fixed alerts

Network access

Supply chain risk

This module accesses the network.

Found 1 instance in 1 package

Long strings

Supply chain risk

Contains long string literals, which may be a sign of obfuscated or packed code.

Found 1 instance in 1 package

URL strings

Supply chain risk

Package contains fragments of external URLs or IP addresses, which the package may be accessing at runtime.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

896499

2.41%

Lines of code

15929

3.15%

Worsened metrics

Number of low supply chain risk alerts

67

9.84%

No dependency changes