"Index" checkbox on advanced field creation UI

[slyk]

Summary

Indexes allow DB work faster. I've implemented it.

Basic Example

No response

Motivation

Seems logical for some simple but bulk data rows to be able to add index fast on field creation. And also see the indexes that are exists. Start from simple checkbox like we have for unique would be ok.

Detailed Design

• New checkbox Indexed next to Unique. Checked on create → the column gets a single-column, non-unique index (Knex default name __index). Unchecking on an existing field drops it.
• The state lives in the database: the schema inspector reports a new schema.is_indexed boolean for every column (single-column, non-unique, non-primary index). It is therefore also available in the fields REST API, GraphQL and schema snapshot|diff|apply. Snapshots taken before the property existed do not produce a diff.
• Not offered for alias, primary key or generated fields; shown checked and locked for unique columns and for indexed foreign keys (MySQL needs that index for the constraint).
• No migration, no change to system tables, nothing changes unless the box is used.

Requirements List

Must have

• An “Indexed” checkbox in the advanced field creation mode (Schema tab). Checking it on create adds a single-column, non-unique index on the column.
• The state is read from the database, not stored in metadata. The schema inspector reports a schema.is_indexed boolean per column, so the checkbox always matches reality, including indexes created outside d9.
• Opening an existing field shows the real state. Unchecking drops the index, and rechecking adds it back.
• No effect unless the box is used: no migration, no change to system tables, and existing instances behave as before.
• The flag works through the fields REST API and GraphQL (schema.is_indexed).
• The MySQL/MariaDB and SQLite implementations have tests. They don’t run on Postgres, so the Postgres path needs a live-server check before release.

Should have

• The flag is part of schema snapshot, diff and apply. Snapshots taken before the property existed must not report every column as changed.
• Primary key and unique columns are skipped, since they are already indexed by their constraint. The checkbox is hidden for primary keys and aliases, and locked for generated fields.
• Foreign-key columns that are already indexed show the box checked and locked, because dropping that index can break the constraint on MySQL.
• Postgres, CockroachDB, MSSQL and Oracle support. The queries are written but still need verifying on real servers.
• A docs entry for is_indexed and a changeset.

Could have

• A hint in the UI that creating an index on a large table can lock it for a while.
• Support for composite (multi-column) indexes, and unique-index management from the same place.
• Letting the inspector return the real index name, so any single-column index can be dropped, not just the one with the default name <table>_<column>_index.

Won’t have (this version)

• Partial, expression, fulltext, spatial or other special index types.
• A UI for managing composite indexes.
• An automatic index-suggestion or performance-analysis feature.
• Translations beyond English (they come through Crowdin).

Drawbacks

One column index not helps in many cases and still need edit DB or have more complex feature editor.

Alternatives

Open some DB editor, analyze the requests and make a real valuable indexes.

Adoption Strategy

The "Indexed" flag is taken from DB (Knex naming)

Unresolved Questions

No response

[slyk]

Sorry, don't know are you open to new features or just maintain the classy old d9 to use in your projects.
With strict licensing and our growth seems like I will need to downgrade from D11, but will need a way to add features.

[louisdussarps]

Hello @slyk,
Thanks for your message! We're definitely open to new features, and we appreciate your suggestion. It would be a really useful features!

I would maybe give composite indexes a higher priority, as they quickly become important in a production database. As a result, perhaps a dedicated section below the list of fields in a collection would be a better place to manage indexes, rather than limiting this to a checkbox on individual fields.

Also, in the meantime, if you're using PostgreSQL, you might find our projen-templates useful. It allows you to save a SQL version of your schema and modify it directly. You can find an example of a generated schema here along with the available CLI commands for the plumbing tool here
This is the approach we use in our own projects to add indexes, and we're planning to document the plumbing utility in the official d9 documentation later this month.

[slyk]

if you're using PostgreSQL

Ah, I was start programming somethin when there was LAMP stack) Moved to nginx but still on MySQL/MariaDB. For table fixes I mostly connect it to Jetbrains IDE, they have nice UI for all the complex work.

What I often use is DB structure export export to TypeScript typings. There are separate modules. Don't know is worth adding some easy export button to core.

[louisdussarps]

Hello @slyk,
Our changes to Directus v9 have actually been relatively subtle.

We use d9 for projects that require regular audits, particularly in the medical field, so most of our work has focused on that. For example, we've added conditional permissions based on revision / activity.item, user creation dates, improved cookie handling, and session IDs to track user activity per session.
We've also made several DevEx improvements through Projen, including the ability to export Directus permissions in Cedar format (but, as mentioned earlier, this is currently PostgreSQL-only).

We've tried to avoid over-engineering things and restricted ourselves to only modifying Directus when we encountered a missing feature. This didn't occur often, as we could handle most of our requirements through extensions.

That said, we're very open to PRs introducing new features, provided they don't break existing functionality and are generic enough to benefit the wider community.

By the end of the year, we'd like to introduce composite and conditional roles and improve the permissions editing experience, which can become quite challenging on projects with 50+ tables. (And, of course, we continue maintaining the security requirements, keeping its dependencies up to date and incorporating relevant security fixes).

Also, it can be worth mentioning Directus v10 has entered the public domain (at least releases that are more than three years old) and we're considering bringing some of its features into the fork. If you are interested in specific features that were in the v10, contributions or expressions of interest in this area could help move that process forward!

[louisdussarps]

Regarding your screenshots, indeed, with d9 (and Directus v9 at the time of the fork), it's possible to change the logo and project color, but the project color isn't applied consistently throughout the interface. It could be a nice improvement to make it a more global color, consistent everywhere.
As for the homepage, if it remains configurable, we could definitely update the default one. Our lead designer would be happy to review the PR (I am quite sure he would like the idea).

[louisdussarps]

Regarding the export, we've already run into issues with the directus snapshots, and our impression is that each team has found its own workaround. I could be wrong, but I'm not sure that broadening its scope in the short term would address all the different use cases.

[Grouzoul]

Hey @slyk !

Love the modern AI chats. At first we decided to stick with the original colors and landing page not to scare away nostalgic Directus users looking for an alternative.

Now that the fork has a growing number of contributors, modernizing its landing page seems relevant and definitely worth a PR.

Regarding the hardcoded purple: that behavior is inherited from an earlier Directus version (a 2021 commit), so your small fix should work on d9 as well. Since it's already possible to customize part of the theme, it seems only fair for it to propagate to all components.

Feel free to open a PR with both changes and I'll review it soon! Thanks

[slyk]

@louisdussarps I have added one column index
1fe2996

But the idea of full-fledged editor of all indexes in collection admin its way better solution, but takes more time and testing :)

[louisdussarps]

This is already a good starting point! It's a feature we'd be interested in as well. I'll see if I can find some time this month to look into the idea of adding a full-fledged editor of all indexes in collection below the fields section, but if time is tight, the changes you're proposing would be a great first step!

[slyk]

#435