Skip to main content

Connections

postgres

Commentary

added in 0.0.5

Connects to a Postgres database instance.

Automatic table creation

By default for convenience, any tables ShadowTraffic writes to will be automatically created. This makes it easier to iterate on your generators without flipping back and forth between ShadowTraffic and Postgres.

ShadowTraffic does this by scanning the structure of your generators and creating a suitable DDL, then executing it on your behalf.

If ShadowTraffic doesn't create the table exactly as you'd want it, you can override each column using the pgHint function modifier. 1

If the table already exists, but you'd like to clear out any existing data, set tablePolicy to dropAndCreate. 2

Manual table control

If you don't ShadowTraffic to control your tables, you can turn this behavior off by setting tablePolicy to manual in the connection map. It'll then be up to you to make sure your tables exist before trying to write to them. 3

Generating updates and deletes

By default, ShadowTraffic only issues INSERT statements to your tables. But sometimes, it might be useful to issue UPDATE and DELETE statements too.

You can control the statement type by setting the op key on your generator. op must be one of insert, update, or delete.

For the latter two, you must also include a where key on your generator, which contains a map of column name to value. You need this because when you issue an update or a delete, you need to tell Postgres what rows you want to change. The where map is used to check equality and select rows that match. 4


Examples

Configuring the connection

Basic configuration for the Postgres connection.

{
"connections": {
"my-pg": {
"kind": "postgres",
"connectionConfigs": {
"host": "localhost",
"port": 5432,
"username": "postgres-user",
"password": "postgres-pw",
"db": "mydb"
}
}
}
}

Automatic table creation

By default, table sandbox doesn't need to be defined. ShadowTraffic will automatically create it for you.

{
"generators": [
{
"table": "sandbox",
"row": {
"id": {
"_gen": "uuid"
},
"level": {
"_gen": "uniformDistribution",
"bounds": [
1,
10
]
},
"active": {
"_gen": "boolean"
}
}
}
],
"connections": {
"postgres": {
"kind": "postgres",
"connectionConfigs": {
"host": "localhost",
"port": 5432,
"username": "postgres",
"password": "postgres",
"db": "mydb"
}
}
}
}

In this case, sandbox will be created like so:

 Column |       Type       | Collation | Nullable | Default
--------+------------------+-----------+----------+---------
id | text | | |
level | double precision | | |
active | boolean | | |

Automatic table truncation

Use dropAndCreate to automatically clear out any rows in an existing target table.

{
"table": "sandbox",
"row": {
"id": {
"_gen": "uuid"
},
"level": {
"_gen": "uniformDistribution",
"bounds": [
1,
10
]
},
"active": {
"_gen": "boolean"
}
}
}

Overriding column types

Use the optional pgHint function modifier on any generator to override how its column is defined.

{
"table": "sandbox",
"row": {
"id": {
"_gen": "uuid",
"pgHint": "TEXT NOT NULL"
},
"level": {
"_gen": "uniformDistribution",
"bounds": [
1,
10
],
"pgHint": "INTEGER"
},
"active": {
"_gen": "boolean"
}
}
}

Now, sandbox will like this:

 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | text | | not null |
level | integer | | |
active | boolean | | |

Manual table creation

Use manual to prevent ShadowTraffic from automatically creating tables for you. When you do this, your tables must already exist before ShadowTraffic tries to write to them.

{
"kind": "postgres",
"tablePolicy": "manual",
"connectionConfigs": {
"host": "localhost",
"port": 5432,
"username": "postgres",
"password": "postgres",
"db": "mydb"
}
}

Upserts and deletes

Use op and where to issue upserts and deletes. It's up to you to make sure that you don't try to update or delete rows that don't exist.

This example uses fork and stateMachine to generate many simultaneous rows being inserted, updated, and deleted.

In the first state, insertIt, a random value of about 50 is generated. In the second state, updateIt, the value gets updated to one of 1, 5, or 10. Finally, in deleteIt, the entire row is deleted.

id is generated once as a fork key and used as the basis for equality to perform updates and deletes.

