20 ms·

Consider SQLite

• Mikepicker 5y ago
  I've recently used SQLite for my personal project rigfoot.com It's a "read only" and small website (at least for now), with just a bunch of daily visitors, a perfect use case for SQLite. Funny thing is that in my case the database it's so small that it's pushed directly on the repo. Especially for startups and little projects, SQLite is your best friend.
• JsticeJ 5y ago
  Don’t consider SQLite for cloud based webservers. The scant upside of 10-50x supposed query latency increase is likely to be worth little. In the extreme this is low single-digit milliseconds, so will be dwarfed by network hops. In return for the above, you’ve coupled your request handler and it’s state, so you won’t be able to treat services as ephemeral. Docker and Kubernetes, for instance, become difficult. You now require a heap of error-prone gymnastics to manage your service. If the query latency really matters, use an in memory db such as Redis. SQLite is great for embedded systems where you’re particularly hardware constrained and know your machine in advance. It could also be a reasonable option for small webservers running locally. For anything remote, or with the slightest ambition of scale, using SQLite is very likely a bad trade off.
• hankchinaski 5y ago
  I have tried adopting SQLite in my side projects. The problem I encountered is that using managed PostgreSQL/MySQL is still more convenient and more reliable than using SQLite on a bare metal VPS. I like to use Heroku or Digital Ocean App platform because I want to spend time creating and not managing the infrastructure (ci/cd, ssl certs, reverse proxy, db backup, scaling, container management and what not). I tried looking for a managed SQLite but could not find one. On an unrelated note I found using Redis a good lightweight alternative to the classical psql/MySQL. Although still multi-tier and more difficult to model data, it’s initially cheaper and easier to manage than its relational counterparts. Anyone has had similar setup/preference?
  • cheeselip420 5y ago
    What would a managed sqlite even look like? I can't tell if this is a real response or not...
    • Something1234 5y ago
      Maybe an NFS mount or something that handles back ups automatically? Scripting to handle an automatic restore of the database? Maybe a heroku that knows about your database file and automatically loads the latest version for you? I kind of feel like GP is a troll comment, as there's no real value add for a managed SQLite.
      • config_yml 5y ago
        Or just use litestream, it‘s perfect for this and the closest you can get to managed by replicating to S3 or Cloud Storage or the likes.
        • spiffytech 5y ago
          Beware that Litestream on PaaS has safety concerns unless you can get your PaaS platform to guarantee that the active app instance is terminated before a new instance is booted. Litestream doesn't turn SQLite into an multi-master distributed system. If two copies of the DB accept writes at the same time, Litestream will just send backups to two different backup generations, and when you restore you'll only pull down one of those generations and won't receive the writes that landed in the other generations. All of your writes will still be in S3, they'll just be peppered across distinct backup snapshots and you can't get all the data back unless you separately restore all relevant generations and manually merge the restored databases.
    • DizzyDoo 5y ago
      When hankchinaski says 'managed' I think they really mean that there's some capital-A App dashboard somewhere, on Digital Ocean or wherever, and they log in and click 'new database' and that's it. No ssh-ing to a VPS, and choosing the file location where the sqlite file will sit, figuring out backups and so on. But as you say, while you can wrap postgres or redis in that sort of 'just take care of it for me' approach, given the simplicity of sqlite it doesn't fit that paradigm, so perhaps hankchinaski is just misunderstanding what sqlite fundamentally is and how it works.
• srcreigh 5y ago
  More info about WAL mode concurrency [0] No reader-writer lock. Still only 1 concurrent writer, but write via append to WAL file is cheaper. Can adjust read vs write performance by syncing WAL file more or less often. Can also increase performance with lower durability by not syncing WAL file to disk as often https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
