5 ms·

So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL s

So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL string concatenations because some genius that also hates ORMs decided to do a giant project that way? Then you call your alternative anti-orm and it generates... orm specs, and then you say "migration commands based on the git history"... what does that even mean? What does git history has to do with the database?

Aeolun 7y ago
> What does git history has to do with the database? Hopefully a lot. Non-versioned database schemas are the devil.
- netfl0 7y ago
  Indeed this is a bad idea for many reasons. I tried to think about it from a gitflow release cycle perspective to be generous. This would mean constant churn of migration code for each branch of the repo. If you have complex migrations which go beyond simply renaming columns, it would require you to write special migration code on a release branch which would then need to be merged back. yuck!
  - reificator 7y ago
    I think you and the post you're replying to have opposite opinions, despite your post seemingly starting off by agreeing with them.
    - netfl0 7y ago
      No, I agree with the post I replied to in that migrations should be human versioned as opposed to auto-versioned via a VCS. Again, I was being generous to the author trying to imagine a scenario where this would not create a dumpster fire. Hopefully I have cleared that up.
      - evanelias 7y ago
        This isn't quite the same as being solely git-based, but there are numerous examples in the industry of successful use of auto-versioned declarative schema management. By this I mean, you have a repo of CREATE statements, and a schema change request = making a new commit that adds, modifies, or removes those CREATE statements. No one actually writes a "migration" in this approach. Facebook, who operate one of the largest relational database fleets in the world, have used declarative schema management company-wide for nearly a decade. If engineered well, you end up with a system that allows developers to change schemas easily without needing any DBA intervention.
        netfl0 7y ago
        This is interesting but I am curious about some of the implications. I am under the impression that migrations encompass more than schema changes. It may require data transformations, index rebuilds, or updates to related tables. I would agree that these things are preferably avoided, but in practice it seems that they happen frequently. I viewed the author as targeting smaller dev shops who don't have a dedicated DBA/expert. Groups who might use an ORM's migration framework for example. In which case, it would appear that there is significant loss in migration flexibility if you remove application code from the architecture.
        evanelias 7y ago
        When operating at scale, it becomes necessary to have separate processes/systems for schema changes (DDL) vs row data migrations (DML) anyway. For example, say you need to populate a new column based on data in other columns, across an entire table. If the table is a billion rows, you can't safely run this as a single DML query. It will take too long to run and be disruptive to applications, given the locking impact; it will cause excessive journaling due to the huge transaction; there can be MVCC old-row-version-history pileups as well. Typically companies build custom systems that handle this by splitting up DML into many smaller queries. It's then no longer atomic, which means such a system needs knowledge about application-level consistency assumptions.
  - shkkmo 7y ago
    In what way is manually tracking the needed DB changes per feature branch even possibly better? Even if you are not running your migrations automatically on deploy, I would still check those migrations into version control. Having auto-runnable migrations checked in alongside the code that depends on them allows you to review and test those migrations as part of your normal code review and CI processes. I think there must be some misunderstanding here because I don't understand why you say either of these things: > This would mean constant churn of migration code for each branch of the repo. > it would require you to write special migration code on a release branch There should only a be at most a few migrations per feature branch and merging is only an issue when you have two feature branches with inconsistent schema definitions. You should only have to add custom migrations to a release branch to fix bugs and those should be merged back into your develop branch as soon as possible to minimize effort spend merging later.
LaGrange 7y ago
> So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL string concatenations because some genius that also hates ORMs decided to do a giant project that way? Those aren't the only alternatives. Query builders exist.
- singron 7y ago
  A lot of languages have pleasant multi-line string literals. Also if you have a 1000 line hand-crafted SQL query, it's probably not going to be pleasant in your ORM either.
alexandercrohde 7y ago
So I want to respond to this because there is a genuine question behind your rhetoric. And that question is "How do I cleanly do complex things in SQL without an ORM?" And there is an answer to that. The idealistic answer is good DB design from the start. The realistic answer is by using views (sometimes materialized views) to create a clean representation out of odd tables. The monster query can be decomposed into simple and clean operations against these views (and if necessary, views upon views). The only caveat is you need a SQL expert to understand the performance if you want to do this at scale.
- netfl0 7y ago
  There were multiple literal questions embedded in his rhetoric; which without answers, invalidate the author's solution.
