

I hate queries - swix

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.<p>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?<p>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.<p>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!<p>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?<p>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...<p>Gaah the frustration,
Please don't hate, ps english is not my mother tongue so forgive me if you can't understand it ;-)
======
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.

------
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.

~~~
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?

~~~
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.

------
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.

------
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.

------
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.

------
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>

~~~
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.

------
michael_dorfman
Are you sure you're not looking for an ORM?

~~~
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.

------
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...](http://en.wikipedia.org/wiki/Computer-
aided_software_engineering))?

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.

------
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.

------
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...](http://www.codinghorror.com/blog/2006/06/object-relational-
mapping-is-the-vietnam-of-computer-science.html)

------
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.

------
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.

------
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.

------
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.

------
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.

------
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.

------
andybak
What language are you using?

~~~
swix
Depends, a lot of python/php and javascript/html5 stuff for frontend. Web
stuff basically.

------
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.

------
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.

------
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).

------
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.

------
wslh
LINQ is great.

