> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Questions about database and queries

## Puri Query Builder

<AccordionGroup>
  <Accordion title="Does Puri directly implement Promise?">
    Yes, Puri directly implements the `Promise` interface.

    ```typescript theme={null}
    // Internal implementation in puri.ts
    export class Puri<TSchema, TTables, TResult> implements Promise<TResult[]> {
      then<TResult1 = TResult[], TResult2 = never>(
        onfulfilled?: ((value: TResult[]) => TResult1 | PromiseLike<TResult1>) | null,
        onrejected?: ((reason: any) => TResult2 | PromiseLike<TResult2>) | null
      ): Promise<TResult1 | TResult2> {
        return this.knexQuery.then(onfulfilled, onrejected);
      }

      catch<TResult2 = never>(...): Promise<TResult[] | TResult2> { }
      finally(onfinally?: (() => void) | null): Promise<TResult[]> { }
    }
    ```

    **Usage:**

    ```typescript theme={null}
    // ✅ Use await directly (returns array)
    const users = await UserModel.getPuri("r").table("users").select("id", "name");

    // ✅ Use .first() for single object query
    const user = await UserModel.getPuri("r").table("users").select("id", "name").where({ id: 1 }).first();

    // ✅ Use Puri.count() for counting
    const result = await UserModel.getPuri("r")
      .table("users")
      .select({ count: Puri.count() })
      .where({ status: "active" });
    const count = result[0].count;
    ```

    **Note:**

    * Methods like `.getMany()`, `.getOne()`, `.getScalar()` **do not exist**
    * Always returns an array (`Expand<TResult>[]`)
    * Use `.first()` for single object
  </Accordion>

  <Accordion title="How do I use aggregate functions in Puri?">
    Use static methods like `Puri.count()`, `Puri.sum()`, `Puri.avg()`.

    ```typescript theme={null}
    // COUNT
    const result = await UserModel.getPuri("r")
      .table("users")
      .select({ count: Puri.count() })
      .where({ status: "active" });
    const count = result[0].count;

    // SUM
    const result = await OrderModel.getPuri("r")
      .table("orders")
      .select({ total: Puri.sum("amount") })
      .where({ status: "completed" });
    const total = result[0].total;

    // AVG
    const result = await ProductModel.getPuri("r")
      .table("products")
      .select({ avg_price: Puri.avg("price") });
    const avgPrice = result[0].avg_price;

    // COUNT + GROUP BY
    const stats = await OrderModel.getPuri("r")
      .table("orders")
      .select("user_id", { count: Puri.count(), total: Puri.sum("amount") })
      .groupBy("user_id");
    ```
  </Accordion>

  <Accordion title="How do I use JOIN in Puri?">
    **INNER JOIN:**

    ```typescript theme={null}
    const ordersWithUser = await OrderModel.getPuri("r")
      .table("orders")
      .select("orders.id", "orders.total", "users.name")
      .join("users", "users.id", "orders.user_id");
    ```

    **LEFT JOIN:**

    ```typescript theme={null}
    const usersWithOrders = await UserModel.getPuri("r")
      .table("users")
      .select("users.id", "users.name", "orders.total")
      .leftJoin("orders", "orders.user_id", "users.id");
    ```

    **Complex JOIN conditions (callback approach):**

    ```typescript theme={null}
    const result = await UserModel.getPuri("r")
      .table("users")
      .select("users.*", "orders.total")
      .leftJoin("orders", (j) => {
        j.on("orders.user_id", "users.id").on("orders.status", "=", "completed");
      });
    ```
  </Accordion>

  <Accordion title="How do I use subqueries in Puri?">
    **WHERE IN subquery:**

    ```typescript theme={null}
    const activeUserIds = UserModel.getPuri("r").table("users").select("id").where({ status: "active" });

    const orders = await OrderModel.getPuri("r").table("orders").select("*").whereIn("user_id", activeUserIds);
    ```

    **FROM subquery:**

    ```typescript theme={null}
    const subquery = UserModel.getPuri("r")
      .table("users")
      .select("user_id", { count: Puri.count() })
      .groupBy("user_id");

    const result = await new Puri(knex, { sq: subquery })
      .select("sq.user_id", "sq.count")
      .where("sq.count", ">", 10);
    ```
  </Accordion>
</AccordionGroup>

***

## Migrations

