5 ms·

Postlite author here. I'm glad to see the interest in this project. This project really only has one use case and that is being able to connect to a remote SQLi

Postlite author here. I'm glad to see the interest in this project. This project really only has one use case and that is being able to connect to a remote SQLite database using GUI database administration tools like DBeaver. The idea came out of this Twitter thread: https://twitter.com/benbjohnson/status/1508927916561743872 https://twitter.com/benbjohnson/status/1508927916561743872 It's a recurring theme with SQLite. Many developers are on-board with using it as a database but when you tell them that they also have to SSH in and use a CLI exclusively then they are turned off. Postlite is meant to alleviate that pain.

thasmin 5y ago
Do you think this technique can be adapter for integration testing? So you wouldn't need a running PostgreSQL server to run tests that connect to a PostgreSQL database, or maybe it would make tests easier to run in parallel by using different SQLite files.
- simonw 5y ago
  Tests that run against a different relational database from production make me really nervous. The Django ORM has provided the ability to test against SQLite and deploy against PostgreSQL with the same code base (and the same tests) for years - and while it works incredibly well, I still won't use that in any system that I build. How your database behaves is such a crucial component of your application! These days I find spinning up a real PostgreSQL database to run the tests is so easy there's essentially no reason not to do it. I use GitHub Actions for my PostgreSQL testing insurance, and it ends up just being a few extra lines in the YAML file.
  - benbjohnson 5y ago
    I agree with Simon. I think you could run tests against SQLite locally so they're quick but then run them against Postgres in CI to ensure it works against the database you're running on. There's a lot of subtle differences in how the two databases work even though they both support a lot of the same SQL syntax. You could also run SQLite in production. Then you don't have to test against a different database locally. :)
- icedchai 5y ago
  Why not just use a separate schemas on the same postgres server?
coder543 5y ago
Heh. When I saw this project, I wondered what it would be written in. Using jackc/pgproto3 almost feels like cheating... it's such an unreasonably great protocol library for Postgres. I wrote a PgBouncer alternative awhile back using that library, and in the span of only about 1200 SLoC, I had a fully functional alternative that... - benchmarked better than PgBouncer for me - avoided the need for the annoying session, transaction, statement modes by just Doing The Right Thing. If you prepare an anonymous statement, it holds that connection for you until you execute the prepared statement. If you open a transaction, it holds that connection for you until you commit or rollback that transaction. - offered control over whether to set application_name for the DB connection whenever one is acquired by a client connection, and whether to also clear application_name or not when the connection is released. It's relatively straightforward to detect when a SELECT query comes through, so I had planned to add transparent read replica support to route SELECT queries to read replicas if you aren't in a transaction. I also never got around to implementing TLS support, but... that should be trivial in Go. Longer term, I also thought it would be cool to implement some extensions to the Postgres wire protocol, like end to end compression with zstd. You might have an application in one datacenter querying a Postgres database in another, and depending on the size of datasets that you're getting back from the database, compression could make a huge difference. You could also imagine implementing a "double proxy" where a proxy is running both locally and in the remote datacenter, and it would implement the non-standard postgres extensions behind the scenes, presenting a purely standard wire protocol to any client that connects. Since this was just a fun side project that was never proven in production, I've never gotten around to open sourcing it. I also wish it actually had some tests... but those haven't happened yet. I don't want to mislead people into thinking I consider this side project to be production ready, but I don't remember any obvious problems. If people were actually interested, I could open up the repo, but this comment is less about self-promotion and more about how impressed I've been with that particular wire protocol library, but I admittedly do also enjoy talking about side projects. If anyone needs to do something with Postgres's protocol, I would highly recommend it.
- benbjohnson 5y ago
  Yeah, jackc/pgproto3 is great. When I first started the project, I was digging through the protocol documentation and expecting to write message parsers. But pgproto3 handles all that. Almost all the real code in Postlite is in a 500 LOC file[1]. I was surprised how easy it was. [1]: https://github.com/benbjohnson/postlite/blob/main/server.go https://github.com/benbjohnson/postlite/blob/main/server.go
