19 ms·

SQL is 43 years old – Here’s why we still use it today

• davnicwil 9y ago
  Just for fun: does anyone have any stories about initially using a SQL database for a project, later hitting problems, then switching to (or augmenting with) a NoSQL solution that solved those problems?
  • fs111 9y ago
    I think most people in that group start out with a normalized schema in a SQL db and end up in a highly denormalized or even key/value style setup in said DB. Technically it is still a SQL database, but not used in that way.
    • jerven 9y ago
      public UniProt.org went from SQL normalized -> SQL primary key/blob to proper K/V (BDBje) to custom K/V in the space of ten years. However, data production is still a mix of batch and sql systems.
  • sergiosgc 9y ago
    I've reached a point where my default design is relational database augmented with memcache/redis. The relational DB is the authoritative source of truth, redis absorbs all (most) reads and memcache is, well a distributed cache. Writes go to both redis and relational. Redis data can always be rebuilt, and is regularly rebuilt, from relational data. At project onset, usually only redis or memcache are in use. It's a model that has served me well, apart from bugs that cause divergence between redis and relational. These are usually the class of bugs that would corrupt data were I relying on redis alone, so I just think of them as a lesser evil. There's an obvious bottleneck on writes to the relational DB but I've so far managed to keep from hitting that ceiling.
    • scarface74 9y ago
      I've reached a point where my default design is relational database augmented with memcache/redis. The relational DB is the authoritative source of truth, redis absorbs all (most) reads and memcache is, well a distributed cache. Writes go to both redis and relational. Redis data can always be rebuilt, and is regularly rebuilt, from relational data. I'm really becoming a big fan of CQRS/event sourcing where we have two read models - one NoSql for live data and one relational for BI/Reporting. Events are stored separately in JSON because that's how they come in anyway through the API.
      • remotehack 9y ago
        Wow. Let the maintenance begin.
        • scarface74 9y ago
          CQRS is not rocket science. In any case, your schema for your reporting database is usually different from your online database.
  • johnchristopher 9y ago
    I suppose they'd look first into MySQL's new JSON support. (Or other SQL database that have some kind of JSON support)
    • Klathmon 9y ago
      PostgreSQL has fantastic JSON support and i've used it a few times. Not only is it fairly fast, but it lets us tie JSON blobs that really don't need schema enforcement to be still tagged with the rest of the data and laid out relationally to still allow complex queries.
  • lr4444lr 9y ago
    Yeah, I use MongoDB whenever my tables get too big. It's web scale! </s>
  • pinouchon 9y ago
    Most of the stories I have heard are from people choosing a noSQL store for the wrong reasons. They end up going back to a relational DB because now they see the value of ACID. The typical case is Mongo => Postgres/Redshift.
  • Kiro 9y ago
    Kind of. I started building my game using SQL but realized everything became so much easier if I didn't have to follow a schema. For example I like to add arbitrary properties to the player objects all the time. With MongoDB it's a no-brainer. I just add the property and save.
    • elmigranto 9y ago
      > no schema Do you read them back? Does your code expect property "X" to be on a "Player" object? Do you put a check everywhere for when it doesn't? Do you have default value for objects that were created before new property was added? All that work could've been done by your database (which is probably has some kind of JSON implementation anyways), but somehow it's a "no-brainer" to implement data consistency at app-level. Okay.
      • scarface74 9y ago
        Does your code expect property "X" to be on a "Player" object? Do you put a check everywhere for when it doesn't? Do you have default value for objects that were created before new property was added Why would you put checks everywhere a properly written code base would have one module/service that handles the player object and every consumer would use that one module.
        • aidos 9y ago
          There have to be some checks, somewhere. Even though it's limited to one module, you can get temporal inconsistencies. e.g. you add a new field and an application level default, then later that default is changed. Now there's no notion of which default the docs that never had the property should be using. Sure, you just need to patch the old docs to set the property where it's missing. But the GPs point was that by not keeping a consistent schema you end up moving the definition of the data into the application, and you need to be very aware of the trade-off. Personally I'm really happy using postgres and dropping into jsonb when it's the correct model to use. It's the best of both worlds as far as I'm concerned. I've built and worked on systems using nosql solutions that have been migrated back to sql and the data inconsistencies we found have made me very wary of having a primary datastore that doesn't enforce strict constraints. Though, everybody's use case is different.
  • FLUX-YOU 9y ago
    Logging
  • danso 9y ago
    I had assumed Facebook started off with MySQL and now is NoSQL but that was just ignorant of me: Zuckerberg may have had to rely on a typical LAMP stack but FB's newer user-facing features still use SQL. For example, the Timeline feature released in 2012 was built on MySQL/InnoDB: https://www.facebook.com/note.php?note_id=10150468255628920 https://www.facebook.com/note.php?note_id=10150468255628920 And last year Facebook released MyRocks, which is a space/write optimized replacement for InnoDB, and is being used for their "user database tier" https://code.facebook.com/posts/190251048047090/myrocks-a-space-and-write-optimized-mysql-database/ https://code.facebook.com/posts/190251048047090/myrocks-a-sp... I guess the things I've read about FB using NoSQL [1] is for other parts of their infrastructure, particularly Messages. It sounds like they had considered using MySQL, even though Cassandra was built for the Inbox feature. They ended up building a new system for Messages [2]. Now that I've spent a little time in that rabbit hole, it looks like one answer to your question is Facebook's Inbox Search, which used MySQL originally to store inbox data (7TB for over 100M users, which seems laughably small with respect to Facebook's scale today): http://docs.datastax.com/en/articles/cassandra/cassandrathenandnow.html http://docs.datastax.com/en/articles/cassandra/cassandrathen... > Before launching the Inbox Search application we had to index 7TB of inbox data for over 100M users, then stored in our MySQL[1] infrastructure, and load it into the Cassandra system. The whole process involved running Map/Reduce[7] jobs against the MySQL data files, indexing them and then storing the reverse-index in Cassandra. The M/R process actually behaves as the client of Cassandra. We exposed some background channels for the M/R process to aggregate the reverse index per user and send over the serialized data over to the Cassandra instance, to avoid the serialization/deserialization overhead. This way the Cassandra instance is only bottlenecked by network bandwidth. The acknowledgments section makes it more clear that the MySQL data was indeed migrated over to Cassandra: > Cassandra system has benefited greatly from feedback from many individuals within Facebook. In addition we thank Karthik Ranganathan who indexed all the existing data in MySQL and moved it into Cassandra for our first production deployment. [1] https://news.ycombinator.com/item?id=7891316 https://news.ycombinator.com/item?id=7891316 [2] https://www.facebook.com/notes/facebook-engineering/the-underlying-technology-of-messages/454991608919/ https://www.facebook.com/notes/facebook-engineering/the-unde...
  • 33degrees 9y ago
    If you consider ElasticSearch a NoSQL solution, then yes, many stories. A properly normalized database can be very slow to search, and throwing ElasticSearch in front of it is a very common solution.
  • zzzcpan 9y ago
    Switching a project from SQL to NoSQL is usually too expensive. Traditional relational databases lack a lot of necessary constraints to even have architectures necessary to make the switch to a distributed database, it can only happen with a complete redesign of everything. I think it's more common for those who had problems with SQL databases to drop them for their next projects and move on. For example, I consider a highly-available multi-site distributed database as a minimum viable database and haven't used an RDBMS in like a decade.
  • kalleboo 9y ago
    Does using memcache for caching count as "augumenting with a NoSQL solution"? Because that's pretty popular...
  • prashnts 9y ago
    We used to use Postgres for almost everything, including full-text search. Later when we needed a bit more control over search, we started using Elasticsearch. The way we do it now is to perform joins and get a nice serialized object with all the possible search/filter/sort fields, and put it in ES. All the data still remains in postgres, though.
• plet 9y ago
  SQL is really good at projecting & selecting simple data. My rule of thumb has been to use it first for any per projects and but as soon I need more than one table, think deeper about the data and switch to NoSQL if I need to represent complex data structures or have document storage needs. Its still amazing how far you can go with a single table and few tweaks to a postgres instance.
  • collyw 9y ago
    Sounds like you need to learn how to design a database. Seriously, do you find more than 1 table complex?
    • plet 9y ago
      That's odd. I don't recall saying more than 1 table is complex. For me, it's just a good time to think more about what kind of database to use, esp for pet projects.
      • collyw 9y ago
        The wording of your comment makes it easy to interpret it that way.
  • noja 9y ago
    As soon as you have more than one table you switch to NoSQL? Did you meant to write it that way round?
    • plet 9y ago
      > As soon as you have more than one table you switch to NoSQL? Nope. Just start thinking a bit more if I need to continue using RDMS or switch to NoSQL now. I can when the time is right for the project. It varies for projects. While learning phoenix (elixir) I stuck with postgres because the tutorials were easier to follow. While creating a fancy blog engine I switched to Mongo
  • alunchbox 9y ago
• menzoic 9y ago
  Is it fair to compare the age of SQL to the age of JavaScript or that SQL has survive rapid change? SQL is a class of languages, while Javascipt is a specific language. The concepts behind JavaScript are older than SQL, and the modern SQL languages we use today are younger than 43yrs.
  • Twisell 9y ago
    But so does JavaScript it's not like it hasn't changed...
  • majewsky 9y ago
    > the modern SQL languages we use today are younger than 43yrs. 99% of people use a miniscule subset of SQL, most of which easily dates back decades.
• deleted 9y ago
  [deleted]
• _pmf_ 9y ago
  I'd really like for something like K/Kx to pick up. For server applications, the dichotomy between DB and application seems so artificial for a lot of applications. Think Erlang + Mnesia, but with a fully relational model backed by language primitives. I think LINQ with F#'s type providers would probably be what I have in mind (which works like JOOQ with a tighter integration).
• danso 9y ago
  I've already posted on HN how I use SQL in my public affairs data journalism class [0]. To me, there is no better, in terms of accessibility and return on investment gateway language to the power of computation and programming than SQL, with the exception of spreadsheets and formulas. Even if you don't go further into programming, SQL provides the best way for describing what we always need to do with data for journalistic purposes -- joining records, filtering, sorting, and aggregating. Ironically, I learned SQL late in my programming career, and initially thought its declarative paradigm to be mysterious and inferior to procedural languages. In fact, I don't know how to do anything in SQL beyond declarative SELECT queries (and a handful of database create/update admin queries). Turns out this is just powerful enough for me for most app dev work (Rails, Django), and the simplicity is a boon for non-programmers. ProPublica just published a bunch of data-related jobs and positions. The phrase "Proficiency in SQL is a must" makes an appearance: https://www.propublica.org/atpropublica/item/propublica-is-hiring-a-data-fellow-2017 https://www.propublica.org/atpropublica/item/propublica-is-h... [0] https://news.ycombinator.com/item?id=8505000 https://news.ycombinator.com/item?id=8505000 https://news.ycombinator.com/item?id=10585009 https://news.ycombinator.com/item?id=10585009
  • wodenokoto 9y ago
    I've recently started learning Pandas (dataframe library for python) and i find most queries cumbersome compared to the absolute minimal SQL I know
    • lowmagnet 9y ago
      Pandas is mostly columnar in nature. If you're heavy into Python idioms it's fairly easy to grok, imo, but there is a subtle learning curve about what is in and out of kernel. Since in-kernel operations are about 50-100x faster, it's obvious when you did the wrong thing, but it doesn't show until data sets are huge. I tried doing something regarding browser hits from Akamai data in Pandas and 3 different SQL databases (mysql, postgres, sqlite) and nothing came close to pandas for holding 150m hits (one day's worth across our properties) in memory as well as Pandas. Especially with Dask Dataframes mixed in. No competition for the effort involved.
    • 9y ago
• dghf 9y ago
  > SQL and relational database management systems or RDBMS were invented simultaneously by Edgar F. Codd in the early 1970s. Codd didn't invent SQL. Donald Chamberlin and Raymond Boyce did. > SQL is originally based on relational algebra and tuple relational calculus Maybe "originally based on", but not "an implementation of". For example, it is perfectly possible for an SQL query to return duplicate rows, which isn't possible under relational algebra/calculus, a relation being by definition a set of rows (or, more precisely, of tuples).
• ransom1538 9y ago
  SQL has the great side effect of creating structures that future employees can understand. Its a set of tables with relationships. Given these you can quickly inspect the structures independent of the programming language dejour. With a few commands a new employee can understand: business logic, hr, billing, reporting, and other major backbone principals in a matter of hours. On the other hand, I have been at companies that jumped on the noSQL / ORM / wrap it until you can't wrap it / hide sql (rails). When new employees show up... well... its a bunch of semi/no structured stuff spread across thousands of lines of specific logic.
  • forgotpwtomain 9y ago
    Rails tried to hide SQL so much that one of the consequences was not supporting proper table level constraints (FK) till AR ~4.2 or so. Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations.
    • jgeraert 9y ago
      Seeing this as well in proprietary software sold by one of the biggest erp software vendors... And it's still not fixed and likely won't be fixed anytime soon.
      • mannykannot 9y ago
        The relational model provides a pretty general and unified way to represent information, together with a cohesive and powerful set of primitives for working with it, yet many systems architects insist on hiding it behind an ad-hoc interface that looks like a throwback to pre-relational days.
      • moron4hire 9y ago
        There is a perception amongst lower programmers that FKs are only a thing you enable in production, that they are an impediment to rapid iteration. I mean, it's completely wrong, I know that. But try being the new guy telling a team of 10 people that. There is also a perverse corruption of "if it ain't broke, don't fix it" that goes on. If you can spend 100 hours manually validating every relationship in your application code, that's 100 hours you can put into your estimate and you know you can complete. If you only have a cursory understanding of SQL, then "learn more about FKs and implement them across the DB" seems like a big, unknowable blob of time that is impossible to estimate. It doesn't matter that it might only be 5 hours of work, at least we can be certain about 100 hours and bill the client for it. Finally, there are a small set of things that are fundamentally wrong about all modern RDBMS implementations. For example: it's nonsensical to have a foreign key that isn't indexed. You always want an index on foreign keys, there is never a scenario where you don't want them. But while primary keys are indexed by default, foreign keys are not. And those sorts of things give the anti-RDBMS crowd enough of a foot-hold to argue for continued ignorance.
• konradb 9y ago
  Can anyone shed light on why there has been a phenomenon of people finding SQL 'too complex' and moving to noSQL? (Not sure if that's entirely fair but, from the outside, it is what it looks like). Is it hype driven? Are courses at university not tending to cover SQL that much?
  • erikb 9y ago
    Actually it was an attempt to get better performance when the sizes of data increased drastically. Then of course it became a trend and developed some ideas by itself that are unrelated to where it came from.
  • charles-salvia 9y ago
    The main reason is the object-relational impedance mismatch[1]. Basically, programmers like working with objects that have data fields. This is because most modern, widely-used programming languages treat objects/aggregates with data fields as a first class concept. But SQL isn't designed around objects with fields, it's designed around tables, rows, and result sets from queries. Therefore, working with SQL in most modern programming languages generally requires layers of annoying result-set->object or object->row plumbing/conversion code. (Not to mention the vagaries of type conversions.) Of course, these days, this problem can be substantially mitigated to a certain extent by clever ORMs, but an ORM is generally a leaky abstraction at best. Obviously, whether or not any of this bothers you will depend on your use cases and a lot of other factors. [1] https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch https://en.wikipedia.org/wiki/Object-relational_impedance_mi...
    • MarHoff 9y ago
      But SQL isn't designed around objects with fields, it's designed around tables and rows. I think a more correct analogy would be that table are like classes, columns are the properties, and rows are instances. And so defining foreign keys is like setting a pointer to a parent instance. There is not direct analogy for methods, but you can use function/trigger to do the same job. PostgreSQL is actually an object-oriented RDMBS, it's not because you are meant to manipulate these objects through SQL that they are less powerful. And SQL is actually Turing Complete with PostgreSQL. It's clearly not convenient for general programming, but as soon as data manipulation is involved you benefit from a lot of built-in optimization.
• cygned 9y ago
  In my experience, (O)RDBMS and SQL are very good solutions for most of the business cases. I know a lot of projects that jumped on the "NoSQL for everything" train and eventually migrated partly to a RDMBS. I often don't understand why people try to avoid SQL by any costs instead of just learning and applying it properly. I don't understand those "we do SQL for everything" teams either. RDBMS in conjuction with NoSQL solutions can be a very powerful combination. We do a lot of Postgres + Redis + CouchDB in my projects.
• throwit2mewillU 9y ago
  It works.
  • pc86 9y ago
    "It works" is a pretty poor reason to continue using something. Horses and buggies work just fine. Automobiles are better. I think it's important to look at why SQL is still the best tool for the job 43 years later, especially in the current climate of going to production with 6-week old JS frameworks.
    • throwit2mewillU 9y ago
      It works my young Padawan.
• Skylled 9y ago
  I think it's funny when the author compares SQL being most loved with least dreaded. They're the same thing. The percentages all add up to 100.
• baldfat 9y ago
  SQL is the second most underused tool we have with dealing with data. AWK being the first. SQL is great because the logic works with dealing with data and forces you to make good decisions earlier. Everyone should spend a day learning SQL if for no other reason they the ability to think logically about data.
  • tkyjonathan 9y ago
    Amen.
  • majewsky 9y ago
    awk is really nice. I'm starting to use it more and more in places where I previously used a long pipe of grep, cut and sed.
    • GuB-42 9y ago
      I use perl when grep/cut/sed show their limits. I never really got into awk. I suppose we all have our favorite tools.
      • majewsky 9y ago
        I'm a heavy Perl user, too, but some stuff that is very succinct in awk is a bit more awkward in Perl. Most prominently when you're using $1, $2, etc. which are just there in awk but need a split/\s+/ in Perl.
        • skinniks 9y ago
          -a is your friend.
• cyberferret 9y ago
  Been programming for 30+ years, and 99% of my projects use SQL databases. I've tried and dropped NoSQL many times. I still wake up in a cold sweat thinking about the earliest version of Firebase where I had a project that tried to join three tables together to get some meaningful data. I still remember the response of a Firebase team member to my forum question about it - "These days, storage and computing power is cheap - just duplicate the child table as nodes in the main table and do it all that way for every parent/child relationship you have. Don't worry that you have to duplicate the same set of data for every parent that is related to the same child...That's how NoSQL works..." <shudder> Even though I use ORMs in my project these days, every time I have to test a complex query, I write it in raw SQL first and check it before trying to make the ORM duplicate the same query. Granted, NoSQL has its place and its advantages, but for me, when it comes to "money code", I will stick to SQL.
  • matwood 9y ago
    I feel like this is something I have to keep repeating, "the data always outlives the app code." If app code is required to make sense of the data, you are going to have problems. Of course NoSQL has a place, but only in very certain cases should it be your primary datastore. SQL (and the RDBMSs that leverage it) are built to store, protect, and provide structure to the data. The second annoyance I have is the push for schemaless. Schemaless does not exist. There is always a schema, except in a schemaless data store, the schema has been moved to app code and then my earlier comment applies. One thing I do agree with is that most ORMs are not very good. The best ones I have used are very thin layers over the sql, like jOOQ. I think the lack of understanding of SQL and bad ORM experiences (Hibernate WTF) is what led people to think SQL/RDMBs were the problem when in fact they were not.
    • fnord123 9y ago
      >The second annoyance I have is the push for schemaless. Schemaless does not exist. There is always a schema, except in a schemaless data store, the schema has been moved to app code and then my earlier comment applies. This is a great point. It's like types for programming languages. They always exist; it's just a matter of whether you have the compiler managing it or whether you need to keep it in your head when you're hacking. > I think the lack of understanding of SQL and bad ORM experiences (Hibernate WTF) is what led people to think SQL/RDMBs were the problem when in fact they were not. Again, like types in programming languages, I think the ability to iterate quickly without having to predict how the data will be used in the future leads to 'schemaless' approaches which can be kicked out the door more quickly (maybe) than ponderous schema laden tables.
• cm2187 9y ago
  I think most developpers using SQL use it a bit like most office users using VBA (ie they know how to record VBA macros but not much more). Most developers know how to write basic queries so have a very superficial understanding but most likely know very little about the performance implications, structured indexes, nesting of queries, etc. Whereas I would expect developpers who claim to use C to have more than a superficial understanding of the language.
  • maxxxxx 9y ago
    That describes me. I think it would be good if SQL databases had more accessible tools. The experience is just totally different from other programming languages. When I see a complex query I can't read it and don't understand what its implications could be. It reminds me a litte of regular expressions. If you don't use them all the time you forget the syntax and have to look it up every time you need them. Either a more modern looking language or some easy tools to analyze and build queries would help a lot in my view. Also refactoring tools that can analyze the impact of table changes in all stored procedures would be nice.
    • majewsky 9y ago
      > It reminds me a litte of regular expressions. If you don't use them all the time you forget the syntax and have to look it up every time you need them. Another common ground between regexes and SQL is that you should always have the manual of the tool in question open when you write a regex / SQL query because their syntaxes are all subtly different. For example for regexes: Perl: /^(?:aa|bb\b)+/ vim: /^\%(aa\|bb\>\)\+/
    • 72deluxe 9y ago
      But the same is true of any other language. Given a complex piece of C++, no amount of IDE features is going to help anyone understand it, nor know what is implications could be in a system (without looking at the entire source code to see where it is used). What you are lacking is: a) knowledge of your chosen database system in sufficient detail to know the trade-offs and pros/cons, and b) sufficient knowledge of SQL specific to your chosen database system in order to get the most out of it. This is a bit like knowing C++ in sufficient detail to be able to cope with a complex bit of code that you see, and also knowing your compiler sufficiently well so that you know it has foibles/bugs in certain areas. What you need is more knowledge, not more tools. Even if I have a hammer and a chainsaw, both are useless to me if I don't know enough about wood to build what I want.
• vinceguidry 9y ago
  I came expecting a treatment on Structured Query Language, was disappointed when it turned out to be on Relational Database Management Systems. It doesn't take a rocket scientist to figure out why RDBMSes won out over non-relational systems. What I want to know is why nobody ever came up with a better query interface. Every abstraction I've ever seen was built on top of SQL.
  • jrochkind1 9y ago
    Because SQL is so good at representing operations in the relational algebra that rdbms are based on, and such a mature technology, that it's awfully hard to replace it with something better? SQL and rdbms kind of go together, and always have -- it's the query language that parsimoniously represents what you can do to a relational source, whose development has happened in concert with rdbms themselves. What don't you like about SQL?
    • wolfgang42 9y ago
      Not OP, but I find the syntax to be arcane and bizzarely restrictive. Every query has to be SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY; for some reason the table comes after its fields and the ORDER BY can't be expressed using field aliases defined at the start of the query. Instead, I've been toying with the idea of a language where queries are expressed as a pipeline of operations on result sets, like this: FROM employee /* result set: all employees */ LEFT JOIN department ON employee.DepartmentID = department.DepartmentID /* result set: all employees + their department */ ORDER BY employee.last /* result set: all employees + their department, sorted */ WHERE department.name IN ('Sales', 'Engineering') /* result set: Sales and Engineering employees + their department, sorted */ FIELDS employee.first, employee.last, department.name /* result set: Sales and Engineering employees' first, last, and department names, sorted */ This is a SELECT, but you can for example turn it into an update just by adding a clause to the end: UPDATE employee.salary = employee.salary * 1.05 This is a much more regular language; there's nothing enforcing you to write these operations in a certain order, and you can add or remove clauses as necessary. For complex queries, I find that I write in this style anyway, using a chain of WITH clauses as a pipeline with a final SELECT on the end to get the results.
• cousin_it 9y ago
  One of my dreams is building a hybrid of RDBMS and Protocol Buffers. It would look like a bunch of nested structs that can be kept in memory, fetched from disk or over the network. The schema would be kept in .proto files, and you'd be able to reload it at runtime (existing data would be handled according to protobuf schema evolution, because each field in each struct is numbered and old numbers aren't reused). Most joins would be gone because nested structs are allowed, but you'd still have an SQL-like language for more general queries (designing such a language when nesting is allowed is a bit tricky, but not very). Things like indices and transactions would also exist at every level (memory, disk, network) because they are useful at every level. The end goal is eliminating impedance mismatch, because your current in-memory state, your RPCs and your big database would be described by the same schema language, somewhat strongly typed but still allowing for evolution. I have no idea if something like that already exists, though.
  • weavie 9y ago
    The problem is that what joins to what differs depending on the context for what you are looking for. Sometimes you join an order to supplier, other times it will be to customer and other times it will be to a list of products. Sometimes, all of them need to be included. With nested structs the parent/child relationship is fixed. If your query needs to invert this relationship, you essentially have to search through your entire database which will be incredibly slow and resource intensive. Either that, or you just store your data and their relationships separately and then allow for highly optimised searches to be conducted between these relationships according to the query and statistics you hold about the data ... but then you have just reimplemented an RDBMS..
    • cousin_it 9y ago
      I guess the idea is to make the schema as readable as possible using nesting, and then use indices for the rest.
  • TimJYoung 9y ago
    Yep, it was one of the things used in quite a few systems before SQL became more widespread: https://en.wikipedia.org/wiki/MultiValue https://en.wikipedia.org/wiki/MultiValue