- bb88 7y ago
  > The idealistic answer is good DB design from the start. The problem with this statement is that DB schemas like code can evolve badly over time, even if they're maintained by a DBA sometimes (since they're human afterall too). And then the DBA starts to suggest that you use things like triggers in your DB, which may or may not be good, but often surprise developers. "I just wrote 0 into the DB, why did it turn into NULL? What line of code did that? Ohhhh... there's a trigger."
- zzzeek 7y ago
  I wrote SQLAlchemy after a five year job where all we did was work with enormous views in an Oracle database. They performed nightmarishly bad. The slightest attempt to query from one of these views using something as heretical as a join would kill the database. The source code for the views was locked up tight by DBAs who had no idea how our application worked, didn't care, and they could never be bothered to help us with their horrendously inefficient views or to give us new ones that our application sorely needed in order to get data from the database in an efficient manner. SQLAlchemy exists exactly because of the pain the "just use views" approach causes. The entire point is that you can structure a query of arbitrary complexity in a succinct manner using Python structures, while not losing any of the relational capabilities. Of course you can write queries that are too complex, if you're then lucky enough to be able to have materialized views at your disposal, you can turn the SQL you wrote into one. But that's an optimization you can use if you need it, or not.
singron 7y ago
> "migration commands based on the git history"... what does that even mean? It actually seems like a really cool idea. E.g. * Commit A: create a schema.sql file "create table t (a INT);" * Commit B: update the schema.sql to "create table t (a INT, b INT);" * The tool can generate a migration from A..B "alter table t add column b INT;" It seems like it probably would work really well for creating new tables and columns in simple projects. In more complex projects I've worked on, we usually had more complicated migrations to update the data to fit the new schema, and we often had to be careful to interleave schema, data and code updates (especially if you wanted to be able to rollback).
- DaiPlusPlus 7y ago
  Automatic migrations using only source-code snapshots don’t work for things like column renames or splitting/combining columns. Every tool I’ve used requires some migration metadata.
