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

PostgREST – REST API from any PostgreSQL database

github.com

Post reply on HN
Print view View original

201–210 of 210 posts

Re: PostgREST – REST API from any PostgreSQL database

#201

Post by dragonwriter »

kelseydh wrote: post #128
glogla wrote: post #69

Earlier quoted context omitted.

You can do that using SQL views. That means you can provide consistent api of your choosing no matter how the original tables look like. Postgrest docs also describe how you should use this feature to version you API.

Implementing SQL views is not exactly trivial if you are operating under an existing Ruby on Rails app. I suspect most RoR developers don't use SQL views unless they absolutely need to for a certain query that's heavy on performance. As a result, the coupling to the schema does seem to be concerning as moving everything to SQL views appears to be a high amount of overhead if you're already successfully relying on an…

> Implementing SQL views is not exactly trivial if you are operating under an existing Ruby on Rails app.

From the applications side, it should be completely transparent to replace base tables with views.

From the database side, assuming you keep the views and the base tables in the same schema, for the initial transition, you just need to rename the base tables, and then create views with the names of the original tables referencing them; they'll be simple views, and so, in postgres, automatically updatable, so you won't even need to explicitly define update logic.

Re: PostgREST – REST API from any PostgreSQL database

#202

Post by [deleted] »

3pt14159 wrote: post #140
sergiosgc wrote: post #107

Earlier quoted context omitted.

> The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored procedures would restrain you from doing all three. Your argument has a non sequitur right here. A mess of [foo] is a mess; the layer it is in does not matter; the language it is in does not matter. A mess of application layer code is equally effective in…

I purposely chose a non sequitur in the interests of speeding up the prose. It is an acceptable method frequently utilized in language. It roughly translates to: "Than (what will certainly be given the expressiveness and level of abstraction they provide) a mess of stored procedures."

[deleted]

Re: PostgREST – REST API from any PostgreSQL database

#204

Post by _lce0 »

ectoplasm wrote: post #189
kijin wrote: post #187

Earlier quoted context omitted.

Now you're just redefining "developer". No True Scotsman puts sugar on his porridge, and No True Developer just installs WordPress. On the other hand, even Rails and Django encourage you to use the ORM whenever possible, so even a "developer" who builds apps on a modern framework is unlikely to be familiar with advanced SQL features.

For me, if you had to write even a single line of a Turing complete language to a file (so shell scripts yes, but one-time shell commands no) to install WP, that would count as development. Otherwise, it's just installation. Note: I have never installed WP. Do people really consider ./configure && make && make install and its equivalents to be development now?

Not really. Most web developers are scare of using the cli.

Last time I checked the WP install process was something like...

1. download zip, extract

2. change your some config file

3. upload whole folder using FTP

4. go to /install or something, and from there..

5. click, click, click, edit text, click, click, click ...

That was only if the web developer was in hardcore mode, otherwise it was just _log into cpanel to use one-click installer_

Re: PostgREST – REST API from any PostgreSQL database

#205

Post by McElroy »

spacemanmatt wrote: post #200
McElroy wrote: post #78

Between this (yes, I know it's 3rd party) and the support for JSON, PostgreSQL seems to be eating into the market of the NoSQL databases every day. I like that. I like that because the fewer new things I must learn, the more time I can spend on the things I find interesting.

That is entirely on purpose, too.

Tell me about your devolution. (He said to ask him to in his profile.)

Re: PostgREST – REST API from any PostgreSQL database

#207

Post by spopejoy »

spacemanmatt wrote: post #196
spopejoy wrote: post #194

Earlier quoted context omitted.

Agree, the received wisdom has always been database vendor choice as a ten-year commitment, as opposed to almost anything else. It's a reason to avoid databases :)

Strongly disagreed that it's a good reason to avoid databases. If you are going to collect data of any value over a long period of time, pick a horse and stay on it. Over time, technology and business needs might force a re-evaluation of that position, but you don't want to build around unmeasured plans to maybe switch databases at random some day.

It was a joke, implying that people who can't commit will avoid databases like the plague

Re: PostgREST – REST API from any PostgreSQL database

#208

Post by GrinningFool »

kijin wrote: post #188
GrinningFool wrote: post #143

Earlier quoted context omitted.

So you start off ok here: I'm just stating what I believe to be a fact... But then you move on to say: It's just a fact, whether I like it or not. So which is it? Do you believe it to be a fact, or is it a fact? And if it is, where's your evidence? (I happen to agree with your opinion, but the semantics here bug me.)

Sorry for the loose use of language. Everything that follows the colon after "I believe to be a fact", until the end of that paragraph, is the content of what I believe, including the statememt "It's just a fact." I believe that it's a fact. Anecdotal evidence: I've interacted with dozens of other people who call themselves web developers over the years, and most of them (outside of Silicon Valley) have never used Po…

Thanks for clarifying. It does seem all too common to pretend the database is a black box (via ORM) in the most popular frameworks.

Re: PostgREST – REST API from any PostgreSQL database

#210

Post by lpsmith »

batou wrote: post #109
nunwuo wrote: post #94

Earlier quoted context omitted.

> Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. What do you mean by that? How is having a bunch of queries in a stored procedure more "stateful" than having the same queries in the application? > Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field wit…

Stateful: If I have to load the stored procedure into the persistence engine then that step is required. This is no more stateful than queries in the application but it means that the relevant state in both the application and the database engine needs to be reloaded and constantly sychronised. Ergo, two times the work. CHECK constraint violated is no good for humans. Prevention is better than cure here. Why shouldn'…

There is a good chance that your proposed algorithm to enforce a uniqueness constraint in the application won't work. As in, you've left out enough details that would be critical for getting it right, and in my experience, a lot of programmers would only get this right by accident if they get it right at all.

First problem is that the SQL standard provides no way to make this work portably on any standards-compliant database. So right there you are going to have to code to the database to one degree or another.

So, let's say you want to make this work in Postgres. Now, you'll need to be using Postgres 9.0 at least; otherwise your uniqueness constraint won't be a uniqueness constraint.

Try this, in any version of Postgres. Open up two psql sessions. In one, run a `create table unique (x text);`. Then run `begin isolation level repeatable read; select * from unique where x = 'foo';` in one of the sessions. Repeat those two commands in the other sessions.

Neither session sees 'foo'. So now both can go ahead and run `insert into unique values ('foo'); commit;`. Both transactions will succeed, and you can confirm that there are now two instances of 'foo' in the table.

In fact, `begin isolation level serializable` in PostgreSQL 9.0 or later is the minimum isolation level to make this work. And, you will need retry logic around the transaction in case of a serialization failure. (Perhaps your DB access layer or language would hide this latter detail from you, or perhaps not.)

In PostgreSQL 8.4 and before, serializable and repeatable read were equivalent, and both were still SQL standards compliant. In PostgreSQL 9.0, the repeatable read isolation level stayed the same, while the serializable isolation level was strengthened.

Unless you can accept a certain level of degraded accuracy by using a probabilistic construct such as a Bloom filter, by far the biggest cost of maintaining uniqueness is the index. And you'll need that index whether you use the database or the application to enforce uniqueness.

And, judiciously pushing computation onto a database can actually be cheaper for the database as well as its clients. This scenario is likely to be one of those situations.

Post reply on HN