shuttle-hq / synth

The Declarative Data Generator
https://www.getsynth.com/
Apache License 2.0
1.37k stars 107 forks source link

[POSTGRES] Missing public schema x reserved name #372

Open bitbreakr opened 1 year ago

bitbreakr commented 1 year ago

Describe the bug None of the table names of the project I tried to seed have pluralized names, and all are under the schema named "public". When I try to seed data into the "user" table, I get an error. I think this error is triggered because the "user" keyword of the SQL insert statement is a reserved/protected keyword.

To Reproduce Steps to reproduce the behavior:

  1. Schema (if applicable)
    {
    "type": "array",
    "length": {
    "type": "number",
    "subtype": "u64",
    "range": {
      "low": 6,
      "high": 7,
      "step": 1
    }
    },
    "content": {
    "type": "object",
    "id": {
      "type": "string",
      "uuid": {}
    },
    "email": {
      "type": "string",
      "format": {
        "format": "dev-jobber-{name}@email.com",
        "arguments": {
          "name": {
            "type": "string",
            "faker": {
              "generator": "username"
            }
          },
          "email": {
            "type": "string",
            "faker": {
              "generator": "safe_email"
            }
          }
        }
      }
    },
    "notificationEmailEnable": {
      "type": "bool",
      "frequency": 0.5
    },
    "notificationPhoneEnable": {
      "type": "bool",
      "frequency": 0.5
    },
    "type_": {
      "type": "string",
      "categorical": {
        "Admin": 1,
        "User": 2,
        "Jobber": 3,
        "Seller": 4,
        "Customer": 5
      }
    },
    "firstName": {
      "type": "string",
      "faker": {
        "generator": "first_name"
      }
    },
    "lastName": {
      "type": "string",
      "faker": {
        "generator": "last_name"
      }
    },
    "phone": {
      "type": "string",
      "constant": "+33366666666"
    }
    }
    }
  2. See error

    
    [2022-09-15T18:02:40Z INFO  sqlx::query] INSERT INTO user ("email","firstName","id","lastName","notificationEmailEnable","notificationPhoneEnable","phone","type") …; rows: 0, elapsed: 5.391ms
    
    INSERT INTO
      user (
        "email",
        "firstName",
        "id",
        "lastName",
        "notificationEmailEnable",
        "notificationPhoneEnable",
        "phone",
        "type"
      )
    VALUES
      ($1, $2, $3, $4, $5, $6, $7, $8),
      ($9, $10, $11, $12, $13, $14, $15, $16),
      ($17, $18, $19, $20, $21, $22, $23, $24),
      ($25, $26, $27, $28, $29, $30, $31, $32),
      ($33, $34, $35, $36, $37, $38, $39, $40),
      ($41, $42, $43, $44, $45, $46, $47, $48);

[2022-09-15T18:02:40Z INFO sqlx::query] / SQLx ping /; rows: 0, elapsed: 2.210ms Error: At namespace "public"

Caused by: 0: Failed to insert data for collection user 1: One or more database inserts failed: error returned from database: syntax error at or near "user": syntax error at or near "user"



**Expected behavior**
Synth documentation says: 
> synth can import directly from a [PostgreSQL](https://www.postgresql.org/) database and create a data model from the database schema. During import, a new [namespace](https://www.getsynth.com/docs/getting_started/core-concepts#namespaces) will be created from your database schema, and a [collection](https://www.getsynth.com/docs/getting_started/core-concepts#collections) is created for each table in a separate JSON file. synth will map database columns to fields in the collections it creates. It then provides default generators for every collection. Synth will default to the public schema but this can be overriden with the --schema flag.

If schema name is not precisely  given, then for each query prefix the table name with **public.** eg: public.<table_name> or quote it eg: "<table_name>"

**Screenshots**
### Request Synth tries to execute

![Capture d’écran 2022-09-15 à 20 12 39](https://user-images.githubusercontent.com/3123986/190492139-2f23b1a7-ea3c-4ea0-a78d-36d5b2a3f137.png)

![DataGrip_2022-09-15 20-14-50@2x](https://user-images.githubusercontent.com/3123986/190479388-f8f1f0c1-a7bb-4c00-bf53-1a7a2c004702.png)

### Edits to make it work

![Capture d’écran 2022-09-15 à 20 15 56](https://user-images.githubusercontent.com/3123986/190492356-b6f4f54d-9eb2-4f85-8892-8d8925e767d1.png)

**Environment (please complete the following information):**
 - OS: MacOS
 - Version: Latest

**Additional context**
Add any other context about the problem here.