ORM index definitions — researched

How the ORM and application layers declare and use indexes: Prisma @@index/@@unique, Drizzle index()/uniqueIndex(), Convex defineTable().index() and withIndex(), InstantDB indexed attributes. Child pack of Indexing.

Definitions

Indexes are a data structure that allow you to speed up your [document queries](/database/reading-data/.md#querying-documents) by telling Convex how to organize your documents. Indexes also allow you to change the order of documents in query results. [source]

Structure and components

Indexes tell the database to create a lookup structure to make it really fast to filter data. If, in our chat app we wanted to build a way to look up `messages` from just one user, we'd tell Convex to index the `user` field in the `messages` table and write the query with the `withIndex` syntax. [source]
The schema is something else: the database's actual structure, the tables and indexes that exist right now. The contract lives in your repository; the schema lives in the database. Everything Prisma 8 does is a relationship between the two: queries are typed against the contract, migrations move the schema toward the contract, and verification checks that the schema still satisfies the contract. [source]
* New naming convention for constraints and indexes * Clear distinction between `map` (database-level name) and `name` (Prisma Client API name) * Primary and foreign key names are now part of the schema for supporting databases [source]
Everything on this page applies to both database families. On PostgreSQL, operations compile to SQL DDL and the applied state is tracked in a **marker**, a record Prisma 8 keeps in the database itself naming the contract state the database currently matches. On MongoDB, operations create collections, indexes, and JSON Schema validators, and the marker lives in a `_prisma_migrations` collection. The commands, the file layout, the graph, and the precheck/execute/postcheck structure are identical. [source]
This can be used to to model a queue, or to implement an `updatedAt` field. You could define an index on a field that captures the commit time, then use that to iterate over new or updated documents without worrying about missing changes due to out-of-order commits. For tips on using CommitTs for efficient iteration, see the notes in the [Batch Worker Component README](https://github.com/get-convex/batch-worker). [source]
A better option is to build an *index* on `author`. In the library, we could use an old-school [card catalog](https://en.wikipedia.org/wiki/Library_catalog) to organize the books by author. The idea here is that the librarian will write an index card for each book that contains: [source]
then Convex will create a new index called `by_author` on `author`. This means that your `books` table will now have an additional data structure that is sorted by the `author` field. [source]
One interesting detail to think about is the work needed to create this new structure. In the library, the librarian must go through every book on the shelf and put a new index card for each one in the card catalog sorted by author. Only after that can the librarian trust that the card catalog will give it correct results. [source]
If your schema has a composite type with a `@@unique` constraint, MongoDB prevents you from storing the same value for the constrained value in two or more of the records that contain this composite type. However, MongoDB does does not prevent you from storing multiple copies of the same field value in a single record. [source]

How it works

In this example, the range expression is omitted because we're looking for the highest scoring players of all time. This particular query is reasonably efficient for large data sets only because we're using `take()`. [source]
> [!NOTE] > Note the `@@index([authorId])` on the `Post` model for MySQL. PlanetScale MySQL requires indexes on foreign keys when using `relationMode = "prisma"`. [source]
Given all of this, we can conclude that **the performance of indexed queries is based on how many documents are in the index range**. In this case, the performance is based on the number of Isaac Asimov books because the librarian will need to look at each one to examine its title. [source]
This is the same query but we've swapped the order to descending. In the library, this means that the librarian will start on the right edge of the shelf and scan right-to-left. The librarian still only needs to look at a single book to determine the result so this query is also extremely fast. [source]
| Hash | Covers | Changes when | | --------------- | -------------------------------------------------------------------------------------- | ----------------------------------------- | | `storageHash` | Models, fields, relations, and the full storage layout: tables, columns, keys, indexes | Any schema change | | `executionHash` | Defaults Prisma 8 applies before writes, such as `uuid()` generators | Generated defaults change [source]
Introspection will fetch these limits where they are present in your existing database. This allows Prisma ORM to support indexes and constraints that were previously suppressed and results in better support of MySQL databases utilizing this feature. [source]
The `map` argument allows you to specify a custom name for the index or constraint in the underlying database. This is useful when you want to use a specific naming convention or when the auto-generated name doesn't meet your requirements. [source]
2. Persisted computed columns store the computed value on disk and can be indexed when the expression is deterministic. [source]
Additional fields to index for fast filtering when running search queries. [source]
Additional fields to index for fast filtering when running vector searches. [source]
A single field can have values of any [Convex type](/database/types.md). When there are values of different types in an indexed field, their ascending order is as follows: [source]
Be careful when removing indexes [source]
How do I ensure my Convex [database queries](/database/reading-data/.md) are fast and efficient? When should I define an [index](/database/reading-data/indexes/.md)? What is an index? [source]
These index cards will be sorted by author and live in a separate organizer from the shelves that hold the books. The card catalog should stay small because it only has an index card per book (not the entire text of the book). [source]
This is quite fast because the librarian can quickly find the index cards for Jane Austen. It's still a little bit of work to find the book for each card but the number of index cards is small so this is quite fast. [source]
This query instructs Convex to go to the `by_author` index and find all the entries where `doc.author === "Jane Austen"`. Because the index is sorted by `author`, this is a very efficient operation. This means that Convex can execute this query in the same manner that the librarian can: [source]
This query describes how a librarian might execute the query. The librarian will use the card catalog to find all of the index cards for Isaac Asimov's books. The cards themselves don't have the title of the book so the librarian will need to find every Asimov book on the shelves and look at its title to find the one named *Foundation*. Lastly, this query ends with [`.unique`](/api/interfaces/server.Query.md#unique) because we expect there to be at most one result. [source]
In this index, books are sorted first by the author and then within each author by title. This means that a librarian can use the index to jump to the Isaac Asimov section and quickly find *Foundation* within it. [source]
Because this index sorts by `author` and then by `title`, it also efficiently supports queries like "All books by Isaac Asimov that start with F." We could express this as: [source]
Lastly, imagine that a library patron asks for the book *The Three-Body Problem* but they don't know the author's name. Our `by_author_title` index won't help us here because it's sorted first by `author`, and then by `title`. The title, *The Three-Body Problem*, could appear anywhere in the index! [source]
Instead, you can use [indexes](/database/reading-data/indexes/.md) so that the database only needs to read the relevant documents. [source]
We set these timeouts intentionally for performance and reliability. We do not allow timeouts to be configured. Sometimes fixing a timeout is as simple as adding an index. Other times you'll need to iterate to identify the bottleneck. Some common causes of timeouts: [source]
**SSR can be great for search engines.** Web crawlers are getting better with JavaScript, but they generally do the best job at indexing websites when the content is there on the first load. SSR can do this for you. [source]

Parameters and configuration

| Name | Required | Type | Description | | ----------- | -------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `map` | **No** | `String` | The name of the underlying primary key constraint in the database.<br /><br /> Not supported for MySQL or MongoDB. | | ` [source]
| Name | Required | Type | Description | | ----------- | -------- | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fields` | **Yes** | `FieldReference[]` | A list of field names - for example, `["firstname", "lastname"]` [source]

How-to and procedures

CREATE TABLE "user_nulls_example" ( "id" integer UNIQUE NULLS NOT DISTINCT, "id2" integer CONSTRAINT "custom_name" UNIQUE NULLS NOT DISTINCT ); ``` </Section> [source]
You can imagine that Convex is a physical library storing documents as physical books. In this world, every time you add a document to Convex with [`db.insert("books", {...})`](/api/interfaces/server.GenericDatabaseWriter.md#insert) a librarian places the book on a shelf. [source]
* You never hand-write migration steps: declare indexes and validators in the contract (step 2) and `migration plan` derives the changes. * If a `migrate` run is interrupted, rerun it to resume. After fixing anything by hand, run `db sign` so the signature matches the database again. * Prisma 8 adds strict `$jsonSchema` validators by default. Make sure existing documents pass them before running in production: once the validators are live, writes to documents that don't match the contract fail with `Document failed validation`. [source]
Use one polymorphic collection when the variants are handled together far more than separately: a notifications collection of email, SMS, and push messages read as one stream. Prefer separate collections when the types rarely appear in the same query or need very different indexes. [source]
If you use the [Prisma VS Code extension](https://marketplace.visualstudio.com/items?itemName=Prisma.prisma) (or our [language server in another editor](https://www.prisma.io/docs/orm/v7/more/dev-environment/editor-setup)), the warning is augmented with a Quick Fix that adds the required index for you: [source]
You can now specify indexes for `pg_vector` and utilize `pg_vector` functions for querying, ordering, etc. [source]
Run a vector search on the given table and index. [source]
You can define a `"users"` table, optionally with an [index](/database/reading-data/indexes/.md) for efficient looking up the users in the database. [source]
You can query for documents inserted in the current transaction using a commit timestamp index. This can be useful inside components or nested function where you may not have context from the parent transaction. [source]
You can feel free to query an index in the same deploy that defines it. Convex will ensure that the index is backfilled before the new query and mutation functions are registered. [source]
You can then efficiently find the top 10 highest scoring players using your index and [`take(10)`](/api/interfaces/server.Query.md#take): [source]
You can also order by any attribute that is indexed and has a checked type. [source]
Add indexes and checked types to your attributes from the [Explorer on the Instant dashboard](/dash?t=explorer) or from the [cli](/docs/cli). [source]
Add indexes and checked types to your attributes from the [Explorer on the Instant dashboard](/dash?t=explorer) or from the [cli with Schema-as-code](/docs/modeling-data). [source]
You can customize the constraint name with the `name` field: `@@unique(name: "authorTitle", [authorId, title])` [source]

Measurements and reference values

During function execution, new documents inserted with `db.vars.commitTs` will have the `CommitTsPlaceholder`. Resolved commit timestamps are Int64s (bigint in JS). You can use the `v.commitTs()` validator in argument and return validators and schema definitions. It accepts both resolved Int64 values and the commit timestamp placeholder. Fields with the commit timestamp are otherwise like any other field: they can be used in indexes, nested objects, arrays, and unions. [source]

Problems, failure modes and limitations

Because views are virtual tables, they cannot have indexes. Therefore, `@index` and `@@index` cannot be defined on `view` blocks. [source]
The number of calls to `db.get` and `db.query` has a limit to prevent a single query from subscribing to too many index ranges, or a mutation from reading from too many ranges that could cause conflicts. [source]
The reason for this behavior is that all connectors consider `null` values to be distinct, which means that two rows that *look* identical are considered unique: [source]
"Cannot find a fulltext index to use for the search, try adding a @@fulltext(\[Fields...]) to your schema" [source]
`String` parameters in raw queries are encoded as `NVARCHAR(4000)` or `NVARCHAR(MAX)`. When querying `VARCHAR(N)` columns, manually cast to avoid index performance issues: [source]
This is not valid in MySQL because it exceeds MySQL's index storage limit and therefore Prisma ORM rejects the data model. The generated SQL would be rejected by the database. [source]
Convex also supports a slower filtering mechanism that effectively loops through the table to match the filter. This can be useful if you know your table will be small (low thousands of rows), you're prototyping, or you want to filter an index query further. You can read more about filters [here](/database/reading-data/filters.md). [source]
Filters effectively loop over your table looking for documents that match. This can be slow or cause your function to hit a [limit](/production/state/limits.md) when your table has thousands of rows. For faster more database efficient queries use [indexes instead](/database/reading-data/indexes/.md). [source]
If a table has more than a few thousand documents, you should use [indexes](/database/reading-data/indexes/.md) to improve your document query performance. Otherwise, you may run into our enforced limits, detailed in [Read/write limit errors](/functions/error-handling/.md#readwrite-limit-errors). [source]
Convex supports indexes containing up to 16 fields. You can define 32 indexes on each table. Indexes can't contain duplicate fields. [source]
If you are defining a few indexes there is no need to worry about the maintenance cost. As you define more indexes, the cost to maintain them grows because every `insert` needs to update every index. This is why Convex has a limit of 32 indexes per table. In practice most applications define a handful of indexes per table to make their important queries efficient. [source]
In general, if you're running into these limits frequently, we recommend [indexing your queries](/database/reading-data/indexes/.md) to reduce the number of documents scanned, allowing you to avoid unnecessary reads. Queries that scan large swaths of your data may look innocent at first, but can easily blow up at any production scale. If your functions are close to hitting these limits they will log a warning. [source]
If there's a chance the number of results is large (say 1000+ documents), you should use an index to filter the results further before calling `.collect`, or find some other way to avoid loading all the documents such as using pagination, denormalizing data, or changing the product feature. [source]
Try replacing the call to `.filter()` with a call to `.withIndex()` if possible. This is especially important if the number of documents you’re filtering on is large (1000+) or unbounded. [source]

Comparisons and alternatives

**Best practice:** Use `.withIndex()` instead of `.filter()` for efficient queries. Define indexes in your schema for fields you query frequently. [source]
In Convex, you must explicitly use the `withIndex()` syntax to ensure your database uses the index. This differs from a more traditional SQL database, where the database implicitly chooses to use an index based on heuristics. The Convex approach leads to fewer surprises in the long run. [source]
**Best practice:** Always include `args` and `returns` validators. Use `.withIndex()` instead of `.filter()` for efficient database queries. Queries should be fast since they run on every relevant data change. [source]
Using `.filter` on a paginated query (`.paginate`) has advantages over filtering in code. The paginated query will return the number of documents requested, including the `.filter` condition, so filtering in code afterwards can result in a smaller page or even an empty page. Using `.withIndex` on a paginated query will still be more efficient than a `.filter`. [source]

Facts and statements

The `length` and `sort` arguments are added to the relevant field names: [source]
Defines an index in the database. [source]
Query by reading documents from an index on this table. [source]
This query's cost is relative to the number of documents that match the index range expression. [source]
Results will be returned in index order. [source]
* The query that yields documents in the index. [source]
For example, `by_channel_user` includes `channel`, `user`, and `_creationTime`. So queries on `messages` that use `.withIndex("by_channel_user")` will be sorted first by channel, then by user within each channel, and finally by the creation time. [source]
* Block level: `@@id`, `@@unique`, `@@index`, `@@map` * Field level : `@id`, `@unique`, `@default`, `@updatedAt`, `@map`, `@relation` [source]
| Index | Supported | Prisma schema | Prisma Client | Prisma Migrate | | -------------- | :--------------------------------------: | :------------------------------------------------------------------------------------------------------------: | :-----------: | :------------: | | `UNIQUE` | ✔️ | [`@unique` and `@@unique`](https://www.prisma.io/docs/orm/v7/prisma-schema/data-model/models#defining-a-unique-f [source]
The *name* of the `fields` argument on the `@@index` attribute can be omitted: [source]
* Replicate the change you made in production in the schema - for example, add an `@@index` to a particular model. * Generate a new migration and take note of the full migration name, including a timestamp, which is written to the CLI:(`20210316150542_retroactively_add_index`): [source]
* The [`length` argument](#configuring-the-length-of-indexes-with-length-mysql) allows you to specify a maximum length for the subpart of the value to be indexed on `String` and `Bytes` types * Available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes * MySQL only [source]
* The [`sort` argument](#configuring-the-index-sort-order-with-sort) allows you to specify the order that the entries of the constraint or index are stored in the database * Available on the `@unique`, `@@unique` and `@@index` attributes in all databases, and on the `@id` and `@@id` attributes in SQL Server [source]
* The [`clustered` argument](#configuring-if-indexes-are-clustered-or-non-clustered-with-clustered-sql-server) allows you to configure whether a constraint or index is clustered or non-clustered * Available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes * SQL Server only [source]
* The [`map` argument](#configuring-the-name-of-indexes-with-map) allows you to specify a custom name for the index or constraint in the underlying database * Available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes * Supported in all databases [source]
The `length` argument is available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes. [source]
A similar syntax can be used for the `@@unique` and `@@index` attributes. [source]
| Attribute | Value | | ---------- | ------- | | `@id` | `true` | | `@@id` | `true` | | `@unique` | `false` | | `@@unique` | `false` | | `@@index` | `false` | [source]
The `map` argument is available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes. [source]
For MongoDB composite types, use dot notation: `@@index([address.city.name])` [source]
Extracts a sub-array by index range. [source]
An expression representing an index range created by [IndexRangeBuilder](/api/interfaces/server.IndexRangeBuilder.md). [source]
**Important:** Prefer using `.withIndex()` over `.filter()` whenever possible. Filters scan all documents matched so far and discard non-matches, while indexes efficiently skip non-matching documents. Define an index in your schema for fields you filter on frequently. [source]
1. Define the index in your `convex/schema.ts` file. 2. Query via the `withIndex()` syntax. [source]
Let’s assume you’re building a chat app and want to get all messages in a particular channel. You can define a new index called `by_channel` on the `messages` table by using the `.index()` method in your schema. [source]
Queries that use `withIndex` are ordered by the columns specified in the index. [source]
If you use an index without a range expression, you should always use one of the following in conjunction with `withIndex`: [source]
This query demonstrates the difference between filtering using [`withIndex`](/api/interfaces/server.QueryInitializer.md#withindex) and [`filter`](/api/interfaces/server.Query.md#filter). `withIndex` only allows you to restrict your query based on the index. You can only do operations that the index can do efficiently like finding all documents with a given author. [source]
One approach is to build a separate `by_title` index on `title`. This could let us swap the work we do in `.filter` and `.withIndex` to instead be: [source]
Here the index range expression tells Convex to only consider documents where the author is Isaac Asimov and the title is *Foundation*. This is only a single document so this query will be quite fast! [source]
This query uses the index to find books where `author === "Isaac Asimov" && "F" <= title < "G"`. Once again, the performance of this query is based on how many documents are in the index range. In this case, that's just the Asimov books that begin with "F" which is quite small. [source]
Also note that this index also supports our original query for "books by Jane Austen." It's okay to only use the `author` field in an index range expression and not restrict by title at all. [source]
3. You can use `undefined` in filters and index queries, and it will match documents that do not have the field. i.e. `.withIndex("by_a", q=>q.eq("a", undefined))` matches document `{}` and `{b: 1}`, but not `{a: 1}` or `{a: null, b: 1}`.  * In Convex's ordering scheme, `undefined < null < all other values`, so you can match documents that *have* a field via `q.gte("a", null as any)` or `q.gt("a", undefined)`. [source]
| | | Notes | | -------------------------- | ------ | --------------------------------------------------------- | | Data read | 16 MiB | Data not returned due to a `filter` counts as scanned | | Data written | 16 MiB | | | Documents scanned | 32,000 | Documents not returned due to a `filter` count as scanned | | Index ranges read | 4,096 | The number of calls to `db.get` and `db.query`. | [source]
The `extendedIndexes` feature is now generally available, with support for: [source]
The `length` argument can also be used on compound primary keys, using the `@@id` attribute, as in the example below: [source]
Name it as you wish and change the operator. This example allows for a numbers array, strings array, string, or even a select query. Feel free to create any other type you want or even contribute and submit a PR [source]
const table = singlestoreTable("table", { int: int("int").default(42), time: time("time").default(sql`cast("14:06:10" AS TIME)`), }); ``` ```sql CREATE TABLE `table` ( `int` int DEFAULT 42, `time` time DEFAULT cast("14:06:10" AS TIME) ); ``` </Section> [source]
Another option is to duplicate the entire library. We could purchase 2 copies of every book and put them on 2 separate shelves: one shelf sorted by insertion time and another sorted by author. This would work, but it's expensive. We now need twice as much space for our library. [source]
First we make sure that the `price` attribute is indexed: [source]
* model and field names * relation names * mapped database names * defaults, indexes, and constraints * extension-backed column types [source]
Periodically review and refine your essential queries and verify that their tables are properly indexed, so those queries are less exposed to slowdowns from competing workloads. [source]
* Change the Prisma query shape * Add or adjust an index * Return fewer fields or fewer rows * Cache repeated work [source]
* Generating boilerplate for models, indexes, constraints, and relations. * Keeping the schema consistent by sticking to naming and other conventions. * Auto-completing model and field definitions based on common database patterns. * Suggesting relationships and field types based on naming conventions. [source]
Prisma ORM 3 changes how constraints and indexes are named: [source]
1. **Update Raw Queries**: Convert all `$queryRaw` calls to use template literals or switch to `$queryRawUnsafe`. 2. **Test Thoroughly**: Pay special attention to: * Relations and cascading deletes * JSON field operations * Any raw SQL queries * Custom constraints and indexes [source]
1. **Schema Changes** * Explicit `@unique` constraints for one-to-one relations * Enforced `@unique` or `@id` for one-to-one and one-to-many relations in MySQL/MongoDB * Scalar list defaults * Index configuration improvements * Better string literal grammar [source]
1. **Update Schema**: Review and update your schema to handle breaking changes 2. **Update Application Code**: Make necessary changes to your application code 3. **Test Thoroughly**: Test all functionality, especially: * One-to-one relations * JSON field operations * Raw queries * Index configurations [source]
This will update your schema with new capabilities like improved index configuration. [source]
The contract separates what your application models from how it is stored. The `domain` section describes models, fields, and relations; the `storage` section describes tables, columns, keys, and indexes (or collections and indexes for a MongoDB contract); each model's `storage` block bridges the two. Everything is grouped by namespace (on PostgreSQL, the schema, typically `public`). [source]
A UUID is wider than an integer and random UUIDs index a little worse, but independent services never collide and nothing is leaked. [source]
All target PostgreSQL. ParadeDB and Supabase are experimental (ParadeDB supports the `key_field` index option only so far). The rest ship with Prisma 8. Extension names link to each package's README on GitHub. [source]
An index or column mapping references a field the model does not declare (unknown field in the contract definition, or a Mongo model index over an undeclared field). Raised while lowering/building the contract. Meta: `modelName`, `fieldName`, `indexSignature`. [source]
A Mongo variant model declares an index that conflicts with the discriminator scope of its variant, or a SQL index option value is not a string, finite number, or boolean. Raised by the Mongo contract builder and the Postgres index DDL renderer. Meta: `variantName`, `indexLabel`, `reason`, `key`. [source]
Two declarations claim the same name: duplicate namespace entries, model names, value objects, relations, tables (two models mapping to one table, or duplicate table in a namespace), column mappings (two fields to one column), indexes, value-sets (enum and pack entity minting the same value-set), or pack entities of the same kind and name in one namespace. Raised while authoring/building a contract. Meta: `kind`, `name`, `namespaceId`, `first`, `second`. [source]
A foreign key or index references a table name that disagrees with the table the target model is actually mapped to. Raised while building a SQL contract. Meta: `sourceModel`, `referencedTable`, `mappedTable`. [source]
Query Insights is most useful for diagnosing N+1 patterns, missing indexes, over-fetching, offset pagination, and repeated queries. In most cases it points you toward one of four fixes: changing the Prisma query shape, adding or adjusting an index, returning fewer fields or rows, or caching repeated work. [source]
This creates an executable file named `index` (or `index.exe` on Windows) in your project directory. [source]
| Feature | Supported by Prisma ORM | Notes | | ----------------------------------------- | :---------------------: | :------------------------------------------------------------------------------------------------: | | Embedded documents | ✔️ | | | Transactions | ✔️ | [source]
* ¹ Can be required by some of the index and field types. [source]
> [!NOTE] > **MongoDB introspection limitations:** Prisma introspects MongoDB by sampling documents. You may need to manually: > > * Add relation fields using the `@relation` attribute > * Adjust field types if the sampling didn't capture all variations > * Add indexes and constraints not detected during introspection [source]
Currently, there are no plans to add support for [Prisma Migrate](https://www.prisma.io/docs/orm/v7/prisma-migrate) as MongoDB projects do not rely on internal schemas where changes need to be managed with an extra tool. Management of `@unique` indexes is realized through `db push`. [source]
Location: ORM > v7 > Prisma Schema > Data Model > Indexes [source]
The `sort` argument allows you to specify the order that the entries of the index or constraint are stored in the database. This can have an effect on whether the database is able to use an index for specific queries. The behavior and support varies by database: [source]
The following example demonstrates the use of the `sort` and `length` arguments to configure indexes and constraints for a `Post` model: [source]
As an example, the following model adds an index with a `type` of `Hash` to the `value` field: [source]
As an example, the following model configures a custom name for the index on the `title` field: [source]
// `.on()` index('name') .on(table.column1.asc(), ...) .where(sql``) // sql expression [source]
1. **You should specify a name for your index manually if you have an index on at least one expression** [source]
2. **Push won't generate statements if these fields(list below) were changed in an existing index:** [source]
If you are using `push` workflows and want to change these fields in the index, you would need to: [source]
1. Comment out the index 2. Push 3. Uncomment the index and change those fields 4. Push again [source]
For the `generate` command, `drizzle-kit` will be triggered by any changes in the index for any property in the new drizzle indexes API, so there are no limitations here. [source]
<CodeTabs items={["schema.ts", "migration.sql"]}> <CodeTab> ```ts copy {18,19,20,21,22,23,24,28} import { SQL, sql } from 'drizzle-orm'; import { index, pgTable, serial, text, customType } from 'drizzle-orm/pg-core'; [source]
This is how you can create table with `geometry` datatype and spatial index in Drizzle: [source]
Let's take a few examples of `pg_vector` indexes from the `pg_vector` docs and translate them to Drizzle [source]
With the available Drizzle indexes API, you should be able to write any indexes for PostGIS [source]
- **Browse data** with pagination, filtering, multi-column sorting and column reordering - **Edit data** inline — add, update and delete rows, in-place editable `json`, `boolean` and `enum` editors - **Copy & paste** cell ranges, copy rows to clipboard or export them as `json`/`csv`/`sql` - **Import** `json`, `csv` and `sql` files - **Run SQL** in the SQL console with autocompletion, `explain`/`analyze` support and query telemetry - **Run Drizzle queries** with the Drizzle runner - **Explore and manage your schema** — tables, views, columns, indexes, foreign keys, policies and privileges [source]
- [Commit Timestamp](/database/advanced/commit-timestamp.md): Efficient iteration using a strictly increasing sequence identifier. - [OCC and Atomicity](/database/advanced/occ.md): Optimistic concurrency control and transaction atomicity in Convex - [Schema Philosophy](/database/advanced/schema-philosophy.md): Convex schema design philosophy and best practices - [System Tables](/database/advanced/system-tables.md): Access metadata for Convex built-in features through system tables including scheduled functions and file storage information. - [Backups](/database/backup-restore.md): Backup and r [source]
From **`Cursor Settings`** > **`Indexing & Docs`** > **`Docs`** add new doc, use the URL "<https://docs.convex.dev/home>" [source]
The search expression must search for text in the index's `searchField`. The filter expressions can use any of the `filterFields` defined in the index. [source]
| Name | Type | Description | | ----------- | ------------------------------------- | ------------------------------------------------------------------------------------- | | `fieldName` | `SearchIndexConfig`\[`"searchField"`] | The name of the field to search in. This must be listed as the index's `searchField`. | | `query` | `string` | The query text to search for. | [source]
The field to index for full text search. [source]
The field to index for vector search. [source]
The length of the vectors indexed. This must be between 2 and 2048 inclusive. [source]
A type describing the ordered fields in an index. [source]
A type describing the indexes in a table. [source]
A type describing the document type and indexes in a table. [source]
Ƭ **Indexes**<`TableInfo`>: `TableInfo`\[`"indexes"`] [source]
The database indexes in a table for a given [GenericTableInfo](/api/modules/server.md#generictableinfo). [source]
Extract the fields of an index from a [GenericTableInfo](/api/modules/server.md#generictableinfo) by name. [source]
A [GenericDataModel](/api/modules/server.md#genericdatamodel) that considers documents to be `any` and does not support indexes. [source]
Ƭ **DocValidator**<`TableName`, `DocumentType`>: `DocumentType` extends [`VUnion`](/api/classes/values.VUnion.md)<`any`, infer Members, `any`, `any`> ? { \[Index in keyof Members]: WithSystemFieldValidators\<TableName, Members\[Index]> } extends infer NewMembers ? [`VUnion`](/api/classes/values.VUnion.md)<`WithSystemFieldValidators`<`TableName`, `Members`\[`number`]>\[`"type"`], `NewMembers`> : `never` : `WithSystemFieldValidators`<`TableName`, `DocumentType`> [source]
The indexes that Convex automatically adds to every table. [source]
Convex automatically appends "\_creationTime" to the end of every index to break ties if all of the other fields are identical. [source]
* The TypeScript type of this value. * Whether this field should be optional if it's included in an object. * The TypeScript type for the set of index field paths that can be used to build indexes on this value. * A JSON representation of the validator. [source]
5. Push your functions, [indexes](/database/reading-data/indexes/.md), and [schema](/database/schemas.md) to production. [source]
6. Push your functions, [indexes](/database/reading-data/indexes/.md), and [schema](/database/schemas.md) to the deployment. [source]
The "Delete table" button can be found by clicking on the `⋮` overflow menu at the top of the data page. This action will delete all documents this table, and remove the table from your list of tables. If this table had indexes, you will need to redeploy your convex functions (by running `npx convex deploy` or `npx convex dev` for production or development, respectively) to recreate the indexes. [source]
The "Indexes" button can be found by clicking on the `⋮` overflow menu at the top of the data page. [source]
This button will open a panel showing the [indexes](/database/reading-data/indexes/.md) associated with the selected table. [source]
Indexes that have not completed backfilling will be accompanied by a loading spinner next to their name. [source]
This [history page](https://dashboard.convex.dev/deployment/history) is an audit log of configuration-related events that have occurred in the selected deployment, such as function deployments, changes to indexes, and changes to environment variables. [source]
Selecting a table opens a side panel with more detail about that table, including the full type of each field and the table's [indexes](/database/reading-data/indexes/.md). From the side panel you can jump to a referenced table in the diagram, or open the table on the [Data page](/dashboard/deployments/data.md) to view and edit its documents. [source]
With an index on `db.vars.commitTs`, you do not need to worry about this, as the timestamp is strictly increasing. Since inserted documents will always have a greater commit timestamp, the tombstones will always end where the new documents begin. [source]
* Queries and mutations will not view intermediate states where partial data is imported. * Indexes and schemas will work on the new data without needing time for re-backfilling or re-validating. [source]
* Relational data modeling with [Document IDs](/database/document-ids.md) * Fast querying with [Indexes](/database/reading-data/indexes/.md) * Exposing large datasets with [Paginated Queries](/database/pagination.md) * Type safety by [Defining a Schema](/database/schemas.md) * Interoperability with data [Import & Export](/database/import-export/.md) [source]
In your query function, you can now filter your `messages` table by using the `by_channel` index. [source]
For a more in-depth introduction to indexing see [Indexes and Query Performance](/database/reading-data/indexes/indexes-and-query-perf.md). [source]
1. A name.  * Must be unique per table. 2. An ordered list of fields to index.  * To specify a field on a nested document, use a dot-separated path like `properties.name`. [source]
The `by_channel` index is ordered by the `channel` field defined in the schema. For messages in the same channel, they are ordered by the [system-generated `_creationTime` field](/database/types.md#system-fields) which is added to all indexes automatically. [source]
By contrast, the `by_channel_user` index orders messages in the same `channel` by the `user` who sent them, and only then by `_creationTime`. [source]
In addition to adding new indexes, `npx convex deploy` will delete indexes that are no longer present in your schema. Make sure that your indexes are completely unused before removing them from your schema! [source]
The order of the columns in the index dictates the priority for sorting. The values of the columns listed first in the index are compared first. Subsequent columns are only compared as tie breakers only if all earlier columns match. [source]
Since Convex automatically includes `_creationTime` as the last column in all indexes, `_creationTime` will always be the final tie breaker if all other columns in the index are equal. [source]
Sorting with indexes allows you to satisfy use cases like displaying the top `N` scoring users, the most recent `N` transactions, or the most `N` liked messages. [source]
For example, to get the top 10 highest scoring players in your game, you might define an index on the player's highest score: [source]
The `by_creation_time` index is created automatically (and is what is used in database queries that don't specify an index). The `by_id` index is reserved. [source]
This document explains how you should think about query performance in Convex by describing a simplified model of how queries and indexes function. [source]
1. Find the range of the index with entries for Jane Austen. 2. For each entry in that range, get the corresponding document. [source]
Now imagine that a patron shows up at the library and would like to check out *Foundation* by Isaac Asimov. Given our index on `author`, we can write a query that uses the index to find all the books by Isaac Asimov and then examines the title of each book to see if it's *Foundation*. [source]
`filter` on the other hand allows you to write arbitrary, complex expressions but it won't be run using the index. Instead, `filter` expressions will be evaluated on every document in the range. [source]
Unfortunately, Isaac Asimov wrote [a lot of books](https://en.wikipedia.org/wiki/Isaac_Asimov_bibliography_$alphabetical$). Realistically even with 500+ books, this will be fast enough on Convex with the existing index, but let's consider how we could improve it anyway. [source]
In this query, we're efficiently using the index to find all the books called *Foundation* and then filtering through to find the one by Isaac Asimov. [source]
In this case, the best option is probably to create the separate `by_title` index to facilitate this query. [source]
Congrats! You now understand how queries and indexes work within Convex! [source]
See [*Indexes and Query Performance*](/database/reading-data/indexes/indexes-and-query-perf.md) to learn more, and [*Using TypeScript to Write Complex Query Filters*](https://stack.convex.dev/complex-filters-in-convex) for more advanced filtering strategies. [source]
This type includes information about what tables you have, the type of documents stored in those tables, and the indexes defined on them. [source]
* **`function_execution`** — emitted after every query, mutation, action, and HTTP action. Use the resource fields (`database_io_read_bytes`, `database_io_write_bytes`, `execution_time_ms`, `action_memory_used_mb`, `file_storage_read_bytes`, `network_egress_bytes`, `vector_search_query_bytes`, `text_search_query_bytes`, etc.) to compute compute and bandwidth usage per deployment in real time. * **`current_storage_usage`** — periodic snapshots of total document, index, vector, text, file, and backup storage bytes. Use these to track storage usage per deployment over time. [source]
<Stack.Screen name="index" /> [source]
export default function Index() { [source]
1. 1 search expression against the index's search field defined with [`.search`](/api/interfaces/server.SearchFilterBuilder.md#search). 2. 0 or more equality expressions against the index's filter fields defined with [`.eq`](/api/interfaces/server.SearchFilterFinalizer.md#eq). [source]
* An array of numbers (e.g. embedding) to use in the search. * The search will return the document IDs of the documents with the most similar stored vectors. * It must have the same length as the `dimensions` of the index. [source]
For indexes with multiple filter fields, you can also use `.or()` filters on different fields. Here's a filter for dishes whose cuisine is French or whose main ingredient is butter: [source]
In this tutorial we just touched on the very basics. It's ok to just stop here and go explore the rest of the docs, including [efficient queries via indexes](/database/reading-data/indexes/.md) and traversing [relationships through joins](/database/reading-data/.md#join). If you're deeply curious about how Convex works, you can read this [excellent deep dive](https://stack.convex.dev/how-convex-works). [source]
Read through the [indexes documentation](/database/reading-data/indexes/indexes-and-query-perf.md) for an overview of how to define indexes and how they work. [source]
Using `$gt`, `$lt`, `$gte`, or `$lte` is supported on indexed attributes with checked types: [source]
✅ **Correction**: Use comparison operators on indexed attributes [source]
- Missing an index - Fetching or transacting too much data - Expensive `where` clauses - Expensive permission rules that traverse a lot of data [source]
Even if you're not using comparison operators or order clauses, indexing attributes can still speed up queries that filter by that attribute. [source]
The `where` clause supports comparison operators on fields that are indexed and have checked types. [source]
The `where` clause supports `$like` on fields that are indexed with a checked `string` type. [source]
Once you have a sense of how long your queries and transactions take, you can iteratively optimize them. For example, you can use pagination or add indexes to speed up queries, or break up large transactions into smaller ones. [source]
**Expand for example User model with a @@unique block** [source]
* Every record of a model must be *uniquely* identifiable. You must define *at least* one of the following attributes per model: * [`@unique`](#unique) * [`@@unique`](#unique-1) * [`@id`](#id) * [`@@id`](#id-1) [source]
* A model can have any number of `@@unique` blocks [source]
The name of the `fields` argument on the `@@unique` attribute can be omitted: [source]
> ```prisma no-lines > @@unique(_ fields: FieldReference[], name: String?, map: String?, where: raw(String) | { field: value }?) > ``` [source]
For example, in the following schema, `MailBox` has a composite type, `addresses`, which has a `@@unique` constraint on the `email` field. [source]
* In **relational databases**, the ID can be a single field or based on multiple fields. If a model does not have an `@id` or an `@@id`, you must define a mandatory `@unique` field or `@@unique` block instead. * In **MongoDB**, an ID must be a single field that defines an `@id` attribute and a `@map("_id")` attribute. [source]
Unique attributes can be defined on a single field using [`@unique`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference#unique), or on multiple fields using [`@@unique`](https://www.prisma.io/docs/orm/v7/reference/prisma-schema-reference): [source]
While Prisma ORM lets you place `@unique` and `@@unique` attributes on views, the underlying database and Prisma do not enforce those constraints. Multiple rows can therefore share the same value for a supposedly unique field. [source]
.withIndex("by_author", (q) => q.eq("author", identity.email)) [source]

Related concepts

withIndex — is a instance of ORM index definitions; Convex index API
@@index — is a instance of ORM index definitions; Prisma schema index attributes