HN Explorer

I hate queries

27 points by swix ↗ HN
Yes. Queries to me is a "annoying" requirement of software/product/website development. What is strange to me is that we are living in 2010 and yet here we are writing queries for a backend layer that should simply be a lot more easy and fast to do. Imagine for websites, you have to create a database with tables and fields and so on, then you have to write extra code in some perhaps to you foreign language where you select what data you wish to present based on some parameter.

Why oh god why!? I don't want to write queries, and it's not because I think it's hard or anything like that, the reason is more about time and funfactor. Writing queries to me is the most boring part of any project and often a very time consuming process, imagine spending this time designing or improving your app in other ways. So what could we have instead?

Well imagine a perfect world where all you would have todo is create some table/fields or documents depending if it's a relational or no sql db. Then you would simply through some amazingly easy and efficient interface select what data you want to present and it would automagically generate whatever code/query or a process that is necessary, then you would just invoke that process to obtain that data.

Nooo no no I am NOT talking about query builders, those are horrible! They take even longer time to work with than just writing plain simple query code, I am talking about a revolution in the way we deal with this layer - "queries" I call it a "layer" because to me it's like a layer of a development process that is necessary but should not be as time consuming or boring as it currently is. Sorry to all you query writing lovers out there!

There should be some tool or way to generate a function/stored procedure/x that outputs your data as arrays/objects/json/xml/whatever based on some simple dragging or dropping or marking that you would do with a super neat efficient interface. The tool would then do all the work for you, and whatever query/procedure/function that it generates would already be amazingly optimized, now you would just invoke it and bam there is your result set ready in some array or object for you to use as you want. Que rico, verdad?

Maybe I am living in the 90s still, maybe this already exists, and I have been living under a rock, perhaps I am asking for too much, who knows I am just letting the world know and I am sure there is a lot of people that can agree with me. Especially people more oriented at design or frontend stuff...

Gaah the frustration, Please don't hate, ps english is not my mother tongue so forgive me if you can't understand it ;-)

51 comments

[ 3.5 ms ] story [ 119 ms ] thread
photon_off
Of course, there's almost a guaranteed trade-off between making things super easy, and making things super-specific and customizable. There are lots and lots of different requirement possibilities that are possible even with a small amount of tables, and anything that builds these queries for you needs to know about all of them, or be able to know about all of them, at which case you're going to have to describe some things to it in some sort of, um, query.

I'm positive there is some framework that makes touching a DB barely needed, but I don't personal know it. I'm going to upvote your thread and hope somebody comes along and tells you what it is.

michael_dorfman
Are you sure you're not looking for an ORM?
nostrademons
It sounded like he wanted to be able to access data without writing any code at all - ORMs just replace one type of code with another.
Someone
I didn't clearly see the 'R' part of ORM in his text. Also, the 'M' part may not be necessary.

Any object store could be the solution for his problem. Depending on the requirements, this could be as simple as keeping everything his program needs to store in memory.

swix
ORM is definitely a step in the right direction, but I was thinking about something even more awesome. One can dream atleast..
DjDarkman
The best you can do is either write a layer on top of SQL or simply use a NoSQL database... Dragging and dropping won't work, because you would still have to use the results in your application.
kemayo
ORMs do that, mostly. Look at Active Record: http://en.wikipedia.org/wiki/Active_record_pattern

The problem with ORMs is that relational databases are complicated, and you often do wind up having to do manual SQL writing anyway if you want well-performing queries in edge cases. But for simple cases where you have a person table and need to make Person objects in your app... it works.

In fact, Rails used this as one of its big selling points, combined with convention-over-configuration. If you stuck to the naming patterns Rails preferred, it really would Just Work without you having to tell it anything beyond the names of your classes.

adamtj
The problem with ORMs is that life is complicated, and data describes life, so data is complicated. And abundant. SQL queries are a relatively elegant way to encode the often complicated manner in which data answers a particular question.

<sarcasm>Gah! You know what I hate about building software? The programming. All those data structures are no fun! Lists and arrays and vectors and hash tables and trees, red-black trees, binary trees, b-trees, tries. Why can't we have a programming language that automagically stores all our information in simple variables and just gives me the answer I want?</sarcasm>