yellowapple 7y ago
Any SQL wrapper worth its salt provides some mechanism to specify positional (or even named) parameters within that string (without resorting to string interpolation or concatenation) and pass them in when executing the query. Usually it'll be something like foos = execute_sql("SELECT * FROM foo WHERE bar = ? AND baz = ?", [bar, baz]) If you're resorting to concatenating strings, then you're almost certainly opening yourself up to SQL injections (and rather needlessly, I might add!).
- kelnos 7y ago
  I think the issue the parent is referring to is when you have optional clauses in your statement. Like if you have a table with 5 update-able columns (where someone might want to update any 1 to 5 columns that you don't know in advance), and you want some generic code that updates a row, if you're writing raw SQL then you have to do concatenations to build up the "set clause": UPDATE sometable SET {set clause} WHERE id = ? The "set clause" might just be "foo = ?", but it might be "foo = ?, bar = ?", or "foo = ?, baz = ?", etc.
  - squiggleblaz 7y ago
    So you mean I'm writing code like this, in a language somewhat like Typescript but simplified to my end. function update(id: ID_TYPE, value: Partial<sometable>) { const set_clause = value.keys.map(k => k + " = ?").join(", ") const params = value.values.concat([id]) db.query("UPDATE sometable SET " + set_clause + " WHERE = ?", params) } How is code not terrible? It shouldn't pass code review. Once you concatenate strings to generate your sql, all bets are off. There will be a day when your object doesn't match your assumptions. Either you have a series of different functions, each of which will change under different circumstances; or you should just be generating this function like this directly from the schema in which case you might as well just generate code you want at build time so there's string concatenations anywhere. Is there some other circumstance when you want that?
    - kelnos 7y ago
      It is terrible. That's the problem. No one wants to write code like that, so they turn to an ORM to do it for them, in a hopefully safer way.
evanelias 7y ago
> "migration commands based on the git history"... what does that even mean? What does git history has to do with the database? I'm not sure what git-specific interaction the author is envisioning, but more generally: declarative schema management tools are based on the notion of using a repo to store only CREATE statements. It's an infrastructure-as-code style approach, where to modify an existing table, you just modify the CREATE definition. The tool figures out how to translate this to an ALTER. I'm the author of an existing tool in this space, Skeema [1], which supports MySQL and MariaDB. Other recent tools in this space include migra [2] and sqldef [3]. From what I understand, in the SQL Server world there are a number of others. [1] https://www.skeema.io https://www.skeema.io [2] https://github.com/djrobstep/migra https://github.com/djrobstep/migra [3] https://github.com/k0kubun/sqldef/ https://github.com/k0kubun/sqldef/

Aeolun 7y ago

> What does git history has to do with the database? Hopefully a lot. Non-versioned database schemas are the devil.

netfl0 7y ago
Indeed this is a bad idea for many reasons. I tried to think about it from a gitflow release cycle perspective to be generous. This would mean constant churn of migration code for each branch of the repo. If you have complex migrations which go beyond simply renaming columns, it would require you to write special migration code on a release branch which would then need to be merged back. yuck!
- reificator 7y ago
  I think you and the post you're replying to have opposite opinions, despite your post seemingly starting off by agreeing with them.
  - netfl0 7y ago
    No, I agree with the post I replied to in that migrations should be human versioned as opposed to auto-versioned via a VCS. Again, I was being generous to the author trying to imagine a scenario where this would not create a dumpster fire. Hopefully I have cleared that up.
    - evanelias 7y ago
      This isn't quite the same as being solely git-based, but there are numerous examples in the industry of successful use of auto-versioned declarative schema management. By this I mean, you have a repo of CREATE statements, and a schema change request = making a new commit that adds, modifies, or removes those CREATE statements. No one actually writes a "migration" in this approach. Facebook, who operate one of the largest relational database fleets in the world, have used declarative schema management company-wide for nearly a decade. If engineered well, you end up with a system that allows developers to change schemas easily without needing any DBA intervention.
      - netfl0 7y ago
        This is interesting but I am curious about some of the implications. I am under the impression that migrations encompass more than schema changes. It may require data transformations, index rebuilds, or updates to related tables. I would agree that these things are preferably avoided, but in practice it seems that they happen frequently. I viewed the author as targeting smaller dev shops who don't have a dedicated DBA/expert. Groups who might use an ORM's migration framework for example. In which case, it would appear that there is significant loss in migration flexibility if you remove application code from the architecture.
        evanelias 7y ago
        When operating at scale, it becomes necessary to have separate processes/systems for schema changes (DDL) vs row data migrations (DML) anyway. For example, say you need to populate a new column based on data in other columns, across an entire table. If the table is a billion rows, you can't safely run this as a single DML query. It will take too long to run and be disruptive to applications, given the locking impact; it will cause excessive journaling due to the huge transaction; there can be MVCC old-row-version-history pileups as well. Typically companies build custom systems that handle this by splitting up DML into many smaller queries. It's then no longer atomic, which means such a system needs knowledge about application-level consistency assumptions.
- shkkmo 7y ago
  In what way is manually tracking the needed DB changes per feature branch even possibly better? Even if you are not running your migrations automatically on deploy, I would still check those migrations into version control. Having auto-runnable migrations checked in alongside the code that depends on them allows you to review and test those migrations as part of your normal code review and CI processes. I think there must be some misunderstanding here because I don't understand why you say either of these things: > This would mean constant churn of migration code for each branch of the repo. > it would require you to write special migration code on a release branch There should only a be at most a few migrations per feature branch and merging is only an issue when you have two feature branches with inconsistent schema definitions. You should only have to add custom migrations to a release branch to fix bugs and those should be merged back into your develop branch as soon as possible to minimize effort spend merging later.

LaGrange 7y ago

> So you know one guy that quit over having and ORM instead of using SQL... do you have any idea how many quit due having to deal with thousands of lines of SQL string concatenations because some genius that also hates ORMs decided to do a giant project that way? Those aren't the only alternatives. Query builders exist.

singron 7y ago
A lot of languages have pleasant multi-line string literals. Also if you have a 1000 line hand-crafted SQL query, it's probably not going to be pleasant in your ORM either.

alexandercrohde 7y ago

So I want to respond to this because there is a genuine question behind your rhetoric. And that question is "How do I cleanly do complex things in SQL without an ORM?" And there is an answer to that. The idealistic answer is good DB design from the start. The realistic answer is by using views (sometimes materialized views) to create a clean representation out of odd tables. The monster query can be decomposed into simple and clean operations against these views (and if necessary, views upon views). The only caveat is you need a SQL expert to understand the performance if you want to do this at scale.

netfl0 7y ago
There were multiple literal questions embedded in his rhetoric; which without answers, invalidate the author's solution.
bb88 7y ago
> The idealistic answer is good DB design from the start. The problem with this statement is that DB schemas like code can evolve badly over time, even if they're maintained by a DBA sometimes (since they're human afterall too). And then the DBA starts to suggest that you use things like triggers in your DB, which may or may not be good, but often surprise developers. "I just wrote 0 into the DB, why did it turn into NULL? What line of code did that? Ohhhh... there's a trigger."
zzzeek 7y ago
I wrote SQLAlchemy after a five year job where all we did was work with enormous views in an Oracle database. They performed nightmarishly bad. The slightest attempt to query from one of these views using something as heretical as a join would kill the database. The source code for the views was locked up tight by DBAs who had no idea how our application worked, didn't care, and they could never be bothered to help us with their horrendously inefficient views or to give us new ones that our application sorely needed in order to get data from the database in an efficient manner. SQLAlchemy exists exactly because of the pain the "just use views" approach causes. The entire point is that you can structure a query of arbitrary complexity in a succinct manner using Python structures, while not losing any of the relational capabilities. Of course you can write queries that are too complex, if you're then lucky enough to be able to have materialized views at your disposal, you can turn the SQL you wrote into one. But that's an optimization you can use if you need it, or not.