justsomeuser 5y ago
I normally scp the db files down to my machine and use Table Plus locally. Works well if you are not trying to watch real time processes as they write to the db.
debarshri 5y ago
Ben, you have been one of most influential creators I have known. I have been following you since boltdb days. I used boltdb in project that helped scale backoffice system of many counties in tier 2 cities of US, creating huge impact. With litestream and now this, thanks for creating project that are literally changing the way new developers are building systems.
- benbjohnson 5y ago
  Thank you, Debarshi! That means a lot. I really appreciate it.

thasmin 5y ago

Do you think this technique can be adapter for integration testing? So you wouldn't need a running PostgreSQL server to run tests that connect to a PostgreSQL database, or maybe it would make tests easier to run in parallel by using different SQLite files.

simonw 5y ago
Tests that run against a different relational database from production make me really nervous. The Django ORM has provided the ability to test against SQLite and deploy against PostgreSQL with the same code base (and the same tests) for years - and while it works incredibly well, I still won't use that in any system that I build. How your database behaves is such a crucial component of your application! These days I find spinning up a real PostgreSQL database to run the tests is so easy there's essentially no reason not to do it. I use GitHub Actions for my PostgreSQL testing insurance, and it ends up just being a few extra lines in the YAML file.
- benbjohnson 5y ago
  I agree with Simon. I think you could run tests against SQLite locally so they're quick but then run them against Postgres in CI to ensure it works against the database you're running on. There's a lot of subtle differences in how the two databases work even though they both support a lot of the same SQL syntax. You could also run SQLite in production. Then you don't have to test against a different database locally. :)
icedchai 5y ago
Why not just use a separate schemas on the same postgres server?

coder543 5y ago

Heh. When I saw this project, I wondered what it would be written in. Using jackc/pgproto3 almost feels like cheating... it's such an unreasonably great protocol library for Postgres. I wrote a PgBouncer alternative awhile back using that library, and in the span of only about 1200 SLoC, I had a fully functional alternative that... - benchmarked better than PgBouncer for me - avoided the need for the annoying session, transaction, statement modes by just Doing The Right Thing. If you prepare an anonymous statement, it holds that connection for you until you execute the prepared statement. If you open a transaction, it holds that connection for you until you commit or rollback that transaction. - offered control over whether to set application_name for the DB connection whenever one is acquired by a client connection, and whether to also clear application_name or not when the connection is released. It's relatively straightforward to detect when a SELECT query comes through, so I had planned to add transparent read replica support to route SELECT queries to read replicas if you aren't in a transaction. I also never got around to implementing TLS support, but... that should be trivial in Go. Longer term, I also thought it would be cool to implement some extensions to the Postgres wire protocol, like end to end compression with zstd. You might have an application in one datacenter querying a Postgres database in another, and depending on the size of datasets that you're getting back from the database, compression could make a huge difference. You could also imagine implementing a "double proxy" where a proxy is running both locally and in the remote datacenter, and it would implement the non-standard postgres extensions behind the scenes, presenting a purely standard wire protocol to any client that connects. Since this was just a fun side project that was never proven in production, I've never gotten around to open sourcing it. I also wish it actually had some tests... but those haven't happened yet. I don't want to mislead people into thinking I consider this side project to be production ready, but I don't remember any obvious problems. If people were actually interested, I could open up the repo, but this comment is less about self-promotion and more about how impressed I've been with that particular wire protocol library, but I admittedly do also enjoy talking about side projects. If anyone needs to do something with Postgres's protocol, I would highly recommend it.

benbjohnson 5y ago
Yeah, jackc/pgproto3 is great. When I first started the project, I was digging through the protocol documentation and expecting to write message parsers. But pgproto3 handles all that. Almost all the real code in Postlite is in a 500 LOC file[1]. I was surprised how easy it was. [1]: https://github.com/benbjohnson/postlite/blob/main/server.go https://github.com/benbjohnson/postlite/blob/main/server.go

justsomeuser 5y ago

I normally scp the db files down to my machine and use Table Plus locally. Works well if you are not trying to watch real time processes as they write to the db.

debarshri 5y ago

Ben, you have been one of most influential creators I have known. I have been following you since boltdb days. I used boltdb in project that helped scale backoffice system of many counties in tier 2 cities of US, creating huge impact. With litestream and now this, thanks for creating project that are literally changing the way new developers are building systems.

benbjohnson 5y ago
Thank you, Debarshi! That means a lot. I really appreciate it.