PostgreSQL

Overview

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

A lot of users are using PostgreSQL as their database engine, so it's no surprise that creating a PostgreSQL REST API is highly in demand. Therefore, SlashAPI provide a connector to allow user build and publish a PostgreSQL API.

Getting Started

After you create an account and log in to the dashboard, choose PostgreSQL service on the collections page. To make PostgreSQL API you need to provide your database credentials on the PostgreSQL creation form.

• Host
• Port
• Database
• Username
• Password

API Endpoints

Table name act as API endpoints

The name of your table will be used as the API endpoint name. So, if database has a table called "users", the API URL will look like this:

https://v1.slashapi.com/<team>/pgsql/<identifier>/users

GET Records

To Get records from your PostgreSQL table, you just need to put your table name to the base API URL.

For example, if you want to get records from your users table, your API endpoint will look like this:

<team>/pgsql/<identifier>/users

In the GET records endpoint, you can use some of query parameters to allow you modify the fetched data. e.g sort by column, modify limit and pagination, etc.

Ordering

The reserved word is order_by. order_by is used to sort the fetched data in either ascending or descending according to one or more columns.

/users?order_by=name

By default the order_by sorts the data in ascending order. You can use the keyword desc after the column name to sort the data in descending order.

/users?order_by=name.desc

You can also sort the fetched data using multiple columns, Multiple order uses a comma separated.

/users?order_by=name.asc,id.desc

Limit and Pagination

SlashApi provides a page and per_page parameter that is used to paginate your data. SlashApi automatically takes care of setting the query's limit and offset based on the current page being viewed by the user. The current page is detected by the value of the page query string argument on the HTTP request.

To set the limit query, you need to set the per_page value in your query parameter to modify the number of items you would like to displayed. SlashApi set the default value for per_page option to 10. So, if you are not provide the per_page option in your API request, SlashApi will limit the number of items to 10.

Filtering

Database queries are created as valid JSON documents. A query object consists of fields and operators that make up a complete query.

Query example:

/users?q={"name":"John"}

Multiple parameters;

/users?q={"name":"John","age":17}

Logic Operators

Abbr	Description	Example
$ne	Not equal	{"field":{"$ne":value}}
$like	Like operator	{"field":{"$like":value}}
$in	Match any value in array	{"field":{"$in":[value1,value2,...]}}
$nin	Not match any value in array	{"field":{"$nin":[value1,value2,...]}}
$or	Logical operator	{"$or":[{"field":"value"},{"field":"Another Value"}]}
$and	Logical operator	{"$and":[{"field":"value"},{"another_field":"value"}]}

Conditional Operators

Abbr	Description	Example
$gt	Greater than	{"field":{"$gt":value}}
$gte	Greater than equal	{"field":{"$gte":value}}
$lt	Less than	{"field":{"$lt":value}}
$lte	Less than equal	{"field":{"$lte":value}}
$between	Matches field value between two numeric values	{"field":{"$between":[1,5]}}

Date operator

Querying based on dates are done using the our predefined operator combined with date strings.

Operator	Description
$date	Compare a column's value against a date
$day	Compare a column's value against a specific day
$month	Compare a column's value against a specific month
$year	Compare a column's value against a specific year

Query Example:

// Query equal date
{"created_at":{"$date":"2021-07-01"}}

// Query between two dates
{"created_at":{"$gt":{"$date":"2021-07-01"},"$lt":{"$date":"2021-07-20"}}}

// Compare a column's value against a specific day:
{"created_at":{"$day":7}}

// Compare a column's value against a specific month:
{"created_at":{"$month":7}}

// Compare a column's value against a specific year:
{"created_at":{"$year":2021}}

Selecting column

You may not always want to select all columns from a database table. Using the select query parameter, you can specify a custom "select" clause for the query:

/users?select=id,first_name,last_name

Relation

Database tables are often related to one another. For example, a blog post may have many comments or an order could be related to the user who placed it. SlashApi also supports a variety of common relationship.

Has Many

A one-to-many/Has Many relationship is used to define relationships where a single table is the parent to one or more child tables. For example, a blog post may have an infinite number of comments.

/posts?has_many=comments

SlashApi will automatically determine the proper foreign key column for the relation. By convention, SlashApi will take the "snake_case" name of the parent table and suffix it with _id. So, in the above example, SlashApi will assume the foreign key column on the comments table is post_id and the primary key column on the posts table is id

If your database not using the convention like above, you can define the foreignKey and localKey for the relation after the relation table like below:

