object-to-postgrest-query v1.2.4

object-to-postgrest-query

Converts a JavaScript object into a query string compatible with PostgREST, making it easier to construct queries for a PostgreSQL database via PostgREST.

Installation

npm install object-to-postgrest-query --save

or

yarn add object-to-postgrest-query

Usage

Import the objectToPostgrestQuery function from the package, and pass your query object to it. By default, the function will return an object where keys are the query parameters and values are the query values, formatted for use with PostgREST. If you pass true as the second argument, the function will return a URL-encoded string instead.

import objectToPostgrestQuery from 'object-to-postgrest-query';

const obj = {
	name: { 'ilike': 'John' },
	age: { 'gte': 25 },
	number: "Undefined", // this key and value wull be removed 
	place: "null", // this key and value wull be removed
	tags: { 'cs': '{example,new}' }
};

// Get query as an object
const postgrestQueryObject = objectToPostgrestQuery(obj);
console.log(postgrestQueryObject);

// Get query as a URL-encoded string and set other options
const postgrestQueryString = objectToPostgrestQuery(obj, {
	isUrlParams: false,
	removeUndefinedStringValue: true, // removes Undefined even as a string
	removeNullStringValue: true //removes null even as a string
});
console.log(postgrestQueryString);

Query Object Format

The query object should be formatted as follows:

interface QueryObject {
	[key: string]: string | { [operator: string]: string | number | Array<string | number> };
}

Options

Each key represents a column in your PostgreSQL database, and the value represents the condition for that column. Conditions can be specified as a simple string or number, or as an object with a single key representing the operator, and the value representing the value for the condition.

Supported Operators

Example Ussage

• usage examples for all operators with the given objectToPostgrestQuery function, demonstrating how each operator would be used within a query object and the corresponding output when isUrlParams is set to true:

1. Equals (eq):

const obj = { age: { 'eq': 25 } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=eq.25"

2. Greater Than (gt):

const obj = { age: { 'gt': 20 } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=gt.20"

3. Greater Than or Equal (gte):

const obj = { age: { 'gte': 25 } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=gte.25"

4. Less Than (lt):

const obj = { age: { 'lt': 30 } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=lt.30"

5. Less Than or Equal (lte):

const obj = { age: { 'lte': 30 } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=lte.30"

6. Not Equal (neq):

const obj = { name: { 'neq': 'John' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "name=neq.John"

7. Like (like):

const obj = { name: { 'like': 'Jo%' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "name=like.Jo%"

8. ILike (ilike):

const obj = { name: { 'ilike': 'jo%' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "name=ilike.jo%"

9. Match (match):

const obj = { name: { 'match': '^Jo' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "name=~.^Jo"

10. IMatch (imatch):

const obj = { name: { 'imatch': '^jo' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "name=~*.^jo"

11. In (in):

const obj = { age: { 'in': [25, 30] } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=in.(25,30)"

12. Is (is):

const obj = { active: { 'is': true } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "active=is.true"

13. Is Distinct (isdistinct):

const obj = { age: { 'isdistinct': 30 } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=isdistinct.30"

14. Full Text Search (fts):

const obj = { description: { 'fts': 'apple' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "description=@@.apple"

15. Prefix Full Text Search (plfts):

const obj = { description: { 'plfts': 'appl' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "description=@@.appl"

16. Phrase Full Text Search (phfts):

const obj = { description: { 'phfts': 'red apple' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "description=@@.red apple"

17. Websearch Full Text Search (wfts):

const obj = { description: { 'wfts': 'apple' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "description=@@.apple"

18. Contains (cs):

const obj = { tags: { 'cs': '{example,new}' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "tags=@>.{example,new}"

19. Contained By (cd):

const obj = { tags: { 'cd': '{example,new}' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "tags=<@.{example,new}"

20. Overlaps (ov):

const obj = { tags: { 'ov': '{example,new}' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "tags=&&.{example,new}"

21. Strictly Left Of (sl):

const obj = { range: { 'sl': '[1,10]' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "range=<<.[1,10]"

22. Strictly Right Of (sr):

const obj = { range: { 'sr': '[1,10]' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "range=>>.[1,10]"

23. Does Not Extend To The Right (nxr):

const obj = { range: { 'nxr': '[1,10]' } };
const postgrest

Query = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "range=&<.[1,10]"

24. Does Not Extend To The Left (nxl):

const obj = { range: { 'nxl': '[1,10]' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "range=&>.[1,10]"

25. Is Adjacent To (adj):

const obj = { range: { 'adj': '[1,10]' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "range=-|-.[1,10]"

26. Ordering (order):

const obj = {
	order: {
		age: 'desc',  // Order by age in descending order
		name: 'asc'   // Order by name in ascending order
	}
};
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=order.desc&name=order.asc"

27. Or (or):

const obj = { age: { 'or': { 'lt': 25, 'gt': 30 } } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=or.lt.25,gt.30"

28. And (and):

const obj = { age: { 'and': { 'gte': 25, 'lte': 30 } } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=and.gte.25,lte.30"

29. And (and):

const obj = { age: { 'and': { 'gte': 25, 'lte': 30 } } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "age=and.gte.25,lte.30"

30. All (all):

const obj = { tags: { 'all': '{example,new}' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "tags=all.{example,new}"

31. Any (any):

const obj = { tags: { 'any': '{example,new}' } };
const postgrestQuery = objectToPostgrestQuery(obj, true);
console.log(postgrestQuery);  // Output: "tags=any.{example,new}"

These examples demonstrate how the objectToPostgrestQuery function interprets different operators and formats them into a query string compatible with PostgREST when isUrlParams is set to true.

Contributing

Feel free to open issues or pull requests to improve the package.