As my database grows I've been noticing the performance starting to become an issue when compared with other database client implementations. I'm not sure if this is my code in particular or implementation differences.

Consider I have a table with 7 or so columns and about 6000 rows. The columns contain simple data: last name string, some integers, a timestamp, a few other strings. I want to retrieve the entire table - all rows and columns (so a simple SELECT statement). As I read each row from the query, the columns are parsed into their native types and stored in a list of objects.

I have a Java client using JDBC running on a laptop, for which this operation takes about 300 milliseconds. Roughly about 250 Kbytes are retrieved.

An iPhone and iPad on the same Wi-fi network running PostgresClientKit take about 8 seconds.

I don't think this is a network speed issue. The laptop is faster of course, but hard to believe it's that much faster. Both connections use SSL, possibly a performance issue in BlueSSLService.

Possibly the JDBC client is pre-fetching all rows before the cursor increments to them? Is there efficiency to be gained by trying to fetch all rows at once?

Could there be an underlying socket performance issue with blocking/non-blocking reads?

Any thoughts appreciated.

I am creating an application which will upload an image from iPad to postgres server and also download the image data from database and display in iPad.

In my database, I have a column named image with TEXT data type. In my application, I converted the image to string data using base64EncodedString(options: .lineLength64Characters) method.

The upload of the image data to database works well. However, an error occurred when I tried to select the data (including the image) from the database and here is the error message: "Response truncated; no data available" "no data available from server"

Can anyone help with this issue?

Thank you and good day

I would like to be able to set the timeouts for connect/read/write as the defaults are kinda long. I think this is as simple as, after creating the socket calling:

socket.setReadTimeout(value: timeout)
socket.setWriteTimeout(value: timeout)

and replacing the socket.connect() method with the overloaded one that takes a timeout.

The timeout values could be stored in the ConnectionConfiguration struct.

Hi there, in my swift project I call an example right at the beginning:

do { var configuration = PostgresClientKit.ConnectionConfiguration() configuration.host = "127.0.0.1" configuration.database = "example" configuration.user = "bob" configuration.credential = .scramSHA256(password: "welcome1")

let connection = try PostgresClientKit.Connection(configuration: configuration)
let text = "SELECT start();"
try connection.prepareStatement(text: text).execute()

} catch { print(error) }

I need the connection not to be terminated and continue in another function:

