[HN Gopher] Migrating from SQLite to PostgreSQL
___________________________________________________________________
Migrating from SQLite to PostgreSQL
Author : cloudsql
Score : 87 points
Date : 2022-04-15 10:42 UTC (12 hours ago)
(HTM) web link (bytebase.com)
(TXT) w3m dump (bytebase.com)
| flakiness wrote:
| Note that they aren't SaaS but are providing a locally-
| installable software it seems [1]. Then SQLite seems to be a
| reasonable option and PG can be overkill (but switched to PG
| anyway because of the listed motivations.)
|
| [1] https://github.com/bytebase/bytebase#installation
| sebazzz wrote:
| > Then it is a lot of engineering work to update existing SQL
| queries. SQLite (similar to MySQL) dialect uses the question mark
| parameters while PostgreSQL uses positioned parameters for
| prepared statements.
|
| Many database engines, including SQL Server for instance, also
| support question marks. Surely PostgreSQL also supports this?
| mjw1007 wrote:
| ? in PostgreSQL is available for use as a user-defined
| operator.
|
| I suppose they made that decision a long time ago.
| packetlost wrote:
| No, it doesn't. Using a '?' will cause a syntax error.
| masklinn wrote:
| Interestingly, there is one "platform" which supports ? as
| placeholder: ECPG (where SQL is directly embedded in C code,
| and post-processed to generate straight C you can feed to a
| compiler).
|
| In ecpg, prepared statements use ?:
| https://www.postgresql.org/docs/current/ecpg-
| commands.html#E...
| uhoh-itsmaciek wrote:
| As some other replies noted, Postgres does not support that.
| Some drivers or ORMs do their own pre-processing on query text,
| and add their own support for that. E.g., in ActiveRecord, you
| can do: User.where("status = ?",
| "active").all
|
| ActiveRecord itself sanitizes the input here and the query
| that's sent to Postgres does not use parameter markers at all.
| nikeee wrote:
| SQLite also supports referencing by parameter number (and
| name):
|
| https://www.sqlite.org/lang_expr.html#varparam
|
| So, changing "?" to "?1" could be done in SQLite before the
| actual migration, leaving just the change from ? to $ when
| migrating to Postgres.
| impoppy wrote:
| There's only one reason I keep using PostgreSQL instead of SQLite
| for my pet projects and it is the "RETURNING" statement
|
| UPD: I know it's there, but the library version shipped with
| interpreters and compilers are often outdated
| jmmv wrote:
| What I've been doing in some of my recent projects is to support
| _both_ SQlite and PostgreSQL. I do this via an abstraction layer
| that hides the two databases behind it, and I run the exact same
| set of unit tests against the two implementations.
|
| The reason I do this is for testing. In fact, the SQLite backend
| is only compiled for test builds. With that, I can test my
| application logic against the SQLite implementation and be
| reasonably confident that things work with the PostgreSQL backend
| as well. And this is infinitely easier and faster than relying on
| a "real database server". No installation involved, and the same
| tests run in 300ms as opposed to 8+ seconds.
|
| Yes, it's significantly more work to develop around these two
| implementations, but the benefits in build/test iteration times
| are immense. I haven't ever had to touch docker yet. And the
| abstraction layer also keeps me honest by forbidding me from
| poking into database internals.
| throwthere wrote:
| I tried that but moved completely away from it. Too many issues
| around sql dialect and type differences for starters. I'd much
| rather run postgres on my dev/testing boxes, use a single set
| of sql-based migrations and not have to maintain an abstraction
| layer.
| colinchartier wrote:
| When you say "8+ seconds", is that the startup time for the
| postgres instance?
| listenallyall wrote:
| SQLite is nice, but if you have a JDK, H2 is so much better.
| sebastianconcpt wrote:
| Sounds like it was a smooth migration. Good to hear that because
| in this precise moment I'm adding SQLite support to _Mapless_ [1]
| (PostgreSQL had support already [2]) so people can do these kind
| of smooth transitions in their Smalltalk apps.
|
| [1] https://github.com/sebastianconcept/Mapless
|
| [2] https://blog.sebastiansastre.co/article/mapless-is-online-
| ag...
| lysecret wrote:
| Main thing I am missing in SQLite is array or Json support.
| benbjohnson wrote:
| SQLite has JSON support via the json1 extension[1]. IIRC it's
| bundled into SQLite by default in recent releases. As for
| arrays, you're SOL unless you use JSON arrays. :)
|
| [1]: https://www.sqlite.org/json1.html
| JamesSwift wrote:
| SQLite JSON support is quite robust. I leverage it heavily for
| https://github.com/J-Swift/cod-stats
|
| e.g. https://github.com/J-Swift/cod-
| stats/blob/master/frontend/ge...
| fmajid wrote:
| I'd love to see a good open-source sync solution between SQLite
| and PostgreSQL to get the best of both worlds, like what the
| Watcom/Sybase/SAP SQLanywhere offered.
| dannyibunny wrote:
| Totally agree, compatibility is really the key for any database
| product or any product. Minimizing the migration gap helps
| adoption.
| simonw wrote:
| I have a tool that can do that in one direction - db-to-sqlite
| (built on top of SQLAlchemy) can create a SQLite database with
| a direct copy of the schema and data from a MySQL or PostgreSQL
| database.
|
| I've contemplated building it to work on the other direction
| too but I haven't needed it myself yet so I've not done the
| work.
|
| https://datasette.io/tools/db-to-sqlite
| thunderbong wrote:
| There's a Ruby ORM called Sequel [0] (Github [1]) which
| provides a cli [2] to do just that across different databases.
|
| Example from the docs - sequel -C
| mysql://host1/database postgres://host2/database2
|
| or for Postgres > SQLite sequel -C
| postgres://host2/database2 sqlite://database.sqlite
|
| and vice-versa sequel -C
| sqlite://database.sqlite postgres://host2/database2
|
| [0]: https://sequel.jeremyevans.net
|
| [1]: https://github.com/jeremyevans/sequel
|
| [2]:
| https://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdo...
| simonw wrote:
| My sqlite-utils CLI tool and Python library includes a workaround
| for SQLite's ALTER TABLE limitations.
|
| The transform CLI command (and Python method) automates the
| process of starting a transaction, creating a new table with the
| required changes, copying across the data from the old table,
| then dropping the old and renaming the new before committing the
| transaction.
|
| Documentation here:
|
| - Python library: https://sqlite-
| utils.datasette.io/en/stable/python-api.html#...
|
| - CLI command: https://sqlite-
| utils.datasette.io/en/stable/cli.html#transfo...
|
| - Blog entry: https://simonwillison.net/2020/Sep/23/sqlite-
| advanced-alter-...
| pizza234 wrote:
| For those who don't know, there's no need to download the full
| MySQL package(s) that the article references ("200+ MB"); there
| is the "minimal" package, which includes all the required
| binaries - as of v8.0.28, it's ~52 MiB.
|
| This is a relatively new introduction; v5.7 doesn't have any
| minimal packages.
| teddyh wrote:
| The MariaDB packages for Debian and Ubuntu are similarly tiny.
| habibur wrote:
| I was more interested in knowing performance characteristics.
|
| For the same service how did RAM+CPU load change pre and post
| migration?
| dannyibunny wrote:
| Postgres is much heavier than SQLite in terms of binary
| size/CPU/RAM, but I think scalability is the win.
| masklinn wrote:
| OTOH if you don't tune it the default postgres configuration
| remains extremely low-resource (which also hampers its
| performances): 128MB shared buffers, 8MB temp buffers, 4MB
| work_mem, 64MB maintenance_work_mem, 100 connections, 1000
| files open.
| mayli wrote:
| Compared to less than 1MB for sqlite in this use case.
| config_yml wrote:
| > it's a database for localhost only
|
| This is sticking so hard to Sqlite, but it's really no longer
| true I think. Given WAL mode and tools like litestream, it's even
| feasible to run Sqlite in serverless offerings like Cloud Run.
| The schema change constraints are annoying though, even when
| there are known workarounds.
|
| And, this is off topic, but Tailwind is really becoming the new
| Bootstrap. I open a page and it's instantly visible that this has
| been implemented with Tailwind (and often Tailwind UI).
| benbjohnson wrote:
| Litestream author here. Pure ephemeral serverless offerings
| aren't currently supported with Litestream so you'll run into
| issues with something like Google Cloud Run. It's on the
| roadmap to support in the future though.
| mtlynch wrote:
| FWIW, I've used Litestream on Google Cloud Run and never run
| into issues, but I haven't pushed it much:
|
| https://github.com/mtlynch/logpaste/blob/master/docs/deploym.
| ..
| benbjohnson wrote:
| You're a brave soul, Michael. :)
| jpcapdevila wrote:
| @ben Same as the other commenter, I'm currently using it
| without problems. I configured max instances to 1 and that
| seems to be working fine. What am I missing?
| benbjohnson wrote:
| The main issue is if GCR tries to start up another instance
| before tears down your old instance during a deploy. If GCR
| only runs a single instance at a time then you should be
| fine.
| trinovantes wrote:
| One of my favorite features of SQLite is its memory mode. It
| makes writing tests really quick and easy compared to having to
| spin up a test Postgres database and then deleting it.
| bob1029 wrote:
| We use sqlite for everything. One novel application is the
| evaluation of business rules. What we do is create an in-
| memory sqlite DB, fill in with relevant domain info, then
| execute the queries against it to evaluate logical outcomes.
| Sqlite's unique application-defined function capability is
| what sold us on this path. It's very very fast too. Thousands
| of queries per second _per instance_ are no problem.
| munk-a wrote:
| We've found quite a few ways to tune DB testing to make it as
| light as possible but yea - it's still not free by any
| measure - you can ensure a sane base data set (any static
| data, all the table schemas) and have minimally necessary
| writes to set up test cases but you'll never get as close to
| pure in memory exercise.
|
| I wonder if you could write a postgres shim layer to run your
| tests on a SQLite in-memory instance - though I guess you're
| at the whims of whatever that shim layer is doing matching
| actual postgres functionality bug for bug.
| [deleted]
| giraffe_lady wrote:
| It's no longer technically true that sqlite is limited in that
| way. But that is _relatively_ new territory (compared to plain
| sqlite or postgres for sure) so you can expect some unknown
| troubles there.
|
| I'd be willing to launch a product on it in some circumstances,
| but depending highly on the resources, composition, goals, and
| risk tolerance of the team. I'm excited about where it's going
| but DB is so important and the alternatives are so well
| understood it still feels like a big move.
| _wldu wrote:
| You can also run a simple rest API on the machine hosting the
| sqlite DB and access it from other machines. I've found that
| works fairly well.
|
| Clients -> Internet -> API -> sqlite
| RedShift1 wrote:
| That's really pushing SQLite where it was clearly not
| engineered to go. If you need something that is accessible over
| the network, use a database server that is designed for it. The
| right tool for the right job.
| er4hn wrote:
| Nonsense. Just be like the Tailscale team and use SQLite [1]
| to serve your entire customer network.
|
| [1] https://tailscale.com/blog/database-for-2022/
| jpalomaki wrote:
| "SQLite does not compete with client/server databases. SQLite
| competes with fopen()."
|
| https://www.sqlite.org/whentouse.html
| jhgb wrote:
| Firebird can do both, though.
| tianzhou wrote:
| One of the Bytebase authors here and I am the one who chose
| SQLite in the first place.
|
| The project started in 2021 Jan and I did notice litestream.
| And actually, one of the main reasons I chose SQLite is because
| of litestream. Because I could see it could be our HA solution
| when needing it.
|
| SQLite is great, but the schema change constraint is really
| painful as you mentioned.
|
| As to the Tailwind, as a backend engineer working on database
| for most of my 10+ career and single-handedly built the site, I
| can only say it's godsend;)
| born-jre wrote:
| any plan to maintain this[0] as separate library so anyone
| can embed pg in go binary?
|
| [0] https://github.com/bytebase/bytebase/tree/6a72eb630c604e2
| fe7...
|
| edit: there is already sth like that
| https://github.com/fergusstrange/embedded-postgres
| no_wizard wrote:
| Completely unrelated to the thread, but the graphic on your
| home page doesn't quite re-size correctly, at least in the
| latest version of Firefox. It blocks the star button at i'd
| say tablet -> desktop range (somewhere between full width and
| not quite tablet)
|
| if you go smaller, it does at certain sizes block a bit of
| the text.
|
| tl;dr it doesn't look like the main graphic on your home page
| handles fluid layouts
|
| EDIT: this goes with a few other graphics on the front page
| as well.
|
| I also noticed some of your copy doesn't read well
| grammatically. "For Developer" I think reads better as "For
| Developers", as an example.
|
| Only mentioning this cause this is a really cool concept that
| I wish I had thought of years ago when I worked more heavily
| with databases and want you to succeed! One of the reasons
| ORMs are "nice" is the built in schema migration of a lot of
| the most mature ones (coming from .NET and previously
| Django). This is a similar idea, but for teams that need high
| performance SQL optimizations to be able to change schema
| that may be written against "raw" SQL / Window functions etc.
| ochoseis wrote:
| The bottom of the article states:
|
| > Bytebase offers a web-based collaboration workspace to help
| DBAs and Developers manage the lifecycle of application
| database schemas.
|
| So, I guess it's not surprising you'd be annoyed by not being
| able to change schema constraints :)
| theobr wrote:
| Re: the "tailwind is the new Bootstrap" bit - half agree.
|
| tailwind-typography is highly opinionated and "automatically
| applied", unlike Tailwind itself. I don't think that Tailwind
| will be quite as immediately visible outside of blogs....unless
| TailwindUI really takes off.
|
| Unless you're really sensitive to colors, then you might notice
| the Tailwind gray
| christophilus wrote:
| I wouldn't have noticed the Tailwind thing, and I'm building a
| product with Tailwind at the moment. But yeah. I agree that
| Tailwind does seem to be gaining that level of traction.
|
| It's so, so nice, though, so I think it deserves its success.
| CodesInChaos wrote:
| I'd consider using _generated as identity_ instead of _serial_ in
| postgres.
|
| https://www.2ndquadrant.com/en/blog/postgresql-10-identity-c...
|
| And if any of your tables might grow beyond 2B rows, I'd use
| 64-bit IDs (serial8) from the start to avoid an expensive
| migration later.
| candiddevmike wrote:
| Anyone know how far you could get with just SQLite and some kind
| of in-memory cache?
| avdempsey wrote:
| You can get pretty far if you don't have much in the way of
| concurrent writes. Concurrent writes can be safe mind you, but
| that safety comes from locks that are polled against at
| increasingly long intervals (at least in my experience backing
| Django with SQLite). If you have one thread making many short
| writes over a long period, another thread will check to see if
| it can acquire a lock at 1 second, 2 seconds, 5 seconds, etc
| and if it doesn't time that check exactly perfectly you'll keep
| polling until your timeout. That is, out of the box you don't
| have a fair queue for the write lock. That kind of access
| pattern doesn't describe all apps of course! And in WAL mode
| your reads won't be blocked by these long sequences of writes.
___________________________________________________________________
(page generated 2022-04-15 23:01 UTC)