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.

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.

While true to a degree, it's worth noting that some languages also reduce the number of types considerably at the same time, and maybe even provide some other conveniences when dealing with types (e.g. automatic interpretation as numeric when used in a numeric operation).

You still have to keep types in your head, but the reduction of types generally makes this non-problematic. Although, as your code grows and you define your own types (either formally through an included system or as ad-hoc data structures), you start needing to keep more types in your head, and this starts eating away at any benefit you had from not having to define them in the first place.

As you create any non-trivial program, you'll likely create some types, whether in C or Perl (as classes). What was initially a benefit in Perl, since you didn't have many typed to keep track of, becomes a liability, as there are more types but still no formal way to ensure they are being used in the correct locations and as expected arguments without runtime checking (and manual checking at that, unless you use a good module to handle a lot of that for you).

That some languages keep types fairly low level (C, with typed relating fairly closely to machine representation) and some are high level (Perl, with each type being a higher level container) is really irrelevant. In the end, they are just labels that codify behavior (size, acceptable methods of use).

In SQL and NoSQL systems, you have the same trade-offs. Do I define everything up-front so I know (presumably) when there's a problem, or do I create the structure ad-hoc as needed and enforce the structure through the function of the application that creates the data?

Also, sometimes we make classes out of scalars, arrays and globs as well. It was interesting the first time I looked into the source of IO::Handle (and IO).

Hmm, I bet there's a module to create classes that uses pack and unpack to store byte level representations in a scalar as the blessed type of a class.

I guess there could be a few senses of types for a programming practitioner:

1. integral types (int, float, etc) 2. product types (structs, objects) 4. sum types (enums) 5. containers (vec, dict, list, etc).

Typeless types would be dicts of lists of dicts. i.e. shitty product types. e.g. `x = dict(y=1, dict(z=[1,2,dict(u='good data')]))`. Passing this to functions that want to get 'good data' out of the structure basically end up treating this as a type, if that makes sense.

If I understand kbenson's point, it's that sometimes this is better than a tower of babel of type hierarchies that you may have confronted in Java or C++ and you end up needing to ponder how to convert your IWiggleWoggleSupplierProvider into an IWoggleWiggleProviderSupplier even though they do almost the same thing and have the same data but somehow they have different inheritance hierarchies.