• chasil 5y ago
  There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past time. If your application can live with these limitations, then it does have some wonderful features.
  • lelanthran 5y ago
    > It is not client/server; a process must be able to fopen() the database file. The one time I actually wanted to do that, I wrote the server that `accept`ed incoming connections and used the single `fopen`ed SQLite DB. It can be very flexible that way, TBH, but if you really need that type of thing a more traditional DB is better.
  • mushufasa 5y ago
    Note -- the single process write at any one time is a killer for most web apps, where for example within SaaS you have many users doing things at the same time.
    • js4ever 5y ago
      It's not really an issue if you have 1 db per customer
      • rguillebert 5y ago
        If a customer has 1000 employees all using your app, it is.
        • chasil 5y ago
          If the app is designed correctly, then the thousand employees would write to their own temporary databases, and a background job would pull their changes into the main database sequentially. If the app is not specifically designed to do this, then SQLite would not be an option.
• greatjack613 5y ago
  I use SQLite exclusively on a high performance crypto sniper project - https://bsctrader.app https://bsctrader.app and I could not be happier with it. Performs much better then postgres in terms of query latency which is ultra important for the domain we operate in. I take machine level backups every 2 hours, so in the event of an outage, just boot the disk image on a new vm and it's off. I would never do this on my professional job due to the stigma, but for this side project, it has been incredible
  • catillac 5y ago
    The stigma of using the most popular database in existence?
    • copperx 5y ago
      For the domain of webapps, where multiple concurrent writers are often expected, yes, I would say it's a stigma.
      • pdimitar 5y ago
        That's only a theoretical limitation. 99% of all your typical insert / update / delete operations finish in the single digits of milliseconds, making the serial nature of SQLite writes a problem when you get north of 5000+ requests per second.
      • usrbinbash 5y ago
        Are they expected, or are the required? Because, serializing db access through a single process only becomes a problem when the number of reads/writes get so large, that the process becomes a bottlenec. And judging by the test the author of the linked article did, that would have to be a HUGE number.
  • dkjaudyeqooe 5y ago
    > I would never do this on my professional job due to the stigma, but for this side project, it has been incredible Using the right tool for the job does indeed carry a lot of stigma in many professional environments. Instead you use the tool that some VP has been sold by some salesman.
• galaxyLogic 5y ago
  SQLite database can be stored in git which seems like a great benefit. But I wonder would it also be possible to have different "branches" of the database and then merge them at some point?
  • pgwhalen 5y ago
    It's not integrated with git the way you're perhaps imagining, but SQLite sessions[0] is adjacent to what you're imagining. [0] https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html
• davidhariri 5y ago
  Just a note that there are significant features of SQLAlchemy that don’t work with SQLite such as ARRAY columns, UUID primary keys and certain types of foreign key constraints.
  • zzzeek 5y ago
    well no major database other than PostgreSQL has native support for UUID or ARRAY, you can certainly use string-based types for these things for other databases. the DB agnostic UUID is at https://docs.sqlalchemy.org/en/14/core/custom_types.html?highlight=uuid#backend-agnostic-guid-type https://docs.sqlalchemy.org/en/14/core/custom_types.html?hig... and for ARRAY it's likely most convenient to use the SQLite JSON datatype which is also supported directly.
• brunoluiz 5y ago
  I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that uses Golang and is backed by SQLite? This because SQLite would require CGO and CGO generally adds extra complexities and performance costs. I wonder how big Golang applications fare with this.
  • mholt 5y ago
    Not a "big project/service" but a Go project that uses Sqlite is one of my own, Timeliner[1] and its successor, Timelinize[2] (still in development). Yeah the cgo dependency kinda sucks but you don't feel it in code, just compilation. And it easily manages Timeline databases of a million and more entries just fine. [1]: https://github.com/mholt/timeliner https://github.com/mholt/timeliner [2]: https://twitter.com/timelinize https://twitter.com/timelinize
    • brunoluiz 5y ago
      Interesting project! It seems to be perfect for SQLite, considering it seems to be mostly for reads instead of writes. I wonder if heavy write applications are a bit of a trouble in Golang because of Golang goroutines x C threads model (which I believe SQLite might use?).
  • hoaljasio 5y ago
    https://github.com/gravitational/teleport/ https://github.com/gravitational/teleport/ has the option to use it, but it only uses it as a key value store. CGO isnt too big a problem and if it is a real dealbreaker something like https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite will work as it transpiled the c into go and passes the sqlite test suite. I think there is performance degradation with writes but reads are still pretty quick.
  • koeng 5y ago
    You can use pure Golang SQLite (without requiring CGO) - https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite It works well, but the performance is worse than C version. Not a big deal for what I used it for, though. It was approx. 6x worse at inserts.
