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().select("id", "name");

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

// ✅ Use Puri.count() for counting
const result = await UserModel.getPuri()
  .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()
  .select({ count: Puri.count() })
  .where({ status: "active" });
const count = result[0].count;

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

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

// COUNT + GROUP BY
const stats = await OrderModel.getPuri()
  .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()
  .select("orders.id", "orders.total", "users.name")
  .join("users", "users.id", "orders.user_id");

LEFT JOIN:

const usersWithOrders = await UserModel.getPuri()
  .select("users.id", "users.name", "orders.total")
  .leftJoin("orders", "orders.user_id", "users.id");

Complex JOIN conditions (callback approach):

const result = await UserModel.getPuri()
  .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")
  .select("id")
  .where({ status: "active" });

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

FROM subquery:

const subquery = UserModel.getPuri("r")
  .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 Puri.rawString() and .whereRaw() for JSONB operators.JSONB property query (->>, ->):

// metadata->>'brand' = 'Samsung'
const products = await ProductModel.getPuri("r")
  .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 (@>):

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

JSONB key existence (?):

// metadata ? 'discount'
const productsWithDiscount = await ProductModel.getPuri("r")
  .select("*")
  .whereRaw("products.metadata ?? 'discount'");  // Use ?? instead of ?

Nested JSONB properties:

// metadata->'specs'->>'cpu'
const laptops = await ProductModel.getPuri("r")
  .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);

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()
  .select("*")
  .whereTsSearch("search_vector", "typescript database", {
    parser: "websearch_to_tsquery",
    config: "simple"
  });

Highlighting:

const posts = await PostModel.getPuri()
  .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: Model’s transaction method

await UserModel.transaction(async (trx) => {
  const user = await UserModel.save({ name: "John" }, { trx });
  const profile = await ProfileModel.save({ user_id: user.id }, { trx });
  return user;
});

Method 2: DB.transact

import { DB } from "sonamu";

await DB.transact("w", async (trx) => {
  await trx("users").insert({ name: "John" });
  await trx("profiles").insert({ user_id: 1 });
});

Auto-rollback on error.

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

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Puri Query Builder

​Migrations

​PostgreSQL Specific

​Transactions

​Related Documentation

Puri Query Builder

Migrations

PostgreSQL Specific

Transactions

Related Documentation