List function returns string for numeric column

Help me!
1

Feature(s) impacted

The list of dataSource

Observed behavior

I have a table with a column in numeric format (sgbd is postgres)
When I use the list function

const data = await context.dataSource
      .getCollection('MyCollection')
      .list({conditionTree: {...} }, ['myNumericColumn']);

I get an array of data but myNumericColumn is type string (for example "84.00") but typescript says it is a number

Expected behavior

The return value of the list function give me a float for the numeric column instead of the string representation of this float

Context

  • Project name: Roundtable
  • Team name: -
  • Environment name: local
  • Agent technology: nodejs
  • Agent (forest package) name & version: “@forestadmin/agent”: “^1.41.7”,
  • Database type: postgres
  • Recent changes made on your end if any: -
2

Hi @Quentin_Somerville1 ,

For further investigation, I would need

  • your team name
  • the collection
  • the column name

Thank you.
Best regards,

Shohan

3

Hi @shohanr
I’m not sure what you mean by team name but I think there is only one in Roundtable project
The collection is “Commitment” and the column is “amountCommitted”

Thanks

Quentin

4

Hi again @Quentin_Somerville1 ,
We have this column registered with a Number type. Could you please double check that the type declared in your database is string ?

Best regards,

Shohan

6

Hi @shohanr
The database type is a numeric.
But it’s the javascript value returned by the list function that is type “string” even if typescript say it is a number
So basically I need to do

parseFloat(commitment.amountCommitted as unknown as string)
7

Hi @Quentin_Somerville1 ,

I am sorry but I was not able to reproduce your issue.
By declaring the column as float, I do have a number type by typescript and float returned.

What is the exact type that you declared in your postgres ?

8

Hi @shohanr
The type of the column in postgres is numeric(16,2)
In the .forestadmin-schema.json it is generated as "Number"
And in the smart field the following code

getValues: async (records, context) => {
    const allCommitments = await context.dataSource
      .getCollection('Commitment')
      .list(
        {
          conditionTree: ..., //
        },
        ['amountCommitted'],
      );

    const firstCommitment = allCommitments[0];
    if (firstCommitment) {
      console.log(firstCommitment.amountCommitted);
      console.log(typeof firstCommitment.amountCommitted);
    }
}

I get the following log

10000.00
string
11

Hello @Quentin_Somerville1,

This is a known limitation of types in Javascript. As BIGINT and NUMERIC types are too “big” to fit inside a js Number, the libraries pg and Sequelize which our @forestadmin/datasource-sql package uses to interface with SQL databases will return such column types as String to avoid loss of data.

If this is an appropriate answer to you, I would simply suggest parsing the field as you have shared earlier.

If you really wish to have js Number’s, you will need to override the default parser in Sequelize for this DataType:

:warning: Note that this will override the parser for every column type DECIMAL which could incur data loss if the value is larger that what can fit inside of a js Number

import Sequelize from 'sequelize';
...
  const sqlDS = createSqlDataSource(process.env.DATABASE_URL);
  const sequelize = (await sqlDS(console.log)).childDataSource.sequelize;
  class SafeDecimal extends Sequelize.DataTypes.DECIMAL {
    static parse(value) {
      return parseFloat(value);
    }
  }

  const dTypes = {
    DECIMAL: SafeDecimal,
  };
  // This is to update the definition in Sequelize, if not done the change is not propagated
  sequelize.connectionManager.refreshTypeParser(dTypes);

  createAgent({
    options
  })
    .addDataSource(sqlDS)
 ...

The workaround shown higher does not have proper typing for most of the objects shown, so not a great answer if your main issue was bad typing.

1 Like
12

Hello @dogan.ay
Ok. understand why the function is returning a string.
But typing should be consistent so I think in this case the return type of the list function should be string for numeric columns and not number to avoid this weird type casting as unknown as undefined

13

I’ll forward your point so we can tackle this down the line, but as it is not critical I cannot give you an ETA.

If you want to avoid TS casting you could just cast it to a string:

parseFloat(`${commitment.amountCommitted}`)
14

Hi @dogan.ay

Ok no worries it’s not critical indeed

Thanks !

1 Like