How to query data with SQL — Basic

SQL / Knex Raw SQL

Retrieve rows using SELECT with WHERE, ordering, and limits as needed.

Learn: SELECT is the foundation of read APIs. Filter with WHERE, sort with ORDER BY, and paginate with LIMIT/OFFSET. Select only columns you need to keep responses small.

Supplementary examples

Filtered, ordered query

SELECT paste_id, name, created_at
FROM pastes
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 10;

Course example

{
"data": {
"product_id": 1,
"product_sku": "XLH4P7t3er",
"product_title": "Vanilla Scented Candle",
"product_description": "Vanilla-scented candle, perfect for your living room.",
"product_price": "48.25",
"product_quantity_in_stock": 0,
"product_weight_in_lbs": "2.30",
"supplier_id": 1,
"created_at": "2020-12-01T20:37:09.550Z",
"updated_at": "2020-12-01T20:37:09.550Z",
"category_id": 2,
"category_name": "candles",
"category_description": "A category for gift candles, including both scented and non-scented candles, for the home."
}
}

Additional references & examples

PostgreSQL SELECT
SELECT, JOIN types, and filtering in PostgreSQL.
PostgreSQL documentation
SQL syntax, data types, and database administration reference.