5 ms·
We used SQLite at my company to allow users to write SQL queries against their db. When we hit the limit of it, we had to switch to Postgres. That migration is
by joshdance 7y ago
We used SQLite at my company to allow users to write SQL queries against their db. When we hit the limit of it, we had to switch to Postgres. That migration is quite difficult and I wish we had used Postgres from the start. 20/20 hindsight but that was my first thought.
- kmfrk 7y agoDo you have any good articles or posts you can think of for managing the migration, in case there're some lifesavers out there.
- rocmcd 7y agoNot an article, but I've used pgloader for this purpose in the past: https://pgloader.readthedocs.io/en/latest/ https://pgloader.readthedocs.io/en/latest/ Great tool, I can't recommend it enough.
- throwaway55554 7y agoWere you using an ORM? I ask because most people use database switching as a selling point for using an ORM. I'm rather indifferent on the matter, but I'm curious.
- kirstenbirgit 7y agoYou still have to migrate the data. I faced the same kind of dilemma, but with MySQL, and kind of noped out when I got to stuff like [0]. [0] https://stackoverflow.com/a/87531/1210797 https://stackoverflow.com/a/87531/1210797
- jtdev 7y agoHa! This selling point is just one in a miserable litany of poor reasons to use an ORM - you absolutely cannot simply switch between databases without doing some work to ensure that the data is migrated correctly and the SQL statements translate properly.
- nomel 7y agoYeah, but verification of correctness is much less work than implementation.
- jimbokun 7y agoAs an alternative to an ORM, there is another great abstraction layer that works across a large number of databases. It's called SQL!
- kingbirdy 7y agoThat's true in theory, but unfortunately you can still run in to issues when different databases support different parts of SQL, and the db you're migrating from has different features than the one you're migrating to.
- shakna 7y agoThere are a huge number of differences in the SQL supported by different engines. You can't just switch from one to another, unless you're only using a small subset of SQL to begin with.
- imtringued 7y agoUntil you realize that not even booleans are standardized between SQL dialects...
- legulere 7y agoHow do you insert a row and get the automatically set Id set by the database portably? That’s a standard create operation.
- imtringued 7y agoAn ORM does indeed force you to write lowest common denominator code but I wouldn't rely on that.
- jessermeyer 7y agoDid you use WAL?
- fyfy18 7y agoFor anyone wondering why: I was working on a hobby project a few years ago that had to do a lot of inserts in a SQLite DB. Performance was ok, but it wasn't great either. Turning on WAL greatly sped up performance. In both cases just a single thread was writing to the database. With the WAL turned off (the default), when you start a transaction SQLite will duplicate the data into a new file, change the original file, and delete the backup file when the transaction is committed. With WAL turned on it will write each change to a journal file, and merge this into the main file periodically (by default when the change file grows to 1000 entries) - so only a single write is needed.