node-sqlite3 needs more hands on the deck

[kewde]

Hi,

If you are dependent on this package for your project, please read the following: https://kewde.github.io/sqlite

I hope some people are willing to step up and take some responsibility off my back. Thanks.

[ErisDS]

Hey there 👋 I have commit access. I was originally given commit to help manage issues that were being raised from people using sqlite3 with Ghost. I am always around, although I don't pay close attention to this repo.

I tried to help a bit recently, updated the readme and an appveyor build failed. I don't have access to restart jobs and I don't think I have ability to release.

I'll happily help out a little more if needed, would love to help get 5.0.1 released :)

[anlexN]

1. i am willing to help, but can you teach me how to learn C/C++? every time i google, those search result is very old.
2. i think node native should have sqlite3, like php. can we merge into node source code?

[stoneRdev]

I agree with @anlexN , that this functionality should be merged into nodejs core. I currently have to use this because I have issues building better-sqlite3 on windows (refusing to download gigabytes of VisualStupidity, even thought better-sqlite3 is my favorite way of storing data)

[anlexN]

@kewde can we work together with better sqlite3 team to develop one sqlite3 library?

[stoneRdev]

@anlexN Joshua Wise is strapped for resources as well. I think this should be picked up by the ndoejs core dev team and forked from Joshua Wise's repo

[anlexN]

did you review better-sqlite3 source code, it use lzz syntax, what is it? that is not C++, oh my god. i think nodejs core may not compile lzz.

[stoneRdev]

Yes, I'm familiar with better-sqlite3 source, and I really don't think lzz is a problem, it's really just a source/header generator, the same effect could be done without lzz (and with many more files), and, really, we have modules like lzz-gyp, so I just can't see lzz being a problem

[anlexN]

@stoneRdev can you teach me C/C++?

[stoneRdev]

@anlexN haha I'm flattered, but no. What I can do is point you here. I taught myself C about a decade and a half ago. There's so much more resources out there today then when I started, take advantage of them, create some pet projects for yourself (servers are fun), do some open source work, and make a habit of it all. I don't have a count, but can definitely say I have over 100 professional projects involving C/CPP under my belt, and maybe 1000ish personal/pet projects as well.

If your just starting out, don't expect too much, check out some learning curves, and brace for a long ride

[anlexN]

google search results is so old. it make me very angry.

[stoneRdev]

I'm sorry, here try this.

All jokes and yokes aside, if you are serious about learning CPP, your gonna have to get used to google searches (or whatever floats your boat), and your definitely going to have to get used to being mad and frustrated. It's a long, bumpy road my friend

[kewde]

@ErisDS The only way to trigger builds again, is to close and open the PR again. I do not have access either. Anyways, after a busy few months, I have some more time again and more contributors seem to have popped up. Thanks for the support everyone.

[ErisDS]

@kewde there was no PR. I made a readme change so did a direct commit to master. If noone at mapbox is working on this anymore and they don't want contributors having access to things like appveyor, then for the future of the project it's going to need to move to it's own GitHub org.

Do you have contact with any of the original contribs?

[kewde]

Yes I do. cc @mapsam @springmeyer

[kewde]

The only reason why I would be against creating a new org is because the development infrastructure is currently funded by MapBox and if they were to decide to cut ties completely than someone else has to make the commitment for paying for these resources.

• S3 bucket
• AppVeyor (I think is being ran on a paid subscription?)

I wish I could provide more details about the current operating costs but I do not have access to this information. Given knowledge of the current operating costs on a yearly basis, I would be more than happy to forecast the costs because the usage of this repository is growing every month.

[springmeyer]

👋 @ErisDS @kewde - perhaps we could move to using free resources by:

• migrating from s3 bucket -> github releases for the binaries
• migrating from the paid AppVeyor account -> free account

I wish I could provide more details about the current operating costs but I do not have access to this information.

I can try to get more info here.

[anlexN]

good idea.

