9 ms·

Postgres wire compatible SQLite proxy

288 points by ithkuil 5y ago

throwaway81523 5y ago
This is a cute idea but it sounds headed for pain, as most typical client environments will come with bindings for multiple db's including sqlite. So if you want to use pg bindings, that probably means you want to use pg features, which aren't present in sqlite. There might be a use case where you want to use sqlite remotely as a client/server db, but even then, is this such a great approach? Sqlite has its own special features that likely don't fit the pg wire protocol that well.
- dhzhzjsbevs 5y ago
  It's literally in the first paragraph... > Postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.
  - throwaway81523 5y ago
    Yes, I'm saying if the gui tools expect postgres, they are likely to use postgres features, which expands the amount of work this thing needs. If the tools are written specifically for this proxy, then ok, but why bring postgres into it at all? Write something sqlite-specific that exposes sqlite's features.
    - lmm 5y ago
      > Yes, I'm saying if the gui tools expect postgres, they are likely to use postgres features, Unlikely. A lot of such tools are mainly for displaying the data that's present; they might have support for displaying e.g. GIS types but they're not going to need to write GIS data into the database, and certainly not to the point where you can't run them at all if that's not possible.
      - tpetry 5y ago
        All of these tools will at least run an introspection query to get the available tables and columns to display the data. But these queries will fail because SQLite doesn't have these tables. So the idea is very nice, but any existing tool for PostgreSQL will not work. You need to either work with the psql terminal application or write a new client.
        lukebennett 5y ago
        This is explicitly accounted for, as per the README: > Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables.
        jpgvm 5y ago
        I believe you are looking for these: https://github.com/benbjohnson/postlite/blob/main/server.go#L288-L305 https://github.com/benbjohnson/postlite/blob/main/server.go#...
    - ricardobeat 5y ago
      > Write something sqlite-specific that exposes sqlite's features This is about using SQLite with existing tools that don't have support for it. It's very common since it's easier to support multiple remote database servers (MySQL, Postgres) vs adding the in-process model for SQLite only. Also you need to take the context into account - Fly.io has SQLite databases distributed across their nodes (see their post about replacing Consul [1]) and most likely want to be able to manage those remotely. Since SQLite doesn't have a server protocol, tools that have bindings for it only support working with local databases. [1] https://fly.io/blog/a-foolish-consistency/ https://fly.io/blog/a-foolish-consistency/
- tootie 5y ago
  I'd imagine the biggest use case will be to simplify unit tests and local dev.
  - benbjohnson 5y ago
    Postlite author here. The primary use case is to administer remote SQL databases using existing GUI database tools like DBeaver. It's just a thin shim to translate between Postgres wire protocol to SQLite transactions so I don't think it would help with unit tests or local dev.
config_yml 5y ago
That was quick: https://twitter.com/flydotio/status/1508928704348577797 https://twitter.com/flydotio/status/1508928704348577797
- heinrichhartman 5y ago
  Indeed. First commit is two days old: https://github.com/benbjohnson/postlite/commit/35974771877cd71aa39758a4dbed1cab4e47ad34 https://github.com/benbjohnson/postlite/commit/35974771877cd...
endisneigh 5y ago
At this note are there any hosting providers that support update range for files? If there were you could basically query and write to a SQLite database entirely through http.
- unilynx 5y ago
  You also need locking then. WebDav implements locking over HTTP but I doubt most providers will implement it... (let alone according to spec and/or whatever a MS backend expects) https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html
- ruslan_talpa 5y ago
  You can query and write to SQLite over http with [1] (i wrote it) but it's not through the method you describe (range for files). https://github.com/subzerocloud/blue-steel https://github.com/subzerocloud/blue-steel
rkwasny 5y ago
Waiting for first benchmarks, we might have a suprise
- benbjohnson 5y ago
  Postlite author here. This proxy is not meant to be the primary database connection for a SQLite database. It's only use case is to support remote administration of SQLite databases using existing GUI tools like DBeaver.
newusertoday 5y ago
I use usql, it uses different approach but i am able to use same psql commands to query multiple different db's that is postgres,sqlite, mysql etc. I only have one gripe with it and that is its dependency on glibc which could have been avoided given that it is go application.
- benbjohnson 5y ago
  Postlite author here. I haven't used usql but it looks really interesting. I was looking for a way to connect existing GUI tools to remote SQLite databases which I don't believe usql supports.