/posts?has_many=comments(foreignKey|localKey)&primaryKey=primaryKey

For example, your primary key in the posts table is id_post, foreign key in the comments table is post_id, and the foreign key in the comments table is id_comment. Then, you can use the query string like below:

/posts?has_many=comments(post_id|id_comment)&primaryKey=id_post

BelongsTo

A belongs_to relationship is a very basic type of database relationship. For example, a posts table might be associated with users table as the author. To define this relationship, you can use belongs_to followed with the relation table in the query parameter.

/posts?belongs_to=user

When you define belong_to parameter, SlashApi will attempt to find the relationship that has an id which matches the singular name of relation table suffixed with _id column on the parent table.

SlashApi determines the foreign key name by examining the name of the relationship table and suffixing the table name with _id. So, in the above case, SlashApi assumes that posts table has a user_id column. However, if the foreign key on the posts table is not user_id, you may pass a custom key name as the query parameter.

/posts?belongs_to=user(foreignKey|ownerKey)

Multiple relations

You can also show multiple relations by define the relation table separated by comma.

/posts?has_many=first_relation_table,second_relation_table

Selecting relation column

You can also specify a custom "select" clause for the relationship tables by using a dot notation

/posts?has_many=comments&select=comments.id,comments.message

Filtering Relation

Sometimes, you need to filter your relationship data, you can define the relation table as an array with the name of the column as array key in the query parameter. For example, you want to show all post comments that have been approved, you can use comments.approved as a query parameter.

/posts?has_many=comments&q={"comments.approved":true}

Please refer to the Filtering section for the detail.

Get count of relations

Sometimes you may want to count the number of related table for a given relationship without actually loading the data. To accomplish this, you may use the with_count query parameter. The with_count option which will place a {relation}_count attribute on the query result:

/posts?with_count=comments

The endpoint above will place comments_count field on the query result.

Get Specific Record

To get specific record of your table, you can specify the primary key as the identifier.

For example, if you want to get the records for posts table with id = 1, you can use the URL like this:

/posts/1

By default, SlashApi will use the id column as the identifier. If you are not using id as your identifier, you can define the identifier in the URL path. For example, if you are using uuid column as the identifier, you can use the URL path like this:

/posts/uuid:123e4567-e89b-12d3-a456

or you can also use primaryKey query parameter to define the identifier.

/posts/123e4567-e89b-12d3-a456?primaryKey=uuid

Create Record

SlashApi also provides an endpoint that may be used to insert records into your database table. This endpoint using the POST method and accepts an array of column names and values. Missing properties will be set to the default values when applicable.

Request Body

{
    "data": {
        "title": "Some title",
        "content": "Long text"
    }
}

Insert Multiple Rows

SlashApi allows you to insert multiple rows with a single API request. You don't need to send multiple API requests for it. To insert multiple rows into your database table, you just need to pass an array in the data of your request body.

{
    "data": [
        {
            "title": "First title",
            "content": "First content"
        },
        {
            "title": "Second title",
            "content": "Second content"
        }
    ]
}

Primary Key

In order to return the value of your primary key, SlashApi needs to know the name of the primary key that you used. By default, SlashApi will use the id column as the primary key. If you are not using id as your primary key, you need to provide the primary key field in the request body. For example, if you are using uuid column as the primary key, you need to use a request body like below:

{
    "data": {
        "uuid": "123e4567-e89b-12d3-a456-426614174000",
        "title": "Some title",
        "content": "Long text"
    },
    "primaryKey": "uuid"
}

Update Record

To update a row in a table, use the PATCH verb and specify the primary key as the identifier in your API URL path.

Request Body

{
    "data": {
        "title": "Updated title",
        "content": "Updated text"
    }
}

Primary Key

When interacting with your database, SlashApi depends on the primary key of your table. By default, SlashApi will use id as the primary key. When updating data in your database, you need to provide the primary key that you used in your table (only if you are not using id as your primary key)

You can provide the primary key field using 2 options.

• Endpoint path

  In this example, we use uuid as the primary key.

  /posts/uuid:123e4567-e89b-12d3-a456
  

• Request Body

  Another alternative, you can put the primary key name in request body

  {
      "data": {
          "uuid": "123e4567-e89b-12d3-a456-426614174000",
          "title": "Some title",
          "content": "Long text"
      },
      "primaryKey": "uuid"
  }
  

Delete Record

To Delete a row in a table, use the DELETE verb and specify the primary key as the identifier in your API URL path.