rbranson
You have apparently never used a well-designed ORM. A well-designed ORM isn't to create this magical, leak-proof wrapper for the database. The point is the make common use cases simple and allow the developer to incrementally use more SQL as needed, for more control.
barmstrong
Try Ruby on Rails!

With Active Record you rarely if ever need to write SQL any more.

http://guides.rubyonrails.org/association_basics.html

http://guides.rubyonrails.org/active_record_querying.html

I think SQL is slowly going the way of assembly language over time - still useful to write by hand in special cases but most of the time you want to write in something higher level and have it generated automatically.

auxbuss
Just in case the OP is using PHP, there are libraries that support Active Record without necessarily resorting to a PHP framework. (Same as you can AR outside of Rails [caveat emptor, might have changed in Rails3].) Adodb has one. Not a personal recommendation, just saying it's there if you need it.
MichaelApproved
In my experience, most layers that make things easier generally suck under load. I would think it's especially true with something that adds a layer between you and the database.

How is Active Record with regards to performance under load?

emmett
ActiveRecord has great performance. Mostly because it's a really thin layer over database tables. The only additional overhead is instantiating one object per row returned.
prodigal_erik
Much like other ORMs, ActiveRecord makes it almost effortless to accidentally do very stupid queries in bulk. For example (from http://guides.rubyonrails.org/active_record_querying.html) this code

  clients = Client.all(:limit => 10)
  clients.each do |client|
      puts client.address.postcode
  end
looks harmless but requires eleven sequential round-trips to the database. This version

  clients = Client.includes(:address).limit(10)
  clients.each do |client|
      puts client.address.postcode
  end
still requires two, and one of the queries gets bigger as the number of ids increases. You can get into find_by_sql, but at that point ActiveRecord isn't adding any value.
rbranson
Sure, but how many times have you seen someone create something equal to that with SQL queries? Instead of using a join, they execute a SELECT against the database for every row they fetch. This is shockingly common in horribly written PHP code.

In addition, ActiveRecord also supports joins instead of using includes. Includes is almost always fine, unless the initial result set is large (100+ rows).

chrisbolt
At least with SQL, it's obvious and not hidden by a layer of abstraction.
kthxbye
Assembly has been largely replaced by higher level programming languages.

And since SQL is a fourth generation declarative language, I really don't see how it could go the same way as assembly by being replaced with imperative programming. It's already more expressive and succint, and have stronger abstractions from implementation details, than imperative programming languages.

sqrt17
Actually, try any ORM.

Hibernate for Java, SQLAlchemy for Python or Class::DBI for Perl (never used that last one) would be good examples for ORMs that blend well with the language they're written for.

Performance may vary across different ORM modules; the best-of-breed ones are certainly as fast as if you'd create the SQL yourself.

andybak
What language are you using?
swix
Depends, a lot of python/php and javascript/html5 stuff for frontend. Web stuff basically.
mgkimsal
Grails with GORM is the closest I've found. Not perfect, but I rarely write 'queries' for most projects unless I have specific complex reporting needs with performance considerations.

http://www.grails.org/GORM

I don't write create scripts, or data management stuff, or SQL of any kind.

def smithUsers = User.findAllByLastname("smith")

It's not drag/drop/gui, but it's a lot less mental friction for me when writing code.

jcapote
I havent written a query in almost 4 years.

http://rubyonrails.org

http://code.google.com/p/redis/

http://github.com/nateware/redis-objects

wjessup
was thinking "redis" the whole time.
dasil003
Really, no query in 4 years? What happens when the business people need some new data view? Do you dump all the data and reduce it via some one off procedure?
mgkimsal
I can't speak to the GP, but I can say I've written a few systems ('moderate' in size, with a growing dataset) and was able to do the first pass and go for 6 months or so without needing to write any SQL. I ended up writing some because some reports needed to be run fast. The ORM was working, but doing more than it needed to, and it was easier to just write the SQL in that case than it was to try to figure out how to express the use cases in the ORM (GORM/Grails, using Hibernate under the hood).

I can't say I never write queries, but on the few Grails projects I've worked on over the past 18 months or so, I've probably needed to write fewer than 15 queries total.

rbranson
This has been my experience with Rails as well. Most of the time, no SQL is needed, but it's easy to access when it is needed.
nostrademons
Sounds like Microsoft Access.

Couple things:

1. Most of the times, when you're building a webapp, your queries depend upon user input. How would you model that in your GUI? Once you start having to do significant processing on your input to figure out what to query, the visual model falls down pretty hard.

2. I suspect that if you actually used this interface in day-to-day work, you wouldn't like it so much. There's a reason why many hardcore hackers still use the command line: once you've burned the language syntax into your brain, you can get things done much quicker than with a GUI. If you do like it, I'd encourage you to try MS Access.

atomical
I prefer Django's ORM but using the ORM can be quite tricky if the database has not been created with the ORM in mind (i.e. you have a few pages of subqueries).
[dead] _ivan
Find someone to command .. Wizard or so and do nothing :)
ww520
It might be good to go beyond your comfort zone and dive into relational theory and practices. Data tend to live way longer than applications. You want to have a language and application agnostic way to store and access data so that different applications in different languages can share the same data. You also can evolve your application platform and languages using the same data over time. Want to re-write your app in C# instead of Ruby? No problem. The data and access method stay the same.

