Question

0

671

Visualizações

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

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.

over 4 years ago · Santiago Trujillo

2 Respostas

Responde à pergunta

0

Responde à pergunta

Encontrar trabalhos remotos

Answer 1 · 2022-05-11T21:10:16.867Z

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.

Answer 2 · 2022-05-11T21:10:16.876Z

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;

0

671