Get best of the week

5NO - NodeJS ORM für Postgres

Kurz zu diesem Modul


Dieses Modul wurde von mir entwickelt, um die Interaktion zwischen Postgres und JS zu vereinfachen.
Ich verstehe, dass es viele ähnliche Module gibt, aber ich wollte etwas flexibel und einfach für verschiedene Anforderungen verwenden.

Das Modul kombiniert drei wichtige Funktionen: Validierung von Eingabedaten, Erstellen von Abfragen an die Datenbank und Ausgeben von Daten an JSON.

Installation des Moduls selbst


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

Passen Sie die Verbindung an


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

Als nächstes müssen wir Tabellen in der Datenbank erstellen


Benutzertabelle:

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()
)

Tabelle mit zusätzlichen Benutzerinformationen:

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()
)

Tabelle mit Benutzeradressen:

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()
)

Tabelle mit Rollen:

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()
)

Rollen-Benutzer-Beziehungstabelle:

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

Modellerstellung


Vorbild:

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: {},
  }
}

Modell der Beziehung zwischen Rolle und Benutzer:

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: {},
  }
}

Benutzeradressmodell:

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: {},
    }
}

Modell mit zusätzlichen Benutzerinformationen:

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: {},
    }
}

Benutzermodell:

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',
          ],
        },
      },
    }
}

Verwenden Sie Modelle


Schaffung von Rollen:

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


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

Benutzererstellung:

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()

Belegbeleg:

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

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

await user.Roles.join(AdminRoleId)

await user.save()

Abrufen des Datensatzes als JSON:

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

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

console.log(userJsonData)

Ergebnis:

{ 
  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'
    }
  ]
}

Abschließend möchte ich sagen, dass ich mich und meine Bedürfnisse entwickelt und das System so flexibel wie möglich gestaltet habe.

Alle zusätzlichen Informationen finden Sie auf der Website.

More articles:


All Articles