Database

Puri Query Builder

Does Puri directly implement Promise?

Yes, Puri directly implements the Promise interface.

// 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:

// ✅ 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

How do I use aggregate functions in Puri?

Use static methods like Puri.count(), Puri.sum(), Puri.avg().

// 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");

How do I use JOIN in Puri?

INNER JOIN:

const ordersWithUser = await OrderModel.getPuri("r")
  .table("orders")
  .select("orders.id", "orders.total", "users.name")
  .join("users", "users.id", "orders.user_id");

LEFT JOIN:

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):

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");
  });

How do I use subqueries in Puri?

WHERE IN subquery:

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:

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);

Migrations

How do I create and run migrations?

1. Modify EntityEdit Entity in Sonamu UI2. Generate migration

# In Sonamu UI's DB Migration tab
# → Check Prepared Migration Codes
# → Click Generate

Or via CLI:

pnpm sonamu migrate status
# → Check migrations that need to be generated

3. Run migration

# Development environment
pnpm sonamu migrate run

# Production
NODE_ENV=production pnpm sonamu migrate run

# Rollback
pnpm sonamu migrate rollback

How do I safely test migrations?

Use Shadow DB for testing:

# Shadow DB test
pnpm sonamu migrate shadow-test
# → Creates temporary Shadow DB
# → Applies Migration
# → Confirms success
# → Deletes Shadow DB

Shadow DB configuration:

// sonamu.config.ts
export default {
  database: {
    connections: {
      main: {
        // ...
      },
      shadow: {
        host: "localhost",
        port: 5432,
        database: "myapp_shadow",
        user: "postgres",
        password: "password",
      },
    },
  },
} satisfies SonamuConfig;

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

// 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");
  });
}

How do I preserve data when renaming columns?

Entity auto-generation creates DROP/ADD, so manually use RENAME:

// 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");
  });
}

How do I modify columns with FK constraints?

Drop FK constraint, modify column, recreate FK:

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");
  });
}

PostgreSQL Specific

How do I use PostgreSQL JSONB operators in Puri?

Use jsonText() for text extraction, .whereJsonSupersetOf() for containment, and .whereJsonKeyExists() for top-level key existence. Use Puri.rawString() and parameterized .whereRaw() only for JSONB operations that these helpers do not support.JSONB text extraction (->>):

const query = ProductModel.getPuri("r").table("products");
const brand = query.jsonText("products.metadata", "brand");

const products = await query.select({
  id: "products.id",
  name: "products.name",
  brand, // string | null
  brandWithFallback: Puri.coalesce(brand, "Unknown"), // string
});

jsonText() also accepts multiple path segments for nested text. A missing path or JSON null produces SQL NULL, so a direct selection is string | null; Puri.coalesce() provides a non-null fallback.JSONB containment operator (@>):

// metadata @> '{"warranty": 2}'
const productsWithWarranty = await ProductModel.getPuri("r")
  .table("products")
  .select({
    id: "products.id",
    name: "products.name",
  })
  .whereJsonSupersetOf("products.metadata", { warranty: 2 });

The value is serialized and bound internally, so do not call JSON.stringify() yourself. For an OR condition inside parentheses, use orWhereJsonSupersetOf() on a whereGroup().JSONB key existence (?):

// metadata ? 'discount'
const productsWithDiscount = await ProductModel.getPuri("r")
  .table("products")
  .select("*")
  .whereJsonKeyExists("products.metadata", "discount");

whereJsonKeyExists() checks only top-level keys. A key still exists when its JSON value is null.Nested JSONB properties:

// metadata->'specs'->>'cpu'
const query = ProductModel.getPuri("r").table("products");
const laptops = await query.select({
  id: "products.id",
  name: "products.name",
  cpu: query.jsonText("products.metadata", "specs", "cpu"),
  ram: query.jsonText("products.metadata", "specs", "ram"),
});

Notes:

->> returns text, -> returns JSONB
For unsupported operations, use raw expressions with parameter binding to prevent SQL injection
GIN index recommended: CREATE INDEX ON products USING gin(metadata);

How do I use PostgreSQL Full-Text Search?

Create ts_vector column:

// Entity definition
{
  "name": "search_vector",
  "type": "customType",
  "id": "TsVectorType",
  "nullable": true
}

Search query:

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

Highlighting:

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");

Transactions

How do I use transactions?

Method 1: Manual transaction (getPuri("w").transaction())

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

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 and @transactional Decorator for details.

Get Started

Core Concepts

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

Database

Puri Query Builder

Migrations

PostgreSQL Specific

Transactions

​Puri Query Builder

​Migrations

​PostgreSQL Specific

​Transactions

​Related Documentation

Puri Query Builder

Migrations

PostgreSQL Specific

Transactions

Related Documentation