251

Are there advantages or disadvantages to the file extension used for SQLite databases?

It seems that SQLite itself does not require a naming convention, but there might be other reasons that a particular extension would be useful or problematic - for instance, certain tools, programming languages, installers, etc. Or end user considerations.

The most common ones appear to be .sqlite, .db, and .db3.

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
Fermin
  • 34,961
  • 21
  • 83
  • 129

5 Answers5

234

Pretty much down to personal choice. It may make sense to use an extension based on the database scheme you are storing; treat your database schema as a file format, with SQLite simply being an encoding used for that file format. So, you might use .bookmarks if it's storing bookmarks, or .index if it's being used as an index.

If you want to use a generic extension, I'd use .sqlite3 since that is most descriptive of what version of SQLite is needed to work with the database.

Brian Campbell
  • 322,767
  • 57
  • 360
  • 340
60

In distributable software, I dont want my customers mucking about in the database by themselves. The program reads and writes it all by itself. The only reason for a user to touch the DB file is to take a backup copy. Therefore I have named it whatever_records.db

The simple .db extension tells the user that it is a binary data file and that's all they have to know. Calling it .sqlite invites the interested user to open it up and mess something up!

Totally depends on your usage scenario I suppose.

Karl
  • 8,967
  • 5
  • 29
  • 31
  • 6
    just opening the sqlite3 file in notepad will reveal that its a sqlite3 database though :p – hanshenrik Oct 03 '15 at 22:25
  • 16
    I don't really get the logic of this. It's a non-trivial file format to mess with. I can't see anyone who doesn't understand the consequences for messing with such a file being able to mess with such a file. But ultimately, I think it comes down to "who cares" with regards to file extensions. Anyone who really needs to be able to edit the file and has the skill to do so should have no trouble figuring out what kind of file it is. So name it whatever pleases you. – Kat Jan 08 '16 at 02:43
  • 3
    I agree with Mike - a user who knows a db file is a database file will have enough skill to muck with it. even if he does not know what it is he might try to open with notepad. I sure would. Sometimes just to see what settings are stored - and certainly there are enough tools available to open it as well - DB Browser ...etc.. – Stix Feb 03 '16 at 21:01
  • I think this is a reasonable precaution. Very low effort and if it avoids a few cases of having to fix a customer's problem then it would be worthwhile. – StayOnTarget Feb 14 '18 at 16:03
  • 1
    I think the main danger is the ability for someone to double-click to open it, which a user who isn't tech-savvy may try to do. A custom extension provides one hoop of technical expertise for the user to jump through (not everyone knows how to choose a program, or that many filetypes can be opened in a text editor). Another thing to consider is, if it's a file intended to work with software you're developing, your software can let the OS know that it supports opening files with a custom extension. Then, double-clicking the file will open your app instead of a database program. – Samuel Bradshaw Jan 21 '21 at 00:38
39

If you have settled on a particular set of tools to access / modify your databases, I would go with whatever extension they expect you to use. This will avoid needless friction when doing development tasks.

For instance, SQLiteStudio v3.1.1 defaults to looking for files with the following extensions:

enter image description here

(db|sdb|sqlite|db3|s3db|sqlite3|sl3|db2|s2db|sqlite2|sl2)

If necessary for deployment your installation mechanism could rename the file if obscuring the file type seems useful to you (as some other answers have suggested). Filename requirements for development and deployment can be different.

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
35

SQLite doesn't define any particular extension for this, it's your own choice. Personally, I name them with the .sqlite extension, just so there isn't any ambiguity when I'm looking at my files later.

Chad Birch
  • 73,098
  • 23
  • 151
  • 149
22

Emacs expects one of db, sqlite, sqlite2 or sqlite3 in the default configuration for sql-sqlite mode.

Matthias
  • 221
  • 2
  • 2