From that perspective, "treating the shape of your dicts as its type" certainly makes sense - it determines how you can use it correctly. It's incidental that you're not encoding this information in the "type system" your language provides (and that may or may not be a good idea, depending - there are always tradeoffs and we've left an awful lot of context unspecified).

From the same perspective, if you can use your IWWSProvider and your IWWSSupplier identically, we can say that they're arguably not different types and only being treated so because someone has given the Java or C++ "type system" an incorrect specification.

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.

There is one case where Hibernate can save you a significant amount of time vs plan SQL, and that's arbitrary update statements. But the moment there are two joins in there, predicting hibernate performance is not necessarily easy, and differentiating a query that will run well from one that will kill your system at scale is far harder than in plan SQL. In practice, the only way to use Hibernate responsibly is to examine the likely queries that it will generate.

My least favorite example was a situation where manual SQL gave me a very fast query that took a couple of milliseconds to run on a large database, but Hibernate's answer was not even just one query: It was an accidentally quadratic monstrosity, when the original programmer wanted was one row: Hibernate just wasn't smart enough to figure out which row it wanted in the DB, so it grabbed half the database. After I saw the beautiful queries that Hibernate had created, I had to give the finger to the existing data layers and do my own thing, which I am told remains to this day.

The way Hibernate is built only makes sense if it can hide the database behind it. In reality, it doesn't, so I not only have to make sure everyone knows SQL well, but they also have to learn hql and hibernate's most common pitfalls. All to save a little bit of time on the rare cases where I might have very dynamic queries that somehow hibernate won't screw up.

Java EE was a pretty unfortunate thing, but under duress, I'd much rather use EJB3 and straight SQL over the Spring + Hibernate stack that many of us had to deal with between 2000 and 2010.

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.

I wouldn't even know what metric to use to quantify that. Number of tables? On the order of hundreds, probably low hundreds... definitely not thousands.

It may just be that I've been lucky in that the data models I've worked with have been things that map well to using Hibernate. I will say that I haven't worked with a lot of data models where you need more than 3 or 4 joins in any given query. I know some people write far more complex queries than that, and maybe those are the kinds of scenarios that Hibernate doesn't handle well?

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

Do you hand-roll your own caching mechanism when just writing plain SQL?

Using something like jOOQ has meant I rarely need any caching, unlike Hibernate where caching almost seems required.

https://www.jooq.org/doc/3.9/manual-single-page/#arrays-maps...

Never touched it again since then (not doing much Java anyway).

More recently, we've had n+1 issues (putting a printing breakpoint in EntityType.loadByUniqueKey helped me track down where). My hack to get around that was to do a bulk Criteria query up front to pre-load the session cache with the entities that were being fetched one at a time.

There were a lot of quirks that I've had to learn over the last decade, and I'm still discovering more.

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.

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

A lot of inexperienced devs look at NoSQL as being simpler than SQL because the dev documentation is shorter and has a more stylish theme. They then assume that all the touted benefits of SQL are YAGNI and they'll cross that bridge when they come to it.

The mistake they make is assuming that the benefits of SQL databases are something that only is needed for a mature application. To the contrary, SQL gives an incredible hedge against the uncertainty of change in the early days of an app when churn is high. A normalized data structure allows much more query flexibility; you should only denormalize for performance once you realize what the workload and logic will look like for the long haul.

Of course if you have a known workload that you want to scale up huge then that's a legitimate use case for NoSQL, but the purpose is not flexibility.

Scale ruins everything. Most SQL databases are pretty terrible at schema maintenance at scale (i.e. hundreds of MySQL shards). This (among other ways that scale ruins the RDBMS) is what led to NoSQL in the first place at the largest of web properties.

Sure, joe average startup probably doesn't have the same problems. But that's marketing.... The relational model and SQL have been beaten up for decades by those who prefer to remain closer to the metal.

I recommended that JPA Repositories shouldn't be stored at the lowest level of Maven jar package's eg... we have a very deep nested Maven pom dependencies (about 6 layers deep) of which the JPA repositories are defined in the lowest of lowest levels. Granted I gave references to research papers from Google that JPA Repositories and Hibernate ORM should be at the project layer (due to their nature of changing rapidly).

Turns out, still going with the NoSQL solution. Although it seems like everyone have convinced themselves that NoSQL is faster development processes than a simple table with constraints.

Since my experience is probably just limited, I'm curious if there is an easy to explain example where this is incorrect? (Or I guess maybe most people disagree with me and see my above opinion as always/most of the time incorrect)

I've seen too often jobs being quoted for clients for simply adding a column to table blow out to 4-5 month development tasks. Turns out the table was being used by an old C/C++ program with oracle C bindings. That had to be updated, and that in turn caused another application to stop working.

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;

    CREATE VIEW public.customers AS
        SELECT c.id, c.contact_name, c.email
        FROM crm.customers AS c;

    CREATE VIEW public.customers AS
        SELECT c.id, c.contact_name, c.email
        FROM crm.customers AS c
        WHERE deleted = false;

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

There is a place for unorganized, disorganized, unengineered, unplanned data structures, but its not in the DB.

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.

That's because the column name is part of the RDBMS' structure, as is the type, for the most part. If you want to read a column and use the row values as the column names, that can be done, it's just a query. The DBMS is going to have to scan the whole table (or lean on the index) for that column anyway. I can't think of why I'd want the RDBMS to do that for me and imply the column names, rather than me implement it in code. I'm managing the column names as data at that point. That's if you still think it's a good idea.

The other piece that jumps out at me is that when you do something like that, you're really building a variably wide table, but not letting the DBMS manage it as a wide table. Instead, every query is going to require either you or the DBMS to transpose rows to columns at some point in the query - if it's a simple select, only on the return, which won't be that expensive, but some joins are going to require it to happen earlier. I'm not smart enough to know whether it'll be computationally expensive to the point of being a showstopper, but I do know it'll require a lot of memory if you want it done fast, or you have to do it slow in scratch/spool.

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.

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.

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.

I'm currently implementing a mixed solution where clearly defined properties (and thus ID) have columns and constraints but I also include a schema-less "all you can eat" jsonb field so that experimental properties can be stored.

Upon each release I will migrate validated shema-less properties to static columns or even sub-tables. Similarly if a column is remove on next iteration I might transfer attributes to the jsonb to revert it later if needed. This does include a migration step but this seems only like good practice. Of course scaling... but I'm really not limited by this now.

The client will interact with the model through views or functions so that he don't have to care if columns are real or not. But anyway as soon as PK/FK are needed I will probably straight-out think that jsonb will be insufficient and take 5 minutes to think of a relational way to implement.

I don't have a lot of experience with it, but Postgres supports indexing on jsonb fields. If you do end up in a situation where your keys are in the right place and you have some data in a jsonb, you may still be able to get at it efficiently.

Part of this is how we approach development as well. For one, I'm now some management type that makes powerpoints, so any code I write is on my own projects. But when I did more of this, my approach was always to start with an RDBMS unless I had a pretty specific use case to not use one. Having done it long enough, I am being honest that I find maintaining a database schema, migrating changes, and all of that to be pretty trivial. That's my approach, but I don't see an issue with yours if you think it's making you more efficient.

I should ask, because I'm assuming it, but make sure whatever you're stuffing into the jsonb field is true row-level data. Try to avoid putting data in there that "belongs" on another row - at the extreme it could provide some unintended exposure. Hypothetically, a row containing Customer A's demographics in the "person" table has a jsonb field - it may be tempting to put some basic demos about other customers B, C and D connected to that customer A in some way, but that's data you'd not want to expose as belonging to Customer A. Yet, if it comes back on the same row, it's as trivial to leak it as it is to retrieve it.

EDIT: To actually answer your question, I don't see how schema-less can have foreign constraints. Foreign constraints are defined in the schema. What I was saying is that if you're going to need joins later, have enough schema to support that.

I've only start to explore all possibilities, but main use so far:

-Store experimental properties before next schema revision

-Particularly good alternative to composite-type in early stage dev if you want to make live modifications without replaying the whole deployement script. (Composite type usually have huge cascading constraints)

-Storing "metadata row" for later use

PS: Actually it may even be an alternative to composite type in production because I can totally picture case when you need row level "objects" that vary depending on the value of another column. Yes you can normalize, but it might be more maintainable to keep everything in one table an write an appropriate check constraint. And it will still be easily human readable as opposed to a literal representation of a custom type or whatever (think of PostGIS geometry type that is very versatile but totally opaque to humans).

In MySQL 5.7 you can have tables with JSON columns (like PostgreSQL's JSONB), and also "Generated columns" which are expressions over other columns (e.g. extract a value from the JSON). You can then use those generated columns as the source or destination of foreign key constraints.

On those "generated columns" you can create indexes, reference other tables (foreign keys) or be referenced by other tables (foreign keys).

MySQL has a binary JSON data type (like most databases) and also expressions to extract data from those JSON fields.

MySQL has a lot of disadvantages vs PostgreSQL, but having foreign keys on elements within JSON (via this mechanism) is a thing MySQL can do that PostgreSQL can't :) (Unless I'm mistaken?)

However on a solution I've implemented on PostgreSQL I expand jsonb as regular field in a view and made that view editable via trigger. This way users can update jsonb_field without being aware of because they edit it through a view as if they are normal fields.

It is fundamental to the relational model that metadata like that is stored in and queryable from relations just like normal data.

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.

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.

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

An example that uses the Spring Exception translator: https://github.com/jOOQ/jOOQ/blob/master/jOOQ-examples/jOOQ-...

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.

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.

It has a name. Schema-on-write (static schema) vs. schema-on-read (dynamic schema).

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 });
        }
    }

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.

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.

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.

