5 ms·

Show HN: My Go SQLite driver did poorly on a benchmark, so I fixed it

234 points by ncruces 3y ago

https://github.com/ncruces/go-sqlite3 https://github.com/ncruces/go-sqlite3 was doing poorly on this benchmark that was posted yesterday to HackerNews [1]. With the help of some pprof, I was able to trace it to a serious performance regression introduced two weeks ago, and come up with the fix (happy to field questions, if you're interested in the nitty gritty). It's not the fastest driver around, but it's no longer the slowest: comfortably middle of the pack. It's based on a WASM build of SQLite, and thanks to https://wazero.io https://wazero.io doesn't need CGO. [1]: https://news.ycombinator.com/item?id=38626698 https://news.ycombinator.com/item?id=38626698

38 3y ago
[flagged]
- ARandomerDude 3y ago
  First part of the README that hasn't changed in 2 months: > Sqinn-Go is a Go (Golang) library for accessing SQLite databases without cgo. It uses Sqinn https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn under the hood. It starts Sqinn as a child process (os/exec) and communicates with Sqinn over stdin/stdout/stderr. The Sqinn child process then does the SQLite work. > If you want SQLite but do not want cgo, Sqinn-Go can be a solution. This seems pretty clear to me about what's happening. It makes an OS call to a third-party executable (sqinn), then pipes the result from stdout back into the Go code. The advantage is you don't have to compile the C code alongside your Go code. Honestly, I don't really know how much more clear the author could be. I guess if you don't know what stdin, stdout, and stderr are it might be confusing? It's hard to imagine a programmer who is interested in this library but isn't immediately familiar with those concepts.
ridiculous_fish 3y ago
You mentioned a compiler crash - I suppose that means the Go compiler? That's pretty interesting! Any more information about the crash? Did you file a bug report?
- ncruces 3y ago
  No, I meant in wazero, when using their AOT WASM-to-native compiler engine. So, my bindings wrap a WASM build of SQLite. The WASM module exports functions (like sqlite3_exec) for Go to call, and imports Go functions (like go_full_pathname) for WASM to call. To call these functions you need to get “function pointers” to them by “name”, and this is actually a very slow process, orders of magnitude slower than actually calling the function. So I used to setup all the function pointers at instantiation time, and then reuse them for every call. But this is not supported, and fails, when used reentrantly: Go calls sqlite3_step, which in turn calls some Go code (e.g. to implement a virtual table) which then calls sqlite3_step again (the virtual table reads data from another table). Only happens when the same function is called: stack pointers are kept in a structure that gets reused, so the stack gets corrupted. The simple fix is to never reuse these structures, but that makes everything significantly slower. So I needed to cache them, but not allow concurrent reuse. The caching I was already doing (hash based) was not good enough. So I switched to a 4x wider PLRU bit cache. Hit rates are now over 99%, the cache is itself fast, and lazily filling it improves startup times. Fixing that hot path surfaced others, also recent features. Doing badly on a benchmark is a great way to improve! PS: wazero is a great piece of software, tested and fuzzed to death. They delayed releasing their next compiler engine for 3 months because of a stack corruption bug that only happened rarely in fuzzing (because of an interaction with signal handlers used by the Go fuzzer). They were all over my bug, because the result was also stack corruption, on a compiler that had been tested to death and passed. But my bug was due to API misuse, not any wazero fault.
  - tedunangst 3y ago
    That's annoying about wazero function not allowing reentrant calls. The docs do say it's not goroutine safe, which is easy to miss on its own, but nothing about this.
    - ncruces 3y ago
      https://github.com/tetratelabs/wazero/pull/1853 https://github.com/tetratelabs/wazero/pull/1853
