Can I get postgres to automatically generate ids for pairs of objects using typeorm?

Question

Background

Our node app uses a postgres database and interacts with it via TypeORM.

I know how to create a table with a primary key generated by a postgres sequence as described here:

import {Entity, PrimaryGeneratedColumn} from "typeorm";

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;
}

Goal

I am trying to create a table for objects that are always created in pairs; let's call the objects "shoes" and say that the two members of the pair are the left shoe and right shoe. I would like the left and right shoes to be separate entries in my table, and I want to be able to easily query for both shoes in a pair.

One approach would be something like this, which gives me an index on pairId and enforces only one left shoe and one right shoe for each pair (TypeORM index docs)

enum Foot {
    Left = "Left",
    Right = "Right"
}

@Entity()
@Index(["foot", "pairId"], { unique: true })
export class Shoe {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ type: "enum", enum: Foot })
    foot: Foot;

    @Column
    pairId: number
}

However, this approach I am still responsible for generating the values in the pairId field.

Question

Is there a way I can get postgres to generate the values in pairId for me? Perhaps using the same kind of sequence that is automatically generated by the @PrimaryGeneratedColumn decorator?

Update

Response below proposes a possible solution in postgres, though I would still need to know how to do this in either by using TypeORM's API or getting TypeORM to run the relevant raw SQL commands at the right time.

I could write a TypeORM migration to run the raw SQL, but I'm not sure how to get it to run on table creation -- it seems like the documentation primarily anticipates running them from the command line.

Adrian Klaver · Accepted Answer · 2020-07-04 21:51:26Z

1

Yes you can assign a sequence to column in Postgres. First use:

https://www.postgresql.org/docs/12/sql-createsequence.html

CREATE SEQUENCE my_seq OWNED BY shoes.pair_id

The OWNED BY is optional but 'ties' the sequence to the table so it is automatically dropped with the table.

Then you can make the sequence the DEFAULT for the column:

test(5432)=# create table shoes (pair_id integer DEFAULT nextval('my_seq'));
CREATE TABLE
test(5432)=# \d shoes 
                          Table "public.shoes"
 Column  |  Type   | Collation | Nullable |           Default           
---------+---------+-----------+----------+-----------------------------
 pair_id | integer |           |          | nextval('my_seq'::regclass)

Or for existing table:

test(5432)=# create table shoes (pair_id integer);
CREATE TABLE

test(5432)=# alter table shoes alter COLUMN pair_id SET DEFAULT nextval('my_seq');
ALTER TABLE

I have no idea how you would do that in TypeORM though.

answered Jul 4, 2020 at 21:51

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brahn Over a year ago

Thanks! That gets us at least partway there :-) Hopefully some TypeORM experts will know how to do this in TypeORM (or at least get TypeORM to execute these SQL commands at the right time.

Belayer · Accepted Answer · 2020-07-05 20:45:43Z

I do not have the foggiest idea of TypeORM, but is straight SQL just create a table with a compound key, a generated id and left/right value. You can then insert both via a single statement.

create table shoes (pair_id integer generated by default as identity
                    ,foot    text
                    ,constraint shoes_pk
                                primary key (pair_id, foot)
                    ,constraint foot_check 
                                check (lower (foot) = any('{left, right}') )
                    );                                          
 
with this_pair(id) as
   ( insert into shoes(foot) 
     values ('left')
     returning pair_id 
   )
insert into shoes(pair_id,foot)
  select id,'right'
    from this_pair
returning pair_id ; 
 
select * from shoes;

Collectives™ on Stack Overflow

Can I get postgres to automatically generate ids for pairs of objects using typeorm?

Background

Goal

Question

Update

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background

Goal

Question

Update

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related