frabert 5y ago
Well now I want a virtual table interface to pgsql so I can query Postgres databases from SQLite too!
- ansible 5y ago
  It would be neat to have a library that has the same API as Sqlite3, but then acts as a network proxy for a remote PostgreSQL database. You could then use that plus the above network proxy to move a Sqlite3 database off of the local system to a remote system, without having to change the existing codebase. This would also give you multiple remote clients as well.
  - frabert 5y ago
    You could also join tables from different databases! Imagine the performance!
eatonphil 5y ago
Without query translation you won't be able to do much date querying since date formatting and getting now() are very different in postgres and sqlite. Also, sqlite doesn't support INTERVAL which I personally use heavily in postgres. Curious how the author plans to deal with this since they mentioned they're interested in supporting BI tools that presumably wouldn't issue sqlite queries if they think they're talking to postgres.
- benbjohnson 5y ago
  Postlite author here. Patrick DeVivo mentioned using BI tools against Postlite, which sounded really interesting, but I think you're right. Those tools are going to heavily rely on dates which will not translate well between Postgres & SQLite. I don't have any plans for query translation except for some basic client startup query handling (e.g. SET, SHOW). Postlite is really just meant as a thin shim to allow GUI tools to work remotely on SQLite databases.
edwinyzh 5y ago
Very coo! I think the most useful use case would be managing remote SQLite dbs using existing database GUI tools. One question - does it support custom collations? That might prevent us from using it if it doesn't...
- benbjohnson 5y ago
  Postlite author here. Yes, remote administration with existing GUI tools is the primary (and probably only) use case for Postlite. It's just a slim passthrough layer between Postgres and SQLite so there's nothing explicitly supported surrounding collations. What kind of support are you thinking?
  - edwinyzh 5y ago
    I don't remember the details, but the framework I use uses some custom collation when creating tables in a sqlite db, and when tools like SQLite Studio is performing some kind of actions it complains something like "collation not recognized/supported"...
traceroute66 5y ago
I have to say I have a disliking for what appears to be the current trend of marketing things as "postgres (wire) compatible". CockroachDB is another example that does the same. The implication is that "if you can do it in postgres, you can do it in X". But the reality is, of course, different. If you are using databases properly, not just as a blackbox dumping ground, then if you use postgres on the wire, its because you are talking to postgres and using postgres specific features (like you should). Unless you have feature parity, don't tell me I can connect to another database using my postgres libraries. Its pointless. Its meaningless. Why would I want to do that if I can't use the features ? Rant over. ;-)
- kasey_junk 5y ago
  Largely for driver coverage. Postgres drivers are available in tons of languages so by being wire compatible you can skip all that work.
  - traceroute66 5y ago
    > Postgres drivers are available in tons of languages So are SQLite drivers (and MySQL and all the other top-10/top-20 databases). Why add un-necessary abstraction ? Why add an un-necessary layer with bugs and edge-cases that you will subsequently waste hours of your life debugging ? Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. > What SQLite3 driver out there allows you to talk over a network connection to a remote database? (Replied here because I can't reply to your point below) Maybe that's because, as per the SQLite website[1], SQLite was never intended to be used as a client/server database !!! I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. There are many excellent client/server databases out there (including the postgres referred to here). If you need that functionality you should be using the right tool for the job, there's no need to shoehorn SQLite into it. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
benbjohnson 5y ago
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. :)
eigenvalue 5y ago
For the stated use case, I have found DB Browser to be an invaluable tool to graphically interact with SQLite databases: https://sqlitebrowser.org/ https://sqlitebrowser.org/ You can do basically anything you would need to do to a database with it, and it’s very performant and cross platform.
- benbjohnson 5y ago
  DB Browser for SQLite is a great tool but I don't believe it supports connecting to remote databases, does it?
  - Asmod4n 5y ago
    Why should it need to? sshfs should work fine, probably.
    - benbjohnson 5y ago
      I don't believe sshfs is safe with SQLite with concurrent use: https://github.com/libfuse/sshfs/issues/204#issue-561260021 https://github.com/libfuse/sshfs/issues/204#issue-561260021
      - deleted 5y ago
        [deleted]
  - londons_explore 5y ago
    I run it with ssh -X to run the remote GUI. It's a bit sluggish, but it's a good way to quickly browse a DB to see whats there.
