# Don't query your PostgreSQL db anymore, prefer PostgREST > Use Docker and PostgREST to instantly turn your PostgreSQL database into a powerful RESTful API. Eliminate SQL queries and complex database models from your application code with this simple setup tutorial. ---  **TL;DR** This article introduces PostgREST, a tool that turns a PostgreSQL database directly into a RESTful API, removing the need to write models and queries in your application code. It walks through creating a sample database in Docker, running PostgREST against it, and querying the resulting API with `curl`, including filtering, full-text search, joins, pagination, casting, and output formats (JSON/CSV/text). Last year I had a large application developed in Laravel that required a MySQL database. When I was migrating to PostgreSQL I discovered PostgREST, which allowed me to completely remove queries from my code. Don't get me wrong: my Laravel/PHP code was launching dozens of queries to the database and, after migration, none at all. My tables, my models, my SQL queries, I was able to remove everything from my code. My PHP code has been greatly lightened and simplified. ## What PostgREST does for you There is a `todos` table in a PostgreSQL database. No controller, no route file, no model — nothing has been written. And yet: ```bash $ curl http://localhost:3000/todos | jq ``` ```json [ { "id": 1, "done": false, "task": "finish tutorial 0", "due": null }, { "id": 2, "done": false, "task": "pat self on back", "due": null } ] ``` Ready-to-consume JSON, straight from the table, without a single line of backend code. It's awfully easy, isn't it? ## Why it works * **The database schema *is* the API.** "PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permission in the database determine the API endpoints and operations" (the [official documentation](https://postgrest.org/en/)). A table becomes an endpoint, a column becomes a field. * **Permissions are the security layer**, and they live where they belong: in PostgreSQL. A `users` table you never grant to the anonymous role simply doesn't exist as far as the API is concerned. * **There's nothing left to keep in sync.** After my own migration, I deleted 100% of the code declaring tables and fields, the relationship declarations, and the queries. What used to be a model layer became URLs. ## Let's play For this post, let's create a temporary folder in your `/tmp` folder: start a Linux console and run `mkdir /tmp/postgrest && cd $_`. ### Step 1 - Create and populate our PostgreSQL database We'll create a Docker container for our PostgreSQL database: ```bash $ docker run --name tutorial -p 5433:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres ``` Now, we'll enter in our PostgreSQL container and run `psql`: ```bash $ docker exec -it tutorial psql -U postgres ``` Copy/Paste the code below in your console. This will create a database called `api` with a table called `todos`; with two records. This will also create a `web_anon` user that we'll use with PostgREST to query our data: **create\_db.sql** ```sql create schema api; create table api.todos ( id serial primary key, done boolean not null default false, task text not null, due timestamptz ); insert into api.todos (task) values ('finish tutorial 0'), ('pat self on back'); create role web_anon nologin; grant usage on schema api to web_anon; grant select on api.todos to web_anon; create role authenticator noinherit login password 'mysecretpassword'; grant web_anon to authenticator; ``` Now, to leave the postgres console, just type `\q`. ### Step 2 - Install and execute PostgREST PostgREST is a binary, download it by running: ```bash curl -o postgrest-v14.16-linux-static-x86-64.tar.xz -L https://github.com/PostgREST/postgrest/releases/download/v14.16/postgrest-v14.16-linux-static-x86-64.tar.xz tar xJf postgrest-v14.16-linux-static-x86-64.tar.xz && rm -f postgrest-v14.16-linux-static-x86-64.tar.xz ``` You've now a file called `postgrest` in your folder. We need to create a configuration, please create a file called `tutorial.conf` with this content: **tutorial.conf** ```conf db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres" db-schemas = "api" db-anon-role = "web_anon" ``` > **PostgREST will start as a service on port 3000 by default:** > > The instruction `./postgrest tutorial.conf` will start a service. You can stop it by pressing `CTRL`-`C` but leave it right now and start a new console. > > Add the line below to your conf file if you wish to use another port; f.i. port 3000: > > **tutorial.conf** > > ```conf > server-port = 3000 > ``` Now, we'll run a Docker container for PostgREST: ```bash $ ./postgrest tutorial.conf ``` ## More queries So, in step 1, we've created and populated our PostgreSQL database and, in step 2, we've installed and configured PostgREST to use that database. From now, we can directly use it like any API. Every query is made of a fixed part (the URL of our PostgREST server), which is `http://localhost : 3000`, followed by the query itself. To get the content of a table, just mention its name — that's the `http://localhost : 3000 /todos` call shown at the top of this article. For aesthetic reasons here, I'm using `| jq` (you can remove that part if you want). See my [The jq utility for Linux](/blog/linux-jq) article to learn more about `jq`. ### Using a filter You can use filters by typing f.i. `?` followed by a field name and a criteria. To get only the record having the `id` 2, here is how to do: ```bash $ curl http://localhost:3000/todos?id=eq.2 | jq ``` And if you wish to make a *Full text search* to retrieve a content based on a value, here is how to do: ```bash $ curl http://localhost:3000/todos?task=fts.tutorial | jq ``` In the example above, we'll search any todo where the field `task` contains the word `tutorial`. Below, a query on the field `done` which should be true. ```bash $ curl http://localhost:3000/todos?done=is.true | jq ``` Below, we are asking for getting only fields `id` and `task`: ```bash $ curl http://localhost:3000/todos?select=id,task | jq ``` ### From the frontend Since these are plain HTTP endpoints, your frontend consumes them directly. In JavaScript, using axios: ```js const todos = axios.create({ baseURL: 'http://localhost:3000/todos', headers: { 'Accept': 'application/json' } }) ``` ## Under the hood (skip this if you just want to use it) ### Permissions required By using PostgREST you expose your tables and records through a RESTful API. Naturally, there is a system of permission so that you can define what can be accessed (e.g. a `users` table will remain secret) and what can be done (e.g. one user will only have read access but another will have read-write access). ### OpenAPI PostgREST is compliant with [OpenAPI](https://swagger.io/specification/). It's then possible to auto-document its routes using the [Swagger UI](https://hub.docker.com/r/swaggerapi/swagger-ui) Docker image. This means that running `curl http://localhost : 3000` (the PostgREST URL), you'll get the list of all tables accessible to you (using your access key). This makes your database open to the world (once again, only what you've allowed using correct permission). ### Close PostgREST Return to the console in which you've started PostgREST and press `CTRL`-`C` to stop PostgREST from running. If you've started PostgreSQL here above, you can stop and kill it using `docker container stop tutorial ; docker container rm tutorial`. ## Illustration of some calls > **These queries target another database:** > > The examples below come from my [PostgREST](https://github.com/cavo789/postgrest) demo repository, whose database holds `citizens`, `workers`, `levels`, `translations` and `generic_profiles` tables. They will **not** run against the `todos` database built earlier in this article — read them as a syntax catalogue, not as commands to copy right now. ### Citizens * Get the list of all citizens: `clear ; curl http://127.0.0.1 : 3000 /citizens | jq` * Only citizen ID 69: `clear ; curl http://127.0.0.1 : 3000 /citizens?id=eq.69 | jq` * Only citizen ID 69 and id, firstname and lastname: `clear ; curl http://127.0.0.1 : 3000 /citizens?select=id,first_name,last_name&id=eq.69 | jq` * Get Jean but only the one speaking Dutch (language ID `2`) (i.e. a WHERE with two conditions): `clear ; curl http://127.0.0.1 : 3000 /citizens?first_name=fts.Jean&language_id=eq.2 | jq` ### Workers * Get the list of all workers, the first five: `clear ; curl http://127.0.0.1 : 3000 /workers?select=id,email&limit=5 | jq` * Get the list of all workers, the next five: `clear ; curl http://127.0.0.1 : 3000 /workers?select=id,email&limit=5&offset=5 | jq` * Reverse order, get the last 10: `clear ; curl http://127.0.0.1 : 3000 /workers?select=id,email&limit=10&offset=0&order=id.desc | jq` * Get worker id 15: `clear ; curl http://127.0.0.1 : 3000 /workers?id=eq.15 | jq` — as we can see, the output is an array with only one record * Get worker id 15 - no more array: `clear ; curl http://127.0.0.1 : 3000 /workers?id=eq.15 -H "Accept: application/vnd.pgrst.object+json" | jq` (here it's easier and more logic for the frontend) ### Using inner join * Get the list of workers and their first\_name and last\_name (limit to worker ID `73`): `clear ; curl http://127.0.0.1 : 3000 /workers?select=id,email,citizens(first_name,last_name)&id=eq.73 | jq` * Add the language code: `clear ; curl http://127.0.0.1 : 3000 /workers?select=id,email,citizens(first_name,last_name,language_id),languages(code)&id=eq.73 | jq` ### Levels * Get the list of all levels: `clear ; curl http://127.0.0.1 : 3000 /levels | jq` * Get the list of levels where ID is greater than 10, only ID and code: `clear ; curl http://127.0.0.1 : 3000 /levels?select=id,code&id=gt.10 | jq` ### Translations * Get the list of translations with the word *Technical*: `clear ; curl http://127.0.0.1 : 3000 /translations?value=fts.Technical | jq` * Get the list of translations starting with the word *Technical*, case insensitive: `clear ; curl http://127.0.0.1 : 3000 /translations?value=ilike.technical* | jq` ### Generic profiles * Get the list of active generic profiles: `clear ; curl http://127.0.0.1 : 3000 /generic_profiles?active=is.true | jq` * and rename the field `code` to `GenericProfileCode`: `curl http://127.0.0.1 : 3000 /generic_profiles?select=id,GenericProfileCode :code &active=is.true | jq` ### Output format * Get the list as JSON: `clear ; curl http://127.0.0.1 : 3000 /workers -H "Accept: application/json"` * Get the list as CSV: `clear ; curl http://127.0.0.1 : 3000 /workers -H "Accept: text/csv"` * Get the list as TEXT - Here we need to return only one value: `clear ; curl http://127.0.0.1 : 3000 /workers?select=email&id=eq.15 -H "Accept: text/plain"` ### Casting * Get the ID and email of worker `15`: `clear ; curl http://127.0.0.1 : 3000 /workers?select=id,email&id=eq.75 | jq` * Cast the ID as string: `clear ; curl http://127.0.0.1 : 3000 /workers?select=id::text,email&id=eq.75 | jq` ## Conclusion A container, a binary and a `.conf` file, and your database answers HTTP. What I find remarkable isn't the speed of the setup, it's what disappears afterwards: the models, the relationship declarations, the query builder calls — an entire layer whose only job was to describe, in your language, something PostgreSQL already knew. Oracle has its own equivalent, described in [Transform an Oracle DB as OpenData using Oracle REST Data Services](/blog/docker-oracle-ords). And to look at the database behind the API, [Adminer, pgadmin or phpmyadmin](/blog/docker-adminer-pgadmin-phpmyadmin) remains handy.