Of course if you are just building throw-away prototype or single usage app, you don't have to use RDBMS. Use object serialization, persistent hashtable, or OODBMS. Those tend to mesh well with the language you are using.

shib71
Clearly you have never needed to do anything remotely complex with a database. There is a reason visual programing languages aren't successful outside of a few extremely simple domains.
swix
I have, I have actually worked with financial systems using Oracle, Forms etc, but even with that it's hell. I don't HATE "SQL" the language itself, I think it's very clever, beautiful etc. What I don't like is the whole part of actually creating the queries.

I understand that obviously for some applications a simple model is impossible, but for a lot of things, probably 80% of the work I currently do, webapps/simple listings, etc it would be very possible that a tool could solve the query writing part for the less complex type of things.

mikeklaas
I don't get it. Queries are a precise, textual specification of the data you need from a database. How can you hate that?

Perhaps what you mean is you hate dealing with converting the results to the data types you will use in your application?

Perhaps what you mean is you hate the administrative overhead of creating separate files and dealing with deployment when creating stored procedures?

Perhaps what you mean is you hate properly inserting your native data types into a written SQL query in a secure way?

Or do you actually hate writing SQL? To me, that's the best part of interacting with databases.

dasil003
No doubt, I mean any kind of coding can be tedious. I feel that way about virtually anything in Java. SQL while far from perfect in many ways is at least extremely terse and flexible. Sounds like maybe he just needs an ORM for his day-to-day stuff so he can focus on only the interesting queries.
[deleted]
(comment deleted)
tmcneal
I agree. I'm developing on Google App Engine now and the lack of a true SQL language in the Datastore is my biggest pain point right now.

The expressiveness of SQL and performance of a mature DBMS beats the pants off of the GAE Datastore at the moment. Obviously scalability is the major benefit of App Engine, but I'm not so sure the trade-offs are worth it.

swix
like I said further down the thread, I don't hate "SQL" the language itself, I hate the time I have to spend writing queries, I see it as some kind of "extra layer of butter" between the website/app and me.

I would like to automate it more that's all. I get that SQL is required for a lot of reasons a lot of times, but for what I do currently webapps/sites/etc probably 80% could be done with some sort of tool if it existed, it's not very complex queries, but yet someone have to create them and that's time and thats boring (to me).

I don't find it interesting at all, I do like to develop stuff, websites, apps but more oriented at the visual stuff, the data to me is just.. should just be accessible there ready to play with. I guess I see writing query code as going to the toilet and taking a dump, it's not really "funny" and yet you have to do it, only that maybe the query part could be improved upon with some tooling to reduce time etc.

klahnako
...

Perhaps what you mean is you hate the escape characters used to switch between html and sql.

Perhaps what you mean is you hate the MVC separation.

I do not agree with swix that drag and drop is that solution. If there is one thing that has burned me too many times it is visual programming. Visual programming, to me, converts somatic gestures into black-box code. I love casting magic spells, but the lack of tools make them hard to fix: You must start from scratch for a small change.

I believe the solution is textual: Where different text means different things, and text can be edited to correct your mistakes. This means SQL will stay.