swlkr 5y ago
This is great! Thanks for this, it never occurred to me that I could improve on ssh + sqlite3 on the cli, but here it is. Great for those times when I want to check some data but don’t want to ssh in
philshem 5y ago
One could also use sshfs to access a remote SQLite DB file. https://github.com/libfuse/sshfs https://github.com/libfuse/sshfs
- benbjohnson 5y ago
  I don't believe sshfs is safe to use with concurrent access on the database: https://github.com/libfuse/sshfs/issues/204#issue-561260021 https://github.com/libfuse/sshfs/issues/204#issue-561260021
ejones 5y ago
Awesome to see work in the DB wire compatible space. On the MySQL side, there was MySQL Proxy (https://github.com/mysql/mysql-proxy https://github.com/mysql/mysql-proxy), which was scriptable with Lua, with which you could create your own MySQL wire compatible connections. Unfortunately it appears to have been abandoned by Oracle and IIRC doesn't work with 5.7 and beyond. I used it in the past to hack together a MySQL wire adapter for Interana (https://scuba.io/ https://scuba.io/). I guess these days the best approach for connecting arbitrary data sources to existing drivers, at least for OLAP, is Apache Calcite (https://calcite.apache.org/ https://calcite.apache.org/). Unfortunately that feels a little more involved.
- westurner 5y ago
  NYTimes/DBSlayer (2007) wraps MySQL in JSON: https://open.nytimes.com/introducing-dbslayer-64d7168a143f https://open.nytimes.com/introducing-dbslayer-64d7168a143f ODBC > Bridging configurations: https://en.wikipedia.org/wiki/Open_Database_Connectivity#Bridging_configurations https://en.wikipedia.org/wiki/Open_Database_Connectivity#Bri... awesome-graphql > tools: security: https://github.com/chentsulin/awesome-graphql#tools---security https://github.com/chentsulin/awesome-graphql#tools---securi... ... W3C SOLID > Authorization and Access Control: https://github.com/solid/solid-spec#authorization-and-access-control https://github.com/solid/solid-spec#authorization-and-access... "Hosting SQLite Databases on GitHub Pages" (2021) re: sql.js-httpvfs, DuckDB https://news.ycombinator.com/item?id=28021766 https://news.ycombinator.com/item?id=28021766 [edit] TIL the MS ODBC 4.0 spec is MIT Licensed, on GitHub , and supports ~challenge/response token auth: "3.2.2 Web-based Authentication Flow with SQLBrowseConnect" https://github.com/microsoft/ODBC-Specification/blob/master/ODBC%204.0.md#322-web-based-authentication-flow-with-sqlbrowseconnect https://github.com/microsoft/ODBC-Specification/blob/master/... > Applications that are unable to allow drivers to pop up dialogs can call SQLBrowseConnect to connect to the service. > SQLBrowseConnect provides an iterative dialog between the driver and the application where the application passes in an initial input connection string. If the connection string contains sufficient information to connect, the driver responds with SQL_SUCCESS and an output connection string containing the complete set of connection attributes used. > If the initial input connection string does not contain sufficient information to connect to the source, the driver responds with SQL_NEED_DATA and an output connection string specifying informational attributes for the application (such as the authorization url) as well as required attributes to be specified in a subsequent call to SQLBrowseConnect. Attribute names returned by SQLBrowseConnect may include a colon followed by a localized identifier, and the value of the requested attribute is either a single question mark or a comma-separated list of valid values (optionally including localized identifiers) enclosed in curly braces. Optional attributes are returned preceded with an asterix (*). > In a Web-based authentication scenario, if SQLBrowseConnect is called with an input connection string containing an access token that has not expired, along with any other required properties, no additional information should be required. If the access token has expired and the connection string contains a refresh token, the driver attempts to refresh the connection using the supplied refresh token. Also TIL ODBC 4.0 supports: https://github.com/microsoft/ODBC-Specification https://github.com/microsoft/ODBC-Specification : > Semi-structured data – Tables whose schema may not be defined or may change on a row-by-row basis > Hierarchical Data – Data with nested structure (structured fields, lists) > Web Authentication model
worewood 5y ago
I was looking for something like this ! Thanks for the project

288 points by ithkuil 5y ago

This is a cute idea but it sounds headed for pain, as most typical client environments will come with bindings for multiple db's including sqlite. So if you want to use pg bindings, that probably means you want to use pg features, which aren't present in sqlite. There might be a use case where you want to use sqlite remotely as a client/server db, but even then, is this such a great approach? Sqlite has its own special features that likely don't fit the pg wire protocol that well.

dhzhzjsbevs 5y ago
It's literally in the first paragraph... > Postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.
- throwaway81523 5y ago
  Yes, I'm saying if the gui tools expect postgres, they are likely to use postgres features, which expands the amount of work this thing needs. If the tools are written specifically for this proxy, then ok, but why bring postgres into it at all? Write something sqlite-specific that exposes sqlite's features.
  - lmm 5y ago
    > Yes, I'm saying if the gui tools expect postgres, they are likely to use postgres features, Unlikely. A lot of such tools are mainly for displaying the data that's present; they might have support for displaying e.g. GIS types but they're not going to need to write GIS data into the database, and certainly not to the point where you can't run them at all if that's not possible.
    - tpetry 5y ago
      All of these tools will at least run an introspection query to get the available tables and columns to display the data. But these queries will fail because SQLite doesn't have these tables. So the idea is very nice, but any existing tool for PostgreSQL will not work. You need to either work with the psql terminal application or write a new client.
      - lukebennett 5y ago
        This is explicitly accounted for, as per the README: > Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables.
      - jpgvm 5y ago
        I believe you are looking for these: https://github.com/benbjohnson/postlite/blob/main/server.go#L288-L305 https://github.com/benbjohnson/postlite/blob/main/server.go#...
  - ricardobeat 5y ago
    > Write something sqlite-specific that exposes sqlite's features This is about using SQLite with existing tools that don't have support for it. It's very common since it's easier to support multiple remote database servers (MySQL, Postgres) vs adding the in-process model for SQLite only. Also you need to take the context into account - Fly.io has SQLite databases distributed across their nodes (see their post about replacing Consul [1]) and most likely want to be able to manage those remotely. Since SQLite doesn't have a server protocol, tools that have bindings for it only support working with local databases. [1] https://fly.io/blog/a-foolish-consistency/ https://fly.io/blog/a-foolish-consistency/
tootie 5y ago
I'd imagine the biggest use case will be to simplify unit tests and local dev.
- benbjohnson 5y ago
  Postlite author here. The primary use case is to administer remote SQL databases using existing GUI database tools like DBeaver. It's just a thin shim to translate between Postgres wire protocol to SQLite transactions so I don't think it would help with unit tests or local dev.

config_yml 5y ago

That was quick: https://twitter.com/flydotio/status/1508928704348577797 https://twitter.com/flydotio/status/1508928704348577797

heinrichhartman 5y ago
Indeed. First commit is two days old: https://github.com/benbjohnson/postlite/commit/35974771877cd71aa39758a4dbed1cab4e47ad34 https://github.com/benbjohnson/postlite/commit/35974771877cd...

endisneigh 5y ago

At this note are there any hosting providers that support update range for files? If there were you could basically query and write to a SQLite database entirely through http.

unilynx 5y ago
You also need locking then. WebDav implements locking over HTTP but I doubt most providers will implement it... (let alone according to spec and/or whatever a MS backend expects) https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html
ruslan_talpa 5y ago
You can query and write to SQLite over http with [1] (i wrote it) but it's not through the method you describe (range for files). https://github.com/subzerocloud/blue-steel https://github.com/subzerocloud/blue-steel

rkwasny 5y ago

Waiting for first benchmarks, we might have a suprise

benbjohnson 5y ago
Postlite author here. This proxy is not meant to be the primary database connection for a SQLite database. It's only use case is to support remote administration of SQLite databases using existing GUI tools like DBeaver.

newusertoday 5y ago

I use usql, it uses different approach but i am able to use same psql commands to query multiple different db's that is postgres,sqlite, mysql etc. I only have one gripe with it and that is its dependency on glibc which could have been avoided given that it is go application.

benbjohnson 5y ago
Postlite author here. I haven't used usql but it looks really interesting. I was looking for a way to connect existing GUI tools to remote SQLite databases which I don't believe usql supports.

frabert 5y ago

Well now I want a virtual table interface to pgsql so I can query Postgres databases from SQLite too!

ansible 5y ago
It would be neat to have a library that has the same API as Sqlite3, but then acts as a network proxy for a remote PostgreSQL database. You could then use that plus the above network proxy to move a Sqlite3 database off of the local system to a remote system, without having to change the existing codebase. This would also give you multiple remote clients as well.
- frabert 5y ago
  You could also join tables from different databases! Imagine the performance!

eatonphil 5y ago

Without query translation you won't be able to do much date querying since date formatting and getting now() are very different in postgres and sqlite. Also, sqlite doesn't support INTERVAL which I personally use heavily in postgres. Curious how the author plans to deal with this since they mentioned they're interested in supporting BI tools that presumably wouldn't issue sqlite queries if they think they're talking to postgres.

benbjohnson 5y ago
Postlite author here. Patrick DeVivo mentioned using BI tools against Postlite, which sounded really interesting, but I think you're right. Those tools are going to heavily rely on dates which will not translate well between Postgres & SQLite. I don't have any plans for query translation except for some basic client startup query handling (e.g. SET, SHOW). Postlite is really just meant as a thin shim to allow GUI tools to work remotely on SQLite databases.

edwinyzh 5y ago

Very coo! I think the most useful use case would be managing remote SQLite dbs using existing database GUI tools. One question - does it support custom collations? That might prevent us from using it if it doesn't...

benbjohnson 5y ago
Postlite author here. Yes, remote administration with existing GUI tools is the primary (and probably only) use case for Postlite. It's just a slim passthrough layer between Postgres and SQLite so there's nothing explicitly supported surrounding collations. What kind of support are you thinking?
- edwinyzh 5y ago
  I don't remember the details, but the framework I use uses some custom collation when creating tables in a sqlite db, and when tools like SQLite Studio is performing some kind of actions it complains something like "collation not recognized/supported"...

traceroute66 5y ago

I have to say I have a disliking for what appears to be the current trend of marketing things as "postgres (wire) compatible". CockroachDB is another example that does the same. The implication is that "if you can do it in postgres, you can do it in X". But the reality is, of course, different. If you are using databases properly, not just as a blackbox dumping ground, then if you use postgres on the wire, its because you are talking to postgres and using postgres specific features (like you should). Unless you have feature parity, don't tell me I can connect to another database using my postgres libraries. Its pointless. Its meaningless. Why would I want to do that if I can't use the features ? Rant over. ;-)

