Database

Type-safe database operations with DatabaseReader and DatabaseWriter

Confect provides type-safe database access through the DatabaseReader and DatabaseWriter services, built on top of Convex’s real-time database with full Effect integration.

Overview

DatabaseReader

Query data with full type safety and real-time updates

DatabaseWriter

Insert, update, and delete documents transactionally

Schema Validation

Runtime validation with Effect Schema

Indexes

Efficient queries with secondary indexes

DatabaseReader

The DatabaseReader service provides read-only access to your database. It’s available in queries, mutations, and actions.

Basic Queries

import { DatabaseReader } from "@confect/server";
import { Effect } from "effect";

const listUsers = Effect.gen(function* () {
  const db = yield* DatabaseReader<typeof databaseSchema>();
  
  // Get all users
  const users = yield* db
    .table("users")
    .collect();
  
  return users;
});

Get by ID

import type { Id } from "convex/values";

const getUser = (userId: Id<"users">) =>
  Effect.gen(function* () {
    const db = yield* DatabaseReader<typeof databaseSchema>();
    
    const user = yield* db
      .table("users")
      .get(userId);
    
    return user;
  });

The get method returns an Effect that fails with GetByIdFailure if the document doesn’t exist.

Filtering

const activeUsers = Effect.gen(function* () {
  const db = yield* DatabaseReader<typeof databaseSchema>();
  
  const users = yield* db
    .table("users")
    .filter((q) => q.eq(q.field("status"), "active"))
    .collect();
  
  return users;
});

Using Indexes

Indexes provide efficient queries for specific fields:

const getUserByEmail = (email: string) =>
  Effect.gen(function* () {
    const db = yield* DatabaseReader<typeof databaseSchema>();
    
    // Query using an index
    const user = yield* db
      .table("users")
      .get("by_email", email);
    
    return user;
  });

const getPostsByAuthor = (authorId: string) =>
  Effect.gen(function* () {
    const db = yield* DatabaseReader<typeof databaseSchema>();
    
    const posts = yield* db
      .table("posts")
      .index("by_author", (q) => q.eq("authorId", authorId))
      .collect();
    
    return posts;
  });

Define indexes in your table schema using the .index() method. See the Schema section for details.

Ordering Results

const recentPosts = Effect.gen(function* () {
  const db = yield* DatabaseReader<typeof databaseSchema>();
  
  const posts = yield* db
    .table("posts")
    .index("by_creation_time") // Default index
    .order("desc")
    .take(10)
    .collect();
  
  return posts;
});

Pagination

const paginatedUsers = (cursor: string | null, numItems = 20) =>
  Effect.gen(function* () {
    const db = yield* DatabaseReader<typeof databaseSchema>();
    
    const result = yield* db
      .table("users")
      .paginate({ numItems, cursor });
    
    return {
      page: result.page,
      isDone: result.isDone,
      continueCursor: result.continueCursor,
    };
  });

Query Composition

Build complex queries by chaining methods:

const searchUserPosts = (userId: string, tag: string) =>
  Effect.gen(function* () {
    const db = yield* DatabaseReader<typeof databaseSchema>();
    
    const posts = yield* db
      .table("posts")
      .index("by_author", (q) => q.eq("authorId", userId))
      .filter((q) => q.eq(q.field("tag"), tag))
      .order("desc")
      .take(20)
      .collect();
    
    return posts;
  });

DatabaseWriter

The DatabaseWriter service provides write access to your database. It’s only available in mutations.

Insert

Create new documents:

import { DatabaseWriter } from "@confect/server";
import { Effect } from "effect";

const createUser = (name: string, email: string) =>
  Effect.gen(function* () {
    const db = yield* DatabaseWriter<typeof databaseSchema>();
    
    const userId = yield* db.table("users").insert({
      name,
      email,
      status: "active",
      createdAt: Date.now(),
    });
    
    return userId;
  });

The insert method returns the ID of the newly created document.

Patch

Update specific fields of a document:

import type { Id } from "convex/values";

const updateUserStatus = (userId: Id<"users">, status: string) =>
  Effect.gen(function* () {
    const db = yield* DatabaseWriter<typeof databaseSchema>();
    
    yield* db.table("users").patch(userId, {
      status,
      updatedAt: Date.now(),
    });
  });

patch only updates the specified fields, leaving others unchanged.

Replace

Replace an entire document:

const replaceUser = (userId: Id<"users">, userData: UserData) =>
  Effect.gen(function* () {
    const db = yield* DatabaseWriter<typeof databaseSchema>();
    
    yield* db.table("users").replace(userId, {
      name: userData.name,
      email: userData.email,
      status: userData.status,
      updatedAt: Date.now(),
    });
  });

replace overwrites all fields except system fields (_id, _creationTime). Make sure to include all required fields.

Delete

Remove a document:

const deleteUser = (userId: Id<"users">) =>
  Effect.gen(function* () {
    const db = yield* DatabaseWriter<typeof databaseSchema>();
    
    yield* db.table("users").delete(userId);
  });

