Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Simple API with Nginx and PostgreSQL

rny.io

Post reply on HN
Print view View original

61–70 of 74 posts

Re: Simple API with Nginx and PostgreSQL

#61

Post by akurilin »

asdasf wrote: post #54
dragontamer wrote: post #28

Earlier quoted context omitted.

Timestamptz is one amazing reason to use Postgres over MySQL frankly. Proper time + timezone storage is quite useful.

Postgresql doesn't offer proper timezone storage, or in fact any timezone storage. Timestamptz is just a UTC timestamp that is converted to/from your connection-specified timezone. You completely lose the "what timezone was this originally specified in" data.

The point is that the timestamp type is meant more for calendaring, unless you're manually normalizing the dates you're passing into postgres to a certain timezone like UTC. Timestamptz is what you actually want 99% of the time.

Re: Simple API with Nginx and PostgreSQL

#63

Post by gbog »

AYBABTME wrote: post #48

Why use a SQL database when all you really want is a key-value store?

Who says he wants a key value store?

This setup is powerful by its simplicity. Only two layers. And stored procedures can help you do validation, so one could imagine a full Twitter like implemented this way (lest the ui, obviously)

Re: Simple API with Nginx and PostgreSQL

#64

Post by gbog »

stevoski wrote: post #25

This article gives a good example of why you SHOULD consider using an existing framework to create a REST API. I see no concern about authentication, authorisation, scalability, protection from SQL injection attacks, nor making the output easily parseable by third-party applications. None of these are issues you can simply say, "I'll deal with that later when it becomes a problem." They are reasons why an existing fr…

Again the middle brow dismissal.

Please make a little effort and expand on why this article is interesting instead.

For instance, people are using micro frameworks for a reason. They want less clutter and a more direct grab on what the machine is doing. With the proposed theoretical solution we get to remove one big complexity element in the setup, it is very interesting.

Re: Simple API with Nginx and PostgreSQL

#65

Post by asdasf »

akurilin wrote: post #61
asdasf wrote: post #54

Earlier quoted context omitted.

Postgresql doesn't offer proper timezone storage, or in fact any timezone storage. Timestamptz is just a UTC timestamp that is converted to/from your connection-specified timezone. You completely lose the "what timezone was this originally specified in" data.

The point is that the timestamp type is meant more for calendaring, unless you're manually normalizing the dates you're passing into postgres to a certain timezone like UTC. Timestamptz is what you actually want 99% of the time.

Yes, timestamptz is the one you want 100% of the time. But there are a ton of people who don't realize it doesn't work the way they think it does. Lots of people think it stores the timezone, and it is really important to understand that is not the case when designing your app. Postgresql is actually completely lacking in the ability to store and validate timezones, as well as converting values to other timezones in useful ways. It is quite possibly the biggest weak spot postgresql has.

Re: Simple API with Nginx and PostgreSQL

#66

Post by EmmanuelOga »

Every time I see something like this I think it is an opportunity to finally do something with http://www.eclipse.org/Xtext/. How cool would it be to come up with a simple (external!) DSL to mount SQL queries over HTTP routes, and transform the whole thing to a nginx configuration file?

Re: Simple API with Nginx and PostgreSQL

#67

Post by davyjones »

I want to chime in that I have been using a similar setup: nginx, ngx-postgres, postgresql 9.2.4 in production for a while. It has been very snappy and rock solid till this point.

I used android async-http to talk to the server from my app to have everything online. This is an intranet app so latency is not really an issue.

Re: Simple API with Nginx and PostgreSQL

#68

Post by jeffdavis »

ams6110 wrote: post #41
jeffdavis wrote: post #6

Earlier quoted context omitted.

To clarify, it looks like "postgres_escape" is the way to do escaping. Unfortunately, it seems a bit awkward to differentiate between empty and NULL strings. That's something to be careful of. Also, I really think this should be included in the blog post, even if it's simple. Protecting against SQL injection is not optional, so leaving it out only muddies the comparison with more traditional frameworks. Also, there's…

From the docs re postgres_escape : "Because nginx cannot tell the difference between empty and non-existing strings, all empty strings are by default escaped to NULL value." This behavior actually is what anyone who has used Oracle is accustomed to (empty string is NULL). I don't recall the default behavior in Postgres but Postgres is "Oracle-ish" in a lot of ways so I would be surprised if this is not the default th…

The "empty string is NULL" behavior you describe is a standards violation. Postgres does not follow Oracle off of that particular cliff; postgres does differentiate between the two.
Post reply on HN