kasey_junk 5y ago
Largely for driver coverage. Postgres drivers are available in tons of languages so by being wire compatible you can skip all that work.
- traceroute66 5y ago
  > Postgres drivers are available in tons of languages So are SQLite drivers (and MySQL and all the other top-10/top-20 databases). Why add un-necessary abstraction ? Why add an un-necessary layer with bugs and edge-cases that you will subsequently waste hours of your life debugging ? Plus, if we are talking about drivers, then surely if you're going to be using SQLite, you should be using SQLite drivers because they will be inherently simpler and smaller than the Postgres ones because of the much reduced feature-set of SQLite. > What SQLite3 driver out there allows you to talk over a network connection to a remote database? (Replied here because I can't reply to your point below) Maybe that's because, as per the SQLite website[1], SQLite was never intended to be used as a client/server database !!! I would argue that by attempting to "square peg round hole" SQLite into being a network database, you are only opening yourself up for unknown troubles down the road. There are many excellent client/server databases out there (including the postgres referred to here). If you need that functionality you should be using the right tool for the job, there's no need to shoehorn SQLite into it. [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

benbjohnson 5y ago

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. :)

eigenvalue 5y ago

For the stated use case, I have found DB Browser to be an invaluable tool to graphically interact with SQLite databases: https://sqlitebrowser.org/ https://sqlitebrowser.org/ You can do basically anything you would need to do to a database with it, and it’s very performant and cross platform.

