Get a list of available PostgreSQL databases

I can get a list of available PostgreSQL databases in Terminal by using:

psql -h 127.0.0.1 -l

Can I do that in Xojo without using a shell?

Assuming you make a connection to a PostgreSQL server, you can simply issue this SQL:

SELECT datname FROM pg_database;

And then process the returned data. Use any of the example apps which show connecting to a PG server and iterating over a result. Presumably you may already know how to do this, as a database name is pretty useless otherwise.

Thank you Douglas

It’s probably very simple but I just can’t figure it out.
All the examples I’ve seen connect to a known DB.

I would like to connect to a server without knowing in advance which databases might be available.
Send a query for available DB’s and then connect to a DB among the results.

I have come across
SELECT datname FROM pg_database;
I just don’t know how to send it to the server without allready knowing the name of a DB and then select among the possible results.

The code below works. But only because I know in advance that database “01” exists.

Var db As New PostgreSQLDatabase
db.Host = "localhost"
db.Port = 5432
db.DatabaseName = "01"
db.UserName = "user"
db.Password = "secret"
Try
  db.Connect
  // Use the database
  Var rowsFound As RowSet
  
  rowsFound = db.SelectSQL("SELECT datname FROM pg_database;")
  For Each row As DatabaseRow In rowsFound
    ListBox1.AddRow(row.column("datname").StringValue)
  Next
  rowsFound.Close
  
Catch error As DatabaseException
  // DB Connection error
  MessageBox(error.Message)
End Try

This sounds like a security risk if somebody allows access to the PG server but you don’t already know already know proper credentials to connect. However, your code is using localhost and the standard port, so you could try database “postgres” and user “postgres”. But there is no guarantee that default name will still exist, and it may require a password.

I’m actually not very familiar with psql – doing everything through other interfaces – but if that works for you, what is the aversion to using shell to get the list? And does psql actually let you perform that operation without passing valid credentials? I am guessing here that psql is just using defaults in the absence of command line switches, and that psql will not work either if the defaults are removed from the server.

It’s not a question about credentials. All users will have their own credentials.
I just don’t know what DB’s are available at all times.

This is a multiuser setup in which anybody with the right credentials can create and delete DB’s on the server (not that I want them to :slight_smile: )

Will the “postgres” database always be available then I can connect to that?

The shell would work but I think it’s a detour compared to doing everything in Xojo

Not if somebody deleted it. Or assigned a password to it.

AFAIK, there is no way to generically connect to an arbirtary PG server (even if on your own machine) and list the databases.

And in regards to the shell always working, I would suggest you take a machine and delete the “postgres” database (or rename it or add a password to it) and try psql again. My SWAG is that is won’t work either.

If it does, that sounds like a security risk to me. But then I’d check the source for psql, since PG is an open source product.