nicetryguy 3y ago
I'm glad with community feedback and research you were able to bolster your driver!
carbocation 3y ago
Basically a throwaway comment that is somewhat off topic, but your current Github README is very "inside baseball." I think it would be worth a one-sentence introduction to what your library can do (allows an app to incorporate a database, and it also has compatibility with a very popular library called SQLite, etc), then to tout the benefits of not requiring CGO, etc.
- KingOfCoders 3y ago
  I think when you create a website (write a document, write a README) it is important to imagine the top 3 reader/visitor personas. What do they want from the README (website)? 1. Go+SQLite user: No CGO 2. Go user 3. Wanting to use the library 4. Someone following a link from HN 5. ...
  - carbocation 3y ago
    Agreed, although I think a brief one-line intro to what this thing does wouldn't really interrupt the flow for any of the above use-cases. Right now, the top of the README is a list of sub-libraries, listing the library first. Personally, I would add ~2 sentences in the order I outlined, and then I would reorient the list (currently library-first) in a use-case first arrangement like you mentioned. I mean, realistically my git repos are a mess, so who am I to talk? But this one looks promising enough such that it's worth offering a bit of feedback.
    - KingOfCoders 3y ago
      Agree, 4. Someone following a link from HN I'm often confused when clicking through from HN and can't figure out from the website or README what the project is doing.
    - ncruces 3y ago
      Thank you for the feedback! I tried to improve the copy a bit, but this is clearly not my thing…
  - trevyn 3y ago
jchw 3y ago
Hey, that's awesome. I think if I wanted a CGo-less solution, I would've probably picked the modernc variation, but honestly this looks very polished + the modernc version is known to be slow in some cases (though, the benchmarks actually made it look alright, realistically.) One question I have is, is there a strategy to stop the performance from accidentally regressing in the future? Seems like it might not be a bad idea to include some benchmarks in the repository if you don't already. Also, personally, I am interested in the nitty gritty of why it was slow and how you fixed it, so if you write it anywhere in this thread at least I will definitely read it through.
- shabbyrobe 3y ago
  > I would've probably picked the modernc variation Heads up about the modernc library, it has been stuck on an old version of sqlite for several months [1]. It seems like maintainer time is the limiting factor [2]. There has been a call to arms on that issue page, the maintainer is looking for help, but it looks like not much has arrived. It seems like it might trace back to blockers in the C-to-Go compiler. It's a major undertaking and a very impressive piece of work, but I'm not surprised it's a struggle when big roadblocks get hit. I hope they find a way to progress, but I'm very relieved to be seeing some CGo-free alternatives like ncruces/go-sqlite3 emerging. I'm going to give it a try for sure and see if I can live with the compromises. Squinn-go looks very compelling too, but I don't like that it requires the squinn binary to already be installed on a user's machine, I think that gives with one hand and takes with the other: sure, I get to avoid CGo, but I also lose the turnkey, single-command install, static build benefits Go brings out of the box. Seconding the point about nitty gritty, I'd read it for sure too! [1]: https://gitlab.com/cznic/sqlite/-/issues/154 [2]: https://gitlab.com/cznic/sqlite/-/issues/164
  - ncruces 3y ago
    The nitty gritty is here, mostly: https://news.ycombinator.com/item?id=38652566 https://news.ycombinator.com/item?id=38652566 About my driver, the major compromise to be aware of is that WAL mode support is limited. No tiptoeing here: I call that caveat out in the front page. My driver reimplements the entire SQLite OS Interface (VFS) in pure Go. This makes it very portable, but it's also a risk: it's definitely not as extensively tested as SQLite's. And the WASM sandboxing model makes shared memory a hard problem to solve, which prevents WAL mode from working. I have some ideas for work arounds, but: they're hard to implement, and hard to "prove" correct. Idea 1 is a VFS for WAL where SQLite believes it's using memory journal mode, but the wrapper uses the standard WAL format to persist disk. It's a lot of work, but standard SQLite tools can recover a crashed DB. Idea 2 is for the WASM host to simulate shared memory through copying. This should work (there are explicit locks and memory barriers to use as copy points), but it's hard to "prove" correct (without bothering SQLite developers to death with details). I had some hope that the work being done in libsql to virtualize the WAL would help, but their API for this is just terrible. They simply extracted and exposed internal SQLite stuff, but have no documentation, no usage examples, or even any public VFS implementation using these APIs. And I honestly don't trust their ability to maintain a quality fork. An API that's not dogfooded nor documented is not an API.
