microservices/psql repository overview
PostgreSQL OMG Microservice
Interact with a Postgres database.
Direct usage in Storyscript:
Create table
A table can be created with createTable:
psql createTable table: "books" columns: {
"id": "serial primary key",
"title": "varchar(100)"
}
Insert an entry
psql insert table: "books" values: {"title": "Ulysses"}
# result: {"id": 1, "title": "Ulysses"}
Insert multiple entries
psql insert table: "books" values: [{"title": "Moby Dick"}, {"title": "War and Peace"}]
# result: [{"id": 2, "title": "Ulysses"}, {"id": 3, "title": "War and Peace"}]
Select entries
$andand$orcan be used to combine queries$lt,$lte,$gt,$gteand$eqcan be used as comparison operators- if no comparison operators is provided, the query will match on equality
columnscan be used to filter the selected fields
psql select table: "books" where: {"title": "Moby Dick"}
# result: [{"id": 2, "title": "Moby Dick"}]
psql select table: "books" where: {"$or": {title: "Moby Dick", "id": {"$lt": 2}}}
# result: [{"id": 1, "title": "Ulysses"}, {"id": 2, "title": "Moby Dick"}]
psql select table: "books" columns: ["title"] where: {"title": "Moby Dick"}
# result: [{"title": "Moby Dick"}]
Update entries
psql update table: "books" values: {"title": "UPDATED"} where: {"id": {"$gt": 2}}
# result: [{"id": 3, "title": "UPDATED"}]
The where query is optional, but without it all columns will be updated:
psql update table: "books" values: {"title": "UPDATED"}
# result: [
# {"id": 1, "title": "Ulysses"},
# {"id": 2, "title": "Ulysses"},
# {"id": 3, "title": "War and Peace"}
# ]
Delete entries
delete uses a where select query and will return the deleted columns:
psql delete table: "books" where: {"title": "Moby Dick"}
# result: [{"id": 2, "title": "Moby Dick"}]
The where query is optional, but without it all rows will be deleted.
Drop table
An entire table can be dropped with dropTable:
psql dropTable table: "books"
Execute
# Storyscript
result = psql exec query: "select * from my_table where name=%(username)s" data: {"username": "jill"}
# result is an array, with records as JSON objects inside it.
Curious to learn more?
✨🍰✨
Usage with OMS CLI
Create Table
oms run createTable -a table=<TABLE_NAME> -a columns=<COLUMNS_DATA> -e POSTGRES_DSN=<POSTGRES_DSN>
Drop Table
oms run dropTable -a table=<TABLE_NAME> -e POSTGRES_DSN=<POSTGRES_DSN>
Insert
oms run insert -a table=<TABLE_NAME> -a values=<MAP/LIST_OF_MAP_VALUES> -e POSTGRES_DSN=<POSTGRES_DSN>
Select
oms run select -a table=<TABLE_NAME> -a where=<WHERE_CONDITION> -e POSTGRES_DSN=<POSTGRES_DSN>
Update
oms run update -a table=<TABLE_NAME> -a values=<MAP/LIST_OF_MAP_VALUES> -a where=<WHERE_CONDITION> -e POSTGRES_DSN=<POSTGRES_DSN>
Delete
oms run delete -a table=<TABLE_NAME> -a where=<WHERE_CONDITION> -e POSTGRES_DSN=<POSTGRES_DSN>
Execute
oms run exec -a query=<QUERY> -a data=<DATA_FOR_QUERY_FIELDS> -e POSTGRES_DSN=<POSTGRES_DSN>
License
Tag summary
Content type
Image
Digest
Size
41.8 MB
Last updated
almost 7 years ago
docker pull microservices/psql