Sqlite vs Mysql

Discussion in 'Spigot Plugin Development' started by Hex_27, May 12, 2017.

  1. What's the difference, what are their limitations and strengths, and which database can hold more?
     
  2. One is local, based on a single file. The other is based on an external process. The strength of a SQLite database is that it doesn't require much additional setup (you can create the file programmatically), though it doesn't support concurrent writes (only concurrent reads with the right settings enabled) and it doesn't support clustering et al.
     
  3. SQLite is more limited in terms of changes to the schema as well.
    For example, if you would like to remove a column from a table, in SQLite the whole table needs to be dropped & a new one created. In MySQL a single ALTER TABLE statement can be used.

    If you decide to go with both, the languages are just close enough to drive one nuts in some cases.
    • When adding a column to a table MySQL uses "ALTER TABLE tableName ADD columnName type "and SQLIte uses "ALTER TABLE tableName ADD COLUMN columnName type"
    • When creating an auto incrementing column (For example id), MySQL uses "NOT NULL AUTO_INCREMENT" and SQLite uses "PRIMARY KEY" as the type
    That's what I've run into so far.
     
  4. So it isn't actually easy to just use the same code for both?
     
  5. You can add a simple usingMySQL boolean and then depending on that change the String for the statement. - It is still easier than MySQL vs SQL Server (Microsoft) :D