@vendredix/database-pg

PostgreSQL database module in TypeScript with custom transformers and language service.

TODO: Readme.

Examples

/* entities.ts */
import {Database, Column, constants} from "@vendredix/database-pg";
const {DATE_NOW_STRING} = constants;

@Database.Table()
export class AppGroup {
  @Database.AutoIncrement()
  @Database.PrimaryKey()
  groupId?: number;

  @Database.Column()
  name?: string;

  @Database.Column()
  description?: string;

  @Database.Column()
  created?: Date = DATE_NOW_STRING;
}


@Database.Table()
export class AppUser {
  @Database.AutoIncrement()
  @Database.PrimaryKey()
  userId: number;

  @Database.Unique()
  userName: string;

  @Database.Column()
  email: string | null;

  @Database.ForeignKey(AppGroup)
  @Database.Column()
  groupId: number = 1;

  @Database.Column()
  activated: boolean = false;
  
  @Database.Column({typeName: Column.TYPES.JSONB})
  preferences?: object | null;

  @Database.Method({
    language: "plpgsql",
    name: "user_hasPermission",
  })
  public static hasPermission(@Database.Parameter() userId: number, @Database.Parameter() permissionId: string): Promise<boolean> {
    return <never>`
    DECLARE res boolean;
    BEGIN
      RETURN TRUE;
    END;`;
  }

  public hasPermission(permissionId: string): Promise<boolean> {
    return AppUser.hasPermission(this.userId, permissionId);
  }
}


/* example.ts */
import {db} from "./database";

const QS_GroupUsers = db.compile<"groupId">(c => db.appUser
  .select("userName")
  .where(user => user.groupId === c.groupid)
);
const group1Users = await QS_GroupUsers.prepare({groupId: 1}).toListAsync();

/* Transpiles into:
const QS_GroupUsers = db.appUser
    .select("userName")
    .$where()`${_a => _a._columnAccess(0, "groupId")} = ${_a => _a.$id(c => c.groupid)}`
    .compile();
const group1Users = await QS_GroupUsers.prepare({ groupId: 1 }).toListAsync();
*/

const groupids = [1,2,3];
const users = await db.appUser.select()
  .join(db.appGroup)
  .where((user, group) => user.userName.toLowerCase() !== "test" && group.groupId in groupids)
  .toListAsync();

/* Transpiles into:
const groupids = [1,2,3];
const users = await db.appUser.select()
    .join(db.appGroup)
    .$where()`(LOWER(${_b => _b._columnAccess(0, "userName")}) <> 'test') AND (${_b => _b._columnAccess(1, "groupId")} IN ${groupids})`
    .toListAsync();
*/


await db.appUser.update()
  .setValues((user) => {
    user.someName = `User_${user.groupid}_${user.userName}`;
  })
  .where((user) => user.someName !== "")
  .executeAsync();

/* Transpiles into:
await db.appUser.update()
    .$setValues`${_a => _a._columnAccess(0, "someName")} = CONCAT('User_', ${_a => _a._columnAccess(0, "groupid")}, '_', ${_a => _a._columnAccess(0, "userName")})`
    .$where()`${_b => _b._columnAccess(0, "someName")} <> ''`
    .executeAsync();
*/