<AccordionGroup>
  <Accordion title="How do I create and run migrations?">
    **1. Modify Entity**

    Edit Entity in Sonamu UI

    **2. Generate migration**

    ```bash theme={null}
    # In Sonamu UI's DB Migration tab
    # → Check Prepared Migration Codes
    # → Click Generate
    ```

    Or via CLI:

    ```bash theme={null}
    pnpm sonamu migrate status
    # → Check migrations that need to be generated
    ```

    **3. Run migration**

    ```bash theme={null}
    # Development environment
    pnpm sonamu migrate run

    # Production
    NODE_ENV=production pnpm sonamu migrate run

    # Rollback
    pnpm sonamu migrate rollback
    ```
  </Accordion>

  <Accordion title="How do I safely test migrations?">
    Use Shadow DB for testing:

    ```bash theme={null}
    # Shadow DB test
    pnpm sonamu migrate shadow-test
    # → Creates temporary Shadow DB
    # → Applies Migration
    # → Confirms success
    # → Deletes Shadow DB
    ```

    Shadow DB configuration:

    ```typescript theme={null}
    // sonamu.config.ts
    export default {
      database: {
        connections: {
          main: {
            // ...
          },
          shadow: {
            host: "localhost",
            port: 5432,
            database: "myapp_shadow",
            user: "postgres",
            password: "password",
          },
        },
      },
    } satisfies SonamuConfig;
    ```
  </Accordion>

  <Accordion title="How do I safely migrate existing data when changing column types?">
    Use a temporary column to transform data, then replace the original column.

    **Example: string → integer conversion**

    ```typescript theme={null}
    // migrations/20250115_change_status_type.ts
    import type { Knex } from "knex";

    export async function up(knex: Knex): Promise<void> {
      await knex.schema.alterTable("products", (table) => {
        // 1. Create temporary column (new type)
        table.integer("status_new");
      });

      // 2. Transform data
      await knex.raw(`
        UPDATE products
        SET status_new = CASE
          WHEN status = 'active' THEN 1
          WHEN status = 'inactive' THEN 2
          WHEN status = 'pending' THEN 3
          ELSE 0
        END
      `);

      // 3. Drop old column
      await knex.schema.alterTable("products", (table) => {
        table.dropColumn("status");
      });

      // 4. Rename new column
      await knex.schema.alterTable("products", (table) => {
        table.renameColumn("status_new", "status");
      });

      // 5. Add NOT NULL constraint (optional)
      await knex.schema.alterTable("products", (table) => {
        table.integer("status").notNullable().alter();
      });
    }

    export async function down(knex: Knex): Promise<void> {
      // Rollback: reverse order restoration
      await knex.schema.alterTable("products", (table) => {
        table.string("status_old", 20);
      });

      await knex.raw(`
        UPDATE products
        SET status_old = CASE
          WHEN status = 1 THEN 'active'
          WHEN status = 2 THEN 'inactive'
          WHEN status = 3 THEN 'pending'
          ELSE 'unknown'
        END
      `);

      await knex.schema.alterTable("products", (table) => {
        table.dropColumn("status");
        table.renameColumn("status_old", "status");
      });
    }
    ```
  </Accordion>

  <Accordion title="How do I preserve data when renaming columns?">
    Entity auto-generation creates DROP/ADD, so **manually use RENAME**:

    ```typescript theme={null}
    // Modify auto-generated Migration file after Entity change
    export async function up(knex: Knex): Promise<void> {
      await knex.schema.alterTable("users", (table) => {
        // ❌ table.dropColumn("old_name");
        // ❌ table.string("new_name");

        // ✅ Use RENAME (preserves data)
        table.renameColumn("old_name", "new_name");
      });
    }

    export async function down(knex: Knex): Promise<void> {
      await knex.schema.alterTable("users", (table) => {
        table.renameColumn("new_name", "old_name");
      });
    }
    ```
  </Accordion>

  <Accordion title="How do I modify columns with FK constraints?">
    Drop FK constraint, modify column, recreate FK:

    ```typescript theme={null}
    export async function up(knex: Knex): Promise<void> {
      await knex.schema.alterTable("orders", (table) => {
        // 1. Drop FK constraint
        table.dropForeign(["user_id"]);
      });

      // 2. Change column type
      await knex.raw(`
        ALTER TABLE orders
        ALTER COLUMN user_id TYPE BIGINT USING user_id::BIGINT
      `);

      await knex.schema.alterTable("orders", (table) => {
        // 3. Recreate FK constraint
        table
          .foreign("user_id")
          .references("id")
          .inTable("users")
          .onDelete("CASCADE")
          .onUpdate("CASCADE");
      });
    }

    export async function down(knex: Knex): Promise<void> {
      await knex.schema.alterTable("orders", (table) => {
        table.dropForeign(["user_id"]);
      });

      await knex.raw(`
        ALTER TABLE orders
        ALTER COLUMN user_id TYPE INTEGER USING user_id::INTEGER
      `);

      await knex.schema.alterTable("orders", (table) => {
        table.foreign("user_id").references("id").inTable("users");
      });
    }
    ```
  </Accordion>
</AccordionGroup>

***

## PostgreSQL Specific