• jaked89 9y ago
  "It’s like how MailChimp has become synonymous with sending email newsletters. If you want to work with data you use RDBMS and SQL. In fact, there usually needs to be a good reason not to use them. Just like there needs to be a good reason not to use MailChimp for sending emails, or Stripe for taking card payments." Wow, that's a subtle, almost unnoticeable promotion of MailChinp. /s
• siscia 9y ago
  I love SQL and I believe it will have a big jump in popularity with microservices. Each service should have a separate data source and being the sole responsable of a specific part of the data. In this environment however a full RDBMS is a little an overkill. The solution I am working on is RediSQL: https://github.com/RedBeardLab/rediSQL https://github.com/RedBeardLab/rediSQL It is a Redis module that embed SQLite. Redis is nowadays a common piece in any infrastructure. The little module plugs into Redis and expose a new command REDISQL.EXEC that provide the ability to run SQL statement. It is multithread, does not impact the performance of Redis, and very simple to use. Great write performance, I got 50k inserts per second on my machine, that should be enough for most microservices. I would love any kind of feedback on the module or if you need any help to get you started just open an issues. https://github.com/RedBeardLab/rediSQL https://github.com/RedBeardLab/rediSQL
• edpichler 9y ago
  This remembers what my teacher said to me a decade ago: "- Relational databases are the most successful software humanity have created.".
