JSPM

const users = await querier.findMany(User, {
  $select: { email: true, profile: { $select: { picture: true } } },
  $where: { email: { $endsWith: '@domain.com' } },
  $sort: { createdAt: 'desc' },
  $limit: 100,
});

import { v7 as uuidv7 } from 'uuid';
import { Entity, Id, Field, OneToOne, OneToMany, ManyToOne, ManyToMany, type Relation } from '@uql/core';

@Entity()
export class User {
  @Id({ onInsert: () => uuidv7() })
  id?: string;

  @Field({ length: 100, index: true })
  name?: string;

  @Field({ unique: true, comment: 'User login email' })
  email?: string;

  @OneToOne({ entity: () => Profile, mappedBy: 'user', cascade: true })
  profile?: Relation<Profile>; // Relation<T> handles circular dependencies

  @OneToMany({ entity: () => Post, mappedBy: 'author' })
  posts?: Relation<Post>[];
}

@Entity()
export class Profile {
  @Id({ onInsert: () => uuidv7() })
  id?: string;

  @Field()
  bio?: string;

  @Field({ reference: () => User })
  userId?: string;

  @OneToOne({ entity: () => User })
  user?: User;
}

@Entity()
export class Post {
  @Id()
  id?: number;

  @Field()
  title?: string;

  @Field({ reference: () => User })
  authorId?: string;

  @ManyToOne({ entity: () => User })
  author?: User;

  @ManyToMany({ entity: () => Tag, through: () => PostTag })
  tags?: Tag[];
}

@Entity()
export class Tag {
  @Id({ onInsert: () => uuidv7() })
  id?: string;

  @Field()
  name?: string;
}

@Entity()
export class PostTag {
  @Id({ onInsert: () => uuidv7() })
  id?: string;

  @Field({ reference: () => Post })
  postId?: number;

  @Field({ reference: () => Tag })
  tagId?: string;
}

// file: ./shared/orm.ts
import { SnakeCaseNamingStrategy } from '@uql/core';
import { PgQuerierPool } from '@uql/core/postgres';

export const pool = new PgQuerierPool(
  {
    host: 'localhost',
    user: 'theUser',
    password: 'thePassword',
    database: 'theDatabase',
    min: 1,
    max: 10,
  },
  // Extra options (optional).
  {
    // Pass any custom logger function here (optional).
    logger: console.debug,
    // Pass a naming strategy here (optional, by default no automatic names translation).
    // This affects both queries and schema generation.
    // E.g. `SnakeCaseNamingStrategy` automatically translate between TypeScript camelCase and database snake_case.
    namingStrategy: new SnakeCaseNamingStrategy()
  },
);

import { User } from './shared/models/index.js';
import { pool } from './shared/orm.js';

// Get a querier from the pool
const querier = await pool.getQuerier();

try {
  // Advanced querying with relations and virtual fields
  const users = await querier.findMany(User, {
    $select: {
      id: true,
      name: true,
      profile: ['picture'], // Select specific fields from a 1-1 relation
      tagsCount: true       // Virtual field (calculated at runtime)
    },
    $where: {
      email: { $iincludes: '@example.com' }, // Case-insensitive search
      status: 'active'
    },
    $sort: { createdAt: 'desc' },
    $skip: 10
    $limit: 10,
  });
} finally {
  // Always release the querier to the pool
  await querier.release();
}

import { pool } from './shared/orm.js';
import { User } from './shared/models/index.js';

const querier = await pool.getQuerier();

try {
  const authorsWithPopularPosts = await querier.findMany(User, {
    $select: {
      id: true,
      name: true,
      profile: {
        $select: ['bio'],
        // Filter related record and enforce INNER JOIN
        $where: { bio: { $ne: null } },
        $required: true
      },
      posts: {
        $select: ['title', 'createdAt'],
        // Filter the related collection directly
        $where: { title: { $iincludes: 'typescript' } },
        $sort: { createdAt: 'desc' },
      }
    },
    $where: {
      name: { $istartsWith: 'a' }
    }
  });
} finally {
  await querier.release();
}

import { v7 as uuidv7 } from 'uuid';
import { Entity, Id, Field, raw } from '@uql/core';
import { ItemTag } from './shared/models/index.js';

@Entity()
export class Item {
  @Id()
  id: number;

  @Field()
  name: string;

  @Field({
    virtual: raw(({ ctx, dialect, escapedPrefix }) => {
      ctx.append('(');
      dialect.count(ctx, ItemTag, {
        $where: {
          itemId: raw(({ ctx }) => ctx.append(`${escapedPrefix}.id`))
        }
      }, { autoPrefix: true });
      ctx.append(')');
    })
  })
  tagsCount?: number;
}

import { pool } from './shared/orm.js';
import { User, Profile } from './shared/models/index.js';

const result = await pool.transaction(async (querier) => {
  const user = await querier.findOne(User, { $where: { email: '...' } });
  const profileId = await querier.insertOne(Profile, { userId: user.id, ... });
  return { userId: user.id, profileId };
});
// Connection is automatically released after a transaction.

import { Migrator } from '@uql/core/migrate';
import { pool } from './shared/orm.js';

const migrator = new Migrator(pool);
await migrator.autoSync({ logging: true });