<AccordionGroup>
  <Accordion title="How do I use PostgreSQL JSONB operators in Puri?">
    Use `Puri.rawString()` and `.whereRaw()` for JSONB operators.

    **JSONB property query (->>, ->):**

    ```typescript theme={null}
    // metadata->>'brand' = 'Samsung'
    const products = await ProductModel.getPuri("r")
      .table("products")
      .select({
        id: "products.id",
        name: "products.name",
        brand: Puri.rawString("products.metadata->>'brand'"),
        tags: Puri.rawStringArray("products.metadata->'tags'"),
      })
      .whereRaw("products.metadata->>'brand' = ?", ["Samsung"]);
    ```

    **JSONB containment operator (@>):**

    ```typescript theme={null}
    // metadata @> '{"warranty": 2}'
    const productsWithWarranty = await ProductModel.getPuri("r")
      .table("products")
      .select("*")
      .whereRaw("products.metadata @> ?", [JSON.stringify({ warranty: 2 })]);
    ```

    **JSONB key existence (?):**

    ```typescript theme={null}
    // metadata ? 'discount'
    const productsWithDiscount = await ProductModel.getPuri("r")
      .table("products")
      .select("*")
      .whereRaw("products.metadata ?? 'discount'"); // Use ?? instead of ?
    ```

    **Nested JSONB properties:**

    ```typescript theme={null}
    // metadata->'specs'->>'cpu'
    const laptops = await ProductModel.getPuri("r")
      .table("products")
      .select({
        id: "products.id",
        name: "products.name",
        cpu: Puri.rawString("products.metadata->'specs'->>'cpu'"),
        ram: Puri.rawString("products.metadata->'specs'->>'ram'"),
      })
      .whereRaw("products.metadata->'specs'->>'cpu' LIKE ?", ["%Intel%"]);
    ```

    **Notes:**

    * `->>` returns text, `->` returns JSONB
    * Use parameter binding to prevent SQL Injection
    * GIN index recommended: `CREATE INDEX ON products USING gin(metadata);`
  </Accordion>

  <Accordion title="How do I use PostgreSQL Full-Text Search?">
    **Create ts\_vector column:**

    ```typescript theme={null}
    // Entity definition
    {
      "name": "search_vector",
      "type": "customType",
      "id": "TsVectorType",
      "nullable": true
    }
    ```

    **Search query:**

    ```typescript theme={null}
    const posts = await PostModel.getPuri("r")
      .table("posts")
      .select("*")
      .whereTsSearch("search_vector", "typescript database", {
        parser: "websearch_to_tsquery",
        config: "simple",
      });
    ```

    **Highlighting:**

    ```typescript theme={null}
    const posts = await PostModel.getPuri("r")
      .table("posts")
      .select({
        id: "posts.id",
        title: Puri.tsHighlight("posts.title", "typescript"),
        content: Puri.tsHighlight("posts.content", "typescript", {
          startSel: "<mark>",
          stopSel: "</mark>",
          maxFragments: 3,
        }),
      })
      .whereTsSearch("search_vector", "typescript");
    ```
  </Accordion>
</AccordionGroup>

***

## Transactions

<AccordionGroup>
  <Accordion title="How do I use transactions?">
    **Method 1: Manual transaction (`getPuri("w").transaction()`)**

    ```typescript theme={null}
    class UserModel extends BaseModelClass {
      async createWithProfile(data: { name: string; bio: string }) {
        const wdb = this.getPuri("w");

        return wdb.transaction(async (trx) => {
          trx.ubRegister("users", { name: data.name });
          const [userId] = await trx.ubUpsert("users");

          trx.ubRegister("profiles", { user_id: userId, bio: data.bio });
          await trx.ubUpsert("profiles");

          return userId;
        });
      }
    }
    ```

    **Method 2: `@transactional()` decorator**

    ```typescript theme={null}
    import { transactional } from "sonamu";

    class UserModel extends BaseModelClass {
      @transactional()
      async createWithProfile(data: { name: string; bio: string }) {
        // The entire method body runs inside a transaction automatically.
        const [userId] = await UserModel.save([{ name: data.name }]);
        await ProfileModel.save([{ user_id: userId, bio: data.bio }]);
        return userId;
      }
    }
    ```

    **Auto-rollback on error.** See [Manual Transactions](/en/database/transactions/manual-transactions) and [@transactional Decorator](/en/database/transactions/transactional-decorator) for details.
  </Accordion>
</AccordionGroup>

***

## Related Documentation

* [Puri Query Builder](/en/database/puri/basic-queries)
* [Puri Advanced Queries](/en/database/puri/advanced-queries)
* [Migration Basics](/en/database/migrations/migration-basics)
* [Migration Strategies](/en/database/migrations/migration-strategies)
* [Transactions](/en/database/transactions)