You've seen how to interact with CrateDB using the web-based console and crash command line tools. In this video I'll show you how to talk to CrateDB using its two primary client interfaces. So let's jump right in.
CrateDB has two main client interfaces. The first of these uses the PostgreSQL Wire Protocol on port 5432. Adopting the PostgreSQL Wire Protocol means that CrateDB is compatible with a wide range of tools and libraries out-of-the-box.
The other interface is the http://-endpoint and it's found at port 4200. Using this endpoint, you can submit SQL statements for execution and receive results back over HTTP.
Both client interfaces can be secured using Transport Layer Security and SSL certificates, but we won't focus on that in this overview.
If you're running CrateDB using Docker, you might have noticed these ports already. Here we're telling Docker to map port 4200 the HTTP interface and 5432 Postgres to the equivalent port numbers on the host machine when running CrateDB. This enables you to connect to the database running in the container using either approach.
Support for the PostgreSQL Wire Protocol means you can use a variety of tools to connect to CrateDB. Here I've installed the PostgreSQL 'psql' command line tool and I'm connecting to a local instance of CrateDB. I've specified the host and port that it's running on, as well as the user to connect as and the default schema to connect to. Note that in Postgres the default schema is called public, in CrateDB it's doc, so I'm making sure to pass that in here. SQL queries work as you'd expect.
Here I'm asking CrateDB to tell me what the most popular types of car in the Chicago taxi fleet are. The query is sent to CrateDB on port 5432 using the PostgreSQL Wire Protocol and the response is received and formatted by the psql CLI for display.
Let's turn our attention to the HTTP endpoint for a moment. It's found at /_sql on port 4200 of your CrateDB node. If you want to use another port number, you can do this by changing the nodes configuration. SQL statements are submitted for execution by sending a POST request to the endpoint with the SQL in the JSON body of the request. Responses containing results are returned as JSON too.
You can use parameterised queries by passing both the SQL statement and an array of arguments in the JSON body, and the HTTP endpoint also supports bulk operations, for example to insert several records in a single request response cycle.
Here's an example HTTP POST that runs a SELECT statement. I'm using the curl command line tool to send this to CrateDB running locally. You can of course use any API or HTTP request testing tool, for example Postman or Insomnia.
The SQL statement is sent to CrateDB inside a JSON object with a key named stmt. CrateDB executes the statement and returns a JSON response containing the result. Here the cols array contains the names of the columns returned, and the rows array contains a further array for each matching row.
For your convenience rowcount indicates how many rows were affected and duration tells you how long the query took to run in milliseconds.
This example inserts 3 records at once. The values are provided in the bulk_args array in the JSON body of the request.
In this video you learnt about the two client interfaces offered by CrateDB. Compatibility with the PostgreSQL Wire Protocol leads to easy integration with a wide range of tools and ecosystems. An HTTP endpoint provides an alternative interface that's also based on common standards, meaning ease of access from many tools and frameworks. Many drivers and SDKs build on top of these to provide rich, idiomatic interfaces to CrateDB for popular programming language. We'll cover these drivers in a separate video.
Take a moment to try out some queries using the HTTP endpoint and the sample data set in your own CrateDB cluster. Remember you can use the curl command or tools such as Postman, Insomnia or whatever your favorite API testing tool is.