SQL is Agile

http://lucumr.pocoo.org/2012/12/29/sql-is-agile/

by johns • 14 years ago

265 160 14 years ago

lucumr.pocoo.org

vyrotek• 14 years ago

This matches my experience from the last few years as well. I was burned by denormalizing too early and not fully understanding the trade-offs I was committing to for the sake of not dealing with 'schemas' (which I actually have no problems with). And just like the OP, I have found far better success sticking with SQL early on for many of the same reasons mentioned. Later, as products and data schemas matured I found very specific uses for data stores like MongoDB. But, they are never the core data store.

dbla• 14 years ago

For me the process of designing my schema and planning my project are almost one in the same. I have a hard time thinking about how I'm going to build something without thinking about the schema.

freework• 14 years ago

Mongo is like the PHP of the database world. Many important features are dropped in the name of "getting started quickly".

What you gain in short term wins, are nullified by all the long term headaches you suffer.

styluss• 14 years ago

Could you give examples? I'm working at a company where Mongo is being used because.

jaequery2• 14 years ago

once you get into creating administrative/reports sections i think you'll quickly find out.

camus5• 14 years ago

what does PHP "drops" in the name of getting started quickly ? PHP is now a robust language with robusts frameworks like SF or ZF.

jeltz9• 14 years ago

Two examples: including loads of functionality into the default namespace instead of developing it as external libraries, and confusing the template language and the programming language which encourages XSS vulnerabilities in PHP written by beginners. When using PHP you normally want to use a different template language to avoid having to audit the code and hunt unescaped output.

tracker1• 14 years ago

I don't think I would quite agree with that.. I would say that mongo is very accessible in terms of getting started... also, you can index nested properties via dot notation, unless you are really deeply denormalized, collections of objects with collections, I haven't been aware of any issues there.

A few things that surprised me about mongo... first that if you do a sort, you either need an index on your sorting fields or your sort info needs to fit in 32mb. I'm using skip/limit for paging, and it really became an issue once I was running against the full datastore. Second, only one single index is used in a query, unike most sql databases that will collate results of mutiple indexes under the covers... this caused me to rethink my index strategy, as well as add both asc/desc indexes for sorting results.