Or you could avoid the conversion step entirely. If the ORM is useful 90% of the time and in the way 10% of the time then use different tools for the 10%.

It still sort of generates SQL for you, but really you end up writing the SQL yourself except in a type-safe way.

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.

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

I do prefer the more fluent-like interfaces though... MongoDB and RethinkDB in node for example, or using the LINQ extension methods with Entity Framework (not LINQ syntax itself, which I find much more difficult). Though with LINQ, it's often interesting how twisted what gets queried from the data source can be vs. what you actually need... but that's a problem with most ORMs.

I do find that SQL queries with Node/JS adapters is pretty damned natural compared to any ORM for Java or C# imo...

Then, "make them declarative and have software generate the diff". So you want the benefits of relational data without the pain, is that it? Why not use Hibernate like the oh so wise ones have told us to use only to now realize trying to bend relational tools into what a language designer's ideas are is maybe, probably, not the best idea after all and perhaps the reason that SQL and relational databases have withstood the test of time and Hibernate is on the road to death is that SQL and relational design were extremely, unabashedly well thought out and designed in the first place.

You read that almost right. See, I don't think the average human who's fluent in English but not SQL will be able to properly understand anything but the most basic queries. Sure,

    SELECT * from users where username = 'remotehack';

    (select users (where {:username "remotehack"}))

Now, give that same average person

    Select Distinct On (u.username, u.email)
         u.username
        ,u.email
        ,l.browser
        ,l.login_time
    From users u
    Join logins l On l.username = u.username
    Order By u.username, login_time Desc

And no, when I ask for declarative schemas, I'm not asking for ORM. I'm asking for a system where I say what the schema is instead of saying how to create it, just as I say what data I want rather than how to retrieve it. This is not incompatible with any of the fundamental concepts of relational databases.

They exist, e.g., Dataphor, Rel, etc. But it's hard to displace SQL.

Perhaps you want a Prolog. :)

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.

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