15 ms·
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 ca
by jgrahamc 12y ago
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(:percentage) }
.group(:locale)
.order(Sequel.desc(:percentage))
just makes me want to cry. Learn SQL rather than wrapping it.
- serve_yay 12y agoGosh, no kidding. (I sorta disagree about ORMs though -- if you're selecting by ID it's monkey work to write those queries, but anything complex, sure, use SQL)
- macavity23 12y agoRight. A good ORM should deal with all your common cases (if not, your schema probably needs improving), and it saves you writing a lot of boilerplate code. For the edge cases where you need complex joins and/or extra performance, don't try and bother with the ORM, just drop to SQL.
- silon3 12y agoAgreed. Also it should be generating queries based on foreign keys/indexes (including joins). Others should not be allowed in an interactive app anyway: developer should be warned/prevented using a "batch" query by accident.
- deleted 12y ago[deleted]
- jgrahamc 12y agoThere's quite a bit more I can do with Arel than I can do with raw SQL, especially if I'm already in Ruby. So... no thanks. :) Instead of making a statement like that... educate me. I'm very happy to learn about new technologies. What extra, interesting things can you do with Arel that aren't possible in SQL?
- barrkel 12y agoArel is just a syntax tree for SQL. It doesn't strictly give you any more power than SQL - I think your question is a little bit disingenuous, as you knew this already - but code manipulating syntax trees is better for query dynamism than code manipulating strings that don't have further typing.
- jgrahamc 12y agoI think your question is a little bit disingenuous, as you knew this already No, it was quite genuine. I am not deeply familiar with Arel.
- JonnieCache 12y agoSequel is the best ORM I've ever seen or used, but that is (probably IMO) not a good application of it. It gives you total choice over what level of abstraction you want, so this is a particularly egregious use of it, being as nothing about that query is dynamic :) You can use it purely to execute handwritten SQL queries loaded from files, or stored procedures, or any level of abstraction between raw SQL and the monstrosity you posted. The model layer is totally optional and is built cleanly on top of the query/relational layer, not inside of it. The thing is a fucking work of art to be honest. The design shows impeccable taste, ruby's more exotic features are deployed only when really needed, everything is so clear, especially if youre used to activerecord. BTW also the developer is super helpful and pops up everywhere to answer questions.
- jonahx 12y agojeremy is incredible. he is more helpful and available (and certainly more knowledgeable) than most paid support people for premium products. if you are learning sequel, stop by the irc channel and he'll probably answer your question.
- JonnieCache 12y agoseriously the man is a hero. I owe him about a brewerys worth of drinks. EDIT: I just went to look at the github: 1821 stars, zero issues. And yes, github is the projects official bug tracker. There are 662 closed issues. I must see if I can arrange for work to send him some money...
- netghost 12y agoOr a brewery!
- yawniek 12y agosequel + postgresql is pretty much a dream team. i really miss it now that i am on a python stack.
- Pxtl 12y agoI use Entity Framework as an ORM and I have to say I like it. As much as there have been a lot of false-starts related to trying to reinvent SQL, I think Entity Framework hits many of the right notes for me: 1) All my code lives in Visual Studio with compile-time type-checking. No maintaining stored procedures outside of my main codebase, no mucking about with strings. And because Entity Framework puts the Select after the From clause, I even get good intellisense on column-names. 2) I can quickly shift code into being in-memory queries from database queries. 3) While it's a little muddy mixing SQL and C#, there are places where C#/EF's syntax is so much more brief and clean since it's hyper-aware of relationships. For example, instead of writing 2 joins to get the country of a user, I can say User.Centre.Country. Of course, the Entity Framework is adding a layer of complexity to my work - you still have to know what's going on in SQL, and that mental overhead is a downside. But it pays off. SQL is a brilliant language, and that's why it's stood the test of time in spite of its advanced age. But seriously, it has a lot of bad flaws - it's often difficult to make reusable components in SQL like you can in other platforms. The underlying relational algebra is brilliant. That's why I like ORMs - they admit that SQL is ideologically beautiful, but the SQL language itself could use some loving.
- jgrahamc 12y agoYes. I pretty much agree with you and am happy for people to use ORMs (or raw SQL if that's their thing and they are into maintaining it). It's actually the underlying 'throw away all this old SQL database stuff' that is the biggest loss for people because they miss out on the power and optimization that's gone into those databases.
- masklinn 12y agoBut doesn't the Sequel snippet just write SQL as Ruby and get Ruby objects out? It's still pretty raw SQL, just not SQL-as-embedded-strings-guess-the-syntax-error. And surely it's easier to extract, compose or otherwise manipulate reified queries than by concatenating floating bits of strings.
- atburrow 12y ago
- jv22222 12y agoI agree, using ORMs and such hide the real SQL and make it much harder to optimize by using explains etc.
- SnacksOnAPlane 12y agoJust have Rails dump the query and stick "EXPLAIN" before it.
- ttty 12y agoI sincerely still prefer mongodb syntax because: - Fits well with a programming language; {a: data.x} is better than 'WHERE A="' + data.x + '"' (sanitize?) or similar which are harder to read. SQL queries are good for direct input, mongodb queries styles are better to be used with a programming language;
- aethertap 12y agoYour comment made me realize why I prefer to work in ORMs instead of raw SQL, even though I'm frequently frustrated by their limitations - it all comes down to composability. The mongodb syntax looks like it supports composing statements much more readily than SQL. It makes me wonder if there's been any serious work done making a query language that's fully as general as SQL, but is designed to be safely and easily composable so that you can build queries up from parts. Anyone know of such a thing? The mongodb approach of query-as-data-structure is as close as I've seen.
- masklinn 12y ago> The mongodb syntax looks like it supports composing statements much more readily than SQL. It makes me wonder if there's been any serious work done making a query language that's fully as general as SQL, but is designed to be safely and easily composable so that you can build queries up from parts. Haven't you noticed that it's exactly what the Sequel snippet does? It's also what SQLAlchemy's Expression Language does. It's a reification of SQL queries in the host language, so it can/should give pretty much all the tools of SQL.
- TylerE 12y agoIn some ways it does, but also remember it's a MUCH less rich query language. It's a lot easier to make a simple api mapping when you don't have such exotic things as "joins".
- aethertap 12y agoThe ORMs I've used have always tried to map tables to classes (or whatever similar language structure is available). That works well almost all the time, but I get tripped up when the queries get to be more complex or specialized to a particular use. I haven't used SQLAlchemy's expression language, but glancing over it now it looks like it might be just what I was looking for (at least for python), so thanks for the pointer.
- coriny 12y agoPostgreSQL is a very different beast to what it was 10 years ago. So for a while it didn't support use cases as well as various NoSQL/NewSQL databases. However, PostgreSQL has also been built by great bunch of developers who are happy to adapt and implement the new, instead of harping on about the past. So personally I adapt that advice to instead "use evidence to guide decisions".
- baldfat 12y agoThe real issue isn't "newtech koolaid" it is the definition of the problem. I find that people that don't understand the problem like MongoDB for the ability to be "flexible" in designing and modifying down the road. Though I like to argue you have flexibility in SQL also.
- warble 12y agoI agree, I've read a few 'screw Mongo, I'm going to SQL' type blogs, it really seems like they either didn't grasp how to architect Mongo correctly and tried to do things that don't work well with it, or they had a problem that was better solved by SQL in the first place. SQL does many things very well that noSQL stuff won't. Also vice-versa.
- jonaldomo 12y agoI couldn't agree more with you. I love SQL for when I have a concrete data model, when I don't I love NoSQL. I could use both if I really wanted to, but you need to know when to force a square peg into a circle. Mongo has a good how to on it here. http://blog.mongodb.org/post/72874267152/transitioning-from-relational-databases-to-mongodb http://blog.mongodb.org/post/72874267152/transitioning-from-...
- SnacksOnAPlane 12y agoI honestly don't think I've ever seen a valid use case for Mongo. If you're going to query your data, you have to know what fields you're looking for, right? So why not create a schema that has those fields?
- platz 12y agothe only valid use case is when your data is so unstructured so that you don't care what the fields in it are and you treat it like a black box. So maybe some kind of user-generated markup that you just shift in and out. those kinds of applications are not usually kinds of problems we have to solve.
- stephenboyd 12y ago
- theseoafs 12y agoIf the API surrounding the ORM is well-designed, using an SQL wrapper like this eliminates the possibility of SQL injection. There are arguments for not always doing things the same way.
- DennisP 12y agoUsing sprocs or parameterized queries also prevents SQL injection.
- api 12y agoAgreed, but it's important to understand what drove the adoption of NoSQL and schema-less stuff as well as the related trend of dynamic languages like Ruby and JavaScript. (1) SQL server software itself was clunky, hard to scale, complex to deploy in a clustered fashion, and generally "old" in a bad way. This made NoSQL seem like a breath of fresh air. (2) Startups! Ship now! MVP! Fail fast! The whole industry has been operating on this ship-fast-fail-fast way of doing things that prioritizes development speed and agility over correctness. That's because half the time what you are developing will be thrown away anyway -- it will fail, or you'll "pivot" and rip it all apart and almost start over. No point in static languages, SQL schemas, or other explicit design elements in that world. #2 is I think the more significant of the two for things like schema-less MongoDB or dynamic languages. Once you get product/market fit, you typically end up taking the crap you wrote with no types and schemas and re-doing it in a "real language" with a "real database." But as they say in Startupistan, that's a "good problem to have."
- pjmlp 12y agoI imagine "whole industry" == Silicon Valley? I am yet to do any project that doesn't use SQL servers for data storage.
- api 12y agoYeah, I mean "Greater Silicon Valley" by which I mean SV itself and its wider diaspora. Other names include Startupistan, Hackerdom, etc.
- dasil003 12y agoWhere this line of reason goes completely off the rails is in thinking that schema-less databases are a hedge for future uncertainty. Yes they let you churn out code slightly faster, but then your data becomes a ball of mud just as quickly as your code base, except the former is much much worse because when you pivot you still need your data even if you decide to chuck out all the code that goes with it. In fact, a traditional RDBMS is designed to allow for any kind of ad-hoc querying you desire with reasonable performance, and the ability to normalize/denormalize, index and materialize views in order to optimize unforeseen use cases. The excuse of poor scalability is just a rationalization that some kids who didn't understand SQL used to justify using something that has a shiny marketing page that they ignorantly found more viscerally appealing. The tradeoff was all wrong, because 99.9% of projects will never need to scale beyond a single DB server, and for those that do, the flexibility of a well-defined schema and ad-hoc queryability will give them an early advantage in what kind of pivots are reasonable—if they are lucky enough to have to scale then they can replace the RDBMS with the context of knowing what exactly their use case that they must scale is. And at that point you'll presumably have the resources to do it, which is much more effective than prematurely attempting to design a scalable infrastructure and discovering that you didn't have the first clue what the real bottlenecks or even the full use case would be.
- matwood 12y agoI started with SQL in the 90s and completely agree with you. People who do not know SQL tend to reinvent it poorly in a language not designed to work with sets of data. We have started using jOOQ and it strikes a great balance of raw sql/typed sql and result -> object mapping. I don't ever seen going back to a heavyweight ORM again. There is just too much magic and lost time spend figuring out performance issues for a minimal gain in ramp up for those who do not know SQL.
- Sivart13 12y agoI assure you that whoever wrote that code knows SQL. The builder syntax is convenient because it allows queries to be composable without ugly string-smashing. For instance, you could take the object built out of that "User.select..." and pass it around into other functions that append extra 'where' clauses, etc.
- joevandyk 12y agoThe advantage of this is that you can pass the relational dataset that's a result of this code around to other methods. Those methods can add additional filters, different ordering, etc. You can't do that with raw sql strings.
- pdeuchler 12y agosigh Not this argument again. I'm all for learning SQL (just like one should always learn foundational concepts), but for some reason there's this notion popping up that Real Programmers use Raw SQL, just like 20 years ago Real Programmers used Assembly. For the love of God, stop hating on basic software engineering principles like code-reuse and allow us to move up the abstraction hierarchy like we do in literally every other facet of software development. Here's a comment I made on the issue of ORMs vs Raw SQL a while back that goes into more detail: https://news.ycombinator.com/item?id=8134205 https://news.ycombinator.com/item?id=8134205 In the past year I've also grown very fond of type safety within my ORM coding as well, something that's hard to achieve at compile time with just raw SQL.
- chousuke 12y agoI may just be rehashing sibling arguments here, but to me that particular API looks very much what I think an SQL-wrapping library ought to be: a replacement for string concatenation and something that allows you to treat SQL queries as data. I don't know anything about Sequel, but that example still feels close enough to SQL. My experience with ORMs has been that I eventually end up regretting using one if I try to model my data as objects because with ORMs it's easy to code yourself into a corner where you end up wishing that your design supported the relational model instead. I do understand the desire for a good ORM, though. SQL is extremely powerful and a well-designed database is a joy to work with, but a sequence of tuples is often not the most convenient datastructure to process in most programming languages.
- fennecfoxen 12y agoIn my experience, it's great to start with ORMs, and they help you go really fast, but if the project is important then sooner or later you're going to find a case where you need to go around it and write custom SQL (probably for performance reasons). And that's fine, but it does mean that you should pick an ORM that plays nicely with that workflow (or otherwise design your ORM to be compatible with it). Back when I was working with Perl I found that you could do some pretty good stuff with RoseDB -- it could load objects from your custom queries and they'd still work like normal ORM objects (i.e. you can load related objects from them). I've missed that capability on several occasions since.
- collyw 12y agoThis sums up my experience as well. Great for all the tedious insert update type stuff, but more hassle than they are worth for complex queries.
- jjtheblunt 12y agoAgreed. And one might note that there's a really good reason Oracle has dominated for decades, and Larry Ellison (who read the SQL research paper, and formed a company to implement it nearing 40 years ago) is a billionaire.
- threeseed 12y agoOracle's success is due to their aggressive expansion into business applications. Their database products have been taking a hammering as enterprises moved to data lakes and horizontally scaled databases like Casssandra. Your argument would've made sense a few years ago but not now.
- meistro 12y agoYou should learn SQL and understand relational databases. But using an abstraction (ORM) to cover 80% of the use cases is much better than writing tons of boilerplate SQL. http://java.dzone.com/articles/martin-fowler-orm-hate http://java.dzone.com/articles/martin-fowler-orm-hate
- clebio 12y agoThank you. I searched to see if his article had been mentioned, and glad to find you did. The link he gives to Ted Neward's post is gone, but I found it in the Wayback Machine: https://web.archive.org/web/20141205230114/http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx https://web.archive.org/web/20141205230114/http://blogs.tedn... Seems like this thread has gone down a very old, well worn rabbit hole.
- evantahler 12y agoWait... does preferring to write SQL (and knowing how) make me old now? Dang.
- YorickPeterse 12y agoIt'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 ORMs go too far or even worse, not implement certain powerful features, Sequel (and similar tools I imagine) strikes a nice balance. The particular examples I gave on their own are not super useful, but we already have quite a few queries that are composed/re-used without having to concat strings. So tl;dr: it's about composability, not being "lazy" or "ignorant" to SQL.
- bigpeopleareold 12y agoI 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 the composibility department for me, while still being able to write SQL.
- deleted 12y ago[deleted]
- ad_hominem 12y agoLet'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/or 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 to just write SQL if the actual query statement needs to change based on the user input? Are you going to hardcode every possible permutation, or are you going to use a query builder? Or are there some advanced Postgres features that I'm not aware of that would allow all these combinations in some kind of prepared statement? edit: The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments. Your ad-hoc query builder implementations with SQL injection vulnerabilities are not relevant to what I'm asking.
- stcredzero 12y agoThe lack of a schema may sound interesting, and in some cases it can certainly have its benefits. However, for many the usage of a schemaless storage engine leads to the problem of implicit schemas. As another greying developer, one thing that was seen with ORMs were implicit schemas, often motivated as bureaucracy shortcuts. So, you need a dictionary of configuration data for your user, but the bureaucratic overhead of adding that to your schema is too much? Why, then take your fancy OO serialization technology and save that little dictionary as a binary blob and stick it into just one new column in the database. Problem solved...until years later when information in one of those blobs becomes tied (by a maintenance programmer who didn't entirely understand the system architecture) to what is effectively an implicit schema.
- pbowyer 12y agoAnd I thought I was the only one who'd encountered that...
- igorgue 12y agoThe fact that you don't know what an ORM is is what makes me cry.
- jakejake 12y agoI think the problem is that NoSQL has been the cool new thing, which causes people to use it without really understanding why it is better/worse than other solutions. Tools like MongoDB can do things that are extremely difficult or impossible with MySQL/PostgreSQL and they are a great choice for those situations. Using it simply out of laziness or misunderstanding, though, is probably going to create problems later.
- scott_s 12y agoI agree with the commenters who argue for the value in a library that wraps strings that happen to be SQL statements, but none that I saw touched on this point: I think most users of such a library will have to know SQL. These libraries seem to have a one-to-one matching between concepts in them, and concepts in SQL. Hence, the point is not protecting developers from learning SQL (because you can't, for these needs), but in finding the least painful way of integrating SQL into the application logic.
- drzaiusapelord 12y ago>As a greying developer The problem with being a young person who wants to make his or her mark on the world is that if the people before you did an excellent job, you can only make things worse. In fact, I think this is one of our larger problems not only in technology but in society (see the recent move to extremism in many aspects of our political and religious life the people who grew up under more moderate times are pushing for). Some things should be boring and stable. Build on top of those things. Redoing the foundation every six months is just sub-optimal. Accept that you may not make that big mark in the world, or if you do, it'll be in ways you completely did not expect, just like everyone else who made their mark.
- JustSomeNobody 12y agoI agree with you. Also, we should be developing products that last. And be proud of that. Why does everything always have to be new?
- threeseed 12y agoYou 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.
- Silhouette 12y agoTechnology changes. Yes. It improves. Sometimes. It gets faster, easier and more responsive to business requirements. Occasionally. There are a lot of ideas in technology that are widely accepted, particularly by younger, less experienced generations, as being The Best Way to do things. However, if challenged, most of those people don't really know why they believe that. They have never personally seen any hard evidence that The Best Way is actually better than known alternatives. They have never spent significant time trying those alternatives for themselves to gain first-hand experience of the pros and cons. In fact, their belief is usually nothing more than a matter of faith, a blind trust in the dogma of the day and the rhetoric they find on-line or hear from their peers or managers. See also: OOP, design patterns, UML and round-trip development processes, frameworks, ORMs, XML, dynamically typed languages, more frameworks, TDD, NoSQL databases, and so on, many of which were either invented or popularised as a way to work around the artificial problems created by one or more of the preceding items. Of course I'm being a little facetious here. But if you think I'm entirely kidding, just spend a few minutes with Google and see how much advocacy there has been for each of those ideas in their day and how much actual data there has ever been to justify that advocacy. And really I'm being kind here, because you talked about things that are supposedly "faster, easier and more responsive to business requirements" and I didn't even mention words like "agile" or "craftsmanship" in my list. If you don't embrace change in the IT industry then get out. Because you simply won't survive. I embrace using the right tool for the job. Whether it's new and shiny or old but tried-and-tested doesn't really matter, though if I had to pick one or the other for some bizarre reason, I'd pick tried-and-tested eight days a week. There have been only a few truly significant advances in the past decade or two, but very many technologies that were initially much-hyped but then failed to stand the test of time for exactly the reasons that critics outside the hype bubble had always predicted.
- izietto 12y agoThis is a `negate` method in Arel (by Rails): class User < ActiveRecord::Base def self.negate(scope) where_values = scope.where_values scope.except(:where).where where_values.reduce(:and).not end def self.suspended where.not(confirmed: true).where(arel_table[:created_at].lt 2.weeks.ago) end end unsuspended_users = User.negate User.suspended Do this in SQL. Composability is the real boost, and you have composabiliy when you don't have to build a string in order to interact with the db.
- bbcbasic 12y agoIn a compiled language like C#, "wrapping" SQL is a good idea, at the very least to catch bugs at compile time rather than runtime. Also makes it easier to switch from one DB provider to another if you need to. You are less likely to suffer from SQL injection if you use at least some kind of wrapper, although a minimal wrapper that takes a SQL with placeholders and arguments would do for that. My preferred way to wrap is to use EF with a dash of Linq, which is excellent and yields great readability and flexibility. There is no way I am going to be willingly writing SQL statements in production code (unless I am forced to do so by some legacy problem). Worse case if the SQL is so complex that it is too much for Linq, then write a stored procedure. Back in 2002 I had to write hand crafted SQL before mainstream ORMs and at the time I thought it sucked bad! I was looking for ways to generate the code from schema but didn't have time to go and back-fix all the legacy code ad-hoc SQL statments!
- rjbwork 12y agoI personally prefer SQL + Dapper instead of ORM stuff if it is Linq-level SQL, and sprocs w/ Dapper otherwise.
- CWuestefeld 12y agoYou are less likely to suffer from SQL injection if you use at least some kind of wrapper Pretty much all of our coding guidelines on my team are just guidelines. The one absolute law is that all data going to the DB must be paramaterized, nothing goes in as string substitutions. But virtually all of our DB access (like, at least 99%) is by stored proc anyway. We're just barely able to keep up with performance requirements by tuning things just right, and in my experience, EF isn't able to generate queries that are as efficient as we can by hand with some fiddling. I know that sounds like "I write in assembler, 'cause no compiler is as good as me", but that is what the reality seems like.
- collyw 12y agoOut of interest, are there more types of SQL injection, or are they all a case of inserting a semicolon, and running a second query afterwards to get the information you want? Surely that is something that could be disabled at the driver level for security? Make sure it only send one statement at a time. Correct me if I have missed something.
- jgreen10 12y agoSQL databases had their imminent death coming. They were stuck. However, PostgreSQL is under more active development than most other databases, SQL or NoSQL. It is new and cool technology. In the end, whether you speak SQL or use NoSQL construct matters as much as whether you write a program in C or Pascal. You can have endless debates about form and function and you might want to see and identify with a winner, but in the end whether the tools and the community surrounding a technology fits people's needs is the only thing that really matters.
- JustGotHere 12y agoWhat 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.
- tom9729 12y agoYou could use Teiid (https://github.com/teiid/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.
- towelguy 12y agoVery interesting, thanks!
- Animats 12y agoRight. 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?
- deleted 12y ago[deleted]
- billrobertson42 12y agoRelevant: https://twitter.com/stuartsierra/status/571386786238369796 https://twitter.com/stuartsierra/status/571386786238369796
- dchest 12y agoStuart Sierra (@stuartsierra): "SQL is already a DSL"
- mtanski 12y agoI 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" ]]]]] Is it more verbose, yes. But much easier to compose, parse and machine transform by software (code is data). Also, makes you think in terms of relational operations/expressions versus SQL the language.
- jrapdx3 12y agoActually, some work on this has been done for CHICKEN Scheme in extensions "ssql" and "ssql-postgresql"[0]. I'm not too familiar with these particular eggs, but the flavor might be conveyed by this example: (ssql->sql #f '(select (columns (col actors name) (count (col roles id))) (from roles actors) (where (= (col roles actor_id) (col actors id))) (group (col actors name)))) translates to "SELECT actors.name, COUNT(roles.id) FROM roles, actors WHERE (roles.actor_id = actors.id) GROUP BY actors.name" In the above sexpr, the SQL query is expressed as a Scheme/Lisp hierarchical list, which I think is a quite elegant notation. I'm sure other Scheme implementations provide variations on this theme of SQL generation. Using some macro magic, there would certainly be a lot of ways to organize it and probably it's not even all that hard to do. [0] documentation at: http://wiki.call-cc.org/eggref/4/ssql http://wiki.call-cc.org/eggref/4/ssql
- deleted 12y ago[deleted]
- deleted 12y ago[deleted]
- spydum 12y agoi think there is a line to be drawn.. If the ORM is saving you no effort, I fully agree. However, I suspect most ORMs in the short term boost early productivity quite a bit. It's like writing an app from scratch in assembly, versus C.. The compiler will do quite a bit of the dirty work for you, and if you look at it in a disassembler no doubt you could find tons of improvements to be made (errmmm depending on the compiler I suppose). Doesn't mean either one is the wrong way to go about it. Now if all you do is stuff ASM code in your C app, yeah.. Why bother?
- collyw 12y agoWhenever I see someone shoehorning something into MongoDB or something else I ask the reasoning for not using a relational database. Its always something along the lines of "We don't do that now" or "schemaless" (which as the article points out means more work for the dev) something similar, with the "fast" added somewhere into the description. The crazy thing is most of them are working on a single node, so no horizontal scaling. And they seem to think non-relational is the new way to do things.