Transactional Operations

All writes in a mutation are transactional:

const transferPoints = (fromUserId: Id<"users">, toUserId: Id<"users">, amount: number) =>
  Effect.gen(function* () {
    const dbRead = yield* DatabaseReader<typeof databaseSchema>();
    const dbWrite = yield* DatabaseWriter<typeof databaseSchema>();
    
    // Read current balances
    const fromUser = yield* dbRead.table("users").get(fromUserId);
    const toUser = yield* dbRead.table("users").get(toUserId);
    
    // Validate
    if (fromUser.points < amount) {
      return yield* Effect.fail(new Error("Insufficient points"));
    }
    
    // Update both users (transactional)
    yield* dbWrite.table("users").patch(fromUserId, {
      points: fromUser.points - amount,
    });
    
    yield* dbWrite.table("users").patch(toUserId, {
      points: toUser.points + amount,
    });
    
    return { success: true };
  });

If any operation fails, the entire mutation is rolled back automatically.

Schema Definition

Define your database schema with type safety:

schema.ts

import { DatabaseSchema, Table } from "@confect/server";
import { Schema } from "effect";

const usersTable = Table.make(
  "users",
  Schema.Struct({
    name: Schema.String,
    email: Schema.String,
    status: Schema.Literal("active", "inactive"),
    points: Schema.Number,
    createdAt: Schema.Number,
  })
)
  .index("by_email", ["email"])
  .index("by_status", ["status", "createdAt"]);

const postsTable = Table.make(
  "posts",
  Schema.Struct({
    title: Schema.String,
    content: Schema.String,
    authorId: Schema.String,
    tag: Schema.optional(Schema.String),
    createdAt: Schema.Number,
  })
)
  .index("by_author", ["authorId", "createdAt"])
  .searchIndex("search_title", {
    searchField: "title",
    filterFields: ["authorId"],
  });

export const databaseSchema = DatabaseSchema.make()
  .addTable(usersTable)
  .addTable(postsTable);

Index Types

Standard Index

Fast equality and range queries on specific fields

Search Index

Full-text search on text fields

Vector Index

Similarity search for embeddings

Standard Indexes

const table = Table.make(
  "products",
  Schema.Struct({
    name: Schema.String,
    category: Schema.String,
    price: Schema.Number,
  })
)
  .index("by_category", ["category"])
  .index("by_price", ["price"]);

Search Indexes

const table = Table.make(
  "articles",
  Schema.Struct({
    title: Schema.String,
    content: Schema.String,
    authorId: Schema.String,
  })
)
  .searchIndex("search_content", {
    searchField: "content",
    filterFields: ["authorId"],
  });

// Usage
const searchArticles = (query: string) =>
  Effect.gen(function* () {
    const db = yield* DatabaseReader<typeof databaseSchema>();
    
    const results = yield* db
      .table("articles")
      .search("search_content", (q) => q.search("content", query))
      .collect();
    
    return results;
  });

Vector Indexes

const table = Table.make(
  "documents",
  Schema.Struct({
    text: Schema.String,
    embedding: Schema.Array(Schema.Number),
  })
)
  .vectorIndex("by_embedding", {
    vectorField: "embedding",
    dimensions: 1536,
  });

Use vector indexes with the VectorSearch service in actions. See the Search documentation for details.

System Tables

Confect provides access to Convex system tables:

const listScheduledFunctions = Effect.gen(function* () {
  const db = yield* DatabaseReader<typeof databaseSchema>();
  
  const scheduled = yield* db
    .table("_scheduled_functions")
    .collect();
  
  return scheduled;
});

const listStoredFiles = Effect.gen(function* () {
  const db = yield* DatabaseReader<typeof databaseSchema>();
  
  const files = yield* db
    .table("_storage")
    .collect();
  
  return files;
});

Error Handling

Database operations can fail with specific errors:

import { GetByIdFailure } from "@confect/server";

const getUserSafe = (userId: Id<"users">) =>
  Effect.gen(function* () {
    const db = yield* DatabaseReader<typeof databaseSchema>();
    
    const user = yield* db
      .table("users")
      .get(userId)
      .pipe(
        Effect.catchTag("GetByIdFailure", () =>
          Effect.succeed(null)
        )
      );
    
    return user;
  });

Best Practices

1. Use Indexes Wisely

   Create indexes for fields you query frequently. Each index adds overhead to writes.

2. Keep Transactions Small

   Mutations should complete quickly. Break large operations into multiple mutations.

3. Validate Input

   Use Effect Schema to validate data before inserting or updating.

4. Handle Errors

   Always handle database errors explicitly using Effect’s error handling.

Queries have read-only access to the database. Attempting to use DatabaseWriter in a query will fail.

Next Steps

Functions

Learn about queries and mutations

Search

Full-text and vector search

Schema

Schema design patterns

Storage

Store and retrieve files