willi59549879 3y ago
I use the Mattn library. I don't care as much about not having Cho in my program. Sqlite project uses c and they have rigourous testing for each release. With a driver written in go I couldn't be sure what is different. This could result in bugs or features that are not available.
- acqq 3y ago
  Moreover, if it's about cross-compilation, why not using zig? https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...
  - ncruces 3y ago
    It's not just about cross compilation. There's also debuggability and other aspects of development. It's an anecdote, but I've had a better experience debugging issues while developing SQLite extensions and VFSes for this driver than I've had in other "cross runtime" / "bindings" work I've done before (with Cgo, JNI, P/Invoke).
    - acqq 3y ago
      I would need to read more about that to understand how it could be a problem. You see, I've literally spend decades using C and I can never imagine how adding that what I understand to be huge dependencies like wazero.io and executing huge wasms binaries made from original C sources can be more debuggable than debugging a directly compiled native code -- I'm just disclosing my bias, and I want to be clear about it, I'm certainly not doubting your "better experience." Edit: have you tried that zig workflow for cross-compiling? Edit2: and I'm also aware that zig also uses wasm binary and its own wasm2c, written from scratch, for its own bootstrapping: https://ziglang.org/news/goodbye-cpp/ https://ziglang.org/news/goodbye-cpp/
      - ncruces 3y ago
        I don't mean debugging the C bits (although having a stack trace and an exact line number when I cause SQLite to crash is rather nice). I mean developing a complex wrapper/binding that involves Go calls C, which then calls back to Go, which again calls C, and putting breakpoints on the Go bits multiple layers deep, and being able to inspect the full stack of those Go bits. Last I've tried with Cgo, stuff has a high probability of hanging your entire process (e.g. if the C library acquired some lock or something). There's also the permanent fear that (bugs, API misuse…) leads to the C library corrupting your Go memory, which is much harder to do if the C bits run in a sandbox. SQLite is "imune" to bugs, but not API misuse. Tbh, I haven't had a better experience with JNI, JNA, PInvoke… And yes, I've looked at zig, even used it initially to build the WASM blob. I actually think it'd be a worthy endeavour to build a better Cgo driver. But having Cgo and WASM versions of this in the same repo is just too much effort at this point (I've considered it).
emmanueloga_ 3y ago
A bit of a tangent but for those who’d like to use SQLite for a backend, running it as a separate daemon could be an interesting choice, which would also remove Cgo from the build and maybe make things like separate background job processes easier to orchestrate. See [1], [2]. —- 1: https://github.com/tursodatabase/libsql/tree/main/libsql-server#client-libraries https://github.com/tursodatabase/libsql/tree/main/libsql-ser... 2: https://news.ycombinator.com/item?id=38602175 https://news.ycombinator.com/item?id=38602175
euroderf 3y ago
Can someone explain what is the essential, practical difference between a library that implements package database/sql (such as mattn's) and one that does not (such as zombiezen or crawshaw) ? Not using a standard interface (i.e. database/sql) sounds dodgy, but maybe there's really no problem. Apologies if this is a stupid question.
- gunapologist99 3y ago
  The database/sql package serves as an intermediary abstraction layer (but still low-level) between the programmer's standardized API and the database. It's responsible for managing interactions with the database, such as communicating with a remote database server or accessing a local database like SQLite. Keep in mind that database/sql requires a compatible database driver (e.g., mattn/go-sqlite3) to facilitate this communication. In other programming environments, this concept is like ODBC or JDBC. If you were using an ORM, usually the ORM would expect to be sitting on top of the lower-level database/sql. (It's turtles all the way down!) Dropping the database/sql compatibility requirement allows for more direct (and possibly faster) interaction with the database. This approach can unlock database-specific features and potentially improve performance, but it clearly deviates from the standard API envisioned in database/sql. You might also end up losing the ability to use an ORM, but usually you don't really want to mix ORM and non-ORM in the same codebase anyway. However, adhering to database/sql also brings significant benefits, such as simplifing switching between different database systems with only small code alterations. While schema changes might still be necessary, they are generally external to the core codebase.
  - euroderf 3y ago
    Thank you for this explanation. I've already decided Yes to SQLite (only) and No to ORMs, so maybe zombiezen is the way to go.
  - ncruces 3y ago
    This is a great answer to the parent question, I'll just add that I (try) to provide both a database/sql driver, and a lower level direct mapping of the SQLite C API. Pretty much the only thing the lower level wrapper does for you is simplify some of the memory management inherent to a C API (who allocates? who frees? for everything even something as trivial as an error message: GC+copying handles lightweight types like strings and errors, io.Closer is used for everything else). At the same time, I also made a serious effort to make many SQLite features (like blob IO, nested transactions, JSON, time handling) easy to access for users of the database/sql driver.
blitz_skull 3y ago
The "[!IMPORTANT]" block in the README is really fancy. I had no idea you could do that.
- acatton 3y ago
  There are more "Alert blocks" available https://docs.github.com/en/get-started/writing-on-github/getting-started-with-writing-and-formatting-on-github/basic-writing-and-formatting-syntax#alerts https://docs.github.com/en/get-started/writing-on-github/get... This is github specific, not part of commonmark AFAIK
  - galleywest200 3y ago
    They are becoming more and more widely used, such as in Obsidian with their "callouts": https://help.obsidian.md/Editing+and+formatting/Callouts https://help.obsidian.md/Editing+and+formatting/Callouts
koeng 3y ago
Very cool! I’ve been looking at using SQLite-vss for fast vector sorting, but it requires CGO. I imagine if I do WASM compilation directly from C, I might be able to get around using CGO. Do you have any idea able the feasibility of that using your package? Thanks!
- ncruces 3y ago
  One problem with WASM is that, at this point (2023), the only easy way of building that, is to statically link everything C into one WASM blob (so you can't use my blob). But you can try to use my build scripts, and tweak my code, to build SQLite+sqlite-vss+Faiss into one large WASM. If you use my patches/imports/exports/config for SQLite, my binding will gladly take your WASM blob, without modification to the Go code. All you need to do is set this global variable (and not import package embed, which embeds my WASM and sets this variable for you at init): https://pkg.go.dev/github.com/ncruces/go-sqlite3#pkg-variables https://pkg.go.dev/github.com/ncruces/go-sqlite3#pkg-variabl... Ping me on GitHub if you need help. Vector search is on my meters long todo list, but I'd rather do it in Go if at all possible.
  - koeng 3y ago
    How would you do it in Go? It is my understanding that FAISS is integrated into the SQL database itself (using embedding in the table), so writing it in Go wouldn't integrate it in the same way. > But you can try to use my build scripts, and tweak my code, to build SQLite+sqlite-vss+Faiss into one large WASM That makes perfect sense to me! I'm looking into your build scripts right now. I'm pretty proficient in Go, but haven't touched wasm or C much, so. I may reach out with more questions.
    - ncruces 3y ago
      You need a virtual table (same as sqlite-vss) and a library that does cosine similarity indexing (Faiss here)… which I know little about. My plan was to evaluate Go for cosine similarity indexing, and if I found something promising, port the virtual table.

234 points by ncruces 3y ago

38 3y ago

[flagged]

ARandomerDude 3y ago
First part of the README that hasn't changed in 2 months: > Sqinn-Go is a Go (Golang) library for accessing SQLite databases without cgo. It uses Sqinn https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn under the hood. It starts Sqinn as a child process (os/exec) and communicates with Sqinn over stdin/stdout/stderr. The Sqinn child process then does the SQLite work. > If you want SQLite but do not want cgo, Sqinn-Go can be a solution. This seems pretty clear to me about what's happening. It makes an OS call to a third-party executable (sqinn), then pipes the result from stdout back into the Go code. The advantage is you don't have to compile the C code alongside your Go code. Honestly, I don't really know how much more clear the author could be. I guess if you don't know what stdin, stdout, and stderr are it might be confusing? It's hard to imagine a programmer who is interested in this library but isn't immediately familiar with those concepts.

ridiculous_fish 3y ago

You mentioned a compiler crash - I suppose that means the Go compiler? That's pretty interesting! Any more information about the crash? Did you file a bug report?

ncruces 3y ago
No, I meant in wazero, when using their AOT WASM-to-native compiler engine. So, my bindings wrap a WASM build of SQLite. The WASM module exports functions (like sqlite3_exec) for Go to call, and imports Go functions (like go_full_pathname) for WASM to call. To call these functions you need to get “function pointers” to them by “name”, and this is actually a very slow process, orders of magnitude slower than actually calling the function. So I used to setup all the function pointers at instantiation time, and then reuse them for every call. But this is not supported, and fails, when used reentrantly: Go calls sqlite3_step, which in turn calls some Go code (e.g. to implement a virtual table) which then calls sqlite3_step again (the virtual table reads data from another table). Only happens when the same function is called: stack pointers are kept in a structure that gets reused, so the stack gets corrupted. The simple fix is to never reuse these structures, but that makes everything significantly slower. So I needed to cache them, but not allow concurrent reuse. The caching I was already doing (hash based) was not good enough. So I switched to a 4x wider PLRU bit cache. Hit rates are now over 99%, the cache is itself fast, and lazily filling it improves startup times. Fixing that hot path surfaced others, also recent features. Doing badly on a benchmark is a great way to improve! PS: wazero is a great piece of software, tested and fuzzed to death. They delayed releasing their next compiler engine for 3 months because of a stack corruption bug that only happened rarely in fuzzing (because of an interaction with signal handlers used by the Go fuzzer). They were all over my bug, because the result was also stack corruption, on a compiler that had been tested to death and passed. But my bug was due to API misuse, not any wazero fault.
- tedunangst 3y ago
  That's annoying about wazero function not allowing reentrant calls. The docs do say it's not goroutine safe, which is easy to miss on its own, but nothing about this.
  - ncruces 3y ago
    https://github.com/tetratelabs/wazero/pull/1853 https://github.com/tetratelabs/wazero/pull/1853

nicetryguy 3y ago

I'm glad with community feedback and research you were able to bolster your driver!

carbocation 3y ago

Basically a throwaway comment that is somewhat off topic, but your current Github README is very "inside baseball." I think it would be worth a one-sentence introduction to what your library can do (allows an app to incorporate a database, and it also has compatibility with a very popular library called SQLite, etc), then to tout the benefits of not requiring CGO, etc.

KingOfCoders 3y ago
I think when you create a website (write a document, write a README) it is important to imagine the top 3 reader/visitor personas. What do they want from the README (website)? 1. Go+SQLite user: No CGO 2. Go user 3. Wanting to use the library 4. Someone following a link from HN 5. ...
- carbocation 3y ago
  Agreed, although I think a brief one-line intro to what this thing does wouldn't really interrupt the flow for any of the above use-cases. Right now, the top of the README is a list of sub-libraries, listing the library first. Personally, I would add ~2 sentences in the order I outlined, and then I would reorient the list (currently library-first) in a use-case first arrangement like you mentioned. I mean, realistically my git repos are a mess, so who am I to talk? But this one looks promising enough such that it's worth offering a bit of feedback.
  - KingOfCoders 3y ago
    Agree, 4. Someone following a link from HN I'm often confused when clicking through from HN and can't figure out from the website or README what the project is doing.
  - ncruces 3y ago
    Thank you for the feedback! I tried to improve the copy a bit, but this is clearly not my thing…
- trevyn 3y ago

jchw 3y ago

Hey, that's awesome. I think if I wanted a CGo-less solution, I would've probably picked the modernc variation, but honestly this looks very polished + the modernc version is known to be slow in some cases (though, the benchmarks actually made it look alright, realistically.) One question I have is, is there a strategy to stop the performance from accidentally regressing in the future? Seems like it might not be a bad idea to include some benchmarks in the repository if you don't already. Also, personally, I am interested in the nitty gritty of why it was slow and how you fixed it, so if you write it anywhere in this thread at least I will definitely read it through.

shabbyrobe 3y ago
> I would've probably picked the modernc variation Heads up about the modernc library, it has been stuck on an old version of sqlite for several months [1]. It seems like maintainer time is the limiting factor [2]. There has been a call to arms on that issue page, the maintainer is looking for help, but it looks like not much has arrived. It seems like it might trace back to blockers in the C-to-Go compiler. It's a major undertaking and a very impressive piece of work, but I'm not surprised it's a struggle when big roadblocks get hit. I hope they find a way to progress, but I'm very relieved to be seeing some CGo-free alternatives like ncruces/go-sqlite3 emerging. I'm going to give it a try for sure and see if I can live with the compromises. Squinn-go looks very compelling too, but I don't like that it requires the squinn binary to already be installed on a user's machine, I think that gives with one hand and takes with the other: sure, I get to avoid CGo, but I also lose the turnkey, single-command install, static build benefits Go brings out of the box. Seconding the point about nitty gritty, I'd read it for sure too! [1]: https://gitlab.com/cznic/sqlite/-/issues/154 [2]: https://gitlab.com/cznic/sqlite/-/issues/164
- ncruces 3y ago
  The nitty gritty is here, mostly: https://news.ycombinator.com/item?id=38652566 https://news.ycombinator.com/item?id=38652566 About my driver, the major compromise to be aware of is that WAL mode support is limited. No tiptoeing here: I call that caveat out in the front page. My driver reimplements the entire SQLite OS Interface (VFS) in pure Go. This makes it very portable, but it's also a risk: it's definitely not as extensively tested as SQLite's. And the WASM sandboxing model makes shared memory a hard problem to solve, which prevents WAL mode from working. I have some ideas for work arounds, but: they're hard to implement, and hard to "prove" correct. Idea 1 is a VFS for WAL where SQLite believes it's using memory journal mode, but the wrapper uses the standard WAL format to persist disk. It's a lot of work, but standard SQLite tools can recover a crashed DB. Idea 2 is for the WASM host to simulate shared memory through copying. This should work (there are explicit locks and memory barriers to use as copy points), but it's hard to "prove" correct (without bothering SQLite developers to death with details). I had some hope that the work being done in libsql to virtualize the WAL would help, but their API for this is just terrible. They simply extracted and exposed internal SQLite stuff, but have no documentation, no usage examples, or even any public VFS implementation using these APIs. And I honestly don't trust their ability to maintain a quality fork. An API that's not dogfooded nor documented is not an API.

willi59549879 3y ago

I use the Mattn library. I don't care as much about not having Cho in my program. Sqlite project uses c and they have rigourous testing for each release. With a driver written in go I couldn't be sure what is different. This could result in bugs or features that are not available.

acqq 3y ago
Moreover, if it's about cross-compilation, why not using zig? https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...
- ncruces 3y ago
  It's not just about cross compilation. There's also debuggability and other aspects of development. It's an anecdote, but I've had a better experience debugging issues while developing SQLite extensions and VFSes for this driver than I've had in other "cross runtime" / "bindings" work I've done before (with Cgo, JNI, P/Invoke).
  - acqq 3y ago
    I would need to read more about that to understand how it could be a problem. You see, I've literally spend decades using C and I can never imagine how adding that what I understand to be huge dependencies like wazero.io and executing huge wasms binaries made from original C sources can be more debuggable than debugging a directly compiled native code -- I'm just disclosing my bias, and I want to be clear about it, I'm certainly not doubting your "better experience." Edit: have you tried that zig workflow for cross-compiling? Edit2: and I'm also aware that zig also uses wasm binary and its own wasm2c, written from scratch, for its own bootstrapping: https://ziglang.org/news/goodbye-cpp/ https://ziglang.org/news/goodbye-cpp/
    - ncruces 3y ago
      I don't mean debugging the C bits (although having a stack trace and an exact line number when I cause SQLite to crash is rather nice). I mean developing a complex wrapper/binding that involves Go calls C, which then calls back to Go, which again calls C, and putting breakpoints on the Go bits multiple layers deep, and being able to inspect the full stack of those Go bits. Last I've tried with Cgo, stuff has a high probability of hanging your entire process (e.g. if the C library acquired some lock or something). There's also the permanent fear that (bugs, API misuse…) leads to the C library corrupting your Go memory, which is much harder to do if the C bits run in a sandbox. SQLite is "imune" to bugs, but not API misuse. Tbh, I haven't had a better experience with JNI, JNA, PInvoke… And yes, I've looked at zig, even used it initially to build the WASM blob. I actually think it'd be a worthy endeavour to build a better Cgo driver. But having Cgo and WASM versions of this in the same repo is just too much effort at this point (I've considered it).

emmanueloga_ 3y ago

A bit of a tangent but for those who’d like to use SQLite for a backend, running it as a separate daemon could be an interesting choice, which would also remove Cgo from the build and maybe make things like separate background job processes easier to orchestrate. See [1], [2]. —- 1: https://github.com/tursodatabase/libsql/tree/main/libsql-server#client-libraries https://github.com/tursodatabase/libsql/tree/main/libsql-ser... 2: https://news.ycombinator.com/item?id=38602175 https://news.ycombinator.com/item?id=38602175

euroderf 3y ago

Can someone explain what is the essential, practical difference between a library that implements package database/sql (such as mattn's) and one that does not (such as zombiezen or crawshaw) ? Not using a standard interface (i.e. database/sql) sounds dodgy, but maybe there's really no problem. Apologies if this is a stupid question.

gunapologist99 3y ago
The database/sql package serves as an intermediary abstraction layer (but still low-level) between the programmer's standardized API and the database. It's responsible for managing interactions with the database, such as communicating with a remote database server or accessing a local database like SQLite. Keep in mind that database/sql requires a compatible database driver (e.g., mattn/go-sqlite3) to facilitate this communication. In other programming environments, this concept is like ODBC or JDBC. If you were using an ORM, usually the ORM would expect to be sitting on top of the lower-level database/sql. (It's turtles all the way down!) Dropping the database/sql compatibility requirement allows for more direct (and possibly faster) interaction with the database. This approach can unlock database-specific features and potentially improve performance, but it clearly deviates from the standard API envisioned in database/sql. You might also end up losing the ability to use an ORM, but usually you don't really want to mix ORM and non-ORM in the same codebase anyway. However, adhering to database/sql also brings significant benefits, such as simplifing switching between different database systems with only small code alterations. While schema changes might still be necessary, they are generally external to the core codebase.
- euroderf 3y ago
  Thank you for this explanation. I've already decided Yes to SQLite (only) and No to ORMs, so maybe zombiezen is the way to go.
- ncruces 3y ago
  This is a great answer to the parent question, I'll just add that I (try) to provide both a database/sql driver, and a lower level direct mapping of the SQLite C API. Pretty much the only thing the lower level wrapper does for you is simplify some of the memory management inherent to a C API (who allocates? who frees? for everything even something as trivial as an error message: GC+copying handles lightweight types like strings and errors, io.Closer is used for everything else). At the same time, I also made a serious effort to make many SQLite features (like blob IO, nested transactions, JSON, time handling) easy to access for users of the database/sql driver.

blitz_skull 3y ago

The "[!IMPORTANT]" block in the README is really fancy. I had no idea you could do that.

acatton 3y ago
There are more "Alert blocks" available https://docs.github.com/en/get-started/writing-on-github/getting-started-with-writing-and-formatting-on-github/basic-writing-and-formatting-syntax#alerts https://docs.github.com/en/get-started/writing-on-github/get... This is github specific, not part of commonmark AFAIK
- galleywest200 3y ago
  They are becoming more and more widely used, such as in Obsidian with their "callouts": https://help.obsidian.md/Editing+and+formatting/Callouts https://help.obsidian.md/Editing+and+formatting/Callouts

koeng 3y ago

Very cool! I’ve been looking at using SQLite-vss for fast vector sorting, but it requires CGO. I imagine if I do WASM compilation directly from C, I might be able to get around using CGO. Do you have any idea able the feasibility of that using your package? Thanks!

ncruces 3y ago
One problem with WASM is that, at this point (2023), the only easy way of building that, is to statically link everything C into one WASM blob (so you can't use my blob). But you can try to use my build scripts, and tweak my code, to build SQLite+sqlite-vss+Faiss into one large WASM. If you use my patches/imports/exports/config for SQLite, my binding will gladly take your WASM blob, without modification to the Go code. All you need to do is set this global variable (and not import package embed, which embeds my WASM and sets this variable for you at init): https://pkg.go.dev/github.com/ncruces/go-sqlite3#pkg-variables https://pkg.go.dev/github.com/ncruces/go-sqlite3#pkg-variabl... Ping me on GitHub if you need help. Vector search is on my meters long todo list, but I'd rather do it in Go if at all possible.
- koeng 3y ago
  How would you do it in Go? It is my understanding that FAISS is integrated into the SQL database itself (using embedding in the table), so writing it in Go wouldn't integrate it in the same way. > But you can try to use my build scripts, and tweak my code, to build SQLite+sqlite-vss+Faiss into one large WASM That makes perfect sense to me! I'm looking into your build scripts right now. I'm pretty proficient in Go, but haven't touched wasm or C much, so. I may reach out with more questions.
  - ncruces 3y ago
    You need a virtual table (same as sqlite-vss) and a library that does cosine similarity indexing (Faiss here)… which I know little about. My plan was to evaluate Go for cosine similarity indexing, and if I found something promising, port the virtual table.