singron 7y ago

> "migration commands based on the git history"... what does that even mean? It actually seems like a really cool idea. E.g. * Commit A: create a schema.sql file "create table t (a INT);" * Commit B: update the schema.sql to "create table t (a INT, b INT);" * The tool can generate a migration from A..B "alter table t add column b INT;" It seems like it probably would work really well for creating new tables and columns in simple projects. In more complex projects I've worked on, we usually had more complicated migrations to update the data to fit the new schema, and we often had to be careful to interleave schema, data and code updates (especially if you wanted to be able to rollback).

DaiPlusPlus 7y ago
Automatic migrations using only source-code snapshots don’t work for things like column renames or splitting/combining columns. Every tool I’ve used requires some migration metadata.

yellowapple 7y ago

Any SQL wrapper worth its salt provides some mechanism to specify positional (or even named) parameters within that string (without resorting to string interpolation or concatenation) and pass them in when executing the query. Usually it'll be something like foos = execute_sql("SELECT * FROM foo WHERE bar = ? AND baz = ?", [bar, baz]) If you're resorting to concatenating strings, then you're almost certainly opening yourself up to SQL injections (and rather needlessly, I might add!).

kelnos 7y ago
I think the issue the parent is referring to is when you have optional clauses in your statement. Like if you have a table with 5 update-able columns (where someone might want to update any 1 to 5 columns that you don't know in advance), and you want some generic code that updates a row, if you're writing raw SQL then you have to do concatenations to build up the "set clause": UPDATE sometable SET {set clause} WHERE id = ? The "set clause" might just be "foo = ?", but it might be "foo = ?, bar = ?", or "foo = ?, baz = ?", etc.
- squiggleblaz 7y ago
  So you mean I'm writing code like this, in a language somewhat like Typescript but simplified to my end. function update(id: ID_TYPE, value: Partial<sometable>) { const set_clause = value.keys.map(k => k + " = ?").join(", ") const params = value.values.concat([id]) db.query("UPDATE sometable SET " + set_clause + " WHERE = ?", params) } How is code not terrible? It shouldn't pass code review. Once you concatenate strings to generate your sql, all bets are off. There will be a day when your object doesn't match your assumptions. Either you have a series of different functions, each of which will change under different circumstances; or you should just be generating this function like this directly from the schema in which case you might as well just generate code you want at build time so there's string concatenations anywhere. Is there some other circumstance when you want that?
  - kelnos 7y ago
    It is terrible. That's the problem. No one wants to write code like that, so they turn to an ORM to do it for them, in a hopefully safer way.

evanelias 7y ago

> "migration commands based on the git history"... what does that even mean? What does git history has to do with the database? I'm not sure what git-specific interaction the author is envisioning, but more generally: declarative schema management tools are based on the notion of using a repo to store only CREATE statements. It's an infrastructure-as-code style approach, where to modify an existing table, you just modify the CREATE definition. The tool figures out how to translate this to an ALTER. I'm the author of an existing tool in this space, Skeema [1], which supports MySQL and MariaDB. Other recent tools in this space include migra [2] and sqldef [3]. From what I understand, in the SQL Server world there are a number of others. [1] https://www.skeema.io https://www.skeema.io [2] https://github.com/djrobstep/migra https://github.com/djrobstep/migra [3] https://github.com/k0kubun/sqldef/ https://github.com/k0kubun/sqldef/