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…
Goodbye MongoDB, Hello PostgreSQL
291–300 of 388 posts
Re: Goodbye MongoDB, Hello PostgreSQL
#292As 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…
I would like to say lets step back and not conflate SQL and relational databases together. Clearly SQL as the language the primary way most people interact with relational database.
In my my mind SQL as a language is a huge PITA. First, parsing of complex statements is expensive (there's workloads where SQL parsing takes more time then processing the results).
Second, as SQL exists today (SQL2011) it's a large, complex language that's not implemented uniformly. So I understand why people want to build programmable interfaces for generating queries versus writing giant string statements that expand to SQL.
I personally would wish that there was an alternative language for interacting with relational databases that isn't SQL. Just expose the whole relational expression tree to users say ala S expressions. It's not like the relational engine is going to optimize / re-order it anyways.
I mean something along the lines of:
[ GROUP_AGGREGATE,
[ "name" ],
[ [ SUM, "COUNT(*)" ],
[ SUM, "cost" ]],
[ JOIN, [ ==, "user_id", "customer_id"],
[ FILTER, [
[ &&,
[ >=, "age", "30" ],
[ IN, "state", "NY", "CT", "NJ"]],
[ TABLE, "customer" ]
[ TABLE, "orders" ]]]]]
Re: Goodbye MongoDB, Hello PostgreSQL
#293Earlier quoted context omitted.
I agree with you. Also, we should be developing products that last. And be proud of that. Why does everything always have to be new?
You sound like someone who thinks we thinks we should still be using Cobol and IBM Series mainframes. Technology changes. It improves. It gets faster, easier and more responsive to business requirements. If you don't embrace change in the IT industry then get out. Because you simply won't survive.
That's rather going to extremes with what I said.
So, no, I don't think like that at all.
Re: Goodbye MongoDB, Hello PostgreSQL
#294Earlier quoted context omitted.
"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?
How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query? It was just a mockup. But you are right, in reality it would end up looking more like this (and use custom interpolation for escaping): s.add "WHERE foo > $(bar)" Likewise a smart syntax for clause combining (AND/OR) and some kind of nesting would probably be needed. I believe both of these problems sh…
Or you could just use Sequel and not have to build up your own library.
Re: Goodbye MongoDB, Hello PostgreSQL
#295This post outlines some of the most expensive parts of deploying, operating, and maintaining an application that operates on "NoSQL" databases like Mongo, and in my experience, DynamoDB:
1) Implicit Schemas. We avoid this completely by doing production migrations and re-indexes on every new field we add. Its expensive, and we have to write/test/run scripts in production. At times I wish I could just write a Rails migration on an RDS instance and call it a day. N-1 compatibility isn't hard to accomplish with good code reviews.
2) Search. Want to search and join like in the old days? Good luck. Using a NoSQL DB as the primary authoritative store of record is great, but you'll need a secondary indexes for any searches you want to do. If you need to look up an object on a new field (or even one that already exists in all of your data) if you haven't built an index for it you will have to.
3) Serving Clients. Because of the schemaless-blobby nature of writing clients, things get real messy the minute you have multiple services or applications reading or writing to a DB. To get around this, you have to put a service in front of it and serve the data from some RPC technology, which is an extra step and requires more development and maintenance.
4) Administration. There aren't a long history yet of robust toolsets and "science" behind different schemaless NoSQL databases. Meaning which one you choose has a huge impact on your ability to fine tune it, debug consistency or other expectation issues, and do things like proper failover, backups, restores, etc. Knowledge between similar NoSQL DBs doesn't transfer as well so your mileage will vary more so than on SQL databases (MySQL vs PostgresSQL, for instance).
NoSQL has its place and purpose, but it is rarely as the "one database that rules them all" that many businesses end up with. I'd be interested in counter stories.
Re: Goodbye MongoDB, Hello PostgreSQL
#296Earlier quoted context omitted.
Really, you can't spend the 10 minutes designing a table structure in a SQL database? And now you have to spend months re-inventing the wheel because you wanted an easy out? This post reflects on developers being lazy, instead of doing it right the first time around. Oh no, you have to log in to the db and run a CREATE TABLE statement every few months when you need to scale. Cry some more. And even then, 'lazy' is su…
Actually, I think that's a great way to think about it: NoSQL is the "dynamic typing" of the database world. Put another way, it's like "what? you couldn't spend 10 minutes declaring types everywhere?" - yeah, it's less robust, yet dynamically typed languages remain popular. My excuse: When I'm just past the mock stage, and still playing with what UI functionality should be, sometimes I just want to get some JSON per…
Your given excuse makes you sound more like a naive amateur instead of a pragmatic architect
Re: Goodbye MongoDB, Hello PostgreSQL
#297Earlier quoted context omitted.
You mean something like this? https://www.jetbrains.com/dbe/
Yes that was in my mind as I was typing it actually :) Notice that its only a yearish old I think?
The IntelliJ IDEA Database plugin [1] on which 0xDBE is based on has existed for quite a while. I'm not sure when it become very useful as I've only used it for a few years, but it is quite powerful and useful.
The SQL code completion is quite good (and it caches your entire database schema so it is very quick). That's not particularly special on it's own, but what makes this especially useful is IDEA's "language injection" [2] feature. This allows you to, for example, get completion for SQL when it is contained in some other language, which could be anything (e.g. a Java String, Ruby String, XML, etc). It will also analyze and report errors in this SQL on the fly.
And SQL statements contained in concatenated strings are no longer a problem because you can edit those in a separate editor window where you are only editing the SQL, and it automatically gets placed into the concatenated string.
Not sure if it addresses all of your specific concerns, though.
[1] https://www.jetbrains.com/idea/features/database_tools.html
[2] https://www.jetbrains.com/idea/help/using-language-injection...
Re: Goodbye MongoDB, Hello PostgreSQL
#298As 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…
What I'd like to see is a universal SQL that can be translated to whatever dialect of SQL my current database is using. That way I won't have to relearn SQL every time I start a project with a different database engine.
You could use Teiid (https://github.com/teiid/teiid). The SQL dialect is similar to Postgres, and it has built-in translators to handle all of the popular relational databases (Postgres, Oracle, MySQL). Added bonus is that even some NoSQL databases are supported, and you can do things like join a table from a MySQL database against a collection in MongoDB. Full CRUD is supported for most translators.
If you're using Java (JDBC), there's an embedded kit so you don't have to run a standalone server. If you're not using Java, you can run the standalone server which also emulates the Postgres ODBC protocol so you can (for example) use `psql` to connect and run queries.
Re: Goodbye MongoDB, Hello PostgreSQL
#299Earlier quoted context omitted.
Manage as in write all the code that ensures adherence to the schema. If you say that the field called "score" is an int in a schema-ful DB, then insert an array, the DB will throw an error. If you do that in a schema-less DB, it will not unless you add a check yourself. If you are not using some type of unified DB access layer, you must perform this check every time you write a value. You must also perform the check…
(solely toward your final point) Many RDBMS' will optimize that for you. PostgreSQL has a bitmap structure on each row that indicates which columns are null and which have data in them. In addition, it automatically compresses certain data types. For example, strings that overflow to the point where they need to be stored in a secondary table (called "toast" in pg) will be automatically compressed.
That's pretty cool. I didn't know Postgres actually did this, though I figured it was at least possible.
Re: Goodbye MongoDB, Hello PostgreSQL
#300Earlier quoted context omitted.
I once agreed with this, but now I don't. I just want to write SQL (dammit!). I can never, ever remember the intricacies of the Sequel API or any one of these query builder APIs. I am always looking up something that is rather trivial because I am thinking in SQL, the language, and always have to convert back to Ruby or whatever language I am working in. CTEs and SQL functions in PostgreSQL strike a good balance in t…
Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price =), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going…
Dynamic prepared(NO ADHOC, THIS IS CRAP) TSQL is a common occurrence for me in production, it is often a better choice that writing a bunch of conditional logic in SQL (to get sargeable queries) or a bunch of really unperformant case statements in where clauses.
I have written TSQL that changes the entire query (which was on the order of 80 joins) dynamically based on about ~20 different parameters, only a few actually passed into the stored procedure, the rest based on settings and configuration. It took a few hours to grok how everything came together at first, but a few tricks that the optimizer will remove means you can add arbitrary conditionals in whatever order you please.
The example you give is VERY trivial for dynamic SQL. postgres - http://www.postgresql.org/docs/9.1/static/ecpg-dynamic.html mssql - https://msdn.microsoft.com/en-us/library/ms188001.aspx