benbjohnson 5y ago
DB Browser for SQLite is a great tool but I don't believe it supports connecting to remote databases, does it?
- Asmod4n 5y ago
  Why should it need to? sshfs should work fine, probably.
  - benbjohnson 5y ago
    I don't believe sshfs is safe with SQLite with concurrent use: https://github.com/libfuse/sshfs/issues/204#issue-561260021 https://github.com/libfuse/sshfs/issues/204#issue-561260021
    - deleted 5y ago
      [deleted]
- londons_explore 5y ago
  I run it with ssh -X to run the remote GUI. It's a bit sluggish, but it's a good way to quickly browse a DB to see whats there.

swlkr 5y ago

This is great! Thanks for this, it never occurred to me that I could improve on ssh + sqlite3 on the cli, but here it is. Great for those times when I want to check some data but don’t want to ssh in

philshem 5y ago

One could also use sshfs to access a remote SQLite DB file. https://github.com/libfuse/sshfs https://github.com/libfuse/sshfs

benbjohnson 5y ago
I don't believe sshfs is safe to use with concurrent access on the database: https://github.com/libfuse/sshfs/issues/204#issue-561260021 https://github.com/libfuse/sshfs/issues/204#issue-561260021

ejones 5y ago

Awesome to see work in the DB wire compatible space. On the MySQL side, there was MySQL Proxy (https://github.com/mysql/mysql-proxy https://github.com/mysql/mysql-proxy), which was scriptable with Lua, with which you could create your own MySQL wire compatible connections. Unfortunately it appears to have been abandoned by Oracle and IIRC doesn't work with 5.7 and beyond. I used it in the past to hack together a MySQL wire adapter for Interana (https://scuba.io/ https://scuba.io/). I guess these days the best approach for connecting arbitrary data sources to existing drivers, at least for OLAP, is Apache Calcite (https://calcite.apache.org/ https://calcite.apache.org/). Unfortunately that feels a little more involved.

westurner 5y ago
NYTimes/DBSlayer (2007) wraps MySQL in JSON: https://open.nytimes.com/introducing-dbslayer-64d7168a143f https://open.nytimes.com/introducing-dbslayer-64d7168a143f ODBC > Bridging configurations: https://en.wikipedia.org/wiki/Open_Database_Connectivity#Bridging_configurations https://en.wikipedia.org/wiki/Open_Database_Connectivity#Bri... awesome-graphql > tools: security: https://github.com/chentsulin/awesome-graphql#tools---security https://github.com/chentsulin/awesome-graphql#tools---securi... ... W3C SOLID > Authorization and Access Control: https://github.com/solid/solid-spec#authorization-and-access-control https://github.com/solid/solid-spec#authorization-and-access... "Hosting SQLite Databases on GitHub Pages" (2021) re: sql.js-httpvfs, DuckDB https://news.ycombinator.com/item?id=28021766 https://news.ycombinator.com/item?id=28021766 [edit] TIL the MS ODBC 4.0 spec is MIT Licensed, on GitHub , and supports ~challenge/response token auth: "3.2.2 Web-based Authentication Flow with SQLBrowseConnect" https://github.com/microsoft/ODBC-Specification/blob/master/ODBC%204.0.md#322-web-based-authentication-flow-with-sqlbrowseconnect https://github.com/microsoft/ODBC-Specification/blob/master/... > Applications that are unable to allow drivers to pop up dialogs can call SQLBrowseConnect to connect to the service. > SQLBrowseConnect provides an iterative dialog between the driver and the application where the application passes in an initial input connection string. If the connection string contains sufficient information to connect, the driver responds with SQL_SUCCESS and an output connection string containing the complete set of connection attributes used. > If the initial input connection string does not contain sufficient information to connect to the source, the driver responds with SQL_NEED_DATA and an output connection string specifying informational attributes for the application (such as the authorization url) as well as required attributes to be specified in a subsequent call to SQLBrowseConnect. Attribute names returned by SQLBrowseConnect may include a colon followed by a localized identifier, and the value of the requested attribute is either a single question mark or a comma-separated list of valid values (optionally including localized identifiers) enclosed in curly braces. Optional attributes are returned preceded with an asterix (*). > In a Web-based authentication scenario, if SQLBrowseConnect is called with an input connection string containing an access token that has not expired, along with any other required properties, no additional information should be required. If the access token has expired and the connection string contains a refresh token, the driver attempts to refresh the connection using the supplied refresh token. Also TIL ODBC 4.0 supports: https://github.com/microsoft/ODBC-Specification https://github.com/microsoft/ODBC-Specification : > Semi-structured data – Tables whose schema may not be defined or may change on a row-by-row basis > Hierarchical Data – Data with nested structure (structured fields, lists) > Web Authentication model

worewood 5y ago

I was looking for something like this ! Thanks for the project