7 ms·

Command Line Shell for SQLite

155 points by happy-go-lucky 6y ago

mixmastamyk 6y ago
Like those dot commands better than the backslash of psql.
CaliforniaKarl 6y ago
Some of my coworkers have been dealing with major Ceph issues for the past month. As part of the work, there've been a number of Ceph information dumps, RBD image information, PG information, and lists of incomplete PDs, all in text or CSV files. To help out my coworkers, I wrote up Python code to parse all of those files into a SQLite database. Why? Well, because I can ship the database to anywhere, as one file, and it can be accessed through the SQLite CLI. In another case, a client was building a list of files to consider for deletion. The list included path, size, type (file or directory), and owning user. Before doing anything else, I imported the entire thing into SQLite using the `.import` command. Which was good, because it was able to identify cases of files that included a tab in their paths!
- pjmlp 6y ago
  I do exactly the same with traditional RDMS. Export to single file isn't nothing new.
  - zdkl 6y ago
    Every time I see a message from you it's a variant of "back in my day", "this isn't New" or some other mildly snooty point about kids these days. At this point I'm curious what even brings you to consistently comment like this. I apologise for the ad hominem but frankly the pattern is frequent and irritating? Could you at least describe your use case or tools please? Share the nostalgia or something, anything.
    - laumars 6y ago
      His posts are ridiculous. I’ve never once seen him say anything positive. It’s always condescending, presumptuous and elitist bullcrap. How he’s ended up with so much karma when his comments are consistently scraping the barrel is beyond me.
      - dvfjsdhgfv 6y ago
        > His posts are ridiculous. I went through some and it doesn't seem so. Yes, he is opinionated, but so are many HN-ers. His set of opinions is quite specific though (based on the comments I see, MS SQL Server users are a minority here, for example) so you might have that impression. > How he’s ended up with so much karma We're supposed to be adults but we still fall for this behavior engineering trick called karma. Really, who cares? What does it change in my life if I have a few points more or less? One could even argue that you can create more value for the community by lowering your karma, making comments that are alien to the collective mindhive. A downvote is almost impulsive in such cases, but your comment (before it greys out) can spark a thought, an interest, an idea, or even some important doubt that would otherwise not arise.
        laumars 6y ago
        I’m a big advocate that incorrect comments shouldn’t be downvoted, just corrected. Incorrect comments are valuable if they are corrected with informative comments. But there is a difference between incorrect comments and comments that are broad generalisations presented in an obnoxious way. The former sparks informative conversations whereas the latter adds little informative value while pissing people off. I suspect the real value of the latter is just likeminded people patting themselves on the back for being “awesome”. Which I don’t particularly rate as a positive effect for discourse let alone the wider HN community
        Annatar 6y ago
        That is ironic because this entire web site consists of what appears to be like-minded people constantly patting each other on the back because they're so awesome; meanwhile they leave a lot to be desired, and the person you're criticizing so staunchly isn't necessarily one those persons... This website has turned into a hangout for "brogrammers", that's why I almost never participate here any more. Now I'll end up writing even less, I think.
  - CaliforniaKarl 6y ago
    That's true, I could've done exactly the same thing with MySQL, MariaDB, Postgres, etc.. But take note of the situations, and you'll notice some things which make SQLite stand out. I said I was working in Python. I was working across three environments: Two separate compute environments, and my work macOS laptop. I didn't want to have to deal with installing the appropriate DB driver, and sqlite is part of the Python standard library. Another thing to note is that I didn't already have a database server spun up, and I didn't think it was worth doing the work of spinning up a Postgres database server. Finally, I didn't intend the "single file" to be an export. I intended it to be an actual, usable database, which my co-workers can use with minimal friction. Those are a few of the reasons why I made the choice I did. And I think SQLite worked quite well!
  - skissane 6y ago
    > I do exactly the same with traditional RDMS. For the use case of "I need a quick database for some ad hoc analysis", SQLite has a lot less friction than a traditional RDBMS. Creating a new DB, backing it up, sharing it with others – all these tasks are doable on a traditional RDBMS too, but generally involve additional steps or prerequisites compared to what SQLite requires. > Export to single file isn't nothing new. Most databases have some kind of export/import function based around CSV, SQL, XML, JSON, or even some proprietary binary format. But SQLite is a lot easier than that – I can just copy a single database file to another system and just open it and it works straight away. (The only proviso is make sure the DB isn't open at the time of copy, to avoid any corruption issues.) No need to run some time consuming import/export process which converts between the external format and the database's native storage format.
    - pjmlp 6y ago
      Same here, depending on the project, I always have either SQL Developer with Oracle, or SQL Server Management Studio with SQL Server installed.
      - skissane 6y ago
        Copying a database to another machine is a lot simpler with sqlite than with either of the tools you mention. So I don't agree with the "same here" part.
