Numeric precision and scale not being set with Automigrate and Autoupdate

[loopbackio]

Numeric precision and scale not being set with Automigrate and Autoupdate

[sebastianhaas]

I can confirm that.

  "properties": {
    "amount": {
      "type": "Number",
      "required": true,
      "postgresql": {
          "dataType": "numeric",
          "dataPrecision": 20,
          "dataScale": 0
        }
    }

results in

CREATE TABLE split
(
  amount numeric NOT NULL,
[...]

[superkhau]

Can you provide a link to a test project on GitHub? See https://github.com/strongloop/loopback/wiki/Reporting-issues#bug-report

[sebastianhaas]

Thank you for your answer @superkhau.
I set up a test project at https://github.com/sebastianhaas/loopback-sandbox.

You have to have a PostgreSQL server running at localhost:5432 with the following credentials and database:

  "postgres": {
    "host": "localhost",
    "port": "5432",
    "database": "dbname",
    "username": "dbuser",
    "password": "dbpassword",
    "name": "postgres",
    "debug": true,
    "connector": "postgresql"
  }

Afterwards you want to call the automigrate script to create the table.

$ node bin/automigrate.js

The newly created table will be:

CREATE TABLE testmodel
(
  "numericValue" numeric NOT NULL,
  id serial NOT NULL,
  CONSTRAINT testmodel_pkey PRIMARY KEY (id)
)

While it should rather be numeric(19,4) .

I took a quick look into the source, I don't see any code inside the automigrate logic that would actually do this. Maybe in node-postgres though.

[madianas]

Found a workaround for this, from @raymondfeng:
You can customize the DB type as follows:

{
  "rate": {
    "type": "number",
   "postgresql": {
     "dataType": "NUMERIC(10,2)"
   }
  }

```}

[alhafoudh]

This does not work @madianas

[ssh24]

Can confirm this is not an issue anymore in the latest connector. If you specify the postgresql column data type on the property you want to inject it into, this works as expected.

Here is a sample code snippet:

model-definition.json

{
  "name": "Item",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "name": {
      "type": "string"
    },
    "price": {
      "type": "number",
      "postgresql": {
        "dataType": "numeric",
        "dataPrecision": 10,
        "dataScale": 2
      }
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": {}
}

boot-script.js

'use strict';
var util = require('util');
var _ = require('lodash');

module.exports = function(app) {
    var db = app.datasources.postgresqlDs;
    var Item = app.models.Item;

    db.automigrate(function(err) {
        if (err) throw err;
        console.log('\nAutomigrate completed');
    });
};

database output

testdb=# \d Item;
                            Table "public.item"
 Column |     Type      |                     Modifiers
--------+---------------+---------------------------------------------------
 name   | text          |
 price  | numeric(10,2) |
 id     | integer       | not null default nextval('item_id_seq'::regclass)
Indexes:
    "item_pkey" PRIMARY KEY, btree (id)

Closing this issue as a result. Feel free to reopen if needed.