Store HTTP response in PostgreSQL

Question

I want to store a whole HTTP response in PostgreSQL database row.

I need the HTTP status, the headers and the body.

AFAIK there is no native data type for this.

How could I store a HTTP response?

Update

I have been thinking about this again. I guess it is best if I follow this pattern: I won't search anything inside the http response. It is like a blob. Everything I want to search in it while be extracted before and put into a different column. Up to now only the http status code will get used and it will get an own column.

• McNets: Usually web servers receive an http response as a bunch of text.
• Patrick Mevzek: It depends on what you store it for, that is how you would use it afterwards. It could make sense to store the first line separately with the status code and the message and then headers could be a simple (name, value) table with the appropriate tables to link everything together. And the body separately.
• Patrick Mevzek: If it is only to store a blob and never retrieve it later on, I see little value to put it in the DB at all.
• guettli: @PatrickMevzek it gets retrieved. But no searching inside of this response happens.
• Patrick Mevzek: A relational database offers no advantages there. You have a blob of text store it as blob of text if you want. If you do not query its content I fail to see where is the real question anyway about how to store it.

Answer 1

The HTTP/1.1 spec says, through RFC-7230, that:

• the status code is a 3-digit number, so an int4 or int2 would do.
• for the entire header, see 3.2.4, "Field parsing":

Historically, HTTP has allowed field content with text in the ISO-8859-1 charset [ISO-8859-1], supporting other charsets only through use of [RFC2047] encoding. In practice, most HTTP header field values use only a subset of the US-ASCII charset [USASCII]. Newly defined header fields SHOULD limit their field values to US-ASCII octets. A recipient SHOULD treat other octets in field content (obs-text) as opaque data.

"opaque" pretty much implies that bytea is the only safe choice, if you want to handle the responses of any HTTP server out there.

• The message body is defined as message-body = *OCTET, so bytea is also pretty much the only type that fits, unless you prefer the large objects storage and API. bytea is limited to 1Gb so you may want to chunk the value across several smaller rows if you target any size. In practice, very large bytea values tend to be unworkable, personally I wouldn't go over 128Mb per row.

• guettli: Just for the records, this brings a new question up: dba.stackexchange.com/questions/194602/… (Chunking data into a PostgreSQL bytea column?)

Answer 2

How could I store a http response?

As a text? If you need to mark it up you could also do that easily in jsonb and a client side library. That's usually very easy to do

Or you could normalize it into a HEADER and BODY table, though it'd be a little more complex with HTTP2.

• guettli: Does text work, if the http response contains binary data like an image or audio data?
• Evan Carroll: @guettli no, it doesn't. you never mentioned any of that in the original post. HTTP supports octet-streams, if you use them though you're not going to be able to usefully store the response. Your best option is to pull the octet stream out of the response, store it on disk. Or, better yet, if you don't need it set your accept request header do disallow all non-html/text. Also HTTP2 isn't guaranteed to come in any order. This is really a fruitless pursuit. You're trying to store a transfer protocol in the database. Just store the data.