[WIP] AWS RDS Postgresql Abstraction

[sam-goodwin]

Working on an abstraction for Postgresql.

Create tables:

class User extends Record({
  userId: string,
  age: number
}) {}

class Friend extends Record({
  userId: string,
  friendId: string
}) {}

const UserTable = new Table({
  tableName: 'user',
  // columns of the table
  type: User,
  // could also be an array of columns for a composite key
  primaryKey: 'userId',
  indexes: [{
    fields: 'userId',
    // optional: condition for partial index
    where: _ => Bool.or(
      _.userId.equals('sam'),
      _.userId.like('sam%')
    )
  }]
});

// no primary key or indexes
const FriendsTable = new Table({
  tableName: 'friends',
  type: Friend,
});

Type-safe queries:

// select * from users where userId = 'userId'
UserTable.get({
  userId: 'userId'
});

// select age as a from users where age = 0
UserTable
  .where(_ => _.age.equals(0))
  .select(_ => ({a: _.user.age}));

/*
select user.userId, f.friendId, avg(users.age) as avg from users
join friends f on user.userId = f.friendId
where user.age = 0 & f.friendId like '%sam'
group by user.userId, f.friendId
having avg(user.age) = 0
*/
Query
  .from(UserTable)
  .join(FriendsTable, { as: 'f', on: _ => _.user.userId.equals(_.f.friendId) })
  .where(_ => Bool.and(
    _.user.age.equals(0),
    _.f.friendId.like('%sam')
  ))
  .groupBy(_ => [_.user.userId, _.f.friendId])
  .having(_ => Agg.avg(_.map(_ => _.user.age)).equals(0))
  .select(([userId, friendId], rows) => ({
    userId,
    friendId,
    avg: Agg.avg(rows.map(_ => _.user.age))
  }))
  ;

Queries are a little bit backwards when compared to SQL because select is at the end of of the statement. Depending on what is easier to read, there exists a select function that attempts to mirror a SQL clause's order of caluses - select, from, join, where, group by, having.

select('*', from(UserTable).where(_ => _.user.age.equals(0)));

select(([userId], _) => ({
  userId,
}),
from(UserTable).
join(FriendsTable).
where(_ => _.user.age.equals(0)).
groupBy(_ => [_.user.userId]));

select(([userId], _) => ({
  userId,
}),
from(UserTable)
  .join(FriendsTable)
  .where(_ => _.user.age.equals(0))
  .groupBy(_ => [_.user.userId])
);

Write queries in SQL and convert to a type.

sql`select * from ${UserTable}`.as(User);