Databases

The database can be defined in the globals part of a configuration or the action itself. Defining it in the globals section allows any action to utilise the database. Any database config defined with a matching name will have its existing connection reused.

Options

Name	Type	Description
driver	string	the database driver (postgres, mysql, mssql, sqlite, mongodb)
conn_string	string (optional)	the full connection string to connect to a database
user	string (optional)	database user
pass	string (optional)	database password
host	string (optional)	database host
dbname	string (optional)	name of the db
params	array	optional parameters for the database
document_operation	object	required for NoSQL operations eg. MongoDB

note if using user, pass, host, dbname is supplied do not use conn_string

document_operation

Name	Type	Description
database	string	the database to select
collection	string	the collection to target
operation	string	find, findOne, findOneAndDelete, findOneAndReplace, deleteOne, deleteMany, updateOne, updateMany, insertOne, insertMany, aggregate
filter	string (optional)	required for any of the above operations except aggregate, supply JSON document as a string
pipeline	string (optional)	required if operation is set to aggregate, supply JSON document as a string
update	string (optional)	document as a JSON string
options	string (optional)	options as a JSON string

Connection Strings

Below are example connection strings, please adjust the parameters to your own requirements.

• Postgres

  • CockroachDB

  • YugabyteDB

  • Timescale

  • Any postgres compatible databases

    postgresql://username:password@host:port/dbname[?paramspec]

• MySQL

  • MariaDB

  • TiDB

  • Any mysql compatible database

    mysql://root:college@localhost:3306/employees

• SQLite

  sqlite:///home/my-user/db-files/Sqlite-Car_Database.db

• MSSQL / SqlServer

  jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;user=SA;password=Passw0rd;trustServerCertificate=true;

• MongoDB

  • Azure Cosmos DB

  • Amazon DocumentDB

Global Example

name: database
version: 0.0.1

  databases:
    main:
      driver: postgres
      conn_string: |
        postgresql://a|env::POSTGRES_USER|:a|env::POSTGRES_PASS|@a|env::POSTGRES_HOST|?connect_timeout=10"

Action Example

Note the query option is for relational based database, and not used for NoSQL/Document Store type databases such as MongoDB, Azure Cosmos DB, Amazon DocumentDB.

name: database
version: 0.0.1

interfaces:
  database/example:
    output: http
    method: POST

    actions:
      - name: UserList
        database:
          dbname: main
          driver: postgres
          conn_string: |
            postgresql://a|env::POSTGRES_USER|:a|env::POSTGRES_PASS|@a|env::POSTGRES_HOST|?connect_timeout=10"
        query: |
          SELECT * FROM Users;

MongoDB Find Example

Example to send a HTTP POST with a JSON payload eg. {"name": "test"} to find a document with username called test.

MongoDB Find

loading...

View on GitHub

MongoDB Aggregate Example

Example to send a HTTP POST with a JSON payload eg. {"starts_with": "test"} to find documents using regex and limiting the result set to 30.

MongoDB Aggregate Pipeline

loading...

View on GitHub