Get best of the week

5NO - NodeJS ORM para Postgres

Brevemente sobre este módulo


Este módulo fue desarrollado por mí para simplificar la interacción entre Postgres y JS.
Entiendo que hay muchos módulos similares, pero quería hacer algo flexible y fácil de usar para diferentes necesidades.

El módulo combina tres funciones importantes: validación de datos de entrada, creación de consultas a la base de datos y salida de datos a JSON.

Instalación del módulo en sí.


npm install --save @5no/pg-model

Personaliza la conexión


DATABASE_URL=postgres://test:123123@127.0.0.1:5432/testDB?ssl=false
DATABASE_QUERY_LOG=true

Luego necesitamos crear tablas en la base de datos


Tabla de usuario:

CREATE TABLE "public"."users" (
	"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
	"email" text NOT NULL COLLATE "default",
        "personalised" jsonb DEFAULT '{}'::jsonb,
	"properties" jsonb DEFAULT '[]'::jsonb,
	"created_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now(),
	"updated_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now()
)

Tabla con información de usuario adicional:

CREATE TABLE "public"."users_info" (
	"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
	"user_id" uuid NOT NULL,
	"first_name" text COLLATE "default",
	"last_name" text COLLATE "default",
	"created_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now(),
	"updated_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now()
)

Tabla con direcciones de usuario:

CREATE TABLE "public"."users_address" (
	"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
	"user_id" uuid NOT NULL,
	"street_name" text COLLATE "default",
	"postcode" text COLLATE "default",
	"created_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now(),
	"updated_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now()
)

Mesa con roles:

CREATE TABLE "public"."roles" (
	"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
	"role" text NULL,
	"created_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now(),
	"updated_at" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now()
)

Tabla de relación usuario-rol:

CREATE TABLE "public"."user_roles" (
	"user_id" uuid NOT NULL,
	"role_id" uuid NOT NULL
)

Creación de modelos


Modelo a seguir:

const { Model } = require('@5no/pg-model')

class Roles extends Model {
  static schema = {
    table: {
      schema: 'public',
      name: 'roles',
    },
    columns: {
      id: {
        type: String,
        primaryKey: true,
        defaultValue: null,
      },
      role: {
        type: String,
        defaultValue: null,
      },
      created_at: {
        type: Date,
        created: true,
        format: 'YYYY-MM-DD HH:mm:ss',
      },
      updated_at: {
        type: Date,
        updated: true,
        format: 'YYYY-MM-DD HH:mm:ss',
      },
    },
    relations: {},
  }
}

Modelo de relación de rol a usuario:

const { Model } = require('@5no/pg-model')

class UserRoles extends Model {
  static schema = {
    table: {
      schema: 'public',
      name: 'user_roles',
    },
    columns: {
      user_id: {
        type: String,
        defaultValue: null,
        primaryKey: true,
      },
      role_id: {
        type: String,
        defaultValue: null,
        primaryKey: true,
      },
    },
    relations: {},
  }
}

Modelo de dirección de usuario:

const { Model } = require('@5no/pg-model')

class UsersAddresses extends Model {
    static schema = {
      table: {
        schema: 'public',
        name: 'users_address',
      },
      columns: {
        id: {
          type: String,
          primaryKey: true,
          defaultValue: null,
        },
        user_id: {
          type: String,
          defaultValue: null,
          required: true,
        },
        street_name: {
          type: String,
          defaultValue: null,
        },
        postcode: {
          type: String,
          defaultValue: null,
        },
        created_at: {
          type: Date,
          created: true,
          format: 'YYYY-MM-DD HH:mm:ss',
        },
        updated_at: {
          type: Date,
          updated: true,
          format: 'YYYY-MM-DD HH:mm:ss',
        },
      },
      relations: {},
    }
}

Modelo con información de usuario adicional:

const { Model } = require('@5no/pg-model')

class UsersInfo extends Model {
    static schema = {
      table: {
        schema: 'public',
        name: 'users_info',
      },
      columns: {
        id: {
          type: String,
          primaryKey: true,
          defaultValue: null,
        },
        user_id: {
          type: String,
          defaultValue: null,
          required: true,
        },
        first_name: {
          type: String,
          defaultValue: null,
        },
        last_name: {
          type: String,
          defaultValue: null,
        },
        created_at: {
          type: Date,
          created: true,
          format: 'YYYY-MM-DD HH:mm:ss',
        },
        updated_at: {
          type: Date,
          updated: true,
          format: 'YYYY-MM-DD HH:mm:ss',
        },
      },
      relations: {},
    }
}

Modelo de usuario:

const { Model } = require('@5no/pg-model')