- emmelaich 6y ago
  Perhaps you should add your tables to Osquery? https://osquery.io/ https://osquery.io/
  - chrisweekly 6y ago
    Thanks for the tip! Bookmarked, will look closer at OSQuery after vaca.
banana_giraffe 6y ago
I use SQLite a ton, including the CLI. Didn't know about the zip file support. Now I'm trying think of a valid use case. Interesting feature to have, nevertheless.
- hanche 6y ago
  Also the “sqlar” archive format. I’ve known about it for a while, but that it is now built into the regular command shell was news to me. I’ll want to see if I can get sqlarfs to work with macFUSE next.
nooyurrsdey 6y ago
SQLite continues to be a great lightweight DB solution with so many great features. I love that for smaller projects (those that might have a few hundred or thousand rows in a DB at most) I can have everything self-contained in a single SQLite file. * Backing up the DB is trivial - it's just backing up the file * Establishing a connection is super quick * No need to worry about Docker images or installation issues for psql, mysql, etc... * A whole host of features like zipping, dumping, etc... make it really easy to use Great work to the entire community maintaining this.
- CaliforniaKarl 6y ago
  I was wondering about backups! I thought, if doing a simple `cp`, you'd also have to make sure any remaining journal or WAL files are also copied. But now as I'm looking through SQLite docs, I found VACUUM INTO (https://www.sqlite.org/lang_vacuum.html#vacuuminto https://www.sqlite.org/lang_vacuum.html#vacuuminto), which lets you do a backup of an in-use database, combined with a VACUUM on the new database file!
  - nathell 6y ago
    There also is a backup API: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
  - otherme123 6y ago
    I use something like: $ sqlite3 filename.sql ".backup backupFilename_YYYYMMDD.sql" But VACUUM INTO looks better.
- schwartzworld 6y ago
  Why only a few thousand rows? Pretty sure sqlite can handle 2^64 rows per table
tannhaeuser 6y ago
That has existed for decades hasn't it? I thought this was about bringing uniform cli support across multiple RDMBs, such as Oracle's SQL*Plus syntax which is also partly supported by DB2 (and has text substitution variables, unlike sqlite's). Should there be an ISO standard for this?
danidiaz 6y ago
One annoyance is that on Windows the binary distribution of the shell doesn't support readline bindings. You have to recompile the shell for that. Instead of recompiling, I wrote a simplistic rlwrap clone in Haskell https://gist.github.com/danidiaz/97edc1bf248f5dce34aeb3703633a4c3 https://gist.github.com/danidiaz/97edc1bf248f5dce34aeb370363... which does the job.
pmontra 6y ago
I use it all the time. I saw a customer using a graphical app last week and I was surprised to see one. Of course there are, I see people connecting to PostgreSQL and MySQL databases on servers with DBeaver or similar apps. I always ssh and run the command line client. I think it's faster to operate. In the case of sqlite the command line client is a bit underpowered compared to the other databases but it's good enough.
pampa 6y ago
For everybody using sqlite cli from a terminal window, check out litecli [1]. It has some nice features like syntax color, better completion and query editing etc. It is part of the dbcli [2] family of database terminal clients. There are cli for mysql, postgres, redis and other databases. [1] https://litecli.com/ https://litecli.com/ [2] https://www.dbcli.com/ https://www.dbcli.com/
- flas9sd 6y ago
  I'm a fan of the series too, for one specific puropse: being able to set a pager. Generally the sqlite cli would be sufficient, but not having a pager for the output overflows the terminal and is a major hindrance in working with standard database tools. Using no GUI helps with learning too I think. There are workarounds to stay in the shell context and pipe to the utils and append a pager. For an analogy, it's `pager less` in mysql and `\pset pager less` in postgres. See my comment on v3.33 (when alot of cli enhancements got introduced): https://www.sqlite.org/forum/forumpost/3d157b175c https://www.sqlite.org/forum/forumpost/3d157b175c The mongodb cli neither has the ability to set a pager I noticed recently.
- deleted 6y ago
  [deleted]
- JNRowe 6y ago
  I've been really enjoying the litecli experience. Having been a ptpython¹ user I already knew about prompt_toolkit², but somehow had missed litecli. This comment is just a general recommendation for Python users to have a poke around the GitHub organisation³, as the packages are awesome. ¹ https://pypi.org/project/ptpython/ https://pypi.org/project/ptpython/ ² https://pypi.org/project/prompt-toolkit/ https://pypi.org/project/prompt-toolkit/ ³ https://github.com/prompt-toolkit https://github.com/prompt-toolkit
- fantod 6y ago
  Nice! I came to the comment section precisely to see if someone could recommend an alternative to the default client.
- leonim 6y ago
CraigJPerry 6y ago
The chap that makes datasette (the video tour on the home page is an excellent way to spend 20 minutes) also makes a few handy tools for common sqlite manipulation tasks: https://datasette.io/tools https://datasette.io/tools You can just “pipx install <tool>” to avoid polluting your system python install with dependencies (pipx handles creating virtual environments and putting the tools on your path). Shout out for the apple healthkit one, i had fun using datasette to explore that db. The hackernews to sqlite tool works fine (user name is case sensitive). The page doesn’t document the most universal tool there - sqlite-utils but there’s a sphinx doc for it https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... some of the transform features are nice time savers.
yread 6y ago
For people who prefer a GUI https://sqlitebrowser.org https://sqlitebrowser.org
- roelschroeven 6y ago
  For those who use HeidiSQL: it has now (experimental) support for SQLite.
- attilakun 6y ago
  I love this tool. There is a feature I use a lot: you can write multi-step queries, select part of them and hit F5 to execute just that part. I haven't really dug into the CLI yet, but I imagine this to be less convenient there.
- stephen82 6y ago
  Personally I prefer SQLiteStudio [1] as it is closer to SQLite Expert Professional [2] that I used to use at work. [1] https://github.com/pawelsalawa/sqlitestudio [2] http://www.sqliteexpert.com/
yread 6y ago
So, how does your ~/.sqliterc file look like? I have .headers on .mode column .timer on .changes on
- Cthulhu_ 6y ago
  Is there a trick to choose whether or not to use the sqliterc file? I work on a pretty bad project that does database migration using thousands of lines of bash code that feeds queries to the sqlite3 commandline utility. For that one I'd like it to not read .sqliterc, but for my own use (on the same system) I'd like some of those options enabled.
  - JNRowe 6y ago
    I wonder if the simplest "trick" is to do the exact opposite. Name your ~/.sqliterc something else, and then "alias sqlite3='sqlite3 -init ~/.sqliterc-interactive'" in your shell's config? Although, I'm wondering if using "sqlite3 -init /dev/null" in scripts would be a better idea in general. That way you would guard yourself against some random user's config doing something really weird.
hypertele-Xii 6y ago
Every so often I get excited to try SQLite, and every time I run into an impenetrable brick wall of unmaintained, deprecated bindings, nonexistant documentation, gotchas and hidden complexity. I wish there was a lite version of SQLite with a much simplified interface that is actually a viable alternative to file_open().
- shazar 6y ago
  Can you share a few of these issues? Been using SQLite for years and don’t remember a running into anything as you described.
- zffr 6y ago
  What language are you using? SQLite has been well supported in most of the ones I’ve used. It also has a pretty straightforward C API, so it might not be too hard to build bindings yourself if they don’t exist
  - hypertele-Xii 6y ago
    Lua. Let me walk you through my experience: Google for "Lua SQLite", click top link. http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki Go to download page, ok. http://lua.sqlite.org/index.cgi/index http://lua.sqlite.org/index.cgi/index Download latest version, unzip it. It's in C. What? I thought this was a Lua binding. Back to Google. Second link. https://makoserver.net/articles/Lua-SQLite-Database-Tutorial https://makoserver.net/articles/Lua-SQLite-Database-Tutorial Umm... part of some Mako Server thing? I just want SQLite. Back to Google, third link. http://lua-users.org/wiki/LuaSqlite http://lua-users.org/wiki/LuaSqlite "LuaSQLite is a Lua binding for SQLite." Alright! Goto website and... http://luaforge.net/projects/luasqlite/ http://luaforge.net/projects/luasqlite/ It's a dead link. Uhh okay, back to previous page... "Other SQLite wrappers include:" Three out of four links dead. Only working link: https://github.com/maxdoom-com/lsqlite https://github.com/maxdoom-com/lsqlite It's C again. What the hell. Baaack to Google. Fourth link. https://mroth.net/lua-sqlite3 https://mroth.net/lua-sqlite3 "Lua-Sqlite3 is a binding of Sqlite3 for Lua." Excellent. Download latest release... Finally, Lua files! Uhh there's a lot of stuff here. Documentation? "These parts needs to be written: ", "Please be patient... ", "Last update: 2006" uh oh. More C code... Prebuilt binaries? Nope! Fifth Google link is a Lua plugin for SQLite, the inverse of what I want. Sixth link, "(Yet Another*) Lua wrapper for SQLite" It's more C. No prebuilt binaries! Seventh link. "LJSQlite3: SQlite3 Interface" No download. It's part of some large scientific computation package. At this point, all I'm thinking is "this isn't worth it". I know I could use LuaJIT's FFI to make my own wrapper to SQLite's C interface but at that point I'm not saving time anymore. Best to ignore the whole thing. Serializing data to/from files shouldn't be that complicated. I'll stick with my "piles of files", no matter how "awesomely engineered" SQLite is supposed to be, if it's this much pain to even start using. The next best thing I have is this dead simple: local blob = filewriter() blob:write(literally_any_data_i_have) writefile("filename", blob:tostring())

Like those dot commands better than the backslash of psql.

Some of my coworkers have been dealing with major Ceph issues for the past month. As part of the work, there've been a number of Ceph information dumps, RBD image information, PG information, and lists of incomplete PDs, all in text or CSV files. To help out my coworkers, I wrote up Python code to parse all of those files into a SQLite database. Why? Well, because I can ship the database to anywhere, as one file, and it can be accessed through the SQLite CLI. In another case, a client was building a list of files to consider for deletion. The list included path, size, type (file or directory), and owning user. Before doing anything else, I imported the entire thing into SQLite using the `.import` command. Which was good, because it was able to identify cases of files that included a tab in their paths!

pjmlp 6y ago
I do exactly the same with traditional RDMS. Export to single file isn't nothing new.
- zdkl 6y ago
  Every time I see a message from you it's a variant of "back in my day", "this isn't New" or some other mildly snooty point about kids these days. At this point I'm curious what even brings you to consistently comment like this. I apologise for the ad hominem but frankly the pattern is frequent and irritating? Could you at least describe your use case or tools please? Share the nostalgia or something, anything.
  - laumars 6y ago
    His posts are ridiculous. I’ve never once seen him say anything positive. It’s always condescending, presumptuous and elitist bullcrap. How he’s ended up with so much karma when his comments are consistently scraping the barrel is beyond me.
    - dvfjsdhgfv 6y ago
      > His posts are ridiculous. I went through some and it doesn't seem so. Yes, he is opinionated, but so are many HN-ers. His set of opinions is quite specific though (based on the comments I see, MS SQL Server users are a minority here, for example) so you might have that impression. > How he’s ended up with so much karma We're supposed to be adults but we still fall for this behavior engineering trick called karma. Really, who cares? What does it change in my life if I have a few points more or less? One could even argue that you can create more value for the community by lowering your karma, making comments that are alien to the collective mindhive. A downvote is almost impulsive in such cases, but your comment (before it greys out) can spark a thought, an interest, an idea, or even some important doubt that would otherwise not arise.
      - laumars 6y ago
        I’m a big advocate that incorrect comments shouldn’t be downvoted, just corrected. Incorrect comments are valuable if they are corrected with informative comments. But there is a difference between incorrect comments and comments that are broad generalisations presented in an obnoxious way. The former sparks informative conversations whereas the latter adds little informative value while pissing people off. I suspect the real value of the latter is just likeminded people patting themselves on the back for being “awesome”. Which I don’t particularly rate as a positive effect for discourse let alone the wider HN community
        Annatar 6y ago
        That is ironic because this entire web site consists of what appears to be like-minded people constantly patting each other on the back because they're so awesome; meanwhile they leave a lot to be desired, and the person you're criticizing so staunchly isn't necessarily one those persons... This website has turned into a hangout for "brogrammers", that's why I almost never participate here any more. Now I'll end up writing even less, I think.
- CaliforniaKarl 6y ago
  That's true, I could've done exactly the same thing with MySQL, MariaDB, Postgres, etc.. But take note of the situations, and you'll notice some things which make SQLite stand out. I said I was working in Python. I was working across three environments: Two separate compute environments, and my work macOS laptop. I didn't want to have to deal with installing the appropriate DB driver, and sqlite is part of the Python standard library. Another thing to note is that I didn't already have a database server spun up, and I didn't think it was worth doing the work of spinning up a Postgres database server. Finally, I didn't intend the "single file" to be an export. I intended it to be an actual, usable database, which my co-workers can use with minimal friction. Those are a few of the reasons why I made the choice I did. And I think SQLite worked quite well!
- skissane 6y ago
  > I do exactly the same with traditional RDMS. For the use case of "I need a quick database for some ad hoc analysis", SQLite has a lot less friction than a traditional RDBMS. Creating a new DB, backing it up, sharing it with others – all these tasks are doable on a traditional RDBMS too, but generally involve additional steps or prerequisites compared to what SQLite requires. > Export to single file isn't nothing new. Most databases have some kind of export/import function based around CSV, SQL, XML, JSON, or even some proprietary binary format. But SQLite is a lot easier than that – I can just copy a single database file to another system and just open it and it works straight away. (The only proviso is make sure the DB isn't open at the time of copy, to avoid any corruption issues.) No need to run some time consuming import/export process which converts between the external format and the database's native storage format.
  - pjmlp 6y ago
    Same here, depending on the project, I always have either SQL Developer with Oracle, or SQL Server Management Studio with SQL Server installed.
    - skissane 6y ago
      Copying a database to another machine is a lot simpler with sqlite than with either of the tools you mention. So I don't agree with the "same here" part.
emmelaich 6y ago
Perhaps you should add your tables to Osquery? https://osquery.io/ https://osquery.io/
- chrisweekly 6y ago
  Thanks for the tip! Bookmarked, will look closer at OSQuery after vaca.

banana_giraffe 6y ago

I use SQLite a ton, including the CLI. Didn't know about the zip file support. Now I'm trying think of a valid use case. Interesting feature to have, nevertheless.

hanche 6y ago
Also the “sqlar” archive format. I’ve known about it for a while, but that it is now built into the regular command shell was news to me. I’ll want to see if I can get sqlarfs to work with macFUSE next.

nooyurrsdey 6y ago

SQLite continues to be a great lightweight DB solution with so many great features. I love that for smaller projects (those that might have a few hundred or thousand rows in a DB at most) I can have everything self-contained in a single SQLite file. * Backing up the DB is trivial - it's just backing up the file * Establishing a connection is super quick * No need to worry about Docker images or installation issues for psql, mysql, etc... * A whole host of features like zipping, dumping, etc... make it really easy to use Great work to the entire community maintaining this.

CaliforniaKarl 6y ago
I was wondering about backups! I thought, if doing a simple `cp`, you'd also have to make sure any remaining journal or WAL files are also copied. But now as I'm looking through SQLite docs, I found VACUUM INTO (https://www.sqlite.org/lang_vacuum.html#vacuuminto https://www.sqlite.org/lang_vacuum.html#vacuuminto), which lets you do a backup of an in-use database, combined with a VACUUM on the new database file!
- nathell 6y ago
  There also is a backup API: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
- otherme123 6y ago
  I use something like: $ sqlite3 filename.sql ".backup backupFilename_YYYYMMDD.sql" But VACUUM INTO looks better.
schwartzworld 6y ago
Why only a few thousand rows? Pretty sure sqlite can handle 2^64 rows per table

tannhaeuser 6y ago

That has existed for decades hasn't it? I thought this was about bringing uniform cli support across multiple RDMBs, such as Oracle's SQL*Plus syntax which is also partly supported by DB2 (and has text substitution variables, unlike sqlite's). Should there be an ISO standard for this?

danidiaz 6y ago

One annoyance is that on Windows the binary distribution of the shell doesn't support readline bindings. You have to recompile the shell for that. Instead of recompiling, I wrote a simplistic rlwrap clone in Haskell https://gist.github.com/danidiaz/97edc1bf248f5dce34aeb3703633a4c3 https://gist.github.com/danidiaz/97edc1bf248f5dce34aeb370363... which does the job.

pmontra 6y ago

I use it all the time. I saw a customer using a graphical app last week and I was surprised to see one. Of course there are, I see people connecting to PostgreSQL and MySQL databases on servers with DBeaver or similar apps. I always ssh and run the command line client. I think it's faster to operate. In the case of sqlite the command line client is a bit underpowered compared to the other databases but it's good enough.

pampa 6y ago

For everybody using sqlite cli from a terminal window, check out litecli [1]. It has some nice features like syntax color, better completion and query editing etc. It is part of the dbcli [2] family of database terminal clients. There are cli for mysql, postgres, redis and other databases. [1] https://litecli.com/ https://litecli.com/ [2] https://www.dbcli.com/ https://www.dbcli.com/

flas9sd 6y ago
I'm a fan of the series too, for one specific puropse: being able to set a pager. Generally the sqlite cli would be sufficient, but not having a pager for the output overflows the terminal and is a major hindrance in working with standard database tools. Using no GUI helps with learning too I think. There are workarounds to stay in the shell context and pipe to the utils and append a pager. For an analogy, it's `pager less` in mysql and `\pset pager less` in postgres. See my comment on v3.33 (when alot of cli enhancements got introduced): https://www.sqlite.org/forum/forumpost/3d157b175c https://www.sqlite.org/forum/forumpost/3d157b175c The mongodb cli neither has the ability to set a pager I noticed recently.
deleted 6y ago
[deleted]
JNRowe 6y ago
I've been really enjoying the litecli experience. Having been a ptpython¹ user I already knew about prompt_toolkit², but somehow had missed litecli. This comment is just a general recommendation for Python users to have a poke around the GitHub organisation³, as the packages are awesome. ¹ https://pypi.org/project/ptpython/ https://pypi.org/project/ptpython/ ² https://pypi.org/project/prompt-toolkit/ https://pypi.org/project/prompt-toolkit/ ³ https://github.com/prompt-toolkit https://github.com/prompt-toolkit
fantod 6y ago
Nice! I came to the comment section precisely to see if someone could recommend an alternative to the default client.
leonim 6y ago

CraigJPerry 6y ago

The chap that makes datasette (the video tour on the home page is an excellent way to spend 20 minutes) also makes a few handy tools for common sqlite manipulation tasks: https://datasette.io/tools https://datasette.io/tools You can just “pipx install <tool>” to avoid polluting your system python install with dependencies (pipx handles creating virtual environments and putting the tools on your path). Shout out for the apple healthkit one, i had fun using datasette to explore that db. The hackernews to sqlite tool works fine (user name is case sensitive). The page doesn’t document the most universal tool there - sqlite-utils but there’s a sphinx doc for it https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... some of the transform features are nice time savers.

yread 6y ago

For people who prefer a GUI https://sqlitebrowser.org https://sqlitebrowser.org

roelschroeven 6y ago
For those who use HeidiSQL: it has now (experimental) support for SQLite.
attilakun 6y ago
I love this tool. There is a feature I use a lot: you can write multi-step queries, select part of them and hit F5 to execute just that part. I haven't really dug into the CLI yet, but I imagine this to be less convenient there.
stephen82 6y ago
Personally I prefer SQLiteStudio [1] as it is closer to SQLite Expert Professional [2] that I used to use at work. [1] https://github.com/pawelsalawa/sqlitestudio [2] http://www.sqliteexpert.com/

yread 6y ago

So, how does your ~/.sqliterc file look like? I have .headers on .mode column .timer on .changes on

Cthulhu_ 6y ago
Is there a trick to choose whether or not to use the sqliterc file? I work on a pretty bad project that does database migration using thousands of lines of bash code that feeds queries to the sqlite3 commandline utility. For that one I'd like it to not read .sqliterc, but for my own use (on the same system) I'd like some of those options enabled.
- JNRowe 6y ago
  I wonder if the simplest "trick" is to do the exact opposite. Name your ~/.sqliterc something else, and then "alias sqlite3='sqlite3 -init ~/.sqliterc-interactive'" in your shell's config? Although, I'm wondering if using "sqlite3 -init /dev/null" in scripts would be a better idea in general. That way you would guard yourself against some random user's config doing something really weird.

hypertele-Xii 6y ago

Every so often I get excited to try SQLite, and every time I run into an impenetrable brick wall of unmaintained, deprecated bindings, nonexistant documentation, gotchas and hidden complexity. I wish there was a lite version of SQLite with a much simplified interface that is actually a viable alternative to file_open().

shazar 6y ago
Can you share a few of these issues? Been using SQLite for years and don’t remember a running into anything as you described.
zffr 6y ago
What language are you using? SQLite has been well supported in most of the ones I’ve used. It also has a pretty straightforward C API, so it might not be too hard to build bindings yourself if they don’t exist
- hypertele-Xii 6y ago
  Lua. Let me walk you through my experience: Google for "Lua SQLite", click top link. http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki Go to download page, ok. http://lua.sqlite.org/index.cgi/index http://lua.sqlite.org/index.cgi/index Download latest version, unzip it. It's in C. What? I thought this was a Lua binding. Back to Google. Second link. https://makoserver.net/articles/Lua-SQLite-Database-Tutorial https://makoserver.net/articles/Lua-SQLite-Database-Tutorial Umm... part of some Mako Server thing? I just want SQLite. Back to Google, third link. http://lua-users.org/wiki/LuaSqlite http://lua-users.org/wiki/LuaSqlite "LuaSQLite is a Lua binding for SQLite." Alright! Goto website and... http://luaforge.net/projects/luasqlite/ http://luaforge.net/projects/luasqlite/ It's a dead link. Uhh okay, back to previous page... "Other SQLite wrappers include:" Three out of four links dead. Only working link: https://github.com/maxdoom-com/lsqlite https://github.com/maxdoom-com/lsqlite It's C again. What the hell. Baaack to Google. Fourth link. https://mroth.net/lua-sqlite3 https://mroth.net/lua-sqlite3 "Lua-Sqlite3 is a binding of Sqlite3 for Lua." Excellent. Download latest release... Finally, Lua files! Uhh there's a lot of stuff here. Documentation? "These parts needs to be written: ", "Please be patient... ", "Last update: 2006" uh oh. More C code... Prebuilt binaries? Nope! Fifth Google link is a Lua plugin for SQLite, the inverse of what I want. Sixth link, "(Yet Another*) Lua wrapper for SQLite" It's more C. No prebuilt binaries! Seventh link. "LJSQlite3: SQlite3 Interface" No download. It's part of some large scientific computation package. At this point, all I'm thinking is "this isn't worth it". I know I could use LuaJIT's FFI to make my own wrapper to SQLite's C interface but at that point I'm not saving time anymore. Best to ignore the whole thing. Serializing data to/from files shouldn't be that complicated. I'll stick with my "piles of files", no matter how "awesomely engineered" SQLite is supposed to be, if it's this much pain to even start using. The next best thing I have is this dead simple: local blob = filewriter() blob:write(literally_any_data_i_have) writefile("filename", blob:tostring())