[mapsam]

Thanks for the ping, @kewde! @springmeyer let me know if I can lend a hand moving away from the s3 bucket.

[JoshuaWise]

Hello, I'm the author of better-sqlite3.

I'm not opposed to the idea of merging better-sqlite3 and node-sqlite3 and/or having it picked up by the Node core team (if they're even interested in such an idea, which I currently have no evidence of).

As for lzz, it's just me being lazy and not wanting to write header files when everything just gets compiled into a single translation unit anyways. But if better-sqlite3 were to become part of a larger project, or get maintained by other developers, I could, of course, just write header files and remove lzz. Besides the lack of header files (and a few special preprocessor directives), lzz is exactly the same as C++.

better-sqlite3 provides a synchronous interface for SQLite, while node-sqlite3 provides an asynchronous interface. Experience has shown that an asynchronous interface introduces many gotchas and restrictions. For example, each transaction requires its own database connection, as discovered here. Additionally, it makes custom functions and custom aggregate/window functions practically impossible, which are readily supported in better-sqlite3. That said, there are cases where an async interface is required, such as slow queries that would block the main thread. Up until recently, I recommended node-sqlite3 to people who had such cases. However, now with worker threads, I no longer see the motivation. Worker threads allow you to write synchronous transaction code with a single database connection, while taking advantage of custom functions and aggregate functions, while not blocking the main thread.

All that said, I'm open to hearing others' thoughts on the matter, and your opinions about what a unified library would ideally look like, and if you think async still has a valid use case. I'm sure I empathize with @kewde's stress in maintaining a project used by so many people (and node-sqlite3 has 5x the user base of better-sqlite3!). I've also been feeling the weight myself.

P.S. If you're interested in learning C++, I can personally attest to the validity of @stoneRdev's comment:

It's a long, bumpy road my friend

[anlexN]

no matter what, no matter how, we need wonderful sqlite3. can we unify opinion?

[stoneRdev]

I agree, I would actually like to see both api's, sync and async, just for the flexibility, but definitely I feel that sqlite should be a part of nodejs core. I feel that sqlite being native to nodejs would be a huge leap forward

[anlexN]

1. PHP stable release: 7.4.9 / August 6, 2020; 27 days ago, and long time ago, php have built-in sqlite3.
2. now node release: 2020-09-15, Version 14.11.0 (Current) and node can call C/C++ code,
3. i am learning C/C++.
4. I am learning node source code. So i hope sqlite3 go into nodejs very much.

[naz]

