Here’s a copy of a support ticket I sent to Glitch’s support team. Thought I’d get the community’s input too. Essentially, there’s many files that Glitch hides from the public no matter what your privacy settings are (including .env, .data/ folder, .gitignore, etc. This is a proposal to add another file type, .sqlite. Although files can be put in a .data/ folder, the limitations of most SQLite databases are the file must be hosted on the root directory.
Hello folks!
I know by default many files are privated/removed from remix (including .env, and .data folder). However I have a specific issue with the default hidden assets for free-tier users. My file name is “json.sqlite”, required to be hosted on the main directory (a disadvantage to the database package I use).
So with this being said, is it possible to, in the future, hide “.sqlite” files by default? I’m sure we can assume that most private data is being held in the DB file anyway (of course encrypted, but atlas). There’s also no need for database data to be Remixed or viewed publicly anyway (unless of course, .example is included in the name, which already copies).
I do feel as though this file type should be treated the same as an .env as it’s a database/unique storage. I also understand that full project privacy is available in the paid plan, but that’s not something I’d like to partake in at the moment. Perhaps there can be a toggle of which types of files to hide from the public, since an open source app never needs the database copied when someone clones it.
Thoughts? Questions? Opinions? I feel this is a necessary change to be made, and it seems pretty easy to implement – just hide all .sqlite files.
Hey will, thanks for posting! Could you elaborate on this statement:
the limitations of most SQLite databases are the file must be hosted on the root directory.
I’m not familiar with this, and in fact the official sqlite starter keeps its SQLite database in the .data folder-- you can see it being loaded in src/sqlite.js. What are you using that has that restriction?
Sure, so I haven’t touched the SQL starter project before. I am actually using quick.db on NPM for databasing.
Quick.db, along with many other “easy” SQLite databases, save to a file called json.sqlite. Of course the file path is changeable in the actual package files, but Glitch doesn’t give us an easy way to reach the node_modules folder. In general I honestly recommend that .sqlite files are hidden by default (possibly with an option to display them) for security reasons.
A lot of new developers may not think to encrypt database objects which leads to .sqlite account information, passwords, anything – you name it – being displayed publicly for anybody to see – with a Glitch account or not.
Or just hide anything that contains sqlite. And if you’re concerned about doing that in the event that someone renames all project files to include sqlite, somebody can just put all project files in a .data/ folder and hide everything that way to “private” their project while being on the free-tier.
For what it’s worth, you can change the file that quick.db persists to in the constructor using the filePath property of the options object; it’s mentioned in the comment block in their README’s Example code.
I’ll bring this up with the team next week. (As always, there’s no guarantee that we’ll come to a decision about this, or that we’d be able to prioritize it on any specific timeline.) My gut feeling (not speaking for the team as a whole) is that it might be a good approach to not show the contents of the file (since it’s a binary file anyway and editing it in the editor isn’t a great idea), but leave it in the sidebar since hiding it entirely could be confusing.
In how I’m thinking about this, the files would still be retained in remixes-- although I see how that’s sort of a conflicting stance, I do think there are legitimate use cases for projects that are interested in providing data, especially in the Datasette world, for people who remix them. But seeing that file get created in your editor file tree actually serves as a warning that there’s information there that would be in remixes. If we can find time to do this, we could even have the contents of the editor pane be replaced with a warning to project members that links out to help docs about using the .data folder. Does that make sense?
Yes, that does make sense! And I am aware of the file path option, choosing not to use it for a variety of reasons.
Perhaps Glitch can implement viewing/editing support for sqlite files instead of hiding it completely? At times I do sort through the binary to find human-readable information to confirm something was changed/added. And a warning for sure that it may be shown to users who decide to remix the project.
an interactive database editor would be really neat but is probably beyond the scope of what we can do in any reasonable timeframe you never know, I guess!
But until then (and even now) you’re certainly able as a project owner/member to open the integrated terminal from the bottom bar and double-check the contents of the database using the sqlite3 REPL or, similar to what you’re doing now with the editor pane, use any tool like cat, less, grep (with the -a flag) or even vim to try and find the text blobs in the binary.
you never know, I guess!