Repository: shuttle-hq/synth (language: rust)

Issue:
Importing with Postgres enum types
Thanks for your work on synth!

**Required Functionality**
Currently, trying to import from a database that uses enum types, e.g.
```sql
CREATE TYPE MyType AS ENUM ('Left', 'Right');
CREATE TABLE my_table (my_type MyType);
```
results in
```console
Error: We haven't implemented a converter for mytype
```

**Use case**
I have a database where some Rust enums are represented as enum types and it would be nice to be able to use synth's import feature with it.


Discussion / hints:
Hey @Heliozoa - thanks for taking the time to write this up.

Even though you are correct in saying that we don't support Enums as a type, you've basically stumbled on the fact that we don't support user-defined types in general. Currently we just get the type name from the database (in this case `mytype`) and pattern match against a list of known types. Obviously since your type is user defined we're not matching against it.

We (probably) need to use something like the `sqlx` [PGTypeKind](https://docs.rs/sqlx/0.5.5/sqlx/postgres/enum.PgTypeKind.html) enum to support composite user-defined types.

Thanks for bringing this up, we need to fit it into the roadmap soon.

Fix patch:
diff --git a/synth/src/cli/import_utils.rs b/synth/src/cli/import_utils.rs
index 9c4a0887..b4c19dbf 100644
--- a/synth/src/cli/import_utils.rs
+++ b/synth/src/cli/import_utils.rs
@@ -172,10 +172,7 @@ impl<T: RelationalDataSource + DataSource> TryFrom<(&T, &ColumnInfo)> for FieldC
     type Error = anyhow::Error;
 
     fn try_from(column_meta: (&T, &ColumnInfo)) -> Result<Self> {
-        let data_type = &column_meta.1.data_type;
-        let mut content = column_meta
-            .0
-            .decode_to_content(data_type, column_meta.1.character_maximum_length)?;
+        let mut content = column_meta.0.decode_to_content(column_meta.1)?;
 
         if column_meta.1.is_nullable {
             content = content.into_nullable();
diff --git a/synth/src/datasource/mysql_datasource.rs b/synth/src/datasource/mysql_datasource.rs
index 7314cf8f..6b2b4a82 100644
--- a/synth/src/datasource/mysql_datasource.rs
+++ b/synth/src/datasource/mysql_datasource.rs
@@ -148,11 +148,13 @@ impl RelationalDataSource for MySqlDataSource {
             .collect()
     }
 
-    fn decode_to_content(&self, data_type: &str, char_max_len: Option<i32>) -> Result<Content> {
-        let content = match data_type.to_lowercase().as_str() {
+    fn decode_to_content(&self, column_info: &ColumnInfo) -> Result<Content> {
+        let content = match column_info.data_type.to_lowercase().as_str() {
             "char" | "varchar" | "text" | "binary" | "varbinary" | "enum" | "set" => {
-                let pattern =
-                    "[a-zA-Z0-9]{0, {}}".replace("{}", &format!("{}", char_max_len.unwrap_or(1)));
+                let pattern = "[a-zA-Z0-9]{0, {}}".replace(
+                    "{}",
+                    &format!("{}", column_info.character_maximum_length.unwrap_or(1)),
+                );
                 Content::String(StringContent::Pattern(
                     RegexContent::pattern(pattern).context("pattern will always compile")?,
                 ))
@@ -188,7 +190,10 @@ impl RelationalDataSource for MySqlDataSource {
                 begin: None,
                 end: None,
             }),
-            _ => bail!("We haven't implemented a converter for {}", data_type),
+            _ => bail!(
+                "We haven't implemented a converter for {}",
+                column_info.data_type
+            ),
         };
 
         Ok(content)
@@ -216,6 +221,7 @@ impl TryFrom<MySqlRow> for ColumnInfo {
             is_nullable: row.try_get::<String, usize>(2)? == *"YES",
             data_type: row.try_get::<String, usize>(3)?,
             character_maximum_length: extract_column_char_max_len(4, row)?,
+            is_custom_type: false,
         })
     }
 }
diff --git a/synth/src/datasource/postgres_datasource.rs b/synth/src/datasource/postgres_datasource.rs
index 8035c897..6a61d9e8 100644
--- a/synth/src/datasource/postgres_datasource.rs
+++ b/synth/src/datasource/postgres_datasource.rs
@@ -8,14 +8,14 @@ use async_std::task;
 use async_trait::async_trait;
 use rust_decimal::prelude::ToPrimitive;
 use rust_decimal::Decimal;
-use sqlx::postgres::{PgColumn, PgPoolOptions, PgQueryResult, PgRow};
+use sqlx::postgres::{PgColumn, PgPoolOptions, PgQueryResult, PgRow, PgTypeInfo, PgTypeKind};
 use sqlx::{Column, Executor, Pool, Postgres, Row, TypeInfo};
 use std::collections::BTreeMap;
 use std::convert::TryFrom;
 use synth_core::schema::number_content::{F32, F64, I32, I64};
 use synth_core::schema::{
-    BoolContent, ChronoValueType, DateTimeContent, NumberContent, RangeStep, RegexContent,
-    StringContent, Uuid,
+    BoolContent, Categorical, ChronoValueType, DateTimeContent, NumberContent, RangeStep,
+    RegexContent, StringContent, Uuid,
 };
 use synth_core::{Content, Value};
 
@@ -153,7 +153,7 @@ impl RelationalDataSource for PostgresDataSource {
 
     async fn get_columns_infos(&self, table_name: &str) -> Result<Vec<ColumnInfo>> {
         let query = r"SELECT column_name, ordinal_position, is_nullable, udt_name,
-        character_maximum_length
+        character_maximum_length, data_type
         FROM information_schema.columns
         WHERE table_name = $1
         AND table_schema = $2
@@ -234,15 +234,23 @@ impl RelationalDataSource for PostgresDataSource {
             .collect()
     }
 
-    fn decode_to_content(&self, data_type: &str, char_max_len: Option<i32>) -> Result<Content> {
-        let content = match data_type.to_lowercase().as_str() {
+    fn decode_to_content(&self, column_info: &ColumnInfo) -> Result<Content> {
+        if column_info.is_custom_type {
+            return Ok(Content::String(StringContent::Categorical(
+                Categorical::default(),
+            )));
+        }
+
+        let content = match column_info.data_type.to_lowercase().as_str() {
             "bool" => Content::Bool(BoolContent::default()),
             "oid" => {
                 bail!("OID data type not supported")
             }
             "char" | "varchar" | "text" | "citext" | "bpchar" | "name" | "unknown" => {
-                let pattern =
-                    "[a-zA-Z0-9]{0, {}}".replace("{}", &format!("{}", char_max_len.unwrap_or(1)));
+                let pattern = "[a-zA-Z0-9]{0, {}}".replace(
+                    "{}",
+                    &format!("{}", column_info.character_maximum_length.unwrap_or(1)),
+                );
                 Content::String(StringContent::Pattern(
                     RegexContent::pattern(pattern).context("pattern will always compile")?,
                 ))
@@ -272,7 +280,10 @@ impl RelationalDataSource for PostgresDataSource {
                 end: None,
             }),
             "uuid" => Content::String(StringContent::Uuid(Uuid)),
-            _ => bail!("We haven't implemented a converter for {}", data_type),
+            _ => bail!(
+                "We haven't implemented a converter for {}",
+                column_info.data_type
+            ),
         };
 
         Ok(content)
@@ -300,6 +311,7 @@ impl TryFrom<PgRow> for ColumnInfo {
             is_nullable: row.try_get::<String, usize>(2)? == *"YES",
             data_type: row.try_get(3)?,
             character_maximum_length: row.try_get(4)?,
+            is_custom_type: row.try_get::<String, usize>(5)? == "USER-DEFINED",
         })
     }
 }
@@ -344,6 +356,11 @@ impl TryFrom<PgRow> for ValueWrapper {
 }
 
 fn try_match_value(row: &PgRow, column: &PgColumn) -> Result<Value> {
+    if let PgTypeKind::Enum(_) = column.type_info().kind() {
+        let s = row.try_get::<EnumType, &str>(column.name())?;
+        return Ok(Value::String(s.into()));
+    }
+
     let value = match column.type_info().name().to_lowercase().as_str() {
         "bool" => Value::Bool(row.try_get::<bool, &str>(column.name())?),
         "oid" => {
@@ -382,3 +399,30 @@ fn try_match_value(row: &PgRow, column: &PgColumn) -> Result<Value> {
 
     Ok(value)
 }
+
+/// Special type for importing enums
+struct EnumType(String);
+
+impl sqlx::types::Type<Postgres> for EnumType {
+    fn type_info() -> PgTypeInfo {
+        <&str as sqlx::types::Type<Postgres>>::type_info()
+    }
+
+    fn compatible(ty: &PgTypeInfo) -> bool {
+        matches!(ty.kind(), PgTypeKind::Enum(_))
+    }
+}
+
+impl<'r> sqlx::Decode<'r, Postgres> for EnumType {
+    fn decode(
+        value: sqlx::postgres::PgValueRef<'r>,
+    ) -> std::result::Result<Self, Box<dyn std::error::Error + 'static + Send + Sync>> {
+        Ok(Self(<String as sqlx::Decode<Postgres>>::decode(value)?))
+    }
+}
+
+impl From<EnumType> for String {
+    fn from(enum_type: EnumType) -> Self {
+        enum_type.0
+    }
+}
diff --git a/synth/src/datasource/relational_datasource.rs b/synth/src/datasource/relational_datasource.rs
index 75022a1d..300fda42 100644
--- a/synth/src/datasource/relational_datasource.rs
+++ b/synth/src/datasource/relational_datasource.rs
@@ -15,6 +15,7 @@ pub struct ColumnInfo {
     pub(crate) column_name: String,
     pub(crate) ordinal_position: i32,
     pub(crate) is_nullable: bool,
+    pub(crate) is_custom_type: bool,
     pub(crate) data_type: String,
     pub(crate) character_maximum_length: Option<i32>,
 }
@@ -153,7 +154,7 @@ pub trait RelationalDataSource: DataSource {
 
     async fn get_deterministic_samples(&self, table_name: &str) -> Result<Vec<Value>>;
 
-    fn decode_to_content(&self, data_type: &str, _char_max_len: Option<i32>) -> Result<Content>;
+    fn decode_to_content(&self, column_info: &ColumnInfo) -> Result<Content>;
 
     // Returns extended query string + current index
     fn extend_parameterised_query(query: &mut String, curr_index: usize, extend: usize);


Test patch:
diff --git a/synth/testing_harness/postgres/.gitignore b/synth/testing_harness/postgres/.gitignore
index d8d5d5eb..b900073e 100644
--- a/synth/testing_harness/postgres/.gitignore
+++ b/synth/testing_harness/postgres/.gitignore
@@ -1,1 +1,2 @@
 hospital_data_generated.json
+hospital_import
diff --git a/synth/testing_harness/postgres/0_hospital_schema.sql b/synth/testing_harness/postgres/0_hospital_schema.sql
index 60f82e1d..0a6b8f99 100644
--- a/synth/testing_harness/postgres/0_hospital_schema.sql
+++ b/synth/testing_harness/postgres/0_hospital_schema.sql
@@ -1,5 +1,9 @@
+drop table if exists patients;
+drop table if exists doctors;
 drop table if exists hospitals;
 
+drop type if exists gender;
+
 create table hospitals
 (
     id            int primary key,
@@ -7,8 +11,6 @@ create table hospitals
     address       varchar(255)
 );
 
-drop table if exists doctors;
-
 create table doctors
 (
     id          int primary key,
@@ -17,15 +19,16 @@ create table doctors
     date_joined date
 );
 
-drop table if exists patients;
+create type gender as enum ('male', 'female', 'unspecified');
 
 create table patients
 (
     id          int primary key,
     doctor_id   int references doctors (id),
     name        varchar(255),
+    gender      gender,
     date_joined date,
     address     varchar(255),
     phone       varchar(20),
     ssn         varchar(12)
-);
\ No newline at end of file
+);
diff --git a/synth/testing_harness/postgres/1_hospital_data.sql b/synth/testing_harness/postgres/1_hospital_data.sql
index 083f0577..a42d5204 100644
--- a/synth/testing_harness/postgres/1_hospital_data.sql
+++ b/synth/testing_harness/postgres/1_hospital_data.sql
@@ -43,80 +43,80 @@ INSERT INTO public.doctors (id,hospital_id,name,date_joined) VALUES
 
 -- Patients
 
-INSERT INTO public.patients (id,doctor_id,name,date_joined,address,phone,ssn) VALUES
-(1,1,'Thomas Dixon','2010-02-24','Studio 6\nCross lock\nEmilyside\nS2E 5RY','(0114)4960562','145-94-5022'),
-(2,2,'Anthony Morales','2013-07-06','Flat 52D\nAkhtar overpass\nNew Bernardshire\nLN8 1RE','+441154960433','812-01-4209'),
-(3,3,'Curtis West','2010-05-06','6 Fox roads\nSouth Martynchester\nHS3W 1TT','(0116) 496 0076','548-03-1303'),
-(4,1,'Nicholas Ryan','2010-10-31','90 Grace radial\nEast Terenceside\nRH87 6GH','(028) 9018 0291','504-87-6136'),
-(5,2,'Kristy Rodriguez','2017-08-27','035 Oliver forks\nThompsonborough\nB6 2NE','(0808)1570377','689-22-2477'),
-(6,3,'Tracy Pacheco','2011-12-28','Studio 52\nDorothy tunnel\nMartinstad\nRH69 3AA','(0121)4960038','431-83-7953'),
-(7,1,'Tracy Stevens','2010-10-17','Flat 74\nAndrew manor\nDianaland\nMK5A 2ZT','01632 960167','552-39-8225'),
-(8,2,'Thomas Scott','2012-01-04','Flat 26q\nPowell manor\nBarrymouth\nM2 0QU','01184960712','151-56-5875'),
-(9,3,'Tammy Charles','2013-10-23','798 Jenkins harbor\nNorth Jeremy\nIG49 7YS','+44161 496 0324','474-29-4412'),
-(10,1,'Jeanette Nichols','2010-04-06','3 Valerie tunnel\nNorth Colin\nSA2N 2QR','+44113 496 0050','378-64-1750');
-INSERT INTO public.patients (id,doctor_id,name,date_joined,address,phone,ssn) VALUES
-(11,4,'Brett White','2010-11-13','Studio 31\nHughes mountains\nEast Norman\nTS23 7JW','+44909 8790083','548-42-7713'),
-(12,5,'Raymond Rodriguez','2017-06-10','Studio 23a\nSam turnpike\nEmmaview\nOX3P 1DN','+44(0)909 8790659','079-84-1510'),
-(13,6,'Lisa Johnson','2018-12-12','Flat 7\nRachel hill\nJenniferfurt\nG2 0QD','(0161)4960608','865-55-3656'),
-(14,4,'Christopher Gilbert','2010-12-29','Studio 0\nJulie motorway\nWatersville\nEN98 4SJ','(0808)1570048','121-28-3435'),
-(15,5,'Dr. Evan Garcia','2012-02-11','688 Glenn cliff\nAmberton\nN0 4PZ','(0161) 4960368','159-49-5330'),
-(16,6,'Christine Phillips','2012-07-06','2 Damian lights\nReecemouth\nMK15 1JQ','+44(0)117 4960500','725-75-1763'),
-(17,4,'Joseph Stone','2011-12-10','203 Rachael union\nJeanmouth\nG07 8DA','+44121 4960898','839-95-0445'),
-(18,5,'Donald Ford','2013-12-29','064 Glover forges\nNorth Philipport\nL88 8QU','01144960306','197-85-6864'),
-(19,6,'Robert Mayer','2017-03-30','Studio 91n\nLambert squares\nBrownside\nB6 9YP','01174960906','696-18-2664'),
-(20,4,'Deborah Watkins','2011-01-19','0 Paul port\nNorth Benjamin\nL7 0ZX','+44117 4960736','862-01-7079');
-INSERT INTO public.patients (id,doctor_id,name,date_joined,address,phone,ssn) VALUES
-(21,7,'Hannah Kelly','2018-01-17','66 Billy turnpike\nNorth Melissa\nAL34 2RG','(0113) 4960268','388-46-1305'),
-(22,8,'Jesus Davis','2014-11-28','0 Taylor path\nNorth Judithville\nSY6 8SA','+44116 496 0687','020-44-4656'),
-(23,9,'Tammy Green','2015-08-06','5 Thomas mountain\nSouth Callumland\nE9C 0LQ','+44306 999 0527','086-87-2967'),
-(24,7,'Gina Hunt','2014-09-15','59 Gordon bridge\nPort Sandraberg\nWS8A 2AS','0151 496 0045','449-82-8506'),
-(25,8,'Brittany Matthews','2012-09-06','1 Walker station\nEast Alanfurt\nIP7 1QN','0141 496 0206','310-40-7955'),
-(26,9,'Jason Miller','2011-05-16','Studio 91e\nSheila field\nParkerbury\nLU1 3BP','+44(0)115 496 0265','197-90-4619'),
-(27,7,'Victoria Phillips','2014-11-13','47 Mason highway\nLake Andreaview\nTS1E 3XQ','+44(0)114 4960023','276-80-3097'),
-(28,8,'Robin Fowler','2019-04-19','Flat 8\nHeather ridge\nJessicahaven\nGL9R 7SX','+44(0)131 496 0765','544-59-7500'),
-(29,9,'Gregory Valencia','2016-02-19','090 Dunn shoal\nEast Katyville\nL9J 9GQ','+44(0)118 4960683','477-72-8246'),
-(30,7,'Maria Fletcher','2017-09-12','Flat 53\nRachael station\nNew Jemma\nE0 1JD','+4428 9018615','591-54-6181');
-INSERT INTO public.patients (id,doctor_id,name,date_joined,address,phone,ssn) VALUES
-(31,10,'Jennifer Medina','2017-07-12','0 Murphy track\nJohnsonmouth\nW8W 8GZ','01164960054','172-54-0780'),
-(32,11,'Mr. Brian Porter','2016-10-29','Flat 4\nRice camp\nGoodwinview\nW20 5XZ','01164960868','179-79-1760'),
-(33,12,'Mark Young','2011-03-16','Flat 75\nCarol radial\nNorth Valerie\nHX62 6QA','(0114) 4960210','579-70-1181'),
-(34,10,'Michael Nicholson','2017-11-16','53 Nicole camp\nGeoffreyside\nNN3H 1FH','(0141) 4960237','155-92-3714'),
-(35,11,'George Mcguire','2013-11-07','189 Gould overpass\nCookefurt\nHD9 9RF','+44141 4960493','113-40-5786'),
-(36,12,'Karen Watkins','2019-11-21','76 Carol view\nNew Stephanie\nRH7H 0FQ','+441314960135','567-67-7368'),
-(37,10,'Stephanie Gamble','2014-08-09','97 Ann flats\nEast Charleneside\nWC7 1HT','+44121 4960213','250-03-1755'),
-(38,11,'Kim Meyers','2018-11-26','409 Butcher light\nLake Victoriafort\nB65 5FE','(0117)4960932','739-57-9547'),
-(39,12,'Ashley Harris','2012-05-19','06 Matthews mews\nWoodshire\nUB82 1JY','+44(0)121 4960110','723-90-8396'),
-(40,10,'Justin Kim','2010-11-20','Flat 25\nLisa inlet\nPort Ritafurt\nHS41 5ER','+44(0)118 496 0663','823-58-6236');
-INSERT INTO public.patients (id,doctor_id,name,date_joined,address,phone,ssn) VALUES
-(41,13,'Mrs. Kristin Lewis DVM','2010-12-25','00 Barnes wells\nNew Sian\nS00 8YR','(020) 74960924','156-29-6887'),
-(42,14,'Janet Fernandez','2016-11-29','Flat 79\nNorman run\nSharonmouth\nB4U 7BL','+44(0)115 496 0126','460-18-0819'),
-(43,15,'Leslie Casey','2018-05-19','66 Smith expressway\nNormantown\nL1 5WR','+44(0)306 999 0764','067-93-6633'),
-(44,13,'Samantha Wright','2015-02-03','629 Burton gateway\nHughville\nS9 3LJ','0118 4960244','738-95-0807'),
-(45,14,'Brett Miller','2018-04-11','436 Graham hill\nShirleyborough\nPL5Y 3LD','0141 496 0793','726-38-6007'),
-(46,15,'Jason Crane','2017-04-26','Flat 99\nYoung fall\nAliceton\nB0S 2AT','+44(0)1174960479','833-04-0115'),
-(47,13,'Madison Yoder','2015-03-23','062 Katy landing\nPhillipsview\nSS91 9YP','01184960060','006-10-0679'),
-(48,14,'Michael Sellers','2019-10-28','142 Stephen junction\nBennettfurt\nW2W 9BD','+44(0)8081570284','472-17-6148'),
-(49,15,'Rebecca Brown','2011-10-20','Flat 64\nDennis throughway\nEast Benjaminland\nDT37 3FL','(0117) 4960069','233-17-3594'),
-(50,13,'Cody Bryant','2015-08-23','Studio 5\nLewis hollow\nEdwardsland\nS96 4TG','+44115 496 0334','649-23-3293');
-INSERT INTO public.patients (id,doctor_id,name,date_joined,address,phone,ssn) VALUES
-(51,16,'Kevin Payne','2019-03-24','Studio 8\nBethan stream\nNew Elliottshire\nSL7H 0SS','+441314960539','585-82-2591'),
-(52,17,'Megan Ryan','2016-04-21','Flat 6\nHenry inlet\nWest Samantha\nHS7N 9FT','+44(0)116 4960007','698-18-1758'),
-(53,18,'Matthew Henry','2018-10-20','743 Shaw knoll\nLeeside\nM01 3HY','01154960297','775-07-6124'),
-(54,16,'Noah Chandler','2013-03-31','Studio 73\nBell loaf\nAlitown\nCA0 8AY','+44121 4960245','761-56-1725'),
-(55,17,'Garrett Fowler','2011-07-17','Flat 23A\nMitchell squares\nHoltchester\nE23 9WJ','+441632 960 338','859-35-9693'),
-(56,18,'Spencer Campbell','2013-06-16','915 Mason bridge\nSouth Patriciachester\nDA61 0ZN','+44(0)808 157 0553','363-69-2489'),
-(57,16,'Robert Becker','2018-11-27','900 Boyle forest\nPort Andrewburgh\nM6 2HT','(020) 7496 0080','788-71-1402'),
-(58,17,'Matthew Murillo','2015-03-01','Studio 30\nHughes river\nWhitemouth\nS10 0LN','02074960325','330-99-9498'),
-(59,18,'Brandy Hernandez','2018-05-27','Flat 3\nAntony forges\nLake Eric\nPO0 8UP','09098790239','803-29-7495'),
-(60,16,'Jamie Sutton','2010-11-19','927 Gibbs cliff\nGregoryshire\nUB6H 9XQ','+44(0)29 2018479','645-23-9803');
-INSERT INTO public.patients (id,doctor_id,name,date_joined,address,phone,ssn) VALUES
-(61,19,'Renee Robertson','2019-07-24','0 Jodie pike\nEast Alan\nBH7Y 2WN','(0115) 4960107','787-06-1801'),
-(62,20,'Rachel Nelson','2012-01-11','Studio 6\nAdam lodge\nNew Maureen\nG4W 3UN','0131 4960314','829-19-7394'),
-(63,21,'Melissa Silva','2019-05-09','Studio 58\nGreen shore\nNorth Shauntown\nS54 5NY','(0121) 4960580','321-13-7363'),
-(64,19,'Frank Bradley','2012-09-08','9 Sandra avenue\nJaniceville\nM1 0JP','(0161) 496 0548','187-91-0569'),
-(65,20,'Stephen Briggs','2010-03-29','Flat 82\nQuinn parkways\nEast Joyceland\nG2 2TX','029 2018 0186','231-59-8966'),
-(66,21,'Emily Camacho','2016-12-26','093 Marshall plains\nEast Mathewfort\nKY3 5WU','+44(0)909 8790988','534-62-0149'),
-(67,19,'Ryan Cain','2017-11-18','8 Dawn glens\nLouiseburgh\nE1W 0LH','(0141)4960552','518-81-2366'),
-(68,20,'Daniel Thornton','2017-01-16','01 Lisa cliff\nSmithmouth\nOX92 5ZQ','01414960391','511-66-0104'),
-(69,21,'Kyle Pacheco','2018-06-26','754 Thompson lodge\nThomashaven\nDH41 3JW','(028) 9018567','008-70-2086'),
-(70,19,'Scott Nielsen','2016-10-15','Studio 74y\nBeth path\nMooreburgh\nE2 9ZB','(0808)1570400','823-56-1410');
\ No newline at end of file
+INSERT INTO public.patients (id,doctor_id,name,gender,date_joined,address,phone,ssn) VALUES
+(1,1,'Thomas Dixon','male','2010-02-24','Studio 6\nCross lock\nEmilyside\nS2E 5RY','(0114)4960562','145-94-5022'),
+(2,2,'Anthony Morales','male','2013-07-06','Flat 52D\nAkhtar overpass\nNew Bernardshire\nLN8 1RE','+441154960433','812-01-4209'),
+(3,3,'Curtis West','male','2010-05-06','6 Fox roads\nSouth Martynchester\nHS3W 1TT','(0116) 496 0076','548-03-1303'),
+(4,1,'Nicholas Ryan','male','2010-10-31','90 Grace radial\nEast Terenceside\nRH87 6GH','(028) 9018 0291','504-87-6136'),
+(5,2,'Kristy Rodriguez','unspecified','2017-08-27','035 Oliver forks\nThompsonborough\nB6 2NE','(0808)1570377','689-22-2477'),
+(6,3,'Tracy Pacheco','female','2011-12-28','Studio 52\nDorothy tunnel\nMartinstad\nRH69 3AA','(0121)4960038','431-83-7953'),
+(7,1,'Tracy Stevens','female','2010-10-17','Flat 74\nAndrew manor\nDianaland\nMK5A 2ZT','01632 960167','552-39-8225'),
+(8,2,'Thomas Scott','male','2012-01-04','Flat 26q\nPowell manor\nBarrymouth\nM2 0QU','01184960712','151-56-5875'),
+(9,3,'Tammy Charles','unspecified','2013-10-23','798 Jenkins harbor\nNorth Jeremy\nIG49 7YS','+44161 496 0324','474-29-4412'),
+(10,1,'Jeanette Nichols','female','2010-04-06','3 Valerie tunnel\nNorth Colin\nSA2N 2QR','+44113 496 0050','378-64-1750');
+INSERT INTO public.patients (id,doctor_id,name,gender,date_joined,address,phone,ssn) VALUES
+(11,4,'Brett White','male','2010-11-13','Studio 31\nHughes mountains\nEast Norman\nTS23 7JW','+44909 8790083','548-42-7713'),
+(12,5,'Raymond Rodriguez','male','2017-06-10','Studio 23a\nSam turnpike\nEmmaview\nOX3P 1DN','+44(0)909 8790659','079-84-1510'),
+(13,6,'Lisa Johnson','female','2018-12-12','Flat 7\nRachel hill\nJenniferfurt\nG2 0QD','(0161)4960608','865-55-3656'),
+(14,4,'Christopher Gilbert','male','2010-12-29','Studio 0\nJulie motorway\nWatersville\nEN98 4SJ','(0808)1570048','121-28-3435'),
+(15,5,'Dr. Evan Garcia','unspecified','2012-02-11','688 Glenn cliff\nAmberton\nN0 4PZ','(0161) 4960368','159-49-5330'),
+(16,6,'Christine Phillips','unspecified','2012-07-06','2 Damian lights\nReecemouth\nMK15 1JQ','+44(0)117 4960500','725-75-1763'),
+(17,4,'Joseph Stone','male','2011-12-10','203 Rachael union\nJeanmouth\nG07 8DA','+44121 4960898','839-95-0445'),
+(18,5,'Donald Ford','male','2013-12-29','064 Glover forges\nNorth Philipport\nL88 8QU','01144960306','197-85-6864'),
+(19,6,'Robert Mayer','unspecified','2017-03-30','Studio 91n\nLambert squares\nBrownside\nB6 9YP','01174960906','696-18-2664'),
+(20,4,'Deborah Watkins','female','2011-01-19','0 Paul port\nNorth Benjamin\nL7 0ZX','+44117 4960736','862-01-7079');
+INSERT INTO public.patients (id,doctor_id,name,gender,date_joined,address,phone,ssn) VALUES
+(21,7,'Hannah Kelly','unspecified','2018-01-17','66 Billy turnpike\nNorth Melissa\nAL34 2RG','(0113) 4960268','388-46-1305'),
+(22,8,'Jesus Davis','male','2014-11-28','0 Taylor path\nNorth Judithville\nSY6 8SA','+44116 496 0687','020-44-4656'),
+(23,9,'Tammy Green','female','2015-08-06','5 Thomas mountain\nSouth Callumland\nE9C 0LQ','+44306 999 0527','086-87-2967'),
+(24,7,'Gina Hunt','female','2014-09-15','59 Gordon bridge\nPort Sandraberg\nWS8A 2AS','0151 496 0045','449-82-8506'),
+(25,8,'Brittany Matthews','female','2012-09-06','1 Walker station\nEast Alanfurt\nIP7 1QN','0141 496 0206','310-40-7955'),
+(26,9,'Jason Miller','male','2011-05-16','Studio 91e\nSheila field\nParkerbury\nLU1 3BP','+44(0)115 496 0265','197-90-4619'),
+(27,7,'Victoria Phillips','female','2014-11-13','47 Mason highway\nLake Andreaview\nTS1E 3XQ','+44(0)114 4960023','276-80-3097'),
+(28,8,'Robin Fowler','unspecified','2019-04-19','Flat 8\nHeather ridge\nJessicahaven\nGL9R 7SX','+44(0)131 496 0765','544-59-7500'),
+(29,9,'Gregory Valencia','male','2016-02-19','090 Dunn shoal\nEast Katyville\nL9J 9GQ','+44(0)118 4960683','477-72-8246'),
+(30,7,'Maria Fletcher','female','2017-09-12','Flat 53\nRachael station\nNew Jemma\nE0 1JD','+4428 9018615','591-54-6181');
+INSERT INTO public.patients (id,doctor_id,name,gender,date_joined,address,phone,ssn) VALUES
+(31,10,'Jennifer Medina','unspecified','2017-07-12','0 Murphy track\nJohnsonmouth\nW8W 8GZ','01164960054','172-54-0780'),
+(32,11,'Mr. Brian Porter','unspecified','2016-10-29','Flat 4\nRice camp\nGoodwinview\nW20 5XZ','01164960868','179-79-1760'),
+(33,12,'Mark Young','male','2011-03-16','Flat 75\nCarol radial\nNorth Valerie\nHX62 6QA','(0114) 4960210','579-70-1181'),
+(34,10,'Michael Nicholson','male','2017-11-16','53 Nicole camp\nGeoffreyside\nNN3H 1FH','(0141) 4960237','155-92-3714'),
+(35,11,'George Mcguire','male','2013-11-07','189 Gould overpass\nCookefurt\nHD9 9RF','+44141 4960493','113-40-5786'),
+(36,12,'Karen Watkins','female','2019-11-21','76 Carol view\nNew Stephanie\nRH7H 0FQ','+441314960135','567-67-7368'),
+(37,10,'Stephanie Gamble','female','2014-08-09','97 Ann flats\nEast Charleneside\nWC7 1HT','+44121 4960213','250-03-1755'),
+(38,11,'Kim Meyers','female','2018-11-26','409 Butcher light\nLake Victoriafort\nB65 5FE','(0117)4960932','739-57-9547'),
+(39,12,'Ashley Harris','female','2012-05-19','06 Matthews mews\nWoodshire\nUB82 1JY','+44(0)121 4960110','723-90-8396'),
+(40,10,'Justin Kim','unspecified','2010-11-20','Flat 25\nLisa inlet\nPort Ritafurt\nHS41 5ER','+44(0)118 496 0663','823-58-6236');
+INSERT INTO public.patients (id,doctor_id,name,gender,date_joined,address,phone,ssn) VALUES
+(41,13,'Mrs. Kristin Lewis DVM','female','2010-12-25','00 Barnes wells\nNew Sian\nS00 8YR','(020) 74960924','156-29-6887'),
+(42,14,'Janet Fernandez','unspecified','2016-11-29','Flat 79\nNorman run\nSharonmouth\nB4U 7BL','+44(0)115 496 0126','460-18-0819'),
+(43,15,'Leslie Casey','female','2018-05-19','66 Smith expressway\nNormantown\nL1 5WR','+44(0)306 999 0764','067-93-6633'),
+(44,13,'Samantha Wright','female','2015-02-03','629 Burton gateway\nHughville\nS9 3LJ','0118 4960244','738-95-0807'),
+(45,14,'Brett Miller','male','2018-04-11','436 Graham hill\nShirleyborough\nPL5Y 3LD','0141 496 0793','726-38-6007'),
+(46,15,'Jason Crane','unspecified','2017-04-26','Flat 99\nYoung fall\nAliceton\nB0S 2AT','+44(0)1174960479','833-04-0115'),
+(47,13,'Madison Yoder','female','2015-03-23','062 Katy landing\nPhillipsview\nSS91 9YP','01184960060','006-10-0679'),
+(48,14,'Michael Sellers','male','2019-10-28','142 Stephen junction\nBennettfurt\nW2W 9BD','+44(0)8081570284','472-17-6148'),
+(49,15,'Rebecca Brown','unspecified','2011-10-20','Flat 64\nDennis throughway\nEast Benjaminland\nDT37 3FL','(0117) 4960069','233-17-3594'),
+(50,13,'Cody Bryant','male','2015-08-23','Studio 5\nLewis hollow\nEdwardsland\nS96 4TG','+44115 496 0334','649-23-3293');
+INSERT INTO public.patients (id,doctor_id,name,gender,date_joined,address,phone,ssn) VALUES
+(51,16,'Kevin Payne','male','2019-03-24','Studio 8\nBethan stream\nNew Elliottshire\nSL7H 0SS','+441314960539','585-82-2591'),
+(52,17,'Megan Ryan','female','2016-04-21','Flat 6\nHenry inlet\nWest Samantha\nHS7N 9FT','+44(0)116 4960007','698-18-1758'),
+(53,18,'Matthew Henry','unspecified','2018-10-20','743 Shaw knoll\nLeeside\nM01 3HY','01154960297','775-07-6124'),
+(54,16,'Noah Chandler','male','2013-03-31','Studio 73\nBell loaf\nAlitown\nCA0 8AY','+44121 4960245','761-56-1725'),
+(55,17,'Garrett Fowler','male','2011-07-17','Flat 23A\nMitchell squares\nHoltchester\nE23 9WJ','+441632 960 338','859-35-9693'),
+(56,18,'Spencer Campbell','male','2013-06-16','915 Mason bridge\nSouth Patriciachester\nDA61 0ZN','+44(0)808 157 0553','363-69-2489'),
+(57,16,'Robert Becker','male','2018-11-27','900 Boyle forest\nPort Andrewburgh\nM6 2HT','(020) 7496 0080','788-71-1402'),
+(58,17,'Matthew Murillo','male','2015-03-01','Studio 30\nHughes river\nWhitemouth\nS10 0LN','02074960325','330-99-9498'),
+(59,18,'Brandy Hernandez','male','2018-05-27','Flat 3\nAntony forges\nLake Eric\nPO0 8UP','09098790239','803-29-7495'),
+(60,16,'Jamie Sutton','male','2010-11-19','927 Gibbs cliff\nGregoryshire\nUB6H 9XQ','+44(0)29 2018479','645-23-9803');
+INSERT INTO public.patients (id,doctor_id,name,gender,date_joined,address,phone,ssn) VALUES
+(61,19,'Renee Robertson','female','2019-07-24','0 Jodie pike\nEast Alan\nBH7Y 2WN','(0115) 4960107','787-06-1801'),
+(62,20,'Rachel Nelson','unspecified','2012-01-11','Studio 6\nAdam lodge\nNew Maureen\nG4W 3UN','0131 4960314','829-19-7394'),
+(63,21,'Melissa Silva','female','2019-05-09','Studio 58\nGreen shore\nNorth Shauntown\nS54 5NY','(0121) 4960580','321-13-7363'),
+(64,19,'Frank Bradley','male','2012-09-08','9 Sandra avenue\nJaniceville\nM1 0JP','(0161) 496 0548','187-91-0569'),
+(65,20,'Stephen Briggs','unspecified','2010-03-29','Flat 82\nQuinn parkways\nEast Joyceland\nG2 2TX','029 2018 0186','231-59-8966'),
+(66,21,'Emily Camacho','female','2016-12-26','093 Marshall plains\nEast Mathewfort\nKY3 5WU','+44(0)909 8790988','534-62-0149'),
+(67,19,'Ryan Cain','male','2017-11-18','8 Dawn glens\nLouiseburgh\nE1W 0LH','(0141)4960552','518-81-2366'),
+(68,20,'Daniel Thornton','male','2017-01-16','01 Lisa cliff\nSmithmouth\nOX92 5ZQ','01414960391','511-66-0104'),
+(69,21,'Kyle Pacheco','male','2018-06-26','754 Thompson lodge\nThomashaven\nDH41 3JW','(028) 9018567','008-70-2086'),
+(70,19,'Scott Nielsen','male','2016-10-15','Studio 74y\nBeth path\nMooreburgh\nE2 9ZB','(0808)1570400','823-56-1410');
diff --git a/synth/testing_harness/postgres/hospital_data_generated_master.json b/synth/testing_harness/postgres/hospital_data_generated_master.json
index ab1095cf..cc34853e 100644
--- a/synth/testing_harness/postgres/hospital_data_generated_master.json
+++ b/synth/testing_harness/postgres/hospital_data_generated_master.json
@@ -61,45 +61,50 @@
       "address": "Y8jRmG6xjnchpv08FQOovJDr0nnq4m8Er3IxAhd0ccccvZciPCulne8nuQX0j8ddHUlNrw6dw6GpIWOLEDLCcJiZDj0fkbB9iV1guzLlgxS8zTqxpJM5Q6CBF1WKhZ7RYWt4a5NwKgihcWiKlU6NHDWhf",
       "date_joined": null,
       "doctor_id": 1,
+      "gender": null,
       "id": 1,
-      "name": "hVel8NQPJ0PUL228jE0SCVmYnnuXwQqX5DJTAYdvXxD53ZAGMjFj6K46UIp8CF9xkuGd3DhU9RJyfK0myw5vJXdAqU9Ns9ycOsChvqaHH3Elg5zJaMuo2uftO97TSisawK2PCkqe45tki0A9IX6lWRkQXUjy6rW6HqgDn1kX3mmKXj7HhjLDRRbw3vmLIlrw26rYtdNMeWlmay",
-      "phone": null,
-      "ssn": null
+      "name": null,
+      "phone": "8NQPJ0PUL228jE0",
+      "ssn": "OeE"
     },
     {
-      "address": null,
-      "date_joined": "2012-02-10",
+      "address": "XwQqX5DJTAYdvXxD53ZAGMjFj6K46UIp8CF9xkuGd3DhU9RJyfK0myw5vJXdAqU9Ns9ycOsChvqaHH3Elg5zJaMuo2uftO97TSisawK2PCkqe45tki0A9IX6lWRkQXUjy6rW6HqgDn1kX3mmKXj7HhjLDRRbw3vmLIlrw26rYtdNMeWlmayTleVzvHPCjZFTJm8SzGOe0iTASsVtZjQShDtfYkVEAZrDoCrO6VK",
+      "date_joined": null,
       "doctor_id": 2,
+      "gender": null,
       "id": 2,
-      "name": "HsLAbgeeKzWI1FSANXVUnevpn5LL8M2X0XXhi87wLqTILHcaH0bQBhMdrAQuN1TO2YC5ZY1WlGfrLO52adbvSgFUZCPhUaYCPT3QoQYHEA55ziZvgWRT78Tjn4j8m1zEG0vZ",
-      "phone": null,
+      "name": null,
+      "phone": "0bQBh",
       "ssn": null
     },
     {
-      "address": "cME2jCs01ReQyZyOWOTscJVFvjfryFsTNiBpQZkiB6FADYFNtRzvY0diniut4z3JG270eww5CvhiQwG4Bj6phHTypY1KHOsvr8T79mfr25WdoPZNXmGULGgsC8CdKpjYDN2KFv2he8pT7Ud9qyEXSsQukTjLv3ptEn4sPadtrCXwxo969",
-      "date_joined": "2015-01-23",
+      "address": "SPlQAyXDzayYd5T14t1UdJbN6smOu8wwVrEUgvpp93sJiwQApdmbIrQlUftLVhowjMe0AK1YpCZ4jBfgcME2jCs01",
+      "date_joined": null,
       "doctor_id": 3,
+      "gender": null,
       "id": 3,
-      "name": "ShqhHU3ITYYSISxu2E5r9o8n9Rds89AQOnDikJjvUw5vMoAGfyICJ0Kb0S1aLS21UAHQD9q6SVIIJIg6d3imZDIbwKb7pyUpePW0tQwSf2nde",
-      "phone": null,
-      "ssn": null
+      "name": null,
+      "phone": "tNTT4nskvNkjZ",
+      "ssn": "pQ"
     },
     {
-      "address": "gcC9RxhAwcdJDjznRamVPxCBCdT0YrhTGSZIfKDLedqdUs9BoFKnJvgMb1fYvhmi45S5BtkUhxncCBIQ1NafuqCJrcHBnJWtGo3OtjBJxLCW5LZuyayqm5Z49uva8N5lMErDqpLF9I7xurY1IXp6ilVXLS806HZoQTHXnpQOecyRg97pGW9P",
-      "date_joined": "2010-10-19",
+      "address": null,
+      "date_joined": "2011-03-23",
       "doctor_id": 4,
+      "gender": "male",
       "id": 4,
       "name": null,
-      "phone": "QFnZbRKm",
-      "ssn": "Aoz"
+      "phone": null,
+      "ssn": null
     },
     {
       "address": null,
       "date_joined": null,
       "doctor_id": 5,
+      "gender": null,
       "id": 5,
-      "name": "hkPesQsbjhY7sGRBVrnQMrikzZhKSxkeximiNZg2I7LAQHx0ojaw9OmdMYMsxVcJBX61fB1NbUrBZ63fAOpaPgtCrxDpV5KfDddkIAxrTBlvXpwtd6wMmhMZMRnBkNtw5h9YfxQXtVKixIucvh9kk4v3Fmuw6q0QYokvTXyIKXv2pT8VlCGqZ74",
-      "phone": "ufVRAorr",
+      "name": "OFsP1WBZimZVnJLdz6flyRdthjTr5tdowR7GQbZoTdHui7EZTqVUW3snRjy6esftmtawTqGfov4rKIiI1UOaJV29rz0N77XBiD8VovPFXPSG4g3E9ruTlka7QShqhHU3ITYYSISxu2E5r9o8n9Rds89AQOnDikJjvUw5vMoAGfyICJ0Kb0S1aLS21UAHQ",
+      "phone": "MciC2Mq1",
       "ssn": null
     }
   ]
diff --git a/synth/testing_harness/postgres/hospital_master/patients.json b/synth/testing_harness/postgres/hospital_master/patients.json
index 7aa65a64..9fb77e67 100644
--- a/synth/testing_harness/postgres/hospital_master/patients.json
+++ b/synth/testing_harness/postgres/hospital_master/patients.json
@@ -46,6 +46,24 @@
       "type": "same_as",
       "ref": "doctors.content.id"
     },
+    "gender": {
+      "type": "one_of",
+      "variants": [
+        {
+          "weight": 1.0,
+          "type": "string",
+          "categorical": {
+            "female": 3,
+            "male": 6,
+            "unspecified": 1
+          }
+        },
+        {
+          "weight": 1.0,
+          "type": "null"
+        }
+      ]
+    },
     "id": {
       "type": "number",
       "id": {},
@@ -94,4 +112,4 @@
       ]
     }
   }
-}
+}
\ No newline at end of file