4 ms·

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

by 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)

by wruza 4y ago

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)