Adding phone number to Retool Databse

Queries and Resources
1

Trying to add phone number to retool database, but when I use a form on the site editor, I can only use the text or number component to add a number, when using the phone number component it does not let me add to the database. How would I go about putting a phone number into the database?

2

you can either:

  • use the TEXT type for the column and check if it's valid inside retool after you retrieve the value from the db and before you store a value.
  • define a custom type for the db, then modify the table to add a new column using this new type. values inserted to the table will automatically be checked for validity so you won't need to do this in retool or in any other source that uses the value

the catch is that you won't be able to do the 2nd option with the builtin Retool DB GUI, but you can write a few queries and run them from within your webapp/module or any 3rd party postgresql ide.

this defines a domain named phone_number as a custom type. then it adds a column named phone to the customer table but before doing this we need to 1st check if the domain already exists so we query pg_type

SELECT 1 FROM pg_type WHERE typename = 'phone_number';

you can use the onSuccess/Failure event handlers for the query and on success check if any rows were returned or not. 0 rows = domain doesn't exist, so we need to create it. we need to check first before creating it because if we call CREATE DOMAIN on a domain that already exists you can end up with data in the table that is now invalid (the data originally passed the check and was inserted, after the check was changed the table doesn't re-check all values and just assumes everything in it is still valid)

so if the previous query has 0 rows, we run the following query to create the domain and add the column to the table. if the query has 1 or more rows, we can skip this since it already exists

CREATE DOMAIN phone_number AS TEXT
    CHECK (VALUE ~ '^(\\+\\d{1,2}\\s)?\\(?\\d{3}\\)?[\\s.-]?\\d{3}[\\s.-]?\\d{4}$');

ALTER TABLE customer ADD COLUMN phone phone_number IF NOT EXISTS

now the db and table is all setup for phone numbers.... the db has a new type and our table uses it. when you insert values into the table you'll use a string like
"+1 (913) 123-1234".

the regex is flexible and can check numbers that both do and don't have different parts like country code as well as allowing spaces dots or hyphens. here's a breakdown of the regex:
^(\\+\\d{1,2}\\s)?\\(?\\d{3}\\)?[\\s.-]?\\d{3}[\\s.-]?\\d{4}$

  • ^: Matches the start of the string.
  • (\\+\\d{1,2}\\s)?: Optional international dialing code (e.g., +1 or +44) followed by one or two digits and an optional space.
  • \\(?\\d{3}\\)?: Optional parentheses around a three-digit area code.
  • [\\s.-]?: Optional separator (space, dot, or hyphen).
  • \\d{3}: Three digits for the local exchange code.
  • [\\s.-]?: Another optional separator.
  • \\d{4}: Four digits for the subscriber number.
  • $: Matches the end of the string.
1 Like
4

Hi @Timofey_Sanko ,
The issue arises because in Retool's database, the phone_number field is set to an integer data type. However, when you attempt to add a phone number using the phone_number component in a Retool form, it results in an error. To resolve this, you should change the data type of phone_number from an integer to a string.
image

2 Likes