func test() { do { let text = "SELECT * FROM foo();" try connection.prepareStatement(text: text).execute(). // error: Cannot find 'connection' in scope } catch { print(error) }

My problem: I don't know how set "connection" as global var
Can you please advise me how to solve this?

My app can access our non-TLS, development PostgreSQL instance. But when attempting to test against a production environment, I am forced to use TLS access. So far, I've been unsuccessful in locating an example on how to configure my client cert and key .pem files. I have the correct .pem files, but I'm missing how to specify these files in the 'ConnectionConfiguration()'.

A brief example would be greatly appreciated.

I really like the work you've been doing on this project, as this seems to be the easiest to use Postgres library for Swift so far that also works beautifully on my iOS app. Now to my request: I'm currently trying to build a new app that would allow the user to type in SQL queries himself on a custom database (so I don't know the schema upfront). The user can use that feature to generate custom reports, charts, ..., and there needs to be a way of naming things and do some introspection of query results.

I do agree with your FAQ saying that one shouldn't use name based indices to retrieve values, but would it be possible to somehow retrieve the name of a column and it's type?

Think about a query like this (that the user will input into a text field of the running app):

SELECT count(*) AS population, born_at
FROM people
GROUP BY born_at

I would like to display the label population in the graph, effectively giving the user the possibility to steer everything about the graph/report with SQL (also finding out that born_at is a date, and therefore correctly displaying it in the UI).

Any hints or directions on this?

Hi! Thanks for writing this driver for Swift!

PostgreSQL has supported SCRAM-SHA-256 authentication since version 10 and the community is looking to make it the default password-based authentication method for version 13 (more than a year away, so there is some time). As such, I would strongly recommend to support it :smile:

A list of non-libpq based implementations that are implementing SCRAM can be found here: https://wiki.postgresql.org/wiki/List_of_drivers

I have some practice implementing the method so I'm happy to help where needed.

I've written a RowDecoder, which lets you easily convert from a PostgresClientKit.Row to a Codable struct, like

struct User: Codable {
    let id: Int
    let name: String
}

It's working well in my project, and if you think it'd be good to add to this library, let me know and I would be happy to clean it up and pull request it.

Hallo, I would like to change parameter datestyle from ISO, MDY to ISO, DMY in Parameter.swift When I try: let text = "SET DateStyle TO 'ISO, DMY';" let cursor = try Database.connection().prepareStatement(text: text).execute()

error: Invalid value for Postgres parameter (response.name): ISO, DMY (must be ISO, MDY); closing connection

Please what is the best way to do this?

Although SSL can be enabled to encrypt the connection to the server, the server is not authenticated. The client is vulnerable to a Man-In-The-Middle attack, where it unknowingly connects to a fake server impersonating the real server. Thus it is good practice for the client to authenticate the server while establishing the SSL connection.

I propose two new features:

First feature: allow the client to authenticate the server. Currently the SSL service uses a initializer:

SSLService.Configuration()

which by default allows self-signed server certificates. However by setting the self-signed parameter to false, the server must provide a certificate that can be validated by the client using the client's root certificates.

Second feature: often times servers are using certificates that don't establish a chain of trust to a root certificate on the client. This is true if you are using a self-signed certificate on a server, for instance. You can install those public certificates on the client, but this is an onerous process and must be done by the user on iOS. A more secure approach is to embed the public server certificate in the client and authenticate directly.

I have worked on a new feature for BlueSSLService that has recently been merged into the main trunk. This change allows the client to store several public certificates as .der files to be used to authenticate the server. See pull request here for details:

https://github.com/IBM-Swift/BlueSSLService/pull/81

I request that the new SSL feature be available in this library, so the Postgres client can authenticate a server using embedded certificates.

In essence, the change to this library would be to use the following init method for SSLService, supplying the parameters to the init method from new fields in the ConnectionConfiguration.

public init(withCipherSuite cipherSuite: String? = nil, clientAllowsSelfSignedCertificates: Bool = true, embeddedServerCertPaths : [URL]? = nil)

Note: The embeddedServerCertPaths feature is unavailable (parameter ignored) on MacOS versions less than 10.14 and iOS < 12.0

It is easy to embed certificates into the client. Simply add the .der file to the Xcode project and load them this way:

let path = Bundle.main.url(forResource: "MyServerCert", withExtension: ".der")

You can load multiple certificates if the client is connecting to several servers.

Hello pitfield,

In my project Postgres server is located in Europe - Prague. When I use the select now () command, Postgres returns a datetime in GMT, but I need a datetime of +2 hours. I could ask to adjust the SET TIMEZONE = 'GMT' to parameter SET TIMEZONE = + 2 or SET TIMEZONE = 'Europe / Prague' ?

Best regards Carlos

Migrate from BlueSocket and BlueSSLService to definitely preferred Apple's open source swift-nio.

Currently BlueSocket and BlueSSLService block modern versions of swift-argument-parser. They depend on 'swift-argument-parser' 0.4.1..<1.0.0. It's definitely wrong when networking libraries (not their applications) depend on argument parser.

I've created an extension to the library in my own code which may be useful if you'd consider a pull request. One of my data types is an array of SQL values, which there is currently no support for. The code linked here would add that type:

https://gist.github.com/kkieffer/0adadaaa351871d59d08a95d40f67cc3

The user can create an optionalArray and pull the values from it like this:

let postgresArrayValues : [PostgresValue] = try columns[8].optionalArray()?.arrayValues

Then for each value, they can extract the desired type.

The postgresValue for PostgresArray provides a raw value like this:

{"value1","value2","value3"}