Hi,
I have a project where I’m using Forest Admin Agent, Sequelize with NestJS and the Database is MySQL
"@forestadmin/agent": "1.38.7",
"@forestadmin/datasource-sequelize": "1.7.1",
"@nestjs/common": "10.3.3",
"@nestjs/sequelize": "10.0.1",
"sequelize": "6.37.1",
"sequelize-typescript": "2.1.6"
I’m trying to use the Geometry Point data type
@Column({
type: GEOMETRY('POINT', 4326),
})
location: string;
When I’m setting this column, I get an working
(Unsupported data type: "GEOMETRY")
When I check from where the error came:
node_modules/@forestadmin/datasource-sequelize/dist/utils/type-converter.js
And I can see this function doesn’t support GEOMETRY TYPE
// See postgres enum handling in @datasource-sql
if (dataType.isDataSourceSqlEnum)
return 'Enum';
switch (dataType.key) {
case sequelize_1.DataTypes.BLOB.key:
return 'Binary';
case sequelize_1.DataTypes.BOOLEAN.key:
return 'Boolean';
case sequelize_1.DataTypes.DATE.key:
case sequelize_1.DataTypes.NOW.key:
return 'Date';
case sequelize_1.DataTypes.DATEONLY.key:
return 'Dateonly';
case sequelize_1.DataTypes.ENUM.key:
return 'Enum';
case sequelize_1.DataTypes.JSON.key:
case sequelize_1.DataTypes.JSONB.key:
return 'Json';
case sequelize_1.DataTypes.BIGINT.key:
case sequelize_1.DataTypes.DECIMAL.key:
case sequelize_1.DataTypes.DOUBLE.key:
case sequelize_1.DataTypes.FLOAT.key:
case sequelize_1.DataTypes.INTEGER.key:
case sequelize_1.DataTypes.MEDIUMINT.key:
case sequelize_1.DataTypes.NUMBER.key:
case sequelize_1.DataTypes.REAL.key:
case sequelize_1.DataTypes.SMALLINT.key:
case sequelize_1.DataTypes.TINYINT.key:
return 'Number';
case sequelize_1.DataTypes.CHAR.key:
case sequelize_1.DataTypes.CITEXT.key:
case sequelize_1.DataTypes.STRING.key:
case sequelize_1.DataTypes.TEXT.key:
return 'String';
case sequelize_1.DataTypes.TIME.key:
return 'Time';
case sequelize_1.DataTypes.UUID.key:
case sequelize_1.DataTypes.UUIDV1.key:
case sequelize_1.DataTypes.UUIDV4.key:
return 'Uuid';
default:
throw new Error(`Unsupported data type: "${dataType}"`);
}
}
Is there a way to use the Geometry field?
Thanks
1 Like
Hello @Constantin_Predescu and welcome to our community.
Indeed, the sequelize type Geometry is not supported today in Forest Admin.
I’m not too familiar with how the Geometry type works nor what is its shape but you should be able to find some inspiration from the code below:
The trick would be to declare a new computed field on your collection, such as below.
Then, in the getValues handler, you use the nativeDriver to perform your SQL query directly on the db to map the data to a known Forest Admin type (maybe Point), and of course adapting the table and column name to your own use case.
Something like that:
.addField('location', {
columnType: 'Point',
dependencies: ['id'],
getValues: async (records, context) => {
const rows = await context.collection.nativeDriver.rawQuery(`
SELECT location, id
FROM store
WHERE id in (${records.map(record => record.id)})
`);
return records.map(record => {
const { location } = rows.find(row => row.id === record.id);
return [location.x, location.y];
});
},
});
Please let me know if that would work for you 
Hi @Nicolas.M ,
Thank you for your reply.
To get and display the data from the database is not a problem.
I use the following syntax:
@Column({
type: DataType.STRING,
get() {
const location = this.getDataValue('location');
if (!location) {
return null;
}
const [longitude, latitude] = location.coordinates.map(Number);
return `${latitude},${longitude}`;
},
})
I can’t find a way to set the value (create/update)
I tried this:
@Column({
type: DataType.STRING,
get() {
const location = this.getDataValue('location');
if (!location) {
return null;
}
const [longitude, latitude] = location.coordinates.map(Number);
return `${latitude},${longitude}`;
},
set(val: string) {
if (!val) {
return null;
}
const [latitude, longitude] = val.split(',').map(Number);
this.setDataValue('location', {
type: 'Point',
coordinates: [latitude, longitude],
});
},
})
location: string;
I’m getting this error
SequelizeValidationError: string violation: location cannot be an array or an object
ValidationError: SequelizeValidationError: string violation: location cannot be an array or an object
at handleErrors (node_modules/@forestadmin/datasource-sequelize/src/utils/error-handler.ts:35:11)
If I’m watching the library to allow the GEOMETRY type, I’m getting this error:
Cannot read properties of undefined (reading 'slice')
TypeError: Cannot read properties of undefined (reading 'slice')
at Function.validateValue (node_modules/@forestadmin/datasource-toolkit/src/validation/field.ts:72:10)
I’ve found a solution:
I’ve created a patch to @forestadmin/datasource-sequelize library to allow the Geometry type.
The getColumnTypeFromDataType returns the string ‘Point’ when the type is set as GEOMETRY
diff --git a/node_modules/@forestadmin/datasource-sequelize/dist/utils/type-converter.js b/node_modules/@forestadmin/datasource-sequelize/dist/utils/type-converter.js
index 23a91f2..e6e86c3 100644
--- a/node_modules/@forestadmin/datasource-sequelize/dist/utils/type-converter.js
+++ b/node_modules/@forestadmin/datasource-sequelize/dist/utils/type-converter.js
@@ -43,6 +43,8 @@ class TypeConverter {
case sequelize_1.DataTypes.UUIDV1.key:
case sequelize_1.DataTypes.UUIDV4.key:
return 'Uuid';
+ case sequelize_1.DataTypes.GEOMETRY.key:
+ return 'Point';
default:
throw new Error(`Unsupported data type: "${dataType}"`);
}
Is set to Point because @forestadmin/datasource-toolkit knows about this type
The column definition looks like this:
@Column({
type: DataType.GEOMETRY('POINT', 4326),
get() {
const location = this.getDataValue('location');
if (!location) {
return null;
}
return location.coordinates;
},
set(val: string) {
if (!val) {
return null;
}
const [longitude, latitude] = val.split(',').map(Number);
this.setDataValue('location', {
type: 'Point',
coordinates: [longitude, latitude],
});
},
})
location: string;
P.S.
MySQL keeps data in the format [X, Y] => [Longitude, Latitude]
If you copy data from Google Maps, the format is Latitude, Longitude. (Y, X)
The UI looks like this:
3 Likes
Thanks @Constantin_Predescu for reporting on your progress.
I’m glad that you have found a solution that works for you !
One note though: your patch might make updating @forestadmin/datasource-sequelize to the latest version difficult.
I can expand my proposed solution with the write part, something like that:
collection.replaceFieldWriting('location', value => {
[longitude, latitude] = value;
return {
type: 'Point',
coordinates: [latitude, longitude],
});
});
Another benefit is that it is a pure Forest Admin solution, which doesnt impact your Sequelize models.
Hi @Nicolas.M , thank you for your reply.
I’ve tried your solution, but I could not get it to work.
If I set the type as GEOMETRY without patching the library, I get an error:
warning: Skipping column 'location' (Unsupported data type: "GEOMETRY")
ValidationError: Column not found: 'location'
I can’t save the data if I set the column as a String.
An error occured when trying to edit Map Point: The given value has a wrong type for "location": [object Object]. Expects "String",null
Ok I see.
warning: Skipping column 'location' (Unsupported data type: "GEOMETRY")
ValidationError: Column not found: 'location'
Is actually 2 distinct errors:
the first one is only a warning, saying that the ‘location’ colomn is skipped because Forest Admin doesn’t know about its data type
The second one
ValidationError: Column not found: 'location'
is there because you probably still have customizations declared on this column in your agent.
