4 ms·

Even then, the implementation of consistency levels alone makes you have to rethink swaths of your code when you switch databases. This is why I dont really car

by hobs 4y ago

Even then, the implementation of consistency levels alone makes you have to rethink swaths of your code when you switch databases. This is why I dont really care, changing data stores is almost never trivial even if you just use them for data storage. What if the type isn't supported, what limitations on reading and writing exist in THIS engine? Which types of queries are weirdly slow and amazingly fast? The list goes on for things that you don't need to add additional logic to.

wruza 4y ago
This is why I dont really care, changing data stores is almost never trivial even if you just use them for data storage Agreed. It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Personally I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db.
- Clubber 4y ago
  >It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Many ORM's can solve this problem. >I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db. I've had to do it three times in my career. It usually comes from management. It ain't pretty. Two were from M&A and one was to get rid of an obsolete database.
  - Arch-TK 4y ago
    I think "solve" is too strong a word. Many ORMs can hide the problem.
- bborud 4y ago
  I don't think the expectation that you are going to write DDL statements that will work in all SQL databases is reasonable. Which is why I never do this. My take is: it isn't going to be easy so don't even bother. I have separate DDLs for different databases. And while I've heard a lot of people theorize that this is a maintenance nightmare, it has never been that in my experience. You are going to spend a lot more time being frustrated if you try to make one DDL work for all databases. Especially if a new database is added to the mix (which might not even be an SQL database). I have written perhaps half a dozen applications in the past 3-4 years that supports more than one database. Typically for schemas somewhere in the region of 5 to 15 tables. It rarely takes me more than 5-10 minutes to take the SQLite DDL and adapt it to, say PostgreSQL, including rewriting the SQL statements in the storage API layer. And you can design with this in mind. (I often start with SQLite as it is the least feature rich database. Anything I can express for SQLite is easy to express for other SQL databases, and possibly NoSQL stores. And I can make do with "lowest common denominator" because any fancy stuff tends to just make life harder, so I tend to avoid it unless I have very, very specific needs) Granted, it is a bit annoying to do schema updates across several DDLs, but so far, I've spent far less effort than a colleague of mine who always tries to maintain a single DDL and a single storage API layer implementation that is supposed to work across 2-3 SQL databases. Also granted that it isn't elegant to maintain different DDL and store API implementations, but it turns out to not be as much work as people tend to claim.
  - rufus_foreman 4y ago
    There are tools that can handle this, Liquibase is one that I have used. You write a change set that describes the schema you want to build and it generates DDL for whatever database you are using. It can also reverse engineer a change set from one database that you can use to generate DDL for a different database. I supported an application that ran on half a dozen different DBs and it wasn't much of a problem. There were a few places where I had to specify change sets specific to one database implementation.
    - bborud 4y ago
      I was looking at a schema generator written in Go a while back, but I can't remember the name. It almost did everything I wanted except it didn't support SQLite (since no proper grammar exists for its peculiar variant of SQL). What I'm looking for is something that can generate schemas for SQLite, PostgreSQL, MySQL and perhaps MS SQL Server that can be run from the command line (so I can use it from Make)
