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

Goodbye MongoDB, Hello PostgreSQL

developer.olery.com

Post reply on HN
Print view View original

241–250 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#241

Post by Animats »

jgrahamc wrote: post #13

As a greying developer I am most amused by people discovering that 'old' technologies like SQL databases work really well. The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid. And one more thing: star = Sequel.lit('*') User.select(:locale) .select_append { count(star).as(:amount) } .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percen…

Right. If you write SQL directly, you can also use commands such as EXPLAIN to see what the lookup strategy will be. If there's a full table scan of a large table involved, maybe you need a different query or a new index. If the SQL generation is hidden by some library, you can't do that.

Wikipedia runs on MySQL. They have replicated read-only copies of the database and ngnix caches which handle most read-type operations. Is your site busier than Wikipedia?

Re: Goodbye MongoDB, Hello PostgreSQL

#242

Post by mbreese »

rdtsc wrote: post #214
mbreese wrote: post #162

Earlier quoted context omitted.

Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.

> SQL was never really intended to be used by itself. Hmm I thought that is how it was intended to be used? If it didn't, it would look like datalog, lisp or some binary protocol with prefixed lengths and whatnot. It was intended to be typed in by analysts at a console. Who would then print the report on the dot matrix printer and mail to the headquarters or something of that sort.

That data has to get into the RDMBS somehow...

I guess a better wording may have been to say that SQL was never intended to be used in isolation. It can be, but you've always had interfaces to other languages / platforms. SQL is a data manipulation and retrieval language - the rest of the business logic has to be performed somewhere else (excepting complex stored procedures).

Re: Goodbye MongoDB, Hello PostgreSQL

#243

Post by nknighthb »

emodendroket wrote: post #209
moe wrote: post #180

Earlier quoted context omitted.

Python SQLAlchemy works a bit like this All ORMs work "a bit like this". I don't want "a bit like this". I want exactly like this. Because with my proposed interface I could be productive immediately and permanently. I would never have to refer to any documentation. Not once. With every other ORM this is a pipe dream. Their "fancy" chainable wrapper-APIs are nothing but a ball on a chain.

Good news! Almost every language offers some sort of string concatenation with no protection whatsoever against injection, which seems to be what you're asking for.

His proposal is not string concatenation, and it is not vulnerable to injection.

Re: Goodbye MongoDB, Hello PostgreSQL

#244

Post by [deleted] »

Animats wrote: post #241
jgrahamc wrote: post #13

As a greying developer I am most amused by people discovering that 'old' technologies like SQL databases work really well. The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid. And one more thing: star = Sequel.lit('*') User.select(:locale) .select_append { count(star).as(:amount) } .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percen…

Right. If you write SQL directly, you can also use commands such as EXPLAIN to see what the lookup strategy will be. If there's a full table scan of a large table involved, maybe you need a different query or a new index. If the SQL generation is hidden by some library, you can't do that. Wikipedia runs on MySQL. They have replicated read-only copies of the database and ngnix caches which handle most read-type operat…

[deleted]

Re: Goodbye MongoDB, Hello PostgreSQL

#245

Post by kiyoto »

YorickPeterse wrote: post #85
jgrahamc wrote: post #13

As a greying developer I am most amused by people discovering that 'old' technologies like SQL databases work really well. The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid. And one more thing: star = Sequel.lit('*') User.select(:locale) .select_append { count(star).as(:amount) } .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percen…

It's not so much about not wanting to write/understand SQL (both are still very much required), but about composability. If you want to re-use bits of a SQL query written as a string literal your only option is string concatention or using some kind of string builder/template system. In both cases there's little validation of the query's correctness (syntax wise) until you actually run it. While I agree that many ORM…

I agree with this 100%. SQL works incredibly well for purely declarive, relatively simple operations. However, the moment you start doing "Do this, then that, then that", it starts to look (literally the appearance) daunting.

I myself come from the R/Python background, and my hypothesis is this difficulty around composability (especially for dplyr/pandas crowd) is what deters data scientists from writing more SQL.

Re: Goodbye MongoDB, Hello PostgreSQL

#247

Post by ahachete »

As the post explained, one of the big problems of migration off of MongoDB is finding out the "effective" schema of the data, and then migrating it (according to that schema). By "effective" I mean the resulting structure of the data stored in the database.

I think it would very helpful to use here ToroDB (https://github.com/torodb/torodb). While being MongoDB-compatible, it stores data structured into PostgreSQL tables, automatically identifying the schema of the data. Then, just by looking at the created tables, you very easily have the schema (and the data migrated to that schema). It would make migration easier.

Disclaimer: I am a ToroDB developer

Re: Goodbye MongoDB, Hello PostgreSQL

#248

Post by mbreese »

ad_hominem wrote: post #169
mbreese wrote: post #162

Earlier quoted context omitted.

Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.

Disregarding my edit and how your query builder is irrelevant to my question anyway, if I change the requirement to allow the user to sort by multiple columns instead of one, that case statement is going to either combinatorially explode or you'll also have to add some ad-hoc SQL escaping to guard against SQL injection since you can't use bound parameters in ORDER BY clauses. It's easy to see how libraries like Seque…

Dealing with multiple ORDER BY clauses isn't any more difficult, but you'd have to figure out how to get the user input into some kind of list. That will require validation too... Just using an ORM doesn't let you ignore input validation. You wouldn't trust a user to use their own column names in a WHERE clause anymore than you'd trust them in an ORDER BY clause.

Trying to differentiate between a DSL that interfaces with SQL and SQL is nonsensical. SQL is the original DSL.

Re: Goodbye MongoDB, Hello PostgreSQL

#249

Post by carlio »

spdustin wrote: post #240

I feel like I'm missing something. So many comments in the article and here along the lines of, "just issues a warning and not an error." Am I the only one here who's thinking that this is correct behavior on the part of the DBMS? Three result codes from an operation: 1) everything is okay, 2) I'm sorry Dave, I can't do that (error) and 3) Okay, if you insist, but I'm going to change your data to make it work. Am I t…

Personally I feel like 3 should not be an option. Either things are okay or not okay, I don't like the "sort of okay maybe" option because that implies lots of fuzzy definitions and boundaries that the developer just has to learn.

Schema says int? Give it an int or fail. Not "it's sort of okay if you give it a thing which could be coerced into an int according to the database's ideas of coercability".

Re: Goodbye MongoDB, Hello PostgreSQL

#250

Post by djur »

nknighthb wrote: post #243
emodendroket wrote: post #209

Earlier quoted context omitted.

Good news! Almost every language offers some sort of string concatenation with no protection whatsoever against injection, which seems to be what you're asking for.

His proposal is not string concatenation, and it is not vulnerable to injection.

"WHERE price <= #{price_range}" looks like raw interpolation to me. How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?
Post reply on HN