• sebringj 9y ago
  It lists Redis in there for the SQL ones. Redis has SQL now?
• gigatexal 9y ago
  You can get away with not using SQL if using the functionality from LINQ or Java streams but as a DBA I feel most at home with SQL.
• dangoldin 9y ago
  A bit of a plug but I wrote about this a week or so ago describing SQL as the perfect interface. Databases change and evolve but since they all wrap the underlying engine in SQL it becomes very easy to use new technologies under the same interface: http://dangoldin.com/2017/04/11/sql-is-the-perfect-interface/ http://dangoldin.com/2017/04/11/sql-is-the-perfect-interface...
• js8 9y ago
  SQL is good, but it shows its age. Today somebody should come up with something statically typed and more functional (meaning using lambda calculus as a starting point). The biggest pain points of SQL (IMHO) are: - lack of statically typing guarantees (for example, no guarantee that a certain table has certain column) - bad capability to abstract over parts of the data model (for example, queries have to specify the table that they query) Both of these can be resolved with use of good enough functional language. There are projects like that in the FP/Haskell community (e.g. Ermine), but it's fragmented.
  • elmigranto 9y ago
    > statically typing guarantees int will be an int. You can't store string there. What else do you need? Something along the lines of SQL's `check` or Postgres's domains? > queries have to specify the table that they query How do you imagine not doing that, something like "pull some things and do stuff with it"? I think we are quite far from that kind of reality. > certain table has certain column I don't get it. It either has, or it doesn't. In one case you get a value, in other one — SQL error. If that's so vital, pull up some schema information and check before running an app.
    • deathanatos 9y ago
      > What else do you need? Me personally: sum types. (Some languages call these tagged unions. Rust calls it an enum, but note that enum here does not mean "an integer under the hood") We have the case where we have an entity who, as part of its primary key, has a value that is either a valid integer or a sort of "Empty" value. It's part of the key, so I can't use a nullable integer to describe this column, as doing so would prevent me guaranteeing uniqueness (the "empty" value is only valid once, unlike a NULL in a unique constraint). I can use (bool, int) column pair and some check constraints, but it leaves the integer exposed to poorly formed queries, such as SUM(the_integer_part). If I can dedicate a "special value" in the integer, I can use just the integer; that's similarly brittle. (SUM — and most other arithmetic — is valid, but ONLY if the column isn't the "Empty" value.) It'd be nice to be able to model the table as something like, CREATE TABLE ( ... count_or_empty union { int | empty } NOT NULL, ... PRIMARY KEY (..., count_or_empty, ...) ); If the DB supported sum types, the type of count_or_empty here could deliberately be a union, which would not be compatible with SUM. You'd need to "unwrap" the union prior to doing such operations on it (check if it's Empty or not) and then do the appropriate thing: trying to blindly SUM on it would be a static error.
• agentultra 9y ago
  I've been programming for most of my life. SQL has been a big part of my career. And I love it. It's one of my top 5 languages. It's a nice, functional, declarative language in the vein of prolog and such. You just tell it the shape of the data you want, where to materialize it from, filter, aggregate, calculate the window of, etc... and the system figures out how to execute it as efficiently as it can. It beats out procedurally munging data by a long shot. It's more concise for many operations than ML-like variants. It's a great tool to have. And understanding the underlying maths, relational algebra, is beautiful. I've found trying to implement your own rick-shod relational database is a good way to try to mechanically understand the theory. Then move on to implementing datalog... etc. The reason why SQL continues to stick around is that the fundamental theories are quite sharp! I'd appreciate a more concise syntax some days but overall I can't say I'm displeased. It's great!
• 18nleung 9y ago
  Does anyone know how to generate a visualization like the one under reason #2 ("Battle Tested")? Here's the gif: http://imgur.com/K5a7U9O.gif http://imgur.com/K5a7U9O.gif
  • krallja 9y ago
    http://logstalgia.io/ http://logstalgia.io/
• zephyrfalcon 9y ago
  "Why do we still use SQL" and "Why do we still use relational databases" are two very different questions. They seem like much the same thing, because SQL is pretty much the only query language offered by relational database systems nowadays... so if you use SQL, you use an RDBMS, and vice versa. But other query languages used to exist. There was QUEL [1], for example. It seems to have fallen by the wayside; most people have probably never heard of it. I guess there is very little room for multiple languages in this particular space. [1] https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages
  • marcosdumay 9y ago
    SQL is a great language. So great that it's a superset of everything good ever created on this domain¹, but still cognitively simple. There is space for statistical and search-based (like Prolog) languages, but those are very niche. Proof of that is that they exist, but yet nobody here is talking about them. 1 - Ok, object store query languages are not a strict subset of standard SQL. But it's only a matter of adding one or two commands, like Postgres does.
  • default-kramer 9y ago
    Absolutely. Relational databases are so useful that I happily use SQL even though it is not a very good language. I have thought about making a "compile-to-SQL" language in the same vein as Typescript. (Does this already exist? You would think so, but I can't find one.) HTSQL has some great examples of where SQL could be better: http://htsql.org/doc/overview.html#why-not-sql http://htsql.org/doc/overview.html#why-not-sql. I would love to get that goodness in a language not tied to the rest of HTSQL.
    • Zak 9y ago
      There have been a few languages that compile to SQL. CLSQL for Common Lisp and Korma for Clojure come to mind.
    • jmcqk6 9y ago
      There are quite a few things that "Compile-to-sql". In .NET, the EntityFramework ORM takes the AST from the C# code int he query and generates the SQL from it. I'm not sure how ORMs work in other languages, but I would imagine it's something similar.
• misterbowfinger 9y ago
  Meh. I'm not one to sing the praises of SQL so highly. I understand it's history and its use - but it's also really, really difficult to understand and figure out complex SQL queries. Personally, I thought REQL was a really interesting take on query languages. As a developer, it allowed you write much cleaner code. You barely need an ORM. REQL kinda sucks for analysts at first, but in the long run, it makes writing complicated queries much, much easier.
• chrisan 9y ago
  Most(?) of the articles I read on HN, and then their comments, always seem to put NoSQL in a bad light when people use it for things that "should" be in SQL What _are_ the "correct" use cases for NoSQL? Everything has always been relational data for me
  • ubernostrum 9y ago
    Depends what you mean by "NoSQL". I've gotten plenty of mileage out of using key-value stores to complement relational databases. They're good at acting as task queues and caches, for example.
  • shakna 9y ago
    Something I made that was simpler as NoSQL: a comment system. Specifically a page had a list of comments, and a comment was a date/time and some text. There was no cross-referencing or nesting, just a basic ordered list. There is a relationship there, but it's a simple 1:1 relationship.
  • BoorishBears 9y ago
    We designed an analytics framework that wouldn't gain much from SQL. Our analytics events are arbitrary JSON (with a few required keys like time). The SQL version of that would be to have a column for each required key, and putting all the non-required JSON in a column, and processing that column as JSON/JSONB. Instead we used Elasticsearch and got an efficient layout of our data for queries, and got visualizations for "free" (Kibana)
  • marcosdumay 9y ago
    You can use a NoSQL database when your registers have no relation with each other (not even in different tables). Still, it's not automatically a good choice just because the above applies. It's guaranteed only to not be an horrible choice. The real answer is that nearly nobody has applications where NoSQL is a better choice than SQL databases. And those few that do will know it very well, so they don't need to ask around on internet forums.
  • jalayir 9y ago
    > What _are_ the "correct" use cases for NoSQL? When you need a highly available and reliable DB for your application, then you need a cluster approach for data replication. Most popular SQL DBs are single-node, with some application-level clustering solutions, so the only option is to scale vertically. However, a lot of no-SQL DBs like Redis and DynamoDB do clustering/replication closer to the iron.
• lcfcjs 9y ago
  SQL databases are so old and slow. Mongo is the only way forward.
• LeanderK 9y ago
  Is there any work on a successor to SQL (just the language, maybe as an optional frontend)? I am not a fan of SQL, it works great for simple queries, but fails (in my opinion) for more complicated ones. They get way to complicated and hard to understand for something that would be easy in other languages. This is not a critique of relational databases, only the language.
• skc 9y ago
  I've always thought the main reason NoSQL solutions became popular is that developers could finally get at and manipulate the data the way they wanted to. I've known some very, very prickly DBA's in my time who referred to the databases they looked after as "My database". So they would say things like "Don't put junk in my database" And would give you endless grief over how you wrote your queries or asked you a million questions about why you needed a new table and why your proposed design was shit. As a result, many of us devs tended to view SQL Databases as some sort of dark art. So in this regard, NoSQL is freeing at first glance. But if I'm honest, once I got over my fear, the "pros" of NoSQL solutions in comparison to good ol' SQL seem to be relatively feeble. I think it's easier to get up and running with a NoSQL solution because there is far less friction when it comes to rapid prototyping of ideas, but things get complex pretty quickly. I'd also say that for the vast majority of applications out there, the difference between the two will mostly be a wash.
  • hackits 9y ago
    By any chance did these DBA's be the Linux server administrators at all? My own personal experience is DBA's/Linux server administrators have authority issues. I had one situation where I had a encrypted file on one of the Linux server and the server administrator requested for months to have the decryption key so he could inspect the file.
    • duozerk 9y ago
      That seems highly unprofessional. I won't even ls into users' directories without their express permission.
      • hackits 9y ago
        .... yup, also him going in randomly through the day and killing proc's id's and clearing out log files at random was other issues where reported. I think for development to avoid him and get some level of sanity we used AWS and install ubuntu/postgres/java sdk to get work done.
• crimsonalucard 9y ago
  When the best SQL guy is a dude that memorized a bunch of language hacks to get the underlying algorithm to be more efficient I question the design principles of the language. Instead wouldn't it be better for the language to explicitly allow the user to apply algorithms or procedures to make things more efficient rather than apply hacks? The language is too heavy of an abstraction away from what's really going on under the hood. In a way it suffers from the same issue as functional programming. Not saying functional programming/sql is bad but... it has issues like almost everything.
  • remotehack 9y ago
    SQL is about relational theory; all that matters is the data. > Instead wouldn't it be better for the language to explicitly allow the user to apply algorithms or procedures to make things more efficient That...is hacking.
    • crimsonalucard 9y ago
      No it is not hacking. By explicit I mean BinarySearch(Table, x = name) rather than "SELECT * FROM Table WHERE x = name" Let me explain to you why "explicit" is better... Why should "SELECT column_name1, column_name2 FROM table" be more efficient than "SELECT * FROM table"? The abstraction is so leaky that in order to make a query better you resort to a language hack that only makes sense when you understand the instructions SQL compiles down into. This is bad. Leaky abstractions are bad. I shouldn't have to know what the SQL query is doing to optimize.... In web development your application servers use languages like go or python that are closer to the metal which allow us to explicitly deploy certain algorithms without this strange layer of SQL expressions that compiles to imperative code. This leads to faster applications that are easier to optimize at the expense of using terse highly abstract expressions such as those found in SQL. Here's the strange part of web development. Everyone knows that the bottleneck for most websites are in the database. Yet why do we deploy easily optimizable imperative languages in the application server while putting a highly inefficient SQL expression language over the main bottleneck (the database)? Shouldn't it be the other way around? Shouldn't we have Web application servers written in highly abstract functional languages while Database languages written in easily optimizable imperative code that is closer to the metal?
• threepipeproblm 9y ago
  Possibly relevant: Modern SQL is a good resource, by Markus Winand, on the newer aspects of the language. http://modern-sql.com/ http://modern-sql.com/
• eddd 9y ago
  SQL as language is one thing. The fact that RDBMS systems with some consistence and isolation guarantees is a different story.
• carapace 9y ago
  Should mention SQL wasn't the original RM language: https://en.wikipedia.org/wiki/Alpha_(programming_language) https://en.wikipedia.org/wiki/Alpha_(programming_language) ;-)
• tannhaeuser 9y ago
  While SQL has a large class of uses, and also a smaller class of problems for which better solutions exist (CAP constraints, time series and other massive self-join apps, session storage ...) the reason we're going to use SQL in another 40 years still is that there's no cross-vendor standardization effort going on anymore (NoSQL vendors don't seem to find it necessary to drive sales and market growth, and customers don't demand it either).
• jackfoxy 9y ago
  There just is no substitute for SQL. Some thoughts on what has given it a bad name: 1) The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately probably 99% of real-world databases were designed with artificial keys. I wish I could point to some literature on this topic. It is very rare and I only came to learn about this from a DBA who is well-versed in designing with natural keys. I'm trying to get him to publish more on this topic. 2) ORMs. This is just a bad practice. Their use in part derives from the awful schemas designed with artificial keys, requiring another layer of complexity to get a more intuitive model of the data. Fortunately for me over the past 3 years I've been doing almost all my application I/O with F#'s SQL Type Provider, SqlClient, http://fsprojects.github.io/FSharp.Data.SqlClient/ http://fsprojects.github.io/FSharp.Data.SqlClient/ , which strongly types native query results, functions, and SPROCS. Just does not work if you need to construct dynamic SQL. I've been trying to goad the author into also providing meta data retrieval. That would be the icing on the cake. 3) SQL does not seem to be a required topic for undergrads. There are really no unsolved problems (of note), so it's not interesting to academics. 4) Most app programmers don't get much practice writing difficult queries or tuning problem queries, so that one time every 9 months when you do something hard, it is hard. (And again, often compounded by the complexity introduced by artificial keys.)
  • sobani 9y ago
    Can you give 1 example of a good natural key? Note I will disqualify anything that has a reasonable chance of changing, like the primary email address of an account, a persons name, a persons day of birth or the 'public id' of a bank account
    • yawaramin 9y ago
      A composite key made up of foreign keys in a junction table.
    • irishsultan 9y ago
      How often does the birthday of people change? (Then again, how likely is it to be unique?)
• davidw 9y ago
  I think back to my first programming job and some of the stuff I used then: Perl, early versions of PHP and other such tools that I haven't used in a long time. Two of the things I still use to this day, though, are: * Postgres * Emacs
• z3t4 9y ago
  i guess a lot of performance was sacrified and a lot of optimizations made, witch make sql very fast in the current era.
• rconti 9y ago
  My favorite SQL fact: The San Carlos Airport (which is about 1mi from Oracle HQ; a plane losing power on takeoff would very plausibly crash into the towers, and in fact one did fall into the Redwood Shores lagoon a few years back, likely a choice by the pilot to avoid hitting a populated area) is KSQL, so airport code SQL. And its existence predates Oracle's headquarters being located there. It's just a coincidence. KSQL and Oracle towers: http://www.bayareapilot.com/IMG_0317%20Large%20Web%20viewnearingSQL.jpg http://www.bayareapilot.com/IMG_0317%20Large%20Web%20viewnea...
• ryanar 9y ago
  A lot of mentions to ORMs being the problem, them being poor abstractions, etc. In my work with Django's ORM I have run into problem queries as often as I have with doing SQL, and Django's ORM has never let me down. ORM keeps your thinking in line with your object oriented code, and I find it very easy and natural to use. Who cares if there are a bunch of artificial keys underneath, imo caching queries is a better solution to slow queries than trying to optimize SQL. So using the ORM is never a pain point. The other advantage is automatic escaping to prevent SQL injection, which is still a top contender on OWASP's list. I never have to worry ahout SQL injection when using the ORM. Maybe other ORMs are poor solutions, but at least with Django I have been very happy using it.
  • scriptkiddy 9y ago
    +1 for Dajngo's ORM. I find it to be extremely simple to work with. It also does a fantastic job generating efficient queries due to the way it forces you to structure your models. The `F`, `Q`, and aggregation functions are top-notch as well. If none of the ORM features suit you for a particular query, Django ORM allows you to write the raw query yourself in a secure manner. Couple this with the Migration system, and I doubt you'll find a better ORM suite anywhere else. I find Python has some of the best ORMs out there between Django ORM, SQLAlchemy, Peewee, and Pony.
• EternalData 9y ago
  Good old SQL. Your reliable friend that always shows up with the right amount of booze and gas money, and which, when you stop to think about, basically hasn't ever majorly fucked up around you.
• joeldg 9y ago
  what is that roman candle looking image under the "Battle-tested" section of this article?
• manigandham 9y ago
  The amount of confusion in the comments highlights why we don't have anything better. SQL is just a language, it specifically stands for Structured Query Langauge. It has nothing to do with the underlying database. Relational databases all implement SQL because that's what the language was originally created for but it's just an interface. Relational databases can also implement other interfaces like mysql with its X protocol. Other database types like key/value, document, graph, columnar, time-series, RDFs, etc can also implement SQL and many are starting to for easier interoperability, like Cassandra with CQL. There is definitely potential for a better query language and there are examples like ReQL and GraphQL but SQL is still just fine for most use-cases.
• swalsh 9y ago
  I'm not as old as some of you guys, been programming professionally for 11 years. The one constant in my life is SQL. I like it. My C# code is all obsolete now, the javascript code I wrote a year ago is obsolete, the Ruby code I've written never became a successful business. About half the PHP code I've written has been rewritten by now probably. But the SQL I've written is still living on, the tables I designed are still in use, the queries are still querying.
• wittgenstein 9y ago
  I'm wondering what is the revenue of SQLizer?
• Animats 9y ago
  The great thing about SQL databases is that the expected standard of performance is "just works". Works all the time, for years, without trouble, even for the hard cases. All the major SQL databases, SQLite, MySQL, MariaDB, Postgres, Microsoft SQL Server, and Oracle, achieve that. Contrast this with most webcrap. Or most of the NoSQL databases.
• rumcajz 9y ago
  Unlike with imperative languages which are dozen a dime, there's almost no alternative to SQL when it comes to relational languages. That being the case, people rarely even think about whether SQL is a good language or a bad language, whether it's lacking something etc. But once you actually try thinking about it, it turns out that it's a pretty well designed language and any alternatives you can think of are usually much inferior to it.
• arnon 9y ago
  When we approach a customer with our database, SQL compliance is super important to them. Some of our competitors used to be 'SQL-like', and even they swapped to using full SQL. I think the fact that SQL is based on solid mathematical principles really helps it stay relevant.
• ianamartin 9y ago
  SQL was my entry point into software development, and I have a somewhat emotional attachment to it. And I'm quite glad that it worked that way. SQL, relational theory, and set theory are a great place to start understanding how to work with data. And a great way to start understanding software. All software deals with data. If you don't have a good understanding of data, you are never going to have a good understanding of software. One of the best books I've ever read was Applied Mathematics for Database Professionals by Lex de Haan, and Toon Koppelaars. I think that's the database equivalent of SICP. You need to read it and understand it if you want to seriously deal with data. And you want to if you want to write software. I'm obviously biased because of the way I got into things, but I look at things as a top-down vs a bottom-up point of view. I was a violinist and music theorist before I got into technology, and the bottom-up approach has always resonated with me. In classical music, pretty much everything bubbles up from a baseline foundation and a structure; the stuff at the top that you actually see is a result of that structure. You don't start with some notes that you want to play on an instrument and then go and try to find a structure that supports those notes. You go bottom-up. You lay the foundation and build on that. It was easy for me to map that idea of musical theory onto a database early in my career. And I moved up in the stack as I needed to. I started by building things entirely in SQL. You want complex statistical analysis? Sure, I'll do that . . . in SQL. Because I didn't know any better. Then I found out that there are actually other languages that can do certain types of things much better. R, Python, C#, etc. 11 years later, I'm now very capable in a number of languages, and I don't suck. Along the way, I've had to put a lot of effort into learning the things I would have got from a comp sci degree program, and I'm probably not the best at certain types of software challenges. I use noSQL stuff for caches and data warehouses, I use some of that for offloading traffic and keeping the reads separate from the writes. But there isn't a project that I touch that doesn't involve SQL in some way. SQL is incredibly useful every day. Learning it, knowing it, understanding it, is a bare minimum for people I hire. If you have a comp sci degree, and you don't know SQL, I'm going to probably write you off. If you have a liberal arts degree of some kind, and you do know SQL, I'm probably going to hire. You can learn everything else on the job. None of that is an excuse for the totally shitty article linked here. We use SQL today because it's good and it works. Not many languages can say that these days.
  • 9y ago
• csours 9y ago
  Since a lot of people are thinking about this: Is there a good way to compose SQL? I see a bunch of repeated items in many SQL queries, things that would be functions in another language. One of my colleagues pointed out that this is indicative of poorly formed queries. What do you all think?
  • combatentropy 9y ago
    It is very likely that repetitive SQL could be helped by views (database views, not views in the MVC sense). Database views are just named queries: create view red_shirts as select * from shirts where color = 'red' ; Then you can just say: select * from red_shirts; This is a simple example. Views are normally much more intricate and useful. Basically any select-statement could be saved as a view. Databases also let you define functions.
• jordanthoms 9y ago
  SQL's just so much more flexible than the competing query languages, there's not a true alternative to it. One syntax does a decent job for transactional processing, key/value tables, heavily relational data, massive analytical processing, data warehousing, etc. It's pretty ugly, but it's flexible enough that you can get the job done even if you need to do something complex.