For me it was a matter of getting away from an over normalized data set (30+ queries for a single vehicle classified ad display, 23-joins if you wanted to reduce 15 of those queries (my main VIEW created for migration)... denormalized data makes more sense for this use case. For others it may or may not make sense.

aidos• 14 years ago

Relational systems use multiple indexes for queries, though I'm not aware of an implementation that uses multiple indexes for a single table.

Remembering that a query in Mongo is basically a query on a single table it's acting in the same way a relational db does - ie, check statistics, choose best index, perform lookups.

xweb• 14 years ago

I've found that the primary value for me in NoSQL implementations is in allowing for customization at the data level for the end users without having to explicitly build structures for it. So if User A wants to add a tracking field to all her data, she can do so without my having to build an extensible data structure on the back end; it's already there, and it doesn't affect any other user records.

dotborg2• 14 years ago

I'm not sure about your point on schemas. When using relational database you must have your schema in two places: 1. database and 2. ORM - the second one is enough to avoid most of problems.

What's the point of having schema in database then?

pielud3• 14 years ago

1. Your ORM can derive it's schema from the database's.

2. I think the main point of the post was that you can run ad-hoc SQL queries no matter how much you've denormalized. You can't necessarily do that with a NoSQL database.

camus5• 14 years ago

Yet you are still using schemas in 2 places : not DRY , SQL just doesnt fit OOP design. Most of languages today have strong functional capabilities , that makes SQL obsolete , they have functions , event systems , RDBMS exist because people use to query those systems directly , does your users log into your database directly ? no they connect your database through middleware, that's where the job should be done.

thezilch2• 14 years ago

For one, your DB schema can define constraints or procedures that are triggered inline to the work being done. If building those into the application layer -- not including a middleware -- you are going to pay in network IO and probably wire protocols to guarantee constraints or execute procedures. A middleware could reduce some of the costs, but then you are again moving some logic outside of your local model.

wvenable5• 14 years ago

A fixed schema in the database means you only have one schema to deal with. But in schema-less DBMS you really have as many schemas as changes you've made. If you add a field to new records coming in, you now have 2 schemas. Every change you make is another schema.

Your ORM likely represents the last iteration of that schema and then you have code to handle all the past iterations.

So the point of having a schema in the database is avoiding that kind of hell.

jt21902• 14 years ago

  > in schema-less DBMS you really have as many schemas as 
  > changes you've made.

Unless you migrate data with each change, which is actually easier when you don't have a static schema. The choice to do "lazy migrations" is the unusual, not typical case, as it has all of the implications that you've mentioned. Bottom line: Data normalization doesn't suddenly go out the window just because it's MongoDB.

thezilch2• 14 years ago

How can you have a project without models and models without schema? And so you have to change a schema; is there usually much difficulty? More than supporting multiple schema across a single set?

Even if DBs were still bad at migrating a schema (eg. table locking horrors), the problem usually only presents with millions of rows, which one presumably doesn't have in the early stage of a project -- "start[ing] a project." Nonetheless, even with millions of rows, there have been solutions for all the major RDB since before MongoDB.

camus5• 14 years ago

data mining : you dont know how exactly how your data will look like , and how much datas you'll have to handle. And you are opened to new types of datas. data logging : you dont need schema databases for that. file upload : GridFS , ... User generated content : save semi structured documents ( like a configuration , a spreadsheet , ... ) that need to be parsed but dont need a "schema".

disgruntledphd2• 14 years ago

True, typically you don't know in advance of a data mining operation. Nonetheless, you have some invariants that you know you'll need and these can be modelled with a schema.

Additionally, when attempting to put a model into production, you'll normally know what it is you need, and this can be put into a schema for ease of interoperability with other requirements (CMS, booking etc).

hayksaakian• 14 years ago

I avoid SQL not for the notions, or concepts behind SQL Databases, but for the SQL itself; the LANGUAGE of it all (specifically the syntax).

Nowadays I work with things like ActiveRecord and Mongoid, which add an abstraction layer on top of the literal interface language. However, at the very beginning of my intro to databases (quite recent, last 6 months), the prospect of learning a new language just to talk to databases was off putting. The notion of documents and fields made more sense to me, as it was more comparable to a file system. SQL tables, rows, and columns were daunting and seemed like a relic of paper spreadsheets.

The ecosystem around SQL solutions if clearly far more developed though, however I don't think we should avoid non-SQL solutions for that reason.

Give the alternatives a chance to develop.

Don't look at MongoDB through SQL tinted lenses.

lucian1900• 14 years ago

Having to know yet another language is annoying, but mongo has one too (and one that's less consistent and powerful).

A good SQL library (like SQLAlchemy) lets you easily build queries without looking at SQL. The only case you might have to is debugging, which is the same time you'd be looking at mongo queries.

wheaties• 14 years ago

I still like GridFS for simple distributed file systems and Redis for N-length lists of string and/or binary content. That said I have to agree with the author whole heartedly.

FuzzyDunlop• 14 years ago

The thing I noticed about NoSQL is that, at least with Mongo, you just end up putting your schema in your app. You probably do it by saying a particular class has a bunch of fields or instance variables that you expect to be a certain type.

I wrote a portion of an app that relies on Redis, and despite the fact that it's really nice to work with, especially in Ruby (that practically mimics Redis' native API), it was incredibly difficult to remember the structure. What keys do I need? What type are they? Oh I'll have to do `KEYS *` and look I guess. Then I'll remember to document it (and thus create the schema).

Of course, the problem on my part was, in part, that I probably didn't need to use Redis. And when I needed to use Mongo, well, I wasn't storing things that look like documents. I think this is an easy mistake to make, but one you learn from quite easily once you've experienced it yourself.

sprobertson• 14 years ago

> at least with Mongo, you just end up putting your schema in your app

I do this all the time, but I think it's a good thing. When rapidly prototyping it's nice to be able to just write up a few classes to define the necessary data types -- which is exactly how most SQL ORMs work, but Mongo's big advantage is that those classes can be totally rearranged without having to do migrations or go in and mess with underlying table structures. When SQL is really necessary, those classes are usually easy to translate to use something like SQLAlchemy.

Skywing• 14 years ago

I recently switched from Mongo over to CouchDB. The reason that I favor Couch over Mongo is primarily because I felt like Mongo's querying capabilities suck when dealing with embedded documents and that Mongo's MapReduce is not their first-class focus. Second, though, was because with CouchDB, I'm forced to basically put all of my query logic on the database server, which keeps it out of my client code. This cleans up my client code a ton, and querying from views gives context to what the data that you will be getting in return is. I don't think I'll ever look back to Mongo as long as I can choose CouchDB.

marcosdumay3• 14 years ago

> The thing I noticed about NoSQL is that, at least with Mongo, you just end up putting your schema in your app.

And, in my point of view, that is the main advange of NoSQL. You can distribute all the schema related functionality while keeping the data centralized in a server. Of course, you'll only want to do that when the performance requirements are hight enough that the centralized schema becomes a botleneck...

What of course isn't a problem when you are prototyping and have no user yet. Or, in other words, the article is right on the mark.

jaequery2• 14 years ago

mongo is easy to start with but it sure makes your project more complicated and painful as time goes on. it was to the point, i actually stopped enjoying work.

the power of sql is just too great to ignore, had gone through too many times where i wish i was on sql.

i will probably use mongo for those times where i absolutely need to store a schema-less data (custom attributes), but will only do so in conjunction with a sql database.

there is also that reliability and maintenance factor of securing it, making it redundant, and correcting problems when problem arises. all of which takes time off development.

and just like OP, i probably won't ever start another project with mongo from ground up. there are just too much things to worry about and problems come out from all different angles when you least expect it. but in sql, everything just works.

camus5• 14 years ago

impedance mismatch , that's why SQL is uber non sense in OOP architecture, especially when you end up using slow and buggy ORMs...

jeltz9• 14 years ago

The mismatch is quite bad between OOP and MongoDB too. A document database like MongoDB does not represent well recursive data structures or having different objects of different classes where any of them can be the entry point.

MongoDB is good if your data is grouped into logical documents and is not an arbitrary object graph.

meaty13• 14 years ago

If an ORM is slow or buggy, you're using it wrong or its crap. Most things that claim to be ORMs are really not very good quality.

Try hibernate / nhibernate with ehcache cache backend. We find both to be an order of magnitude faster than hand tuned SQL and testable to boot.

ericfrenkiel• 14 years ago

There is something to be said about the ambivalence of using SQL or NoSQL for OLTP-esque applications (loosening the definition of a transaction, that is), but SQL is very useful when working with OLAP-esque applications, i.e. writing a SQL query and avoiding drafting a map-reduce job or building the logic into a custom script, and still using good stuff like secondary indexes, etc.

veesahni• 14 years ago

I've been using MongoDB to "start" with for quite a while (and used AppEngine before that). Instead of using the low level mongo libraries, I use a wrapper like Mongoid to provide a high level object mapping.

With MongoDB + Mongoid, the schema is in the code in the form of Mongoid models and data stored in MongoDB mimics the in-memory application structures. I've found that this makes it much easier to visualize data flow - although it does force one to think carefully about their schema up front.

tgandrews• 14 years ago

As long as you have the data then you can calculate it. It may be easier with SQL and a normalised form but that forces a lot of other constraints.

With a non-relational DB the idea is to attack it with map reduce and possibly in app calculations. This may not be ideal, but the benefits of easy placement and access of the documents outweigh the aggregation problems on most of the problems I have dealt with.

Neither solution is perfect you just need to make a prediction based on the projects requirements and make a choice.

d--b• 14 years ago

This post is not addressing the right question. Think of databases as storage space, like wardrobes. The sql database will allow to store your clothes in spare parts each with its own dedicated space, allowing you to quickly calculate the sum of all stripes on all your socks. That's great but on the other hand, if you want an outfit you have to put it together, starting by sewing the sleeves on the shirt. On the other the NoSQL wardrobe will have various boxes where you can put any cloth you want. Now it's up to you to tidy the whole thing. If you want you can put full outfits in the box so that you don't have to think much about what to wear. However to count the number of stripes on your socks, you'll have to go through the whole wardrobe. The point is: It's not the fault of the wardrobe if your room is a mess

programminggeek3• 14 years ago

I don't think that schema or schemaless should change your app structure. Your entities/models should contain your data structures. If you structure your application right, schema doesn't matter. In fact, your persistence mechanism shouldn't either. Mongo, Mysql, Redis, they're just different ways to store and retrieve your data.

The biggest reason to avoid schemas is to avoid schema migrations. On big enough datasets, they take a long time. That is not every app's problem, so it depends on your app. Also, not having a schema doesn't mean you completely avoid data migrations as a result of changing your schema in all cases.

wvenable5• 14 years ago

The problem with avoiding schema migrations is that you end up with those migrations in your code. If you add a new field or re-arrange your data structure, you have to add code to handle that old data. In many ways, it's simply easier (and less permanent) to do a migration.

I've done it both ways and I wouldn't go schema-less again. Right now I have decade old code in my models that handles old data structures that more than likely don't even exist anymore. With a schema I can just make the change and perform any data transformations in SQL and not be required to have any code to handle legacy data.

programminggeek3• 14 years ago

I agree that avoiding schema doesn't solve the problem. My point is that your schema exists in your code one way or another (explicitly or implicitly), and even without a database schema, you still need to either handle the schema differences in your code, or you need to migrate your data into the new schema in some way or another.

The best use of schema-less data storage I've heard of is Craigslist of MongoDB for their archives. Apparently migrations on their MySQL servers was taking way too long, and switching that system to Mongo made a lot of sense and apparently has worked out pretty well.

In the end it's about understanding the use of the right tool for the right job.

btilly8• 14 years ago

At a past job we had a really fun interview question. What we did was laid out a concrete problem, and asked the interviewee to lay out a schema. Since we know the problem well, for virtually any schema we'd be able to invent reasonable requirements that would force them to change their schema. At the end we'd verify a basic knowledge of SQL itself by asking them to answer a couple of basic questions.

(The actual problem we had them tackle was reports to use in making picks in a fantasy sports game. But it could be anything simple to explain, fairly straightforward to solve.)

The reason why the question worked so well is that this was, in fact, pretty much how we build CRUD applications. We'd decide the schema, and once the schema was known, the CRUD application was fairly obvious and straightforward. And a large part of the programming model would be immediately provided by your ORM once you had done so.

Q6T46nT668w6i3m• 14 years ago

I like it. Would you mind sharing a “concrete problem” and subsequent “reasonable requirements?"

btilly8• 14 years ago

Concrete problem, reporting system to help our boss make fantasy baseball (or other random sport) choices. Your responsibility will be the schema and reports, I'll be responsible for getting data into your schema.

Reasonable requirements that often require changes to an initial schema:

- What happens if I'm trying to update data but I only get partial data, how am I supposed to fix that? (A surprising fraction of people denormalize data too early.)

- Can a player play two different positions?

- Can a player transfer teams from one season to another?

- Can a player transfer teams in a season.

- Can a player change names? (If we threw this one at you, then you probably had a very good schema!)

And then reasonable questions to test their SQL would be things like:

- Who played in game X?

- How many home runs did person Foo have?

- How many home runs did team Bar have?

- Sort all players by their hitting average.

RowanH• 14 years ago

A long standing interview question I've used is "draw us an ERD for this domain" (orders/customer/products).

It's what at first is a seemingly simple question to start drilling into understanding of systems "so if I was going to do X, what would you change in your ERD?" "how about if scenario Z was going to happen, would you change anything?"

Strange how every now and then you get a "system architect" that can't answer that one.

meaty13• 14 years ago

That's usually because the architects don't give a shit about the storage model and recognise that the external interface/contract is far more important and that can be mapped to whatever schema supports the model. That's their domain. All they see is: "how can we ensure that this whole thing still works when someone does scenario X".

The schema is almost 100% irrelevant to us and we have nearly 2000 tables in our system. We have two hibernate implementations which put that all behind a testable interface: something SQL can't give you on it's own.

mattmanser• 14 years ago

There's no difference between this and designing the classes first.

Designing the classes first in an explicitly typed language actually works a lot better than designing a schema in a DB as you're not constrained by relational requirements and it's easier to change imo. Make a change to your classes and you can usually just right-click refactor, try and make a change to your SQL schema and it won't accept it at all until you unhook all the dependant tables.

Basically, I don't really get what point you're trying to make.

You never know your domain well enough to make a decent schema unless you've already written the program once before. Which is a pointless thought exercise where you get to point at flaws in someone else's nascent design. Like it sounds like you do in an interview, which is great for seeing their thought processes, but don't fool yourself that it's because of that design method.

In other words, the reason it worked so well is because you had already built it once, nothing at all to do with your choice of schema first.

In the end do what works for you, but schema first vs classes first is a trivial and pointless argument as both work fine.

codewright2• 14 years ago

Not all of us use a language that has classes or where it's considered good taste to use classes to structure everything.

Xurinos• 14 years ago

Having done this for a while, I can solidly say that the relationships and structure of data has been consistently better when designed as a set of schema tables than as a set of classes because they are especially constrained. The constraints forced people to actually think about the relationships between facets of their data, and the resulting classes were much cleaner and more maintainable. Should we ever decide to move aspects of the classes into a database, the transition requires little code refactoring; this comes up fairly often for us, since we are trying to give users more customization powers.

People should be doing that when they design classes, but the flexibility tends to work against them. It could just be a mindset that makes it work well, and this might be a more appropriate approach for larger projects than for smaller types.

btilly8• 14 years ago

If you think that you're not constrained by relational requirements, when your data is backed by a relational database, then you're living in a fantasy. The constraints exist, the only question is whether you recognize that fact.

When you're answering an interview question on a whiteboard, "right-click refactor" usually fails hard.

The whole point of the exercise is to test someone's design skills, and to find out what interactively designing with them would be like. We didn't care that they came up with a good design - we wanted to see the thought process and the interaction. Because in that organization, on that team, that was actually how we did design - laying out the database schema on the white board. Therefore we were testing design sensibility and a concrete skill that people needed to have.

lucian303• 14 years ago

It's always about the data, never about the code.

jeffdavis6• 14 years ago

"There's no difference between this and designing the classes first."

Databases are designed to be shared structures accessible from many applications, so it's not a 100% direct comparison. When focusing on a single process of a single application, of course that leads to some simplifications (no IPC, all datatypes match up perfectly, no concurrency problems).

"you're not constrained by relational requirements"

I don't see relational as more constrained or less constrained than OO. It's different. For instance, in OOP I often feel like it's forcing a hierarchical structure upon the design. Inheritance feels very constraining to me in comparison to the free joining of one table to another based on the values inside (not necessarily based on explicit connections in the design).

jzwinck2• 14 years ago

Since your coworker liked that problem domain I'm sure you thought it was fun. But what about candidates? Ever have one like the poster in this same thread who doesn't like sports? What about female candidates? Would you agree there are likely to be gender and ethnicity biases in an interview question that discusses American sports?

This of course assumes the actual job is not about sports.

hugh4life• 14 years ago

"What about female candidates? Would you agree there are likely to be gender and ethnicity biases in an interview question that discusses American sports?"

Don't you agree there are gender and ethnicity biases in posing these questions?

yummyfajitas2• 14 years ago

Programming is about learning enough about a problem domain to model it with code. My last job involved women's fashion, which I (and my unfashionable male coworkers) don't give a crap about. My current job involves delivering local news to middle aged female suburbanites, which I (and most of my brooklyn hipster colleagues) are similarly uninterested in.

My lack of interest in school closings in long island doesn't prevent me from making postgres reject articles without a title.

If a developer can't ask questions and figure out enough about football to model the data reasonably well, he/she is a solid NO HIRE.

(I'm also curious what you mean by "gender and ethnicity biases". Are hispanics/women incapable of understanding sports or something?)

btilly8• 14 years ago

For the record I don't like sports.

In the time we gave that question, being a fan of sports did not generally help on that question. The necessary domain information is small, the relational design skills tested are not.

In fact we had one candidate who said up front that he didn't know anything about sports. He wound up doing well on that question and being hired.

thwarted• 14 years ago

We'd decide the schema, and once the schema was known, the CRUD application was fairly obvious and straightforward.

"Show me your flowcharts [code], and conceal your tables [schema], and I shall continue to be mystified; show me your tables [schema] and I won't usually need your flowcharts [code]: they'll be obvious." ~ Fred Brooks

camus5• 14 years ago

SQL is not DRY, especially when people stuff their apps with bloated ORMs. SQL exists because people used to query RDBMS directly , now most of the time databases are simple data repos , it makes sense to put most of the logic in the application itself.

One should not have to deal with types in the app and types in the database, exceptions in the app and database errors , events in the app and triggers , functions in the app and stored procedures , or it means one doesnt trust it's own middleware.

SQL makes apps insecure, and hard to refactor, and is a non sense when doing OOP design ( here comes the bloated ORMs again which usually come up with their own query langage ... linq , dql , hql ... ).

Doesnt mean data modelling is bad , on the contrary that's the first thing to do , but data modelling doesnt need to fit the SQL mindset ( thinking in tables and columns ). Objects are parts of collections that can hold collections of other objects themself. Why should one try to fit that model into SQL ? it does not.

MongoDB may not be the best NoSQL solution out there neither , there are better products.

jeltz9• 14 years ago

One major advantage of using SQL databases is that they provides excellent guarantees for data integrity. This means I do not have to think much when refactoring my data. And I do not have to worry about production systems crashing due to old data being different from new.

When modifying the schema in NoSQL databases I always worry about if I mess up and leave old cruft which will cause my code to crash, or even worse if I lose data (data created between the last backup and the upgrade).

With proper SQL constraints there are many fewer ways where you can screw up when refactoring. And easy refactoring of data is key to agile development of systems which have been put into production.

jfb2• 14 years ago

I have never (in twenty years) seen a non-trivial database that is solely accessed by a single application. Given the ORM anti-pattern, many of these databases had no constraints ("we'll do it in the model!") or had only partially defined schema ("it's faster when we don't have to migrate!"), meaning that anyone who just wanted the data had to either load all the application garbage, or reverse engineer the schema.

If you start with your tables, and let the database engine do its job (of course, this is predicated on using a database and not, say, MySQL), querying data becomes a one-banana operation. Of course, there are downsides to this approach, but at scale, all quick and dirty hacks become nightmarish tarasques. DO FEWER HACKS.

outside1234• 14 years ago

There are no schema-less databases. There are databases where the schema is applied at write and there are applications that apply a schema at read.

PommeDeTerre4• 14 years ago

Very true. I always chuckle when some NoSQLer tells me how much time he's saved by not having to define and update a schema. Then he tells me how all he has to do is check whether fields exist or not in his code, and enforce constraints, and provide default values, and so forth. He has to do this in the Ruby code that he's writing, and his colleague has to do the same in the PHP code she's writing, and then there's the analysis team who has to write the very same code in their Python scripts...

meaty13• 14 years ago

Actually I think the motivation is that the schema exists in code rather than in the storage engine. That gives the amazing productivity boost of not having to load the schema into the database engine - something you appreciate when you hit a big 2000 table / 45000 stored proc sized mess. It's another step towards statelessness.

Basically schema in code scales development-wise better than schema in storage engine.

raverbashing2• 14 years ago

If you're having 3 different codes reading the same DB you're doing something wrong

"check whether fields exist or not in his code, and enforce constraints, and provide default values,"

Well, you need to do that regardless of the DB type. What can be a non existing value in Mongo may be a NULL in SQL, or a many-to-many relationship that's currently empty. So you will get the data in a format or another and your code still has to work it out.

And I chuckle when I see SQLer saying 'how easy it is to add a column to Postgresql on runtime' that's like saying a car is faster than a plane because you can't go to the grocery store by plane.

wpietri3• 14 years ago

I'm not a big fan of either of those approaches.

The cross-implementation schema constraints in SQL DBs are pretty weak. I can only express a small fraction of my application logic there unless I want some serious vendor lock-in.

And then regardless, once I'm referencing the same schema from multiple code bases, schema improvements are basically impossible.

If I need to get at the same data from multiple code bases, I think an API is a lot cleaner. And then I'd rather put the constraints in my API code. The tools are so much better, and programming languages are much more flexible.

lnanek2• 14 years ago

Mmm, there's so much less code when everything is in JSON though. You can often ditch half the classes in the system. Sometimes I just don't need a client side class, a server side class, a schema, etc. just to store a few fields. It violates Do Not Repeat Yourself, especially when client and server are running different technologies like NodeJS server vs. Objective-C client. It is so much simpler to have just JSON in node and a dict on the client than to have a set class JS file with schema in node and a class on the client, and have to change them all whenever the data format changes. Also violates You Ain't Gonna Need It, from the agile rule house. A lot of times you build all that boilerplate, maintain all that bolierplate, and you never end up needing it anyway. Or have to change it all before you would have needed it.

matthavener• 14 years ago

What's the difference between a server side class and a schema? And isn't the necessity of creating a class just a complexity required by some statically typed OO languages. Some data defined by a schema is still just data -- it can be used similarly to JSON.

contingencies2• 14 years ago

I spoke at Code Generation 2010 at Cambridge in the UK on my experiences implementing code generation in various parts of an existing shop's development process.

One of the low-hanging fruits in that regard is selecting existing and under-utilized models and making the most of them.

I found that SQL schemas were a great way to model a business domain even if you need to migrate to another database ... since you have so many tools (including free cross-platform visual ones, like MySQL Workbench) at your disposal, validation, decent type selection, options for more complex validations using triggers, everyone knows it (unlike UML), etc.

Another great 'model' that people are usually sitting on top of, also ripe for code generation, is the software dependency databases in many distributions.

Another is your RCS/VCS system.

Another is - and here comes the kicker - the filesystem, which with modern tools (inotify, etc.) can give an impressive amount of RCS/VCS style functionality (COW, event notifications, etc.) at kernel level.

What was that quote again? "Unix is simple, it just takes a genius to understand its simplicity". They weren't lying.

meaty13• 14 years ago

If you're using code generation, your tooling or architecture needs rethinking. There is 100% no reason to do it.

pdwetz2• 14 years ago

Sorry, to say there's 100% reason not to do anything is rather silly. By your own definition, what if your tooling lets you do codgen? Regardless, I've found code generation to be particularly useful in many scenarios and I would never dismiss it out of hand.

btilly8• 14 years ago

If you don't want to use code generation, go back to assembly language.

Or did you not know what a compiler does? (Yes, modern "interpreters" have a compilation step.)

Of course if you understand what a compiler does, the only question is whether you should be limited to what your official compiler is doing for you. (There are arguments pro and con, but it really isn't as simple as just saying no.)

jzwinck2• 14 years ago

How would you implement something like Google Protocol Buffers without code generation for C++ clients?

What about lex and yacc, or ANTLR? They all use code generation. Should we always write parsers by hand?

contingencies2• 14 years ago

The only correct response is that you are not 100% incorrect.

While there are undeniably benefits of not using code generation and simply writing smarter code, sometimes this is not appropriate.

Examples: (1) you want to keep some high productivity tools to yourself for financial reasons (eg. generating code to distribute to third parties) (2) you need to generate code with built-in documentation in various human languages, that integrates translations as they occur (3) your target system is fairly throwaway, you need to dumb down the abstraction level for some less experienced programmers, and it's easy to generate boilerplates (4) you are writing a model in a model-oriented language (ABNF, ASN.1, etc.), and generating the code in a completely different domain with different (ie. more complete) capabilities (5) you want to maintain retargetability of your code, ie. ease of switching to a totally new language (6) you want to target multiple languages, eg. generating protocol implementations for other developers to use (7) you want to target multiple platforms, eg. generating crappy apps for nth-mobile-platform-of-the-moment .. or some combination of the above.

mistercow2• 14 years ago

It seems to me that of all the advantages of NoSQL, schemalessness is probably the most minor and dubious. Most of the pain of dealing with SQL comes from the relational part, not from needing a schema.

The problem with SQL is that it's clever. To do things efficiently and reliably, you often must do very clever combinatorial operations on different tables, producing elaborate structures which are then winnowed down to a simple list of information. So right off the bat, you encourage programming logic that is difficult for future maintainers to wrap their heads around. Of course, most programmers aren't clever, so their queries end up being both difficult to understand and subtly wrong.

Then, of course, your data comes out as a list of rows. Well that's just great. If your needs involve a more complex data structure than that, well, you're going to need code to rebuild that outside the database.

loxs2• 14 years ago

Yes, and not even only this. The really big difference is the eventual consistency, used as a core concept in most horizontally scalable data stores. If you once design your application with heavy reliance on hard consistency, you will have a huge problem scaling it later on.

ucee0545• 14 years ago

As opposed to if you don't, in which case it will be wrong straight away

jeltz9• 14 years ago

Very few applications need to scale past that point. I know plenty of business where if they would scale past the point of being able to run on a single SQL database instance they would make billions and be larger than their theoretical target market. Not everyone is building the next Facebook (which coincidentally uses MySQL for a lot of things).

ucee0545• 14 years ago

Instead of turning your infrastructure to dumb crap, you instead have the option of firing your not clever programmers.

I mean, they need to be able to deal with database logic just to graduate from a good CS bachelor's program, so that's a pretty low bar.

mistercow2• 14 years ago

There's a difference between "clever" and "smart". Clever is rarely a good thing in programming. Code should be comprehensible, and clever code rarely is. The more proud you feel after writing a line of code, the more likely it is that you should rewrite it. The problem isn't that programmers aren't clever enough to write SQL queries. The problem is that programmers aren't smart enough to later read those SQL queries.

>I mean, they need to be able to deal with database logic just to graduate from a good CS bachelor's program, so that's a pretty low bar.

People manage to get through college without absorbing the basic requirements all the time, and being "able to deal" with database logic is not the same as being proficient with it. The problem with a system that demands cleverness is that maintaining clever code requires a high level of proficiency.

marcosdumay3• 14 years ago

There aren't enough competent programers to automatize the entire world. Of course, if I was hiring for an IT company, I'd look for the best... But most of the world will have to content themselves with poor programers.

Anyway, poor programers work their best (less worst) in strong typed environments. That includes a very restricted schema on the database. It's much better having them write queries that nobody could read than having them insert data that nobody could read.

joe_the_user• 14 years ago

Well, another thing that is not mentioned here is that SQL and the original relational model were effective designed from the start to be "agile", to allow ad-hoc queries and other "quick changes".

The original relation database model was, from the get-go, intend to decouple the database from large-scale programming. The DB administrator would make queries and evaluate their effect on the system without having to be a programmer.

cageface• 14 years ago

At one point you need to have an understanding of what you're dealing with: you need to know of what type a value is.

If we agree with this then why is it so controversial to assert that types are also useful at the language level? For me working in a dynamically typed language is just as awkward as working with a schema-free datastore. Writing code is hard. Why not let the compiler help you?

marcosdumay3• 14 years ago

Typed languages add a ton of boiterplate to what would be simple operations. That slows you down, more than most people notice.

Now, that said, explicity typing makes it easier to catch a lot of mistakes, and helps you keeping you data consistent. It's very usefull... But slows you down.

As a conclusion, you want to explicitly type anything that is important, but also wants to keep the explictly typed data to a minimum. (What makes optional typing, like in Haskel very interesting.) And, in nearly all the applications all the important data (and very little unimportant data) goes to the DB.

mjouni• 14 years ago

More than often, reading the schema is the lowest hanging fruit when you are trying to understand the domain knowledge of a startup. This becomes even more true when the codebase is not that readable. Now this is not an enough of a reason to shy away from NOSQL data stores, but should make you pause and consider what you will be losing when you invest in a schemaless DB.

jbverschoor• 14 years ago

The title is very correct. SQL is agile.

When can we PLEASE stop messing up names? Everybody who's talking about "NoSQL" should be thrown into a deep black pit.

SQL is a query LANGUAGE. It's excellent at what it does. To be honest, I like HQL, even though I'm a ruby guy since about 5 years.

The whole scalability, consistency, acid, schemaless discussions are actually not the point.

SQL is Agile

Related Stories