bborud 4y ago
I have the opposite experience: that for almost all projects I have done in the past 20 years, switching data store is almost always nearly trivial. However, it does come at the cost of giving the design careful thought and sometimes forgoing useful functionality that it is tempting to make use of. One reason for this is that I had a very bad experience with this. I was involved in two projects in the 1990s where we went all in and used a lot of features unique to two different databases. In one of the cases, the database vendor (a small niche player) got acquired by an automation company and their "real-time capable" database was no longer available for licensing as a separate product. This left us stranded. The other project involved loads of PL/SQL in an Oracle and what killed us was the combination of Oracle licensing fees, the scaling limits of the database and that it was really hard to find good PL/SQL developers. Today we use several strategies to keep the data store choice flexible. 1) we use databases as pure data stores. Sure, we use constraints, but only as an (optional) extra safeguard that lets us know when we have screwed up. We aren't dependent on the constraints, but they are immensely useful for rooting out inconsistencies in the code and the model. 2) we try to model the data in ways that can be realized on all databases (or stores) of a certain class. Yes, this means that sometimes you have to use slightly different data types than you'd like, but that isn't the end of the world. In some cases what you store is a reasonably useful representation of a type, and then you create richer types to represent it in the application. 3) we tend to start development using SQL databases, but not all parts of the schema are expressed as a purely relational model. This way we can split the schema and handle parts of it using an entirely different class of database or store. This gives us the convenience of being able to start a project with a single database, exploit the availability of constraint enforcement to show us where we make mistakes, but as we need to scale up, parts of the schema can easily be migrated to, for instance, a timeseries database. This both helps us off the ground quickly and it allows us to postpone actually building a scaled up implementation. (This has proven particularly useful on projects where we have been able to postpone handling a large scale case for years and then having more technology to choose from later). 4) to accomplish 3 we often spend a lot of time understanding what the key data access patterns for the application are going to be and keep this in mind when designing a schema and a scaling plan. It also helps if you can identify where you can decide that data is going to be immutable so that consistency becomes easier later. 5) the application logic never talks directly to the database. There is always a domain specific store service API between the application. We try to make this API as semantically narrow as possible, and try not to make any promises that would be hard to keep if we have to change storage technology. Again, it helps to partition this API into areas that can be realized with different classes of databases, so you can make fewer promises on the parts of the API that will handle the part of the schema that drives most of the operational cost. 6) all tests are written against the store service API, never directly against any database or storage technology. This way you already have a comprehensive test suite if you need to migrate to a new database entirely, or for when you start implementing different parts of the store using different database technologies. In the beginning this approach will require a bit more work, but when you have established practices and it becomes a habit, it actually doesn't represent more work to implement than more direct ways of using databases. Yes, we sometimes forego using functionality offered by the database that would have saved us some work in the short term, but it pays off in the long run because you end up with simpler and more flexible designs. A few years ago I did an experiment with two new hires. Their first task was to add support for a relatively new database to an existing application. They had zero prior experience with the application. I pointed them to the storage service API and the tests and I said "write an implementation of it that uses database X" (which was a NoSQL database). After about two days of work they had an implementation that passed all of the tests and could run all of the benchmarks without any problems. This included adding configuration options that allowed the application to initialize and run with the new store implementation. I think we discovered only 2-3 minor issues in their code over the next 12 months, which isn't bad for a first contribution to an unknown code base. (Since we only had one set of tests which was leveraged across all implementations it also meant that every time we expanded and improved the tests, we'd be able to improve all implementations as the tests got better at finding subtle mistakes). Because it was so cheap and easy to add support for new databases, I think we had support for something like 5 different databases. Including an embedded SQL database so that you could run the application locally for experimentation or integration testing (it is really nice to be able to fire up the application with an in-memory database for integration tests. It wasn't unusual that we ran integration tests that would create and destroy the storage a few hundred times in just a few seconds). After I left the project they added support for another database and migrated terabytes of production data with minimal downtime. The migration tools they wrote for this used the store service API as well, which means they developed one tool that would let them migrate between arbitrary databases. (So if, in the future, they chose to switch databases again, the migration tool already exists) We also had adapters that allowed us to run different permutations of databases. So we could handle parts of the schema suited for relational databases using an SQL database, and handle the bulky bits with somewhat narrower requirements using a NoSQL database. This requires a lot of care when you design the API to have built in "fault lines" where you can split the schema, but with a bit of practice it becomes a habit. (In some cases we've made "conditional" schemas - so for instance if you run the entire thing in one database you may have a bunch of constraints to help you ensure consistency, but you run with mixed stores, you may not apply the constraints that would span across databases. Again, we try not to depend on the constraints, but they are a great help in designing the schema, the store service API and the application).

by hobs 4y ago

wruza 4y ago

This is why I dont really care, changing data stores is almost never trivial even if you just use them for data storage Agreed. It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Personally I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db.

Clubber 4y ago
>It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Many ORM's can solve this problem. >I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db. I've had to do it three times in my career. It usually comes from management. It ain't pretty. Two were from M&A and one was to get rid of an obsolete database.
- Arch-TK 4y ago
  I think "solve" is too strong a word. Many ORMs can hide the problem.
bborud 4y ago
I don't think the expectation that you are going to write DDL statements that will work in all SQL databases is reasonable. Which is why I never do this. My take is: it isn't going to be easy so don't even bother. I have separate DDLs for different databases. And while I've heard a lot of people theorize that this is a maintenance nightmare, it has never been that in my experience. You are going to spend a lot more time being frustrated if you try to make one DDL work for all databases. Especially if a new database is added to the mix (which might not even be an SQL database). I have written perhaps half a dozen applications in the past 3-4 years that supports more than one database. Typically for schemas somewhere in the region of 5 to 15 tables. It rarely takes me more than 5-10 minutes to take the SQLite DDL and adapt it to, say PostgreSQL, including rewriting the SQL statements in the storage API layer. And you can design with this in mind. (I often start with SQLite as it is the least feature rich database. Anything I can express for SQLite is easy to express for other SQL databases, and possibly NoSQL stores. And I can make do with "lowest common denominator" because any fancy stuff tends to just make life harder, so I tend to avoid it unless I have very, very specific needs) Granted, it is a bit annoying to do schema updates across several DDLs, but so far, I've spent far less effort than a colleague of mine who always tries to maintain a single DDL and a single storage API layer implementation that is supposed to work across 2-3 SQL databases. Also granted that it isn't elegant to maintain different DDL and store API implementations, but it turns out to not be as much work as people tend to claim.
- rufus_foreman 4y ago
  There are tools that can handle this, Liquibase is one that I have used. You write a change set that describes the schema you want to build and it generates DDL for whatever database you are using. It can also reverse engineer a change set from one database that you can use to generate DDL for a different database. I supported an application that ran on half a dozen different DBs and it wasn't much of a problem. There were a few places where I had to specify change sets specific to one database implementation.
  - bborud 4y ago
    I was looking at a schema generator written in Go a while back, but I can't remember the name. It almost did everything I wanted except it didn't support SQLite (since no proper grammar exists for its peculiar variant of SQL). What I'm looking for is something that can generate schemas for SQLite, PostgreSQL, MySQL and perhaps MS SQL Server that can be run from the command line (so I can use it from Make)

