10 ms·
Show HN: I open-sourced the in-memory PostgreSQL I built at work for E2E tests
- n2d4 3y agoHey HN! For a few months, I've been building an in-memory version of Postgres at work. It has full feature parity with production databases. The cool thing about it is that you don't need any external processes or proxies. If your platform can run WASM (Node.js, browser, etc.), it can probably run pgmock. Creating a new database with mock data is as simple as creating a JavaScript object. It's a bit different from the amazing pglite [1] (which inspired me to open-source pgmock in the first place). pgmock runs an x86 emulator with the original Postgres inside, while pglite compiles a Postgres fork to native WASM directly and is hence much faster and more lightweight. However, it only supports single-user mode and a select few extensions, so you can't connect to it with normal Postgres clients (which is quite crucial for E2E testing). Theoretically, it could be modified to run any Docker image on WebAssembly platforms. Anything specific you'd like to see? Happy hacking! [1] https://github.com/electric-sql/pglite https://github.com/electric-sql/pglite
- waldrews 3y agoOoh! The 'docker image on WASM' thing sounds promising for a wide range of problems. Recently I wanted to run a FFMPEG/SoX pipeline on the client - too many dependencies to easily recompile with Emscripten; could your approach help there?
- n2d4 3y agoYeah, that should be possible! Though, for audio processing, the performance will probably be terrible (because it's all emulated).
- jasonjmcghee 3y agoThere's already ffmpeg wasm. I've used it in projects. Works great. https://github.com/ffmpegwasm/ffmpeg.wasm https://github.com/ffmpegwasm/ffmpeg.wasm
- waldrews 3y agoThanks, looks good... doesn't look like there's a SoX though.
- jasonjmcghee 3y agoI googled - haven’t tried it https://github.com/rameshvarun/sox-emscripten https://github.com/rameshvarun/sox-emscripten There’s also https://github.com/IPS-LMU/wasmsox https://github.com/IPS-LMU/wasmsox Lots of cool software that people hack together for fun. Just site:github and search!
- samwillis 3y agoThis looks really cool, awesome work! Correct on PGlite only being single user at the moment, and that certainly is a problem for using it for integration tests in some environments. But I'm hopeful we can bring a multi-connection mode to it, I have a few ideas how, but it will be a while before we do. There are a few other limitations with PGlite at the moment (related to it being single user mode), such as lacking support for pg_notify (have plans to fix this too). Whereas with this it should "just work" as it's much closer to a real Postgres. I think there is a big future for these in-memory Postgres projects for testing, it's looks like test run times can be brought down to less than a 1/4 with them. (I work on PGlite)
- justinclift 3y agoAs a data point, the online demo seems broken for queries it doesn't like: select foo(); Error.captureStackTrace is not a function That's when using Firefox 124.0.2 on Linux.
- rezonant 3y agoYes, that's a nonstandard function provided by v8, so it wouldn't work on Firefox. [1] This can be worked around by just constructing an Error and taking it's stack property, captureStackTrace is just a convenience function, so hopefully they can fix that. [1] https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Error#error.capturestacktrace https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
- justinclift 3y agoThanks for suggesting that. Looks like they've implemented that here: https://github.com/stackframe-projects/pgmock/commit/f80d9fa0ee8d420a9d5947f20a1a6c3bc7482d10 https://github.com/stackframe-projects/pgmock/commit/f80d9fa... It seems to have beendeployed out to the demo already as it's working now. :)
- n2d4 3y agoMy bad, gotta fix that one!
- negus 3y agoThat's great. But doesn't the whole concept of E2E test mean that you use real environment without mocking the components?
- refulgentis 3y agoExplicitly mentioned in the comment as a drawback. In practice E2E means "E2E as much as humanly possible", and I'm glad to see any work that can help.
- sverhagen 3y agoIt is, however, humanly possible, to run a real Postgres instance in a Docker container. You may use for example Testcontainers. So, I would not resort to an inferior Postgres "mock" for E2E testing. For unit testing (also mentioned in the tagline of the project on GitHub) I could see you wanting something snappier than a real Postgres in Docker, so then... maybe? Purists of some following will tell you to use a mocking framework instead, but I think that something closer to the real thing would be better in all cases. This might be it, just be careful not to lull yourself into a false sense of security about how close to the real thing this is (isn't).
- justinclift 3y agoIt does seem to be running a real version of Postgres though, unless I'm misreading?
- sverhagen 3y agoOn first reading of the main GitHub page, I didn't take it that way. On second reading, I guess it could go either way. But there's talk about having reimplemented the network stack, so maybe that implies they just repurposed the rest of Postgres. I just don't know enough, happy to take your word.
- wpietri 3y agoIt depends on the humans and what they're up to. Fast feedback is a very valuable attribute of a test suite, so reasonable people may well be willing to go down this road.
- MuffinFlavored 3y ago> pgmock runs an x86 emulator with the original Postgres inside Why can't Postgres compile to WASM instead of x86?
- anarazel 3y agoPostgres uses multiple processes, shared memory etc. The single user thing that OP referenced is single user because of that...
- nborwankar 3y agoIf it could support the pgvector extension it would be a super fast vector database with all the power of Pg - the relational aspect brings the ability to add and query using rich domain specific metadata usually contained in relational databases.
- J_Shelby_J 3y agoI spent last week trying to do that with some of the other pg embeded libs. And then lancedb released their embedded client for rust, so I went towards that. But it's still lacking FTS. So I fell back to sqlite. have some notes here https://shelbyjenkins.github.io/blog/retrieval-is-all-you-need-2/ https://shelbyjenkins.github.io/blog/retrieval-is-all-you-ne...
- rsyring 3y agoWhy not just run Postgres with it's files on a ramdisk? Update: this can apparently run in a browser/Node environment so can be created/updated/destroyed by the tests. I guess I'm too much of a backend dev to understand the advantage over a more typical dev setup. Can someone elaborate on where/when/how this is better?
- orphea 3y agoI don't get it either. I feel like this is so much unnecessary code, an emulator, a network stack... Why not use something like https://testcontainers.com/ https://testcontainers.com/? Is a container engine as an external dependency that bad?
- bastawhiz 3y agoIt's the same amount of code and on Mac you still run a full VM to load containers (with a network stack), so I'm not really sure what your point is. If anything it's less code because the notion of the container is entirely abstracted away, and the whole thing is entirely a wasm dependency that you load as a normal import.
- medellin 3y agoIt is annoying is you want to run your teat inside a container for ci and now you are running a container in a container and all the issues that come with it.
- dorianmariefr 3y agowhich issues?
- c0balt 3y agoDepending on the setup it can be a pain to get nested containers working sometimes. There is, e.g., Docker In Docker but this often required a privileged host container which is often not provided in CI/CD pipelines.
- andrelaszlo 3y agoCool! Which pg version is this based on?
- justinclift 3y agoThe online demo seems to be PG 14.5. Output from "SELECT version()" is: "version": "PostgreSQL 14.5 on i686-buildroot-linux-musl, compiled by i686-buildroot-linux-musl-gcc.br_real (Buildroot 2022.08) 12.1.0, 32-bit"
- rickette 3y agoI used to run all kinds of (custom) fake in-memory servers in my tests. Nowadays I just run the real thing using Testcontainers (https://testcontainers.com https://testcontainers.com)
- herpdyderp 3y agoThis might be a stupid question, but do you know how this might be used with a Prisma client?
- drzaiusx11 3y agoHow does this compare to H2 in postgres compatibility mode?
- noisy_boy 3y agoI may be wrong but I don't think you can use postgresql stored procs with H2.
- tumidpandora 3y agoOff-topic, but the title confused me a bit - "...I built at work." Doesn't this imply that the intellectual property for this project belongs to your employer, assuming you used resources from work? If so, are you technically allowed to open-source it?
- geuis 3y agoI've used pgmem https://github.com/oguimbal/pg-mem https://github.com/oguimbal/pg-mem for the last couple of years for the same thing.
- crakhamster01 3y agoThis is pretty neat! Some questions, if you're able to answer: * What was the inspiration for developing this project at work? Was running Postgres in a Docker container too slow? * What did your CI setup for E2E tests look like before and after integrating pgmock into the flow? * Was migrating over to this solution difficult? Thanks!
- segmondy 3y agoDump your prod data, scrub all the sensitive data, truncate all the unneeded tables like your log tables. You have a good dev copy, replicate for dev ,qa, e2e, etc. Those extensions, triggers, functions, views, indexes, data are what you need for e2e.
- vivzkestrel 3y agosupport for orms like sequelize, prisma, drizzle for testing?
- revskill 3y ago[flagged]
- andy_ppp 3y agoWhy not just use Docker and have a different testing database? Elixir does this, and the testing framework wraps each test into a transaction that is rolled back for isolation. Be interesting to know the advantages of this approach!
- garashovb 3y agoOr this https://testcontainers.com/modules/postgresql/ https://testcontainers.com/modules/postgresql/
- xlii 3y agoHate to be w downer but I’d never consider this for use. For trivial applications maybe it’d work, but with more complexity like anything that has risk of deadlocking or depends on the database shape and such solution subtracts from value as even small shift in behavior can snowball into critical problems. Today I lean towards resource constrained E2E environment so that local test runners have opportunity to break if someone write anything grossly underperforming. Not to mention that snapshotting DB after second and distributing this snapshot to test partitions is super fast and many times shaved multiple minutes from test suites. It’s an interesting idea and definitely great learning experience but I think that target audience is limited.
- davvid 3y agoIf you're into this kinda thing and are in python land you might also appreciate my friend's project in a similar vein https://github.com/ugtar/pg_temp https://github.com/ugtar/pg_temp
- KingOfCoders 3y agoI've been using template databases in the past, to copy a new database for each test. Trying out some in-memory ideas, there was not too much difference to a fast SSD.
- fullstackchris 3y agoThis is amazing! I've been looking for an in-memory postgres mock for integration tests for ages!
- theage 3y agoFor prisma/nodejs devs who just want postgres-in-a-can for local dev you are better off using the recently released serverizing of pglite, pglite-server: https://github.com/kamilogorek/pglite-server https://github.com/kamilogorek/pglite-server It's faster, can persist data to fs, though less stable under heavy use than the full x86 emu e2e test server. I found pglite-server uses only 150MB ram compared to 830MB for pgmock-server. You can then use dotenv to checkout a new .env.local with updated DATABASE_URL for all your nextjs/prisma package.json run scripts DATABASE_URL="postgresql://postgres@localhost:5432/awesomeproject" "db:pushlocal": "dotenv -e .env.local -- pnpm prisma db push" Very easy to add to any project, No wonder neon is sponsoring this space.
- camgunz 3y agoWow very cool that you got to open source this, thanks!
- danpalmer 3y ago> by emulating the Postgres server in an x86 emulator > because performance is not usually a concern in tests I hate to be a downer, especially because it sounds like a lot of complex work has gone into this, but I can't disagree more with test performance. I think for many projects test performance is more important than runtime performance – a 100ms request time is unlikely to cause issues for most applications, but a 10 minute test run can significantly hamper engineering productivity. I don't understand why, as others have suggested, running Postgres in a RAM disk isn't an option. We did this on my previous team, tuned Postgres for better performance in tests, and ran all unit tests against it, the performance was excellent. Setup was trivial, still containerised like this project aims for. Why run Postgres on an X86 emulator, in a WASM VM, in a Docker container... when one could just run Postgres in a Docker container and have all the same advantages? Someone commented that you could run it in a browser... but Postgres doesn't run in the browser normally so why would you need that for tests?
- thibaut_barrere 3y agoIf you use languages that compile to WASM (such as Gleam https://gleam.run https://gleam.run), and can also run Postgres via WASM, then it opens very interesting offline scenarios with codebases which are similar on both the client and the server, for instance.
- danpalmer 3y agoTo be clear, this is if the client is a browser. If the "client" is a Linux machine you can just run a Docker container, or a full VM, or a Firecracker VM, no need for special languages or running software in a WASM VM. If it's a desktop/laptop you can just run a regular Postgres binary in an app (like Postgres.app does). If it's a phone you definitely don't want to be running the extra overhead of a persistent database process emulated on WASM, or something as heavyweight as Postgres. I've heard many people talk about "interesting offline scenarios", but I've yet to see an application do them properly. Some embed SQLite to good effect, but I've not yet seen anything running something like an embedded Postgres, and other than it being a clever trick that might be fun to implement, I'm struggling to think of a use-case.
- henning 3y agoWhy is there a set of binary blobs in the `src/binaries` directory? I don't want to use projects that have that if there isn't a good reason for it.