On another subject, I can see how Model/View separation is causing great pain. Separating a simple view into 2+ files makes a distinction without a measurable benefit. How often has your model changed without touching the view? And when it did, would having it all in one file make that change harder?

From my little experience, the majority cases leave Models and Views in a 1-1 correspondence. I agree with swix that current languages make those cases needlessly difficult.

Locke1689
Here is why SQL exists: http://en.wikipedia.org/wiki/Relational_algebra. Most criticisms of SQL are wrong because the people who write them never bother to read the mathematics or theory behind their construction.

Any language with similar power will be just as complex.

If you just have a limited use case that you want optimized, that's what most ORMs try to do.

ratsbane
Well said. Here's another way of looking at it: if you select an element from a hash (or dictionary or associative array) you're doing the same thing as a simple query. A query is like a higher-order version of a key lookup.
BonoboBoner
The reason we still need 'queries' or maybe 'handmade data access' is because you dont know all the questions your systems will have to answer upfront.
gokhan
Why don't you imagine a perfect world where business people define their rules and easily draw the screen they want, and the rest is auto generated by the tool (http://en.wikipedia.org/wiki/Computer-aided_software_enginee...)?

Apparently, there are some great complexities in the software engineering process which can't be visualized or simplified enough to be aided by a tool.

SQL or the programming language you have is textual dsl's, abstracting you from the details / complexities and powerful enough that you can access all the underlying functionality. ORM's, ActiveRecord pattern etc. are all another abstraction but they're not as powerful as bare SQL. As long as you don't hit their wall, you'll be OK. But keep in mind that each layer makes some functionality inaccessible.

I once wrote a visual designer for Castle ActiveRecord & NHibernate, called ActiveWriter. It was great for the begginer on that technologies. Once you need more, the tool lacks the functionality since it gets harder and harder to implement all the details of a textual DSL in a graphical DSL. Today, I prefer Fluent NHibernate for my mappings since it's more complete on the mapping front. It seems more work but it deals with the complexity better than my own graphical tool.

rbranson
Basically: almost every attempt to simplify programming has failed. We have successfully made the task of programming more efficient and accessible, through the use of libraries, tools, and abstraction, but this is merely the elimination of duplicated effort. At it's core, it's still a task that requires significant mental focus, intelligence, and dedication to craft.
dstein
What you are describing is the combination of a fifth generation programming language, a universal semantic/ontology data layer, and an intelligent search engine with insight into the information you want, where it is, and how you want it computed and displayed.

I assure you, this is an even harder problem than it sounds.

wslh
LINQ is great.
dangrossman
Writing queries is one of my favorite activities. Some of the queries I had to write for w3roi.com are two pages long printed in 10 point font. They're efficient as possible and quite beautiful to me.
mithaler
There should be some tool or way to generate a function/stored procedure/x that outputs your data as arrays/objects/json/xml/whatever based on some simple dragging or dropping or marking that you would do with a super neat efficient interface. The tool would then do all the work for you, and whatever query/procedure/function that it generates would already be amazingly optimized, now you would just invoke it and bam there is your result set ready in some array or object for you to use as you want.

Write it yourself? If you did it well, maybe it would help other people understand your point.

aberkowitz
Queries work -

An SQL statement tells you in almost-English exactly what to expect

The SQL standard allows me to understand what's going on in an MSSQL query when I've never used MSSQL before.

Conclusion: even though you say writing queries is easy for you, it probably isn't. Once you familiarize yourself better with SQL, you will appreciate it.

elviejo
I feel your pain writing SQL Queries is bug prone exercise. And trying to optimize a SQL query shows that it is a leaky abstraction.

Things that I think are a better option are:

1. Object Oriented Databases (OODB) A database that behaves like your objects. I like Gemston(Ruby & smalltalk) and db4o (java & .Net)

2. Graph databases: http://neo4j.org/ Neo4j is a graph database. It is an embedded, disk-based, fully transactional Java persistence engine that stores data structured in graphs rather than in tables. A graph (mathematical lingo for a network) is a flexible data structure that allows a more agile and rapid style of development.

Try this alternative databases... I think they are close to the answer.

PS: ORMapping is not a solution... it always ends up being messy: "Object-Relational Mapping is the Vietnam of Computer Science" http://www.codinghorror.com/blog/2006/06/object-relational-m...