bborud 4y ago

I have the opposite experience: that for almost all projects I have done in the past 20 years, switching data store is almost always nearly trivial. However, it does come at the cost of giving the design careful thought and sometimes forgoing useful functionality that it is tempting to make use of. One reason for this is that I had a very bad experience with this. I was involved in two projects in the 1990s where we went all in and used a lot of features unique to two different databases. In one of the cases, the database vendor (a small niche player) got acquired by an automation company and their "real-time capable" database was no longer available for licensing as a separate product. This left us stranded. The other project involved loads of PL/SQL in an Oracle and what killed us was the combination of Oracle licensing fees, the scaling limits of the database and that it was really hard to find good PL/SQL developers. Today we use several strategies to keep the data store choice flexible. 1) we use databases as pure data stores. Sure, we use constraints, but only as an (optional) extra safeguard that lets us know when we have screwed up. We aren't dependent on the constraints, but they are immensely useful for rooting out inconsistencies in the code and the model. 2) we try to model the data in ways that can be realized on all databases (or stores) of a certain class. Yes, this means that sometimes you have to use slightly different data types than you'd like, but that isn't the end of the world. In some cases what you store is a reasonably useful representation of a type, and then you create richer types to represent it in the application. 3) we tend to start development using SQL databases, but not all parts of the schema are expressed as a purely relational model. This way we can split the schema and handle parts of it using an entirely different class of database or store. This gives us the convenience of being able to start a project with a single database, exploit the availability of constraint enforcement to show us where we make mistakes, but as we need to scale up, parts of the schema can easily be migrated to, for instance, a timeseries database. This both helps us off the ground quickly and it allows us to postpone actually building a scaled up implementation. (This has proven particularly useful on projects where we have been able to postpone handling a large scale case for years and then having more technology to choose from later). 4) to accomplish 3 we often spend a lot of time understanding what the key data access patterns for the application are going to be and keep this in mind when designing a schema and a scaling plan. It also helps if you can identify where you can decide that data is going to be immutable so that consistency becomes easier later. 5) the application logic never talks directly to the database. There is always a domain specific store service API between the application. We try to make this API as semantically narrow as possible, and try not to make any promises that would be hard to keep if we have to change storage technology. Again, it helps to partition this API into areas that can be realized with different classes of databases, so you can make fewer promises on the parts of the API that will handle the part of the schema that drives most of the operational cost. 6) all tests are written against the store service API, never directly against any database or storage technology. This way you already have a comprehensive test suite if you need to migrate to a new database entirely, or for when you start implementing different parts of the store using different database technologies. In the beginning this approach will require a bit more work, but when you have established practices and it becomes a habit, it actually doesn't represent more work to implement than more direct ways of using databases. Yes, we sometimes forego using functionality offered by the database that would have saved us some work in the short term, but it pays off in the long run because you end up with simpler and more flexible designs. A few years ago I did an experiment with two new hires. Their first task was to add support for a relatively new database to an existing application. They had zero prior experience with the application. I pointed them to the storage service API and the tests and I said "write an implementation of it that uses database X" (which was a NoSQL database). After about two days of work they had an implementation that passed all of the tests and could run all of the benchmarks without any problems. This included adding configuration options that allowed the application to initialize and run with the new store implementation. I think we discovered only 2-3 minor issues in their code over the next 12 months, which isn't bad for a first contribution to an unknown code base. (Since we only had one set of tests which was leveraged across all implementations it also meant that every time we expanded and improved the tests, we'd be able to improve all implementations as the tests got better at finding subtle mistakes). Because it was so cheap and easy to add support for new databases, I think we had support for something like 5 different databases. Including an embedded SQL database so that you could run the application locally for experimentation or integration testing (it is really nice to be able to fire up the application with an in-memory database for integration tests. It wasn't unusual that we ran integration tests that would create and destroy the storage a few hundred times in just a few seconds). After I left the project they added support for another database and migrated terabytes of production data with minimal downtime. The migration tools they wrote for this used the store service API as well, which means they developed one tool that would let them migrate between arbitrary databases. (So if, in the future, they chose to switch databases again, the migration tool already exists) We also had adapters that allowed us to run different permutations of databases. So we could handle parts of the schema suited for relational databases using an SQL database, and handle the bulky bits with somewhat narrower requirements using a NoSQL database. This requires a lot of care when you design the API to have built in "fault lines" where you can split the schema, but with a bit of practice it becomes a habit. (In some cases we've made "conditional" schemas - so for instance if you run the entire thing in one database you may have a bunch of constraints to help you ensure consistency, but you run with mixed stores, you may not apply the constraints that would span across databases. Again, we try not to depend on the constraints, but they are a great help in designing the schema, the store service API and the application).