Shared columns?

[magick93]

Hi

I'm interested to know how I might create a collection of columns that can be reused across multiple tables.

For example, this is what I'm trying - though its not working:

let now:Value =  Value('now()');
let created = {
    createdAt: {
    type: 'timestamp',
    notNull: true,
    default: now,
  }}

The idea being, I could create columns that are commonly used, such as createdAt and modifiedAt and reuse them.

[mike-usa]

I'm just learning node-pg-migrate, so take this for what it's worth. It sounds like you could make use of shorthands.

Here is syntax I've used:

const { PgLiteral } = require('node-pg-migrate')

let current_user_id = 123456; // <-- use an environment variable or perform a lookup against the `users` table
exports.shorthands = {
  ':created_by': {type: 'integer',   notNull: true,  references: 'users', default: current_user_id},
  ':created_at': {type: 'timestamp', notNull: true,                       default: new PgLiteral('current_timestamp')}
  ':updated_by': {type: 'integer',                   references: 'users'},
  ':updated_at': {type: 'timestamp}
}

pgm.createTable('entries', {
  id: 'id',
  createdBy: ':created_by',
  createdAt: ':created_at',
  updatedBy: ':updated_by',
  updatedAt: ':updated_at'
})

pgm.createTable('tasks', {
  id: 'id',
  createdBy: ':created_by',
  createdAt: ':created_at',
  updatedBy: ':updated_by',
  updatedAt: ':updated_at'
})

1. The : prefix is a convention I use to quickly comprehend the migration and data types
2. The PgLiteral is imported from the library and used to replace now(); inside a migration pgm.func() can be used
3. Shorthands are kept in memory for each migration processed (until overwritten), so it's good to reset at the top of each file to understand the flow

Using this technique, you could place your shorthands in another file like config/shorthands.js, which you import at the top of your migration (e.g., const {shorthands} = require('config/shorthands')) and extend it if necessary. I'd think this is ill-advised, since the migration would then be dependent on a file who's state/content may change (could lead to errors).

For me, there are certain shorthands I never expect to change (e.g., those timestamps, :required_text) and if I do, I wouldnt want to go through and modify all the files, so I may be talking myself into this approach.