Properties

Properties are used to define columns for Table and User.

Defining a Property

Defining a property for Table

Properties are defined in YML configuration files for Tables. You have to provide a name and a type. For example:

properties:
- name: name
  type: string
- name: enabled
  type: boolean
- name: age
  type: integer

The above configuration adds three properties: "name" of type string, "enabled" of type boolean, and "age" of type integer.

Defining a property for User

The same way you define properties for each Table, you can also define properties for User. A typical use case might be adding authentication and authorization related properties, such as a role property to store the user's role, or a last_sign_in_at datetime property. You can specify the user's properties in the app/user.yml file:

properties:
- name: role
  type: string
- name: last_sign_in_at
  type: datetime

Property Types

Each Property is described with the type of data that is stored within the database.
Please note that when processing your Property in Liquid, types are automatically converted to those supported by Liquid Data Types.

The table below lists available data types for Property configuration, that are used to configure fields on the database:

Type	Description	Example
array	Arrays hold lists of variables of any type. Learn more in Arrays	[1, 2, 3]
boolean	Booleans are used to represent true/false values.	true
date	Stores Date	"2017-07-07"
datetime	Stores DateTime with time zone. It is recommended to store date and time in one format, so if you provide it in different formats, platformOS will convert it to ISO 8601; that’s what GraphQL expects as well. Please note the to_time filter which is very powerful in terms of parsing time. You can display dates/times in any format you want using the localize filter.	"2017-07-07 14:00:00 +0000"
float	Real numbers	1.0
integer	Whole numbers that can be positive, negative, or 0	1
upload	Learn more in User Uploads
string	A UTF-8 character sequence	"Some String"

Fetching a Property with GraphQL casted to the given type

There is "family" of property GraphQL fields GraphQL PropertiesInterface that allow you to specify the type of returned data. o avoid type-related issues (such as comparing a string with an integer), you should ensure that your GraphQL queries/mutations return data in the same type as defined in your yml files. For example, if you have defined a table employees with three properties - name of type string, age of type int, and enabled of type boolean, your GraphQL query would look as follows:

query get_records {
  records(
    per_page: 20,
    filter: {
      table: { value: "employee" }
    }
  ) {
    results {
      name: property(name: "name")
      age: property_int(name: "age")
      enabled: property_boolean(name: "enabled")
    }
  }
}

Filtering Properties with GraphQL

You can filter GraphQL results based on the values of object properties. All available options are defined in GraphQL PropertyFilterInput. There is a family of value GraphQL arguments that correspond to the property type — for example, value_int, value_boolean, etc. Below are some examples of GraphQL queries:

value - matches the value of given property.

query get_all_johns {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "name", value: "John" }]
    }
  ) {
    results {
      first_name: property(name: "name")
    }
  }
}

not_value - does not match the value of given property.

query get_evrybody_except_johns {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "name", not_value: "John" }]
    }
  ) {
    results {
      first_name: property(name: "name")
    }
  }
}

value_int - matches the value of given property.

query get_all_records_with_age_14 {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "age", value_int: 14 }]
    }
  ) {
    results {
      first_name: property(name: "name")
      age: property(name: "age")
    }
  }
}

value_float - matches the value of given property.

query get_all_records_with_discount_twenty {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "discount", value_float: 0.2 }]
    }
  ) {
    results {
      first_name: property(name: "name")
      discount: property(name: "discount")
    }
  }
}

value_boolean - matches the value of given property.

query get_all_records_with_discount {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "discount", value_boolean: true }]
    }
  ) {
    results {
      first_name: property(name: "name")
    }
  }
}

value_in - matches any value of provided array.

query get_all_johns_and_annas {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "name", value_in: ["John", "Anna"] }]
    }
  ) {
    results {
      first_name: property(name: "name")
    }
  }
}

not_value_in - does not match any value of provided array.

query get_everyone_except_johns_and_annas {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "name", not_value_in: ["John", "Anna"] }]
    }
  ) {
    results {
      first_name: property(name: "name")
    }
  }
}

range - available options for range are: lt, lte, gt, gte:

query get_all_adults_below_60 {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "age", range: { gte: $adult_age_treshold, lt: 60 } }]
    }
  ) {
    results {
      first_name: property(name: "name")
    }
  }
}

range uses ISO 8601 date-time formats, for example:

{
  name: "last_time_active",
  range: {
    lte: "2019-10-02T08:13:00",
    gte: "2019-09-01T08:13:00"
  }
}

exists

query get_all_with_name {
  records(
    per_page: 20,
    filter: {
      properties: [{ name: "name", exists: true }]
    }
  ) {
    results {
      first_name: property(name: "name")
    }
  }
}

Managing properties in production

Applications evolve over time. No one creates the perfect schema before going live with the application. You may find yourself needing to add new properties, change existing ones, or remove outdated ones. If you are in this situation, this article should answer any questions you might have.

Adding a new property to an existing Table with data

There are no limitations when it comes to adding more properties to your existing application — simply add the property definition in the yml file and sync/deploy the file.

Deleting an existing property from an existing Table with data

If you delete a property from the table or user.yml definition, it will no longer be accessible. However, the data itself will not be lost—if you re-add the property later, the data will still be there. This is to protect you from accidental removal of a property. To permanently remove a property along with its data, you must first explicitly overwrite the property with null, and then proceed with removing the property.

Changing a property name in an existing Table with data

In platformOS, there is no concept of renaming a property. To achieve the desired result, you need to create a new property with the name you want, copy the data from the old property to the new one (using, for example, CSV Data Import, and then safely remove the old property.

Changing a property type in an existing Table with data

When you change the type of a property, we handle the type conversion for you. We first check if data conversion is possible. Here is a JSON representation of the allowed conversions:

• integer: string, float, array
• float: string, array
• string: integer, float, boolean, array
• boolean: string, array

If you attempt to change to an incompatible type, an error will be raised. We use built-in PostgreSQL functions to perform the casting — if any record cannot be converted, an error will occur, and the transaction will be rolled back with an error message. You can then manually fix the problematic record (the easiest way is to use our GUI for DB management) and re-try the conversion.