4 ms·

I'm here to declare my love for sqlite, a database that doesn't asks me to choose a root password (wtf, everybody hate passwords) and an application user passwo

I'm here to declare my love for sqlite, a database that doesn't asks me to choose a root password (wtf, everybody hate passwords) and an application user password (double wtf), doesn't open a socket (being it unix on TCP), doesn't install a daemon somewhere in the system, doesn't need to be root to start, doesn't asks me to create a new dba user in my system, doesn't require me to be understand how the root daemon starter hands over the control to the dba user when something goes wrong at startup, and generally doesn't bother me with stuff that has really nothing to do with storing and querying data. I don't run this in production only because I would have a hard time convincing everybody to do it or by cargo-culting my fears (depending on the project). But one day I will.

viraptor 13y ago
So in short... if anyone gets the chance of an sql injection, they have access to all tables and all schemas from any app that can access your database. Privilege separation is a very good idea. Not opening the database file directly is a very good idea too. Just to be clear, I'm not saying every application needs it. If you actually use it as a replacement for local files, that's great. If you use a multi-user app containing sensitive data - "root password (wtf, everybody hate passwords)" this is so wrong. Reminds me of "chmod 777 everything" - wtf, everybody hates dealing with permissions.
- abtinf 13y ago
  The gp point that sqlite gets rid of all the "stuff that has really nothing to do with storing and querying data" still stands. Sqlite eliminates almost all of the management overhead that other databases force on you, so its trivial to have one database per application. Indeed, I have never seen anyone bother with a multi-application sqlite db, which could also cause serious performance problems. So that renders you sql injection point moot. As for permissions, its better to let the OS handle that than some half-baked database permissions system. Root accounts/passwords are a wtf in a production environment because they destroy auditability. If you really want permissions, it is trivial to create system user accounts for the application and assign the correct file permissions, or even isolate particularly sensitive information in its own file. Combine this with AppArmor to further lock down individual applications. All of this is overkill, but so is a full blown RDBMS in many cases. If you are starting out building a new application, just target sqlite until you actually need something bigger. Odds are, YAGNI.
  - jacques_chester 13y ago
    > Sqlite eliminates almost all of the management overhead that other databases force on you Multi-user support is what forces that overhead on you. It's just that SQLite consciously aims to be a single-user system. > As for permissions, its better to let the OS handle that than some half-baked database permissions system This is nonsensical. There isn't an isomorphic mapping from the Unix permissions model to SQL's permissions model, particularly when you start introducing stuff like row-level permissions. If you write an application which uses a DB backend, and you require multi-user capability, you can either delegate some of that to the database, or roll your own. In which case, half-baked permissions systems are back! Especially if you can't isolate database connections (because it renders any app-code permissions system moot if you allow 3rd party code execution, such as plugins). All that overhead is there for a reason. Those reasons are irrelevant in the single-user case. But they are not irrelevant in the multi-user case.
    - epo 13y ago
      > All that overhead is there for a reason. SQLite's classic use case is where SQL-like access to data would be convenient but a full RDBMS would be tremendously inconvenient, e.g. embedded systems. People seem to be citing inappropriate environments for SQLite as reasons to say it is of no value. You get the same sort of silliness from NoSQL zealots.
  - viraptor 13y ago
    > As for permissions, its better to let the OS handle that than some half-baked database permissions system. Sorry, but that doesn't make sense. For example: if you want your application to be able to add new rows, but not delete them (for audits actually) you cannot do this with OS permissions. Only database-layer permissions will make it possible. There are many other examples of such use-cases. You simply cannot use OS/apparmor/selinux/whatever external to the database to grant/revoke per-table access. > Root accounts/passwords are a wtf in a production environment because they destroy auditability. On this side however - why would anyone know / have access to your root account? Applications should never have it. Servers should never have it (in a form different than the hash). Only people should have it.
- deleted 13y ago
  [deleted]
- kamaal 13y ago
  If you are using sqlite for hosting a production scale app, you are doing it wrong. If you want an database for your mobile app, or a web app use a full fledged database like MySQL or Postgres. sqlite is for embedded storage applications, especially in the backend. Where the resource utilization foot print is supposed to be minimum, and where you running into situations where are you are reimplementing SQL like DSL's to query and write to files. Or say you just wish to maintain some structured form of inventory. Best example I can give you is a web browser. There is a requirement to store a good deal of structured information, in a mechanism which requires little resource utilization, which doesn't require additional software like demons or a server to run or complicated deploy and maintain scenarios. Or even a strict configuration. If you look at this use case you will see tons of backend application would be better off using a tool like sqlite than play with huge xmls and endless rewrite parts of SQL badly.
simias 13y ago
I love SQLite but I don't think it's fair to compare it directly to other rdbms. As the official website itself puts it: "Another way to look at SQLite is this: SQLite is not designed to replace Oracle. It is designed to replace fopen()." https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html There are very few instances where I end up in a position where I have to decide whether I need to use postgres or sqlite, they have very different use cases.
- Shorel 13y ago
  I don't think it is designed to replace fopen(). They can't say it, but it is designed to replace FoxPro and Access, or more generally: a local ODBC connection.
