Test Postgres Connection

Sometimes you need to quickly test your client libraries to make sure that you can connect to Postgres with your tech stack, network and credentials combination.

Here are a couple of examples of the simplest connection tests. No previous database migrations are required. The query uses the default postgres database and selects the current time.

Test Elixir Postgrex Connection

First, install erlang and elixir.

Then start a mix project:

mix new test_postgrex
cd test_postgrex

And add the postgrex dependency to mix.exs:

defp deps do
  [
    {:postgrex, "~> 0.17.4"}
  ]
end

Then install with:

mix deps.get

Then start the repl with iex -S mix, and type:

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "postgres")

{:ok, result} = Postgrex.query(pid, "select now()", [])

{:ok,
 %Postgrex.Result{
   command: :select,
   columns: ["now"],
   rows: [[~U[2024-02-09 21:42:19.323854Z]]],
   num_rows: 1,
   connection_id: 235,
   messages: []
 }}

Test Erlang ODBC Connection

First, install only erlang.

Then start the repl with erl and type:

odbc:start().

ConnStr = "Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Database=postgres;Uid=postgres;Pwd=postgres".

{ok, Ref} = odbc:connect(ConnStr, []).

{selected, ColNames, Rows} = odbc:sql_query(Ref, "SELECT now();").

{selected,["now"],[{{{2024,2,9},{21,47,23}}}]}

Test node-postgres Connection

First install node, then start a new project:

mkdir test-node-postgres
cd test-node-postgres
npm init -y
npm install pg

Then start the repl with node and type:

const { default: pg } = await import("pg");
const client = new pg.Client({
  user: "postgres",
  host: "localhost",
  database: "postgres",
  password: "postgres",
});
await client.connect();

const res = await client.query("select now()");
console.log(res.rows[0]);
{ now: 2024-02-09T21:50:20.407Z }