class Users extends Model {
    static schema = {
      table: {
        schema: 'public',
        name: 'users',
      },
      columns: {
        id: {
          type: String,
          primaryKey: true,
          defaultValue: null,
        },
        email: {
          type: String,
          required: true,
          validators: [
            'email',
          ],
        },
        personalised: {
          type: Object,
          prefilled: true,
          defaultValue: {
            test: 100,
          },
        },
        countRoles: {
          type: Function,
          fn: (model) => Manager.build(UserRoles).count('user_id', model.id),
        },
        properties: {
          type: Array,
          defaultValue: [],
          schema: {
            name: {
              type: String,
              required: true,
              filters: [
                'lowerCase',
              ],
            },
            value: {
              type: String,
              required: true,
            },
          },
        },
        created_at: {
          type: Date,
          created: true,
          format: 'YYYY-MM-DD HH:mm:ss',
        },
        updated_at: {
          type: Date,
          updated: true,
          format: 'YYYY-MM-DD HH:mm:ss',
        },
      },
      relations: {
        Info: {
          model: UsersInfo,
          local: 'id',
          foreign: 'user_id',
          type: 'one',
          cascade: [
            'save',
            'delete',
          ],
        },
        Addresses: {
          model: UsersAddresses,
          local: 'id',
          foreign: 'user_id',
          type: 'many',
          cascade: [
            'save',
            'delete',
          ],
        },
        Roles: {
          model: UserRoles,
          join: {
            model: Roles,
            local: 'role_id',
            foreign: 'id',
            type: 'many',
          },
          local: 'id',
          foreign: 'user_id',
          type: 'join',
          cascade: [
            'save',
            'delete',
          ],
        },
      },
    }
}

Usa modelos


Creación de roles:

const role = new Roles()
role.role = 'Admin'
await role.save()


const role = new Roles()
role.role = 'Customer'
await role.save()

Creación de usuario:

const user = new Users()

user.email = 'test@test.test'
await user.Addresses.add({
        street_name: 'Test',
        postcode: '100500', 
})
await user.Addresses.add({
        street_name: 'Test 2',
        postcode: '100502', 
})
 
user.Info.first_name = 'Test First Name'
user.Info.last_name = 'Test Last Name'

user.properties = [
        {
          name: 'Test',
          value: 'OK',
        },
]

await user.Roles.join(CustomerRoleId)

await user.save()

Recibo de registro:

const { Manager } = require('@5no/pg-model')

const user = await Manager.build(Users).find(usersId)

await user.Roles.join(AdminRoleId)

await user.save()

Obteniendo el registro como JSON:

const { Manager } = require('@5no/pg-model')

const userJsonData = await Manager.build(Users, true).find(usersId)

console.log(userJsonData)

Resultado:

{ 
  id: '7852468e-ac99-4f5e-9ee3-d506b0c4424e',
  email: 'test@test.test',
  countRoles: 2,
  created_at: '2018-12-20 17:10:31',
  updated_at: '2018-12-20 17:10:31',
  personalised: {
    test: 100
  },
  properties: [
    {
      name: 'test',
      value: 'OK',
    },
  ],
  Info: 
   { id: '0320dc4f-4ca7-4b65-bd42-52f286a0b9db',
     user_id: '7852468e-ac99-4f5e-9ee3-d506b0c4424e',
     first_name: 'Test First Name',
     last_name: 'Test Last Name',
     created_at: '2018-12-20 17:10:31',
     updated_at: '2018-12-20 17:10:31' },
  Addresses: 
   [ 
     { id: 'be40ccb3-3a33-4b6e-9467-6907b0c4396b',
       user_id: '7852468e-ac99-4f5e-9ee3-d506b0c4424e',
       street_name: 'Test',
       postcode: '100500',
       created_at: '2018-12-20 17:10:31',
       updated_at: '2018-12-20 17:10:31' },
     { id: 'f5bae3e9-290b-451e-a0e2-1ec2d9eaf543',
       user_id: '7852468e-ac99-4f5e-9ee3-d506b0c4424e',
       street_name: 'Test 2',
       postcode: '100502',
       created_at: '2018-12-20 17:10:31',
       updated_at: '2018-12-20 17:10:31' } 
    ], 
  Roles: [
    {
      created_at: '2018-12-20 17:10:31',
      id: 'be40ccb3-3a33-4b6e-9467-6907b0c4396b',
      role: 'Admin',
      updated_at: '2018-12-20 17:10:31'
    },
    {
      created_at: '2018-12-20 17:10:31',
      id: 'be40ccb3-3a33-4b6e-9467-7907b1c4396b',
      role: 'Customer',
      updated_at: '2018-12-20 17:10:31'
    }
  ]
}

En conclusión, me gustaría decir que desarrollé para mí y para mis necesidades e hice el sistema lo más flexible posible.

Toda la información adicional está en el sitio.

More articles:


All Articles