{
"table": "sandbox",
"fork": {
"key": {
"_gen": "uuid"
}
},
"row": {
"id": {
"_gen": "var",
"var": "forkKey"
}
},
"stateMachine": {
"_gen": "stateMachine",
"initial": "insertIt",
"transitions": {
"insertIt": "updateIt",
"updateIt": "deleteIt"
},
"states": {
"insertIt": {
"row": {
"value": {
"_gen": "normalDistribution",
"mean": 50,
"sd": 15
}
}
},
"updateIt": {
"op": "update",
"where": {
"id": {
"_gen": "var",
"var": "forkKey"
}
},
"row": {
"value": {
"_gen": "oneOf",
"choices": [
1,
5,
10
]
}
}
},
"deleteIt": {
"op": "delete",
"where": {
"id": {
"_gen": "var",
"var": "forkKey"
}
}
}
}
}
}

A random snapshot of the table might look like:

mydb=# select * from sandbox limit 10;
id | value
--------------------------------------+-------
07f217c6-1c82-e1cb-6dd8-fc614ff1a6e3 | 49
83d35207-ac3f-b8a7-fd7e-c82e1bdc3b05 | 10
184a0e26-237f-915b-4d97-5dc1808d91df | 37

Specification

Connection JSON schema

{
"type": "object",
"properties": {
"kind": {
"type": "string",
"const": "postgres"
},
"connectionConfigs": {
"type": "object",
"properties": {
"host": {
"type": "string"
},
"port": {
"type": "integer"
},
"db": {
"type": "string"
},
"username": {
"type": "string"
},
"password": {
"type": "string"
}
},
"required": [
"host",
"port",
"db"
]
},
"batchConfigs": {
"type": "object",
"properties": {
"lingerMs": {
"type": "integer",
"minimum": 0
},
"batchRows": {
"type": "integer",
"minimum": 1
}
}
},
"tablePolicy": {
"type": "string",
"enum": [
"manual",
"create",
"dropAndCreate"
]
}
},
"required": [
"connectionConfigs"
]
}

Generator JSON schema

{
"type": "object",
"properties": {
"connection": {
"type": "string"
},
"table": {
"type": "string"
},
"row": {
"type": "object"
},
"op": {
"type": "string",
"enum": [
"insert",
"update",
"delete"
]
},
"where": {
"type": "object"
},
"localConfigs": {
"type": "object",
"properties": {
"throttleMs": {
"oneOf": [
{
"type": "number",
"minimum": 0
},
{
"type": "object"
}
]
},
"maxEvents": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
},
"discard": {
"type": "object",
"properties": {
"rate": {
"type": "number",
"minimum": 0,
"maximum": 1
}
},
"required": [
"rate"
]
},
"repeat": {
"type": "object",
"properties": {
"rate": {
"type": "number",
"minimum": 0,
"maximum": 1
},
"times": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
},
"required": [
"rate",
"times"
]
},
"maxHistoryEvents": {
"type": "integer",
"minimum": 0
},
"maxMs": {
"type": "integer",
"minimum": 0
},
"time": {
"type": "integer"
},
"events": {
"type": "object",
"properties": {
"exactly": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
}
},
"delay": {
"type": "object",
"properties": {
"rate": {
"type": "number",
"minimum": 0,
"maximum": 1
},
"ms": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
},
"required": [
"rate",
"ms"
]
},
"history": {
"type": "object",
"properties": {
"events": {
"type": "object",
"properties": {
"max": {
"type": "integer",
"minimum": 0
}
}
}
}
},
"throttle": {
"type": "object",
"properties": {
"ms": {
"oneOf": [
{
"type": "number",
"minimum": 0
},
{
"type": "object"
}
]
}
}
},
"timeMultiplier": {
"oneOf": [
{
"type": "number"
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
}
}
},
"required": [
"table",
"row"
],
"allOf": [
{
"if": {
"properties": {
"op": {
"const": "update"
}
}
},
"then": {
"required": [
"where"
]
}
},
{
"if": {
"properties": {
"op": {
"const": "delete"
}
}
},
"then": {
"required": [
"where"
]
}
}
]
}