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 (and dropped if they already exist). 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. See below for an example.
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. Again, see the examples below.
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. See examples below.
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 | | |
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",
"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": {
"maxEvents": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
},
"events": {
"type": "object",
"properties": {
"exactly": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
}
},
"throttle": {
"type": "object",
"properties": {
"ms": {
"oneOf": [
{
"type": "number",
"minimum": 0
},
{
"type": "object"
}
]
}
}
},
"history": {
"type": "object",
"properties": {
"events": {
"type": "object",
"properties": {
"max": {
"type": "integer",
"minimum": 0
}
}
}
}
},
"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"
]
},
"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"
]
}
}
}
},
"required": [
"table",
"row"
],
"allOf": [
{
"if": {
"properties": {
"op": {
"const": "update"
}
}
},
"then": {
"required": [
"where"
]
}
},
{
"if": {
"properties": {
"op": {
"const": "delete"
}
}
},
"then": {
"required": [
"where"
]
}
}
]
}