having it picked up by the Node core team (if they're even interested in such an idea, which I currently have no evidence of)

@JoshuaWise to keep references in check, had a look if node core team ever considered picking up support of SQLite. They did and rejected the idea so far - https://github.com/nodejs/node/issues/29378.

[ErisDS]

Apologies for going quiet - I have been off on maternity leave for the last 4 months 😬 👶

👋 @ErisDS @kewde - perhaps we could move to using free resources by:

• migrating from s3 bucket -> github releases for the binaries
• migrating from the paid AppVeyor account -> free account

This sounds like a sensible plan - I think the goal should be to make this project independent. Mapbox have done an amazing job of supporting it for years and I'm incredibly grateful to them and to @springmeyer in particular, but it seems the time has come for a different approach :)

IMO what we need to do is:

• immediately: figure out how to get 5.0.1 out the door https://github.com/mapbox/node-sqlite3/issues/1386
• as we are able: determine a plan to make the project independent with stewardship from the existing maintainers

@JoshuaWise if you're open to merging, it sounds to me like we need to adopt some patterns from better-sqlite into node-sqlite. With the project so heavily depended upon I don't think it's a good idea do any sort of switch or rewrite, but if it's feasible to bring tried and tested new interfaces from better-sqlite into node-sqlite I think the node ecosystem would benefit from the pooled resources?

@kewde I hope you don't mind me reopening this, but it seems that closing it was a little optimistic 😬

[kewde]

@ErisDS I don't mind at all.

I've submitted a request (email) to take over the maintainership to someone who I believe has the appropriate resources to make this work. The primary reason that I decided to adopt node-sqlite3 was because we depended on it at the nonprofit where I was working. That non-profit not longer exists and thus I have recently become unemployed, therefore do not hold the capacity to continue this.

I hope they accept my offer, if that is the case, then the only thing left to relieve me of my duties would be for @springmeyer to give them access to publish npm packages, as they already hold the ability to commit code and release(?).

My journey is coming to an end though, the torch in my hand and the boulder on my shoulder will have to be passed to someone new. I'd like to personally thank all contributors and in special (@springmeyer @mtgto @mohd-akram et al).

Take care.

PS: Given that it's a bit of an unsolicited offer, I won't say to whom I requested it. I don't want them to have some kind of public pressure to accept the burden (and joy!) of running a project of this scale.

[springmeyer]

if that is the case, then the only thing left to relieve me of my duties would be for @springmeyer to give them access to publish npm packages, as they already hold the ability to commit code and release(?).

👍

[mohd-akram]

Hi folks, what's the update on this? There are currently several major issues:

• Outdated bundled SQLite
• Vulnerabilities
• Broken build on machines without python 2 (eg. macOS starting from 12.3)
• Broken worker thread support

I'm willing to take over maintaining the package if there are no other volunteers.

[kewde]

@mohd-akram I tried to find an extensive user of this library to take over but had no response. I don't have the necessary infrastructure in place to support the CI for this project, which is responsible for the builds and distribution.

The main blockers are:

• Travis stopped providing free builds so all MacOS & Linux builds can't be published (Microsofts node-sqlite3 fork has Github actions support, can take a look at that)
• I think for distribution we don't need to rely on AWS (I can't financially support S3) but I think it can be replaced with Github releases which are free.

We need to fix the CI (which publishes the release builds) and migrate the secrets to Github Actions. At the moment I do not have the right to use Github Actions on this repo but I did some work here but the guys over at Microsoft did more (cc @bpasero).

Ideally we could also switch to using Github Actions for Windows because once we move this repo into its own org - we would lose access to AppVeyor - breaking the release process for Windows.

I would take over this package completely but I haven't run the numbers on how much it would cost me to run this project so I'm hesitant.

[mohd-akram]

Yes, it makes sense to move everything over to GitHub Actions which AFAIK is free for public repos. It's just a matter of creating releases and uploading artifacts, then having the library fetch them on install. A cursory search brings up prebuild which lets you do this. Alternatively, the prebuilt binaries can be bundled with the npm package itself (see the process via prebuildify-ci), which might be simpler and better. What cost are you referring to?

[kewde]

@mohd-akram I see the limitations only apply to private repos. I will try to find some time to migrate everything to Github.

[ErisDS]

Hey everyone 👋

I apologise, I know it's been a long period of radio silence, but we have been squirrelling away behind the scenes figuring out the best way forward and I'm pleased that we're now able to explain 😊

Since creating this awesome package, Mapbox have done an incredible job maintaining it, including hosting (at their own cost) the builds that make the sqlite3 package such a breeze to install for most environments. Ghost have had a great relationship with Dane and the Mapbox team as we've been so dependent on the library and are very grateful for all the support they've given us through their work on this repo.

Moving forward, Ghost are going to take over stewardship of the repository and package, giving particular care to ensuring that the builds continue to work as seamlessly as they have in the past. In order to facilitate this, the repository is moving under the TryGhost org so that we have all the permissions we need to maintain the build system.

We're really grateful to Mapbox, and @kewde and all the other people who've been actively involved in looking after the repo so far and we are very keen to keep working with all who want to contribute going forward.

I'd also like to introduce you all to @daniellockyer who will be taking point on getting the build system working as smooth as silk again via GitHub actions.

We're really excited to be able to take the repo & package forward 😊