jeltz 13y ago
Most of this is not true for PostgreSQL either. The only two things PostgreSQL needs to run is a daemon (it does not have to be actually daemonized) plus a unix socket, passwords, user accounts, root privileges, etc are all optional. Sure SQLite (which I love) is easier to get running, but do not exaggerate issues of traditional RDBMs. In PostgreSQL 9.3 which will be released in a couple of weeks they will also fix the cases where you may need to use sysctl to iincrease shared memory limits.

viraptor 13y ago

So in short... if anyone gets the chance of an sql injection, they have access to all tables and all schemas from any app that can access your database. Privilege separation is a very good idea. Not opening the database file directly is a very good idea too. Just to be clear, I'm not saying every application needs it. If you actually use it as a replacement for local files, that's great. If you use a multi-user app containing sensitive data - "root password (wtf, everybody hate passwords)" this is so wrong. Reminds me of "chmod 777 everything" - wtf, everybody hates dealing with permissions.

abtinf 13y ago
The gp point that sqlite gets rid of all the "stuff that has really nothing to do with storing and querying data" still stands. Sqlite eliminates almost all of the management overhead that other databases force on you, so its trivial to have one database per application. Indeed, I have never seen anyone bother with a multi-application sqlite db, which could also cause serious performance problems. So that renders you sql injection point moot. As for permissions, its better to let the OS handle that than some half-baked database permissions system. Root accounts/passwords are a wtf in a production environment because they destroy auditability. If you really want permissions, it is trivial to create system user accounts for the application and assign the correct file permissions, or even isolate particularly sensitive information in its own file. Combine this with AppArmor to further lock down individual applications. All of this is overkill, but so is a full blown RDBMS in many cases. If you are starting out building a new application, just target sqlite until you actually need something bigger. Odds are, YAGNI.
- jacques_chester 13y ago
  > Sqlite eliminates almost all of the management overhead that other databases force on you Multi-user support is what forces that overhead on you. It's just that SQLite consciously aims to be a single-user system. > As for permissions, its better to let the OS handle that than some half-baked database permissions system This is nonsensical. There isn't an isomorphic mapping from the Unix permissions model to SQL's permissions model, particularly when you start introducing stuff like row-level permissions. If you write an application which uses a DB backend, and you require multi-user capability, you can either delegate some of that to the database, or roll your own. In which case, half-baked permissions systems are back! Especially if you can't isolate database connections (because it renders any app-code permissions system moot if you allow 3rd party code execution, such as plugins). All that overhead is there for a reason. Those reasons are irrelevant in the single-user case. But they are not irrelevant in the multi-user case.
  - epo 13y ago
    > All that overhead is there for a reason. SQLite's classic use case is where SQL-like access to data would be convenient but a full RDBMS would be tremendously inconvenient, e.g. embedded systems. People seem to be citing inappropriate environments for SQLite as reasons to say it is of no value. You get the same sort of silliness from NoSQL zealots.
- viraptor 13y ago
  > As for permissions, its better to let the OS handle that than some half-baked database permissions system. Sorry, but that doesn't make sense. For example: if you want your application to be able to add new rows, but not delete them (for audits actually) you cannot do this with OS permissions. Only database-layer permissions will make it possible. There are many other examples of such use-cases. You simply cannot use OS/apparmor/selinux/whatever external to the database to grant/revoke per-table access. > Root accounts/passwords are a wtf in a production environment because they destroy auditability. On this side however - why would anyone know / have access to your root account? Applications should never have it. Servers should never have it (in a form different than the hash). Only people should have it.
deleted 13y ago
[deleted]
kamaal 13y ago
If you are using sqlite for hosting a production scale app, you are doing it wrong. If you want an database for your mobile app, or a web app use a full fledged database like MySQL or Postgres. sqlite is for embedded storage applications, especially in the backend. Where the resource utilization foot print is supposed to be minimum, and where you running into situations where are you are reimplementing SQL like DSL's to query and write to files. Or say you just wish to maintain some structured form of inventory. Best example I can give you is a web browser. There is a requirement to store a good deal of structured information, in a mechanism which requires little resource utilization, which doesn't require additional software like demons or a server to run or complicated deploy and maintain scenarios. Or even a strict configuration. If you look at this use case you will see tons of backend application would be better off using a tool like sqlite than play with huge xmls and endless rewrite parts of SQL badly.

simias 13y ago

I love SQLite but I don't think it's fair to compare it directly to other rdbms. As the official website itself puts it: "Another way to look at SQLite is this: SQLite is not designed to replace Oracle. It is designed to replace fopen()." https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html There are very few instances where I end up in a position where I have to decide whether I need to use postgres or sqlite, they have very different use cases.

Shorel 13y ago
I don't think it is designed to replace fopen(). They can't say it, but it is designed to replace FoxPro and Access, or more generally: a local ODBC connection.

jeltz 13y ago

Most of this is not true for PostgreSQL either. The only two things PostgreSQL needs to run is a daemon (it does not have to be actually daemonized) plus a unix socket, passwords, user accounts, root privileges, etc are all optional. Sure SQLite (which I love) is easier to get running, but do not exaggerate issues of traditional RDBMs. In PostgreSQL 9.3 which will be released in a couple of weeks they will also fix the cases where you may need to use sysctl to iincrease shared memory limits.