• pgwhalen 5y ago
  SQLite is great, but it's not a more simple drop in replacement for DB servers like HN often suggests it is. My team at work has adopted it and generally likes it, but the biggest hurdle we've found is that it's not easy to inspect or fix data in production the way we would with postgres.
  • BuckRogers 5y ago
    I'd say the team is using it wrong. SQLite is really intended for embedded use, not a Postgres replacement. The two shouldn't even be mentioned in the same sentence. SQLite is weakly typed, performing autoconversion from ints to strings. The value in SQLite is its light weight, and not it's SQL side. If you're building a mobile app and you're loading a lot of local data, it might be the right choice.
    • pgwhalen 5y ago
      You may have misunderstood, we're not using it as a postgres replacement. I agree with this take, hence my original assertion that it isn't a drop in replacement for a DB server. We are using it as a replacement for RocksDB - we need a richer way to store data than a simple key value store. It still runs on a server though, and therefore it would be useful to be able to read data remotely, even if that isn't the primary purpose.
      • BuckRogers 5y ago
        My mistake then. I read it as "we tried it as a Postgres replacement, even though many here suggest it was going to work". I've toyed with SQLite as replacement for a client-server database for personal projects. While I stand by my overall dim assessment of SQLite, with a statically typed language and a diligently maintained data access layer (ie. one-man project), I would endorse its use on the server.
  • brunoluiz 5y ago
    I believe you mean that you can't easily do a "psql ..." or connect using DataGrid and similars, right? Does this mean that devs need to copy the production database file locally to then inspect it? Or are there tools to connect/bridge to a remote sqlite file?
• matdehaast 5y ago
  Shout out to litestream[0] for backups [0] https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream
• samwillis 5y ago
  I believe SQLite is about to explode in usage into areas it’s not been used before. SQL.js[0] and the incredible “Absurd SQL”[1] are making it possible to build PWAs and hybrid mobile apps with a local SQL db. Absurd SQL uses IndexedDB as a block store fs for SQLite so you don’t have to load the whole db into memory and get atomic writes. Also I recently discovered the Session Extension[2] which would potentially enable offline distributed updates with eventual consistency! I can imagine building a SAAS app where each customer has a “workspace” each as a single SQLite db, and a hybrid/PWA app which either uses a local copy of the SQLite db synced with the session extension or uses a serveless backend (like CloudFlare workers) where a lightweight function performs the db operations. I haven’t yet found a nice way to run SQLite on CloudFlare workers, it need some sort of block storage, but it can’t be far off. 0: https://sql.js.org/ https://sql.js.org/ 1: https://github.com/jlongster/absurd-sql https://github.com/jlongster/absurd-sql 2: https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html
  • paulryanrogers 5y ago
    I evaluated sqlite for a web extension but ultimately decided it wasn't worth it. There is no easy way to save the data directly to the file system. And saving the data in other ways meant I was probably better off with IndexDB instead. Still it is a tempting option and one that seems to work well for separate tenancy.
    • dorian-marchal 5y ago
      > There is no easy way to save the data directly to the file system. That's what absurd SQL is for (link in the parent comment).
      • paulryanrogers 5y ago
        I read that one and agree it feels absurd. Not something I want to depend on.
        • dorian-marchal 5y ago
• kristianpaul 5y ago
• anderspitman 5y ago
  Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with <100 users. These programs need a small amount of state for things like tokens, users accounts, and maybe a bit of domain-specific things. This can all live in memory, but needs to be persisted to disk on writes. I've reached for SQLite several times, and always come back to just keeping a struct of hashmaps[1] in memory and dumping JSON to disk. It's worked great for my needs. Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to exist? Why can't everyone be on a federated server with state that fits in memory/JSON? I guess that's more of a philosophical question about big tech. But I think it's interesting that most of our tech stack choices are driven by projects designed to work at a scale most of us will never need, and maybe nobody needs. As an aside, is there something like SQLite but closer to my use cases? So I guess like the nosql version of SQLite. [0]: https://boringproxy.io/ https://boringproxy.io/ [1]: https://github.com/boringproxy/boringproxy/blob/master/database.go https://github.com/boringproxy/boringproxy/blob/master/datab...
  • Sammi 5y ago
    You seem to be describing leveldb: https://github.com/google/leveldb https://github.com/google/leveldb
  • root_axis 5y ago
    If you care about data normalization and data integrity then SQLite is going to be a much better choice.
    • anderspitman 5y ago
      At the scale I described in my comment, I do not care about normalization. Can you give an example of where I'm likely to lose data integrity?
  • klysm 5y ago
    SQLite hides a ton of complexity that lives in the filesystem. It’s incredibly hard to do robust IO correctly with the APIs we have. I almost always choose SQLite for persisting to disk over JSON files. It essentially removes a large class of bugs and is robust enough that I’m not worried about introducing new problems.
• bonyt 5y ago
  I've always thought it interesting that there was a time when large(ish) websites were hosted using servers that would struggle to outperform a modern smart toaster or wristwatch, and yet modern web applications tend to demand a dramatic distributed architecture. I like the examples in this article showing what a single modern server can do when you're not scaling to Google's level. As an aside, what about distributed derivatives of sqlite, like rqlite, as a response to the criticism that sqlite requires your database server to also be your web server. Could something like rqlite also provide a way for an sqlite database to grow into a distributed cluster at a later point? https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
  • voidfunc 5y ago
    It's mostly because there is a demand for HA which requires multiple replicas. The moment you start going down that route you increase complexity. Whether most things actually require HA is debatable, but a lot of businesses make it a core requirement and so it gets baked into the architecture. Personally I feel like most stuff would be better suited to having fast fail-over and recovery early on, but my advice rarely gets taken. Instead you end up with complicated HA architectures that nobody totally understands, which then (inevitable still) fall over and take hours to recover.
    • lenkite 5y ago
      HA is generally needed because users of a SaaS can crash the instance especially in products that offer customisation abilities.
      • voidfunc 5y ago
        Sure, but that becomes a requirement at that particular design junction. A lot of stuff is built with HA that isn't even close to that complex.
  • freedomben 5y ago
    I don't disagree with you, a single server can go a really, really long way in scale before you run into problems. I know because I've done it a few times. The problem to me isn't ability to scale on one server, it's the single point of failure. My biggest site is a wordpress box with one instance on a pretty big VPS. In the last year I've had several outages big enough to require a post-mortem (not complete outages, but periods with high levels of error rates/failures), and every time it has been because of internal data center networking issues at my preferred cloud provider (and thankfully their customer service is amazing and they will tell me honestly what the problem was instead of leaving me to wonder and guess). So the main incentive for me to achieve horizontal scalability in that app is not scaling, it's high availability so I can survive temporary outages because of hardware or networking, and other stuff outside of my control.
• deleted 5y ago
  [deleted]
• aynyc 5y ago
  I used it for ETL process extensive which is great. I still don't know how people use it for concurrent writes like a simple ToDo webapp?
  • aidenn0 5y ago
    With WAL, writes for something like a ToDo app finish in a small fraction of a millisecond so unless your todo webapp is writing to the DB at a rate exceeding 20k writes per second, the fact that writes are not concurrent becomes largely irrelevant.
    • aynyc 5y ago
      This can't be right. As far as I can tell, WAL allows concurrent READS and WRITE, not concurrent WRITES. Am I doing this wrong all these years?
      • aidenn0 5y ago
        That's correct, it doesn't allow concurrent writes, but if the writes finish fast enough, that's somewhat academic.
        • aynyc 5y ago
          You still have to serialize the writes. If you have a lot of users on a simple ToDo apps, you can't do concurrent writes. I tried several times to run a simple webapp with Sqlite3 that have concurrent write requirement, the work around was too painful. I had to push all writes to an in-memory queue and have a single process pick off the queue. That process is outside of the web framework.
          • aidenn0 5y ago
            Why couldn't you do it in-process with a mutex? IIRC sqlite3 is thread-safe
• zaptheimpaler 5y ago
  I don't doubt the power of SQLite, but its difficult to see why its worth using over Postgres anyways. This is what it takes to run a basic postgres database on my own PC (in a docker compose file): postgres: image: postgres:12.7 container_name: postgres environment: - PGDATA=/var/lib/postgresql/data/pgdata - POSTGRES_PASSWORD=<pw> volumes: - ./volumes/postgres/:/var/lib/postgresql/data/ For someone who's completely allergic to SSH and linux, a managed Postgres service will take care of all that too. SQLite seems simple in that its "just a file". But its not. You can't pretend a backup is just copying the file while a DB is operating and expect it to be consistent. You can't put the file on NFS and have multiple writers and expect it to work. You can't use complex datatypes or have the database catch simple type errors for you. Its "simple" in precisely the wrong way - it looks simple, but actually using it well is not simple. It doesn't truly reduce operational burden, it only hides it until you find that it matters. Similarly postgres is not automatically complex simply because it _can_ scale. It really is a good technology that can be simple at small scale yet complex if you need it.
  • gravypod 5y ago
    I'm not really sure why this post has downvotes. docker-compose dramatically lowers the barrier for setting up a single machine with multiple services (your service, db, etc). For a similar experience you do the same with AWS RDS or equivalent. Performance will be better and worse in various situations but if your software still fits in one machine you're largely going to be "ok." Backups, restore, monitoring, etc are all important for running software and that's something an sqlite file doesn't really offer the best solutions for. It works great for some things (I've used it many many times) but it's not perfect for everything.
    • zaptheimpaler 5y ago
      I call this trend "tech hipster"-ism. Part of the motivation is just to do something different just for the sake of being different. Maybe part of it is a perception that Postgres or Linux are oh-so-scary and difficult things or that using the same technology that Amazon uses makes you evil. ¯\_(ツ)_/¯.
• alin23 5y ago
  I'm exactly at a point where I'm considering SQLite for its single file db advantage, but I'm struggling to find solutions for my use case. I need to import some 30k JSONs of external monitor data from Lunar (https://lunar.fyi https://lunar.fyi) into a normalized form so that everyone can query it. I'd love to get this into a single SQLite file that can be served and cached through CDN and local browser cache. But is there something akin to Metabase that could be used to query the db file after it was downloaded? I know I could have a Metabase server that could query the SQLite DB on my server, but I'd like the db and the queries to run locally for faster iteration and less load on my server. Besides, I'm reluctant to run a public Metabase instance given the log4j vulnerabilities that keep coming.
  • zbentley 5y ago
    Check out datasette: https://datasette.io/ https://datasette.io/
  • dkjaudyeqooe 5y ago
    You could do incremental updates of the local databases using the session extension: https://sqlite.org/sessionintro.html https://sqlite.org/sessionintro.html
• bob1029 5y ago
  We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performance is hardly a concern when you have reasonable hardware (NVMe/SSD) and utilize appropriate configuration (PRAGMA journal_mode=WAL). In our testing, our usage of SQLite vastly outperformed an identical schema on top of SQL Server. It is my understanding that something about not having to take a network hop and being able to directly invoke the database methods makes a huge difference. Are you able to execute queries and reliably receive results within microseconds with your current database setup? Sure, there is no way we are going to be able to distribute/cluster our product by way of our database provider alone, but this is a constraint we decided was worth it, especially considering all of the other reduction in complexity you get with single machine business systems. I am aware of things like DQLite/RQLite/et.al., but we simply don't have a business case that demands that level of resilience (and complexity) yet. Some other tricks we employ - We do not use 1 gigantic SQLite database for the entire product. It's more like a collection of microservices that live inside 1 executable with each owning an independent SQLite database copy. So, we would have databases like Users.db, UserSessions.db, Settings.db, etc. We don't have any use cases that would require us to write some complex reporting query across multiple databases.
  • La-Douceur 5y ago
    Are you capable of achieving no downtime deployment ? I mean, on the product I currently work on, we have one mongo database, and a cluster of 4 pods on which our backend is deployed. When we want to deploy some new feature without having any downtime, one of the pod is be shut down, our product still work with the 3 remaining pods, and we start a new pod with the new code, and do this for the 4 pods. But with SQLite, if I understand correctly, you have one machine or one VM, that is both running your backend, and storing your SQLite.db file. If you want to deploy some new features on your backend, can you achieve no downtime deployment ?
  • gunshowmo 5y ago
• iffycan 5y ago
  I've had great success using SQLite as both a desktop application file format and web server database. I'll mention just one thing I like about it in the desktop application realm: undo/redo is implemented entirely within SQLite using in-memory tables and triggers following this as a starting point: https://www.sqlite.org/undoredo.html https://www.sqlite.org/undoredo.html It's not perfect, but it fills the niche nicely.
• reneberlin 5y ago
  My 2 cents on sqlite: https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/ An interview with one of the creators:Mr. Richard Hipp - for a better and deeper understanding what pitch they took and what industries they were in to. Their approach to overcome the db-world that they saw in front of them. See the obstacles and the solutions and why it came to be that underestimated 'sqlite' that powers a good chunk of all you mobile actions triggered by your apps - but just read that interview - i cannot reproduce the dramatic here in my own words (underestimated).
• mrfusion 5y ago
  I wrote a script to find all csv files in a directory and figure out the best way to load them into SQLite. It gives me a handy way to run queries on data. I tried to make it super smart too and really make educated guesses on what data types to use and even linking foreign keys.
  • lenkite 5y ago
    A language like R (https://www.r-project.org/ https://www.r-project.org/) helps more for CSV data-science. https://www.tutorialspoint.com/r/r_csv_files.htm https://www.tutorialspoint.com/r/r_csv_files.htm
    • eigenvalue 5y ago
      You can use Python with Pandas as an easy and powerful way to import from CSV files and then directly write or append the tables to an sqlite db. The whole thing can be done in just a couple lines of code.
• reneberlin 5y ago
  Wow. So much tech - sqlite this time, and so many opinions to discuss with words / characters. Maybe i could create my own alphabet to have some peace of mind in the end? I don't think so - somebody would make a story of it - wait ... happened! Read the creator of sqlite for more information on all running topics that make you go 'Uh' up to this point of time: https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/
• reneberlin 5y ago
  Before you start throwing your opinions into HN about sqlite, please read: https://corecursive.com/066-sqlite-with-richard-hipp/?utm_source=pocket_mylist https://corecursive.com/066-sqlite-with-richard-hipp/?utm_so...
• newlisp 5y ago
  Postgres is 9.5× slower when running on the same machine as the one doing the query I'm surprised by this, sure in-process is always going to be faster but still find it hard to believe that sqlite can be beat postgres in a single machine.
• pietroppeter 5y ago
  Nim forum uses SQLite as its db since 2012 and it fits perfectly the article’s use case. Code is available and it can be used to run a discourse inspired forum (although much less featured). https://github.com/nim-lang/nimforum https://github.com/nim-lang/nimforum
• reneberlin 5y ago
  What a cruel world- sqlite to the rescue!
• samatman 5y ago
  This excellent article doesn't even mention rqlite, which will synchronize an arbitrary number of SQLite instances using the Raft protocol. There must be some scaling limits to encounter using this combination, but wouldn't you love to have that problem?
• reneberlin 5y ago
  meh https://corecursive.com/066-sqlite-with-richard-hipp/?utm_source=hn_sqlite_facts https://corecursive.com/066-sqlite-with-richard-hipp/?utm_so...
• reneberlin 5y ago
  This "little thingie" is so powerful, that words are missing to tell the story.
• chunkyks 5y ago
  In the past I had a website with hundreds of gigs of data that needed updating regularly, but could be read-only from the web server perspective. I used sqlite for that, and had a mysql server for the user data and stuff that needed to be written to. Performance was fantastic, users were happy, data updates were instantaneous ; copy the new data to the server then repoint a symlink. Most of my work is modeling and simulation. Sqlite is almost always my output format ; one case per database is really natural and convenient, both for analysis, and run management. Anyway. Sqlite is amazing.
• gregors 5y ago
  I love this approach. I've ran sqlite in production for a variety of products over the years and for the most part it's great. One "back up" solution I did was dump tables to text - commit the diff in git. Taking file copies while transactions are in process can lead to corrupted db's.
• vanilla-almond 5y ago
  Is SQLite suitable for a small-to-medium CMS (Content Management System), or a blog platform e.g. WordPress (MySQL) or Ghost (MySQL)?
  • omnimus 5y ago
    Sure. Considering many CMSes are sucessfully doing “flat file” instead of database. Sqlite can for sure do that as well or better.
• bobobob420 5y ago
  i have so much love for SQLite. Consider me, getting my first internship at a startup. They have a bunch of contractors doing work for them as part of their service. The whitelabel application they got developed would export data in CSV. My job was to take that data and get some meaning from it. data included availability, locations, etc (imagine data about delivery drivers). I had no idea what to do but realized I could definitely parse this CSV through Python. Once I had this data in Python I needed a way to analyze it. I never worked with Databases but decided to install a local copy of SQLite. The rest is history. I feel like I learned how to use databases in an organic way: by looking for a solution from raw data. A couple of queries later and python was exporting excel sheets with color coded boxes that indicated something based on the analysis I did. Of course this could be done with any database application but the low weight nature of sqlite allowed me to prototype a solution so easily. We just backed up that native sqlite dump with the cloud and had an easy (super easy) solution to analyze raw data.
• frays 5y ago
  Is anyone here running production workloads which perform read and write operations on a remote SQLite database? Currently using Postgres and I'm open to switching but I haven't seen any libraries or implementations of SQLite being used as a client/server database.
  • danielheath 5y ago
    Why switch? Typically the work involved would need to be justified by some benefit, right?
• gjvc 5y ago
  sqlite as the data store for a system package manager is possibly the most practical single-host example
• smitty1e 5y ago
  I've been doing some ETL exploration with opening a sqlite :memory: connection, ingesting small-to-medium data, and then doing "VACUUM INTO somefile.sqlite;" to dump the RAM copy to disk. What a great tool.
  • zbentley 5y ago
    What does that do? How is VACUUM different from BACKUP in this context?
    • smitty1e 5y ago
      "The VACUUM command with an INTO clause is an alternative to the backup API for generating backup copies of a live database. The advantage of using VACUUM INTO is that the resulting backup database is minimal in size and hence the amount of filesystem I/O may be reduced. Also, all deleted content is purged from the backup, leaving behind no forensic traces. On the other hand, the backup API uses fewer CPU cycles and can be executed incrementally." https://sqlite.org/lang_vacuum.html https://sqlite.org/lang_vacuum.html
• errantmind 5y ago
  Also, consider just using the filesystem with binary encoding. Serialize / deserialize directly from / to data structures. It is faster and simpler than any database you'll ever use, assuming you don't need the functionality a database provides.
• tapvt 5y ago
  I happily use SQLite via `sqflite`, a Flutter library, to store and retrieve data for an offline-first mobile app. This is my first time really using it, and I’m quite pleased with the experience and the familiar feel of using SQL.