9 ms·

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 ab

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 10y 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 10y 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.
  - mindcrime 10y ago
    I think the lack of understanding of SQL and bad ORM experiences (Hibernate WTF) I don't understand this comment. I've never had a bad experience with Hibernate. If anything, Hibernate has saved me tons of time over the years.
    - matwood 10y ago
      I'd be curious about the size of the systems you have used with Hibernate. Do you know off the top of your head what will cause Hibernate to issue a flush? How about the order of operations in a batch? I've read the entire Hibernate book multiple times while trying to squeeze out speed, and finally decided enough. The problem with Hibernate is I have to learn all of these Hibernate quirks to maybe get decent speed out of a system, when I could have just written SQL and been done. This isn't even including debugging when Hibernate decides to issue N+1s.
      - gerbilly 10y ago
        Speed? I found Hibernate is great for CRUD. Like where you have dozens and dozens of domain objects that you want to persist, and you don't want to spend all your time writing DAO code with SQL in in that is all the same but different. Enterprise applications are rife with those. For speed write some hand written SQL to handle those few cases (like batch imports maybe). Also I found a handy rule: Only create an association between entities when the entity makes no sense without the other. For all other cases split them up and perhaps create aggregates by hand.
        mindcrime 10y ago
        That jibes with my experience. We don't have tons of complicated associations, and do a lot of fairly straightforward CRUD operations. Maybe that's just the sweet spot for using Hibernate.
      - cygned 10y ago
        I had to use Hibernate in a project. We were loading an entity from the database, someone invoked a setter and the set value was updated in the database automatically. Totally blew my mind. Never touched it again since then (not doing much Java anyway).
- techno_modus 10y ago
  > Schemaless does not exist. There is always a schema... I also think so but the issue has several important aspects which justify the use of the separate term "schemaless". One of them is that schema elements (say, column names) can be stored as normal data. For example, instead of having normal columns like Name, Age, Department, we could introduce a column storing these strings in 3 rows. As a result, DBMS is simply unaware of the schema - the schema exists only in our head (and in the app). As a consequence, DBMS cannot help us too much in managing data, and instead our app becomes responsible for these tasks, hence we get problems you mentioned. But the major problem is that currently there is no technology that allows us to say that this table column stores actually column names which can be used in queries and have to be treated as normal columns.
  - MarHoff 10y ago
    Use jsonb inside a Database and use constraints to ensure all requested json attributes (store name,etc..) are present at INSERT/UPDATE time? That don't seems like rocket science to me... duh... PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints.
    - jrochkind1 10y ago
      Well, now you're just as 'schemaless' as any NoSQL, so that's neither an advantage nor a disadvantage over it. There are other differences, both advantages and disadvantages to doing this with Postgres (which is the only db with a type called 'jsonb' I think?), vs some kind of non-SQL document store. I agree that as Postgres json(b) gets better and better, the disadvantages decrease. And one of the main advantages to me is simply not having to have another system to run and understand, since postgres is probably already there. But it's always trade-offs and choices, that you make better with more experience and better understanding of your domain. It's definitely not a "duh, it's always obvious" thing. Unless your domain is so simple that it doesn't hardly matter.
    - SmellTheGlove 10y ago
      > PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints. Or anything that you might want to use as a foreign key down the road. It requires you to know that now, or modify later, so we might as well declare a schema and manage it unless we're totally certain we won't need any additional keys. But if we're certain of that, we could just use a schema.
- jasode 10y ago
  >Schemaless does not exist. There is always a schema, except in a schemaless data store, the schema has been moved to app code I get what you're saying but keep in mind that "schemaless" isn't a philosophical statement about denying ontology[1]. Instead, it's an industry label for avoiding database schema operations. E.g. a rapidly-evolving app doesn't know ahead of time all the rigid columns they need so they use "schemaless" db strategy to avoid "ALTER TABLE ADD COLUMN X" or avoid an export/import to new v2,v3,etc tables. From the relative point-of-view of the db engine, it doesn't see a "schema" when the so-called "columns" are embedded as strings inside of generic fields. [1] https://en.wikipedia.org/wiki/Ontology https://en.wikipedia.org/wiki/Ontology
  - VLM 10y ago
    And without the adult supervision that "ALTER TABLE" theoretically implies, you end up with 15 columns named "t1" and the like, no idea what any of it means, and source code to match (assuming its not gone and you're trying to reverse engineer the schema-less data) There is a place for unorganized, disorganized, unengineered, unplanned data structures, but its not in the DB.
  - agentultra 10y ago
    Change management is not something that should be feared if given careful consideration! I don't always know the "perfect" schema design up front but there are great tools in the modern SQL tool belt for managing abstractions and evolving schemas. In PostgreSQL: CREATE SCHEMA crm; The "crm" schema can store all of my customer relationship management related tables. I may not know all of the data I need for them right now and will need to evolve them over time. However if I start with a solid design for my public schema I can use views in the public schema: CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c; This is a simple view. You applications can CREATE/UPDATE/INSERT against the view in the public schema with no changes to your code. However if you want to evolve the schema of customers you can do so in the `crm` schema adding tables, columns, etc. CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c WHERE deleted = false; You can then evolve your public schema to include more information as needed by updating the view. The caveat is that if your view starts materializing data from more than one table you'll have to intercept INSERT/UPDATE/DELETE and replace them with your own functions to put the data in the right place. This works quite well for implementing nice features like administrative flags without having to update your application code or spread that logic out: it's all kept in the database. You still get the power of indexes, a strong query language, and structured data. PostgreSQL is great as an application server allowing you to comment all of your objects and having deep introspection facilities allows you to not only be flexible with your data models but also consistent. Update: Added an example of changing the public schema to add a filter to the query for an administrative flag
- emilecantin 10y ago
  Yeah, I think the good ORMs make the simple case _really_ simple (like User.getById(1234)) while letting you drop down to raw SQL easily when you need it. Any ORM that doesn't let you do that is not worth using, IMO.
- ZenoArrow 10y ago
  Thank you for recommending jOOQ. I find many ORMs seem unnecessarily complicated compared to raw SQL (with a few exceptions), nice to find another one that offers readable code. Is the performance generally decent too?
  - matwood 10y ago
    The performance with jOOQ is great. I find it on par with raw SQL. It also has quite a few advanced ways to map the results that can take more complex queries and map them into usable data easily. The main jOOQ programmer really knows databases and has a very informative blog. My only complaint (and it's a minor one that would be hard to get around) is the need to run a code generator for jOOQ after every schema change.
    - antoaravinth 10y ago
      Just a noob question on jooq. How do you construct error message which you use to send back to user on SQL exceptions. Like in case of duplicate item, you send back "User already exists". How your handling it? I know jooq just sends DataAccessException, now the question is how your converting that exception to a meaningful message which needs to be sent to say UI?
      - lukaseder 10y ago
        That's a really interesting question, and the best answer is: You have to write this manually. The SQL/JDBC/jOOQ layer can only generate system exceptions, like a constraint violation exception in this case. But there are many different types of constraints, and many different things that a constraint violation can mean. Only you and your service layer can possibly know how to translate such a violation to a meaningful (localised?) user error message. Having said so, you could hook in an ExecuteListener into jOOQ to handle errors in a single location: https://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html https://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener... An example that uses the Spring Exception translator: https://github.com/jOOQ/jOOQ/blob/master/jOOQ-examples/jOOQ-spring-example/src/main/java/org/jooq/example/spring/exception/ExceptionTranslator.java https://github.com/jOOQ/jOOQ/blob/master/jOOQ-examples/jOOQ-...
- Sir_Cmpwn 10y ago
  I ended up using NoSQL for ephemeral data and SQL for persistent data in most of my applications.
- sixdimensional 10y ago
  I find a simple analogy for schemaless in the OO world as well - an object without a class definition (for example a dynamic/anonymous object) and/or an interface has unknown properties and methods and is thus of highly limited use. Just like a table without a schema... because after all, it's still likely objects all the way down. There is no such thing as working without a "schema", because software simply can't function without data and corresponding metadata to describe it.
- fortpoint 10y ago
  I saw Mike Stonebraker while he was on his VoltDB "tour" a few years back. He makes the same arguments. In his talk he walked through the history of database technology and discussed a similar "war" between CODASYL and SQL. He framed that debate in terms of the split between separating the concerns of your data's structure (schema) from application logic. Fairly or not, he suggested the current fight between noSQL/Schemaless vs SQL/RDBMS was being fought in ignorance of all that went on in the 70s.
- ams6110 10y ago
  Agree so much. The first thing I do for almost any new system is design the data model. Once that satisfies all the requirements, building an application and UI on top of that is usually pretty straightforward.
  - matwood 10y ago
    Exactly what I do. I think in terms of data. UIs seem to change weekly, but the data lives forever.
- lukaseder 10y ago
  > There is always a schema It has a name. Schema-on-write (static schema) vs. schema-on-read (dynamic schema).
pscarey 10y ago
I'm currently using Firebase at work (startup, mobile app, web dashboards), and I think it's got some great features. It's not a replacement for SQL, but an entirely different product (and has no doubt come a long way since V1, especially with Firebase Functions out recently). You have to make tradeoffs, like data duplication, for the advantages. From the sound of it, the advantages probably aren't relevant to you, but I'm confident they exist for us. - We only have to store a comparatively small amount of user data for the lifetime value of each user, so the actual database cost is marginal (i.e. data duplication is not expensive to us, as long as reads are fast). (Development) time is (very) expensive. - Rapid development and iteration. I can make additions and make fields redundant with ease in response to customer feedback, my own development choices, and changing product needs. There's no API update needed, as reads are client side. I have data model classes which store the reference in the database, some constructors, and getters and setters (About the same as any other data model + API would have). The release goes live and data just starts being manipulated in a new way. Alterations can have simple defaults with || in JS and ?? in Swift (even if it's just an empty string). - Minimal backend. Our application doesn't require much backend logic, and that which we do have is mostly event driven with Firebase Functions, upon certain database writes. Works out quite cheap too (for now - most usages don't trigger any functions). - Caching, and offline first. We take the approach that it doesn't matter if there's a short delay in 95% of our data updating - either due to patchy mobile signal or the data taking time to sync down. Firebase has made on device caching and real time updates to that data a breeze. With GeoFire, we've got instant map search (keys are cached by Firebase and contain sufficient information to filter, etc). - Declarative security rules are very powerful, and can even reference values in the database itself for security control - effectively. We store a permissions tree in the database for our more complex security logic. As a bonus, additional features like Authentication, Analytics, Push Notifications, etc are all convenient to have bundled up. As you can see, most of this isn't about the database tech, but the development speed for us. The perceived costs and inefficiencies may exist in the database itself, but there are massive advantages for the application development as a whole.
- cyberferret 10y ago
  I should probably clarify that I really liked Firebase, for all the reasons you mentioned above. It was really the schema restrictions, and (at the time) the lack of relational functionality that made me scrap it for a mobile app project that I was working on at the time. This was very early days too, and I believe Firebase has improved considerably since then, and indeed I did a little "fun" project in it recently [0] (directly related to HN actually) and enjoyed the process. I may still revisit it sometime, and see if it will suit another project. [0] - https://hackernoon.com/tophn-a-fun-side-project-built-with-vue-js-and-rethinkdb-f22159641c1b https://hackernoon.com/tophn-a-fun-side-project-built-with-v...
gregmac 10y ago
I've largely abandoned ORMs that generate SQL queries for me. A mapping layer that takes a SQL result and stuffs it into an object (DTO) is useful, but anything beyond that is more trouble than it's worth. For complex SQL, it's better to write it by hand. You get to tune it and ensure the indexing is correct (either the query usage, or create/modify your indexes), and you get to see exactly what it's doing. For even simple conditional joins, there's something nice about knowing exactly what's going on, and knowing it's not going to be doing something that results in a nested join loop (eg: the type of slow query you don't notice until you're using it in production and performance suddenly drops). I'm a big fan of Dapper for this, so a lot of my data layer code looks like: public IEnumerable<Order> LoadOrders(int customerId) { using (var db = GetConnection()) { return db.Query<Order>("SELECT * FROM orders WHERE customerId = @customerId", new { customerId }); } } The one exception I make to ORM-generated code is INSERT/UPDATE queries. For most cases, the ORM doesn't have much it can screw up, and since it's essentially just mapping code ("Name = @Name, Address1 = @Address1, etc") it's more likely I'll make a typo or copy/paste error than anything else. I've spent too many hours debugging crappy ORM queries, and I find it generally takes twice as long (with quadruple the frustration) to get the ORM to generate the complex SQL you want it to vs just writing the SQL.
- senorjazz 10y ago
  I fully agree. For simple things ORMs can be useful (very simple) anything else they get in the way. Firstly, have to write out the SQL, then convert it to the ORM format, only to find the ORM doesn't support something you are using, so you rewrite the query in a more long winded way, then convert it to the ORM.
  - gregmac 10y ago
    > Firstly, have to write out the SQL, then convert it to the ORM format Ha, you know I had actually forgotten about that, but I have done that many times. You model the data, write some queries to essentially test out the structure/data/concepts, then instead of pasting the queries into your code, you spend another bunch of time trying to tell your ORM how to come up with the query you pretty much already have. Even if you're using a very well-tested, robust ORM that flawlessly handles your use case and you're an absolute expert at using it, it's still more work than just pasting a query you already have.
samirillian 10y ago
To be clear, though, you're talking more about the Relational Model than SQL per se.
- cryptonector 10y ago
  There aren't very many query languages for relational DBs that come close to SQL's power.
pkulak 10y ago
Most projects I write eventually turn into an RDS and Redis. Storing non-relational data can be super handy, and putting it somewhere where you know it can disappear at any point keeps you disciplined. haha
cryptonector 10y ago
Typically there are two arguments for NoSQL: "no SQL!" (i.e., "the SQL language is so ugly, difficult, and painful") and "no ACID" (i.e., eventually consistent, hopefully, maybe). The first one is always demonstrated to be a terrible argument when someone creates a SQL alike for whatever NoSQL we're talking about. The second one has been a more durable argument. But transactional semantics clearly are independent of query language! And technologies like Lustre and Spanner show that one can still have some measure of traditional transactional semantics in distributed systems (filesystems and databases, respectively). There are many applications where some degree of "eventually-consistent" is a great tool for making them scale, but this is often a function of what can be done in the event that inconsistencies create problems (e.g., a store selling more items of some product than it has available, a case where the store can refund the customer or delay delivery). I'm extremely skeptical of NoSQLs, as you can tell. I would say that NoSQLs have NoPLACE. (Certainly as to that first argument.) I'm also extremely skeptical of ORMs. So far as I can tell ORMs only ever add a layer of headaches. They seem to aim for simplifying the DB experience for developers and users, but the moment you step out of the small world of queries made simpler by the ORM... you're in for a world of hurt.
- Zak 10y ago
  I'd actually quite like a relational database with a language that isn't SQL. I find SQL's syntax pretty irregular and awkward (e.g. update having a different syntax from insert), find the tools available for abstracting and composing queries (views) underwhelming and hate that schemas aren't declarative. The first complaint derives largely from the SQL language being intended to read link English text. Languages like this show up regularly, seemingly based on the misconception that syntax is what's hard about programming. Most of them fail, as they should. SQL didn't, because it was tied to the exceptionally useful relational database. The second is best illustrated by showing an alternative approach as an example: Korma for Clojure (http://sqlkorma.com/ http://sqlkorma.com/). It's easy to compose queries from simpler queries, just as we compose functions from simpler functions. Views provide some of this. The third, if it's solvable would make using relational databases much nicer. Instead of schemas being manually-created diffs full of imperative statements like create and alter, make them declarative and have software generate the diff. If transformations on the data itself are required between versions, require it to include a pure function describing said transformation. By default, require a function to transform it back as well unless it explicitly says what data is to be discarded.
manigandham 10y ago
What does this have to do with the article? SQL - the query language - is completely separate from any database that implements it.

matwood 10y 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 10y 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.
- mindcrime 10y ago
  I think the lack of understanding of SQL and bad ORM experiences (Hibernate WTF) I don't understand this comment. I've never had a bad experience with Hibernate. If anything, Hibernate has saved me tons of time over the years.
  - matwood 10y ago
    I'd be curious about the size of the systems you have used with Hibernate. Do you know off the top of your head what will cause Hibernate to issue a flush? How about the order of operations in a batch? I've read the entire Hibernate book multiple times while trying to squeeze out speed, and finally decided enough. The problem with Hibernate is I have to learn all of these Hibernate quirks to maybe get decent speed out of a system, when I could have just written SQL and been done. This isn't even including debugging when Hibernate decides to issue N+1s.
    - gerbilly 10y ago
      Speed? I found Hibernate is great for CRUD. Like where you have dozens and dozens of domain objects that you want to persist, and you don't want to spend all your time writing DAO code with SQL in in that is all the same but different. Enterprise applications are rife with those. For speed write some hand written SQL to handle those few cases (like batch imports maybe). Also I found a handy rule: Only create an association between entities when the entity makes no sense without the other. For all other cases split them up and perhaps create aggregates by hand.
      - mindcrime 10y ago
        That jibes with my experience. We don't have tons of complicated associations, and do a lot of fairly straightforward CRUD operations. Maybe that's just the sweet spot for using Hibernate.
    - cygned 10y ago
      I had to use Hibernate in a project. We were loading an entity from the database, someone invoked a setter and the set value was updated in the database automatically. Totally blew my mind. Never touched it again since then (not doing much Java anyway).
techno_modus 10y ago
> Schemaless does not exist. There is always a schema... I also think so but the issue has several important aspects which justify the use of the separate term "schemaless". One of them is that schema elements (say, column names) can be stored as normal data. For example, instead of having normal columns like Name, Age, Department, we could introduce a column storing these strings in 3 rows. As a result, DBMS is simply unaware of the schema - the schema exists only in our head (and in the app). As a consequence, DBMS cannot help us too much in managing data, and instead our app becomes responsible for these tasks, hence we get problems you mentioned. But the major problem is that currently there is no technology that allows us to say that this table column stores actually column names which can be used in queries and have to be treated as normal columns.
- MarHoff 10y ago
  Use jsonb inside a Database and use constraints to ensure all requested json attributes (store name,etc..) are present at INSERT/UPDATE time? That don't seems like rocket science to me... duh... PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints.
  - jrochkind1 10y ago
    Well, now you're just as 'schemaless' as any NoSQL, so that's neither an advantage nor a disadvantage over it. There are other differences, both advantages and disadvantages to doing this with Postgres (which is the only db with a type called 'jsonb' I think?), vs some kind of non-SQL document store. I agree that as Postgres json(b) gets better and better, the disadvantages decrease. And one of the main advantages to me is simply not having to have another system to run and understand, since postgres is probably already there. But it's always trade-offs and choices, that you make better with more experience and better understanding of your domain. It's definitely not a "duh, it's always obvious" thing. Unless your domain is so simple that it doesn't hardly matter.
  - SmellTheGlove 10y ago
    > PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints. Or anything that you might want to use as a foreign key down the road. It requires you to know that now, or modify later, so we might as well declare a schema and manage it unless we're totally certain we won't need any additional keys. But if we're certain of that, we could just use a schema.
jasode 10y ago
>Schemaless does not exist. There is always a schema, except in a schemaless data store, the schema has been moved to app code I get what you're saying but keep in mind that "schemaless" isn't a philosophical statement about denying ontology[1]. Instead, it's an industry label for avoiding database schema operations. E.g. a rapidly-evolving app doesn't know ahead of time all the rigid columns they need so they use "schemaless" db strategy to avoid "ALTER TABLE ADD COLUMN X" or avoid an export/import to new v2,v3,etc tables. From the relative point-of-view of the db engine, it doesn't see a "schema" when the so-called "columns" are embedded as strings inside of generic fields. [1] https://en.wikipedia.org/wiki/Ontology https://en.wikipedia.org/wiki/Ontology
- VLM 10y ago
  And without the adult supervision that "ALTER TABLE" theoretically implies, you end up with 15 columns named "t1" and the like, no idea what any of it means, and source code to match (assuming its not gone and you're trying to reverse engineer the schema-less data) There is a place for unorganized, disorganized, unengineered, unplanned data structures, but its not in the DB.
- agentultra 10y ago
  Change management is not something that should be feared if given careful consideration! I don't always know the "perfect" schema design up front but there are great tools in the modern SQL tool belt for managing abstractions and evolving schemas. In PostgreSQL: CREATE SCHEMA crm; The "crm" schema can store all of my customer relationship management related tables. I may not know all of the data I need for them right now and will need to evolve them over time. However if I start with a solid design for my public schema I can use views in the public schema: CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c; This is a simple view. You applications can CREATE/UPDATE/INSERT against the view in the public schema with no changes to your code. However if you want to evolve the schema of customers you can do so in the `crm` schema adding tables, columns, etc. CREATE VIEW public.customers AS SELECT c.id, c.contact_name, c.email FROM crm.customers AS c WHERE deleted = false; You can then evolve your public schema to include more information as needed by updating the view. The caveat is that if your view starts materializing data from more than one table you'll have to intercept INSERT/UPDATE/DELETE and replace them with your own functions to put the data in the right place. This works quite well for implementing nice features like administrative flags without having to update your application code or spread that logic out: it's all kept in the database. You still get the power of indexes, a strong query language, and structured data. PostgreSQL is great as an application server allowing you to comment all of your objects and having deep introspection facilities allows you to not only be flexible with your data models but also consistent. Update: Added an example of changing the public schema to add a filter to the query for an administrative flag
emilecantin 10y ago
Yeah, I think the good ORMs make the simple case _really_ simple (like User.getById(1234)) while letting you drop down to raw SQL easily when you need it. Any ORM that doesn't let you do that is not worth using, IMO.
ZenoArrow 10y ago
Thank you for recommending jOOQ. I find many ORMs seem unnecessarily complicated compared to raw SQL (with a few exceptions), nice to find another one that offers readable code. Is the performance generally decent too?
- matwood 10y ago
  The performance with jOOQ is great. I find it on par with raw SQL. It also has quite a few advanced ways to map the results that can take more complex queries and map them into usable data easily. The main jOOQ programmer really knows databases and has a very informative blog. My only complaint (and it's a minor one that would be hard to get around) is the need to run a code generator for jOOQ after every schema change.
  - antoaravinth 10y ago
    Just a noob question on jooq. How do you construct error message which you use to send back to user on SQL exceptions. Like in case of duplicate item, you send back "User already exists". How your handling it? I know jooq just sends DataAccessException, now the question is how your converting that exception to a meaningful message which needs to be sent to say UI?
    - lukaseder 10y ago
      That's a really interesting question, and the best answer is: You have to write this manually. The SQL/JDBC/jOOQ layer can only generate system exceptions, like a constraint violation exception in this case. But there are many different types of constraints, and many different things that a constraint violation can mean. Only you and your service layer can possibly know how to translate such a violation to a meaningful (localised?) user error message. Having said so, you could hook in an ExecuteListener into jOOQ to handle errors in a single location: https://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html https://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener... An example that uses the Spring Exception translator: https://github.com/jOOQ/jOOQ/blob/master/jOOQ-examples/jOOQ-spring-example/src/main/java/org/jooq/example/spring/exception/ExceptionTranslator.java https://github.com/jOOQ/jOOQ/blob/master/jOOQ-examples/jOOQ-...
Sir_Cmpwn 10y ago
I ended up using NoSQL for ephemeral data and SQL for persistent data in most of my applications.
sixdimensional 10y ago
I find a simple analogy for schemaless in the OO world as well - an object without a class definition (for example a dynamic/anonymous object) and/or an interface has unknown properties and methods and is thus of highly limited use. Just like a table without a schema... because after all, it's still likely objects all the way down. There is no such thing as working without a "schema", because software simply can't function without data and corresponding metadata to describe it.
fortpoint 10y ago
I saw Mike Stonebraker while he was on his VoltDB "tour" a few years back. He makes the same arguments. In his talk he walked through the history of database technology and discussed a similar "war" between CODASYL and SQL. He framed that debate in terms of the split between separating the concerns of your data's structure (schema) from application logic. Fairly or not, he suggested the current fight between noSQL/Schemaless vs SQL/RDBMS was being fought in ignorance of all that went on in the 70s.
ams6110 10y ago
Agree so much. The first thing I do for almost any new system is design the data model. Once that satisfies all the requirements, building an application and UI on top of that is usually pretty straightforward.
- matwood 10y ago
  Exactly what I do. I think in terms of data. UIs seem to change weekly, but the data lives forever.
lukaseder 10y ago
> There is always a schema It has a name. Schema-on-write (static schema) vs. schema-on-read (dynamic schema).

pscarey 10y ago

I'm currently using Firebase at work (startup, mobile app, web dashboards), and I think it's got some great features. It's not a replacement for SQL, but an entirely different product (and has no doubt come a long way since V1, especially with Firebase Functions out recently). You have to make tradeoffs, like data duplication, for the advantages. From the sound of it, the advantages probably aren't relevant to you, but I'm confident they exist for us. - We only have to store a comparatively small amount of user data for the lifetime value of each user, so the actual database cost is marginal (i.e. data duplication is not expensive to us, as long as reads are fast). (Development) time is (very) expensive. - Rapid development and iteration. I can make additions and make fields redundant with ease in response to customer feedback, my own development choices, and changing product needs. There's no API update needed, as reads are client side. I have data model classes which store the reference in the database, some constructors, and getters and setters (About the same as any other data model + API would have). The release goes live and data just starts being manipulated in a new way. Alterations can have simple defaults with || in JS and ?? in Swift (even if it's just an empty string). - Minimal backend. Our application doesn't require much backend logic, and that which we do have is mostly event driven with Firebase Functions, upon certain database writes. Works out quite cheap too (for now - most usages don't trigger any functions). - Caching, and offline first. We take the approach that it doesn't matter if there's a short delay in 95% of our data updating - either due to patchy mobile signal or the data taking time to sync down. Firebase has made on device caching and real time updates to that data a breeze. With GeoFire, we've got instant map search (keys are cached by Firebase and contain sufficient information to filter, etc). - Declarative security rules are very powerful, and can even reference values in the database itself for security control - effectively. We store a permissions tree in the database for our more complex security logic. As a bonus, additional features like Authentication, Analytics, Push Notifications, etc are all convenient to have bundled up. As you can see, most of this isn't about the database tech, but the development speed for us. The perceived costs and inefficiencies may exist in the database itself, but there are massive advantages for the application development as a whole.

cyberferret 10y ago
I should probably clarify that I really liked Firebase, for all the reasons you mentioned above. It was really the schema restrictions, and (at the time) the lack of relational functionality that made me scrap it for a mobile app project that I was working on at the time. This was very early days too, and I believe Firebase has improved considerably since then, and indeed I did a little "fun" project in it recently [0] (directly related to HN actually) and enjoyed the process. I may still revisit it sometime, and see if it will suit another project. [0] - https://hackernoon.com/tophn-a-fun-side-project-built-with-vue-js-and-rethinkdb-f22159641c1b https://hackernoon.com/tophn-a-fun-side-project-built-with-v...

gregmac 10y ago

I've largely abandoned ORMs that generate SQL queries for me. A mapping layer that takes a SQL result and stuffs it into an object (DTO) is useful, but anything beyond that is more trouble than it's worth. For complex SQL, it's better to write it by hand. You get to tune it and ensure the indexing is correct (either the query usage, or create/modify your indexes), and you get to see exactly what it's doing. For even simple conditional joins, there's something nice about knowing exactly what's going on, and knowing it's not going to be doing something that results in a nested join loop (eg: the type of slow query you don't notice until you're using it in production and performance suddenly drops). I'm a big fan of Dapper for this, so a lot of my data layer code looks like: public IEnumerable<Order> LoadOrders(int customerId) { using (var db = GetConnection()) { return db.Query<Order>("SELECT * FROM orders WHERE customerId = @customerId", new { customerId }); } } The one exception I make to ORM-generated code is INSERT/UPDATE queries. For most cases, the ORM doesn't have much it can screw up, and since it's essentially just mapping code ("Name = @Name, Address1 = @Address1, etc") it's more likely I'll make a typo or copy/paste error than anything else. I've spent too many hours debugging crappy ORM queries, and I find it generally takes twice as long (with quadruple the frustration) to get the ORM to generate the complex SQL you want it to vs just writing the SQL.

senorjazz 10y ago
I fully agree. For simple things ORMs can be useful (very simple) anything else they get in the way. Firstly, have to write out the SQL, then convert it to the ORM format, only to find the ORM doesn't support something you are using, so you rewrite the query in a more long winded way, then convert it to the ORM.
- gregmac 10y ago
  > Firstly, have to write out the SQL, then convert it to the ORM format Ha, you know I had actually forgotten about that, but I have done that many times. You model the data, write some queries to essentially test out the structure/data/concepts, then instead of pasting the queries into your code, you spend another bunch of time trying to tell your ORM how to come up with the query you pretty much already have. Even if you're using a very well-tested, robust ORM that flawlessly handles your use case and you're an absolute expert at using it, it's still more work than just pasting a query you already have.

samirillian 10y ago

To be clear, though, you're talking more about the Relational Model than SQL per se.

cryptonector 10y ago
There aren't very many query languages for relational DBs that come close to SQL's power.

pkulak 10y ago

Most projects I write eventually turn into an RDS and Redis. Storing non-relational data can be super handy, and putting it somewhere where you know it can disappear at any point keeps you disciplined. haha

cryptonector 10y ago

Typically there are two arguments for NoSQL: "no SQL!" (i.e., "the SQL language is so ugly, difficult, and painful") and "no ACID" (i.e., eventually consistent, hopefully, maybe). The first one is always demonstrated to be a terrible argument when someone creates a SQL alike for whatever NoSQL we're talking about. The second one has been a more durable argument. But transactional semantics clearly are independent of query language! And technologies like Lustre and Spanner show that one can still have some measure of traditional transactional semantics in distributed systems (filesystems and databases, respectively). There are many applications where some degree of "eventually-consistent" is a great tool for making them scale, but this is often a function of what can be done in the event that inconsistencies create problems (e.g., a store selling more items of some product than it has available, a case where the store can refund the customer or delay delivery). I'm extremely skeptical of NoSQLs, as you can tell. I would say that NoSQLs have NoPLACE. (Certainly as to that first argument.) I'm also extremely skeptical of ORMs. So far as I can tell ORMs only ever add a layer of headaches. They seem to aim for simplifying the DB experience for developers and users, but the moment you step out of the small world of queries made simpler by the ORM... you're in for a world of hurt.

Zak 10y ago
I'd actually quite like a relational database with a language that isn't SQL. I find SQL's syntax pretty irregular and awkward (e.g. update having a different syntax from insert), find the tools available for abstracting and composing queries (views) underwhelming and hate that schemas aren't declarative. The first complaint derives largely from the SQL language being intended to read link English text. Languages like this show up regularly, seemingly based on the misconception that syntax is what's hard about programming. Most of them fail, as they should. SQL didn't, because it was tied to the exceptionally useful relational database. The second is best illustrated by showing an alternative approach as an example: Korma for Clojure (http://sqlkorma.com/ http://sqlkorma.com/). It's easy to compose queries from simpler queries, just as we compose functions from simpler functions. Views provide some of this. The third, if it's solvable would make using relational databases much nicer. Instead of schemas being manually-created diffs full of imperative statements like create and alter, make them declarative and have software generate the diff. If transformations on the data itself are required between versions, require it to include a pure function describing said transformation. By default, require a function to transform it back as well unless it explicitly says what data is to be discarded.

manigandham 10y ago

What does this have to do with the article? SQL - the query language - is completely separate from any database that implements it.