The table below compares the different functionality available between the PostgreSQL adapter, SQLite adapter (sqlite_ecto2), and barebones SQLite. Use it to determine what Ecto functionality you can use with sqlite_ecto2 and whether or not you should consider a more robust database solution, e.g. PostgreSQL, for your application. There are open issues to extend the functionality of sqlite_ecto2, and this table will be updated as they are implemented.
| Supported Functionality |
PostgreSQL |
sqlite_ecto2 |
SQLite |
| Inner Joins |
Yes |
Yes |
Yes |
| (Left) Outer Joins |
Yes |
Yes |
Yes |
| Right Outer Joins |
Yes |
No |
No |
| Full Outer Joins1 |
Yes |
No |
No |
| Foreign Key Constraints2 |
Yes |
Yes |
Optional |
| RETURNING Clause3 |
Yes |
Yes |
No |
| Update/Delete w/ Joins4 |
Yes |
No |
No |
| ALTER COLUMN5 |
Yes |
No |
No |
| DROP COLUMN5 |
Yes |
No |
No |
| Locking Clause on Select |
Yes |
No |
No |
- A "full outer join" first implements an inner join on two tables. Then, for any rows from either table that are missing from the result set, it adds those rows to the result set filling in NULLs for any missing values.
- In SQLite, foreign key constraints must be turned on explicitly for each new database connection with: PRAGMA foreign_keys = ON;. sqlite_ecto2 does this by default for each connection.
- PostgreSQL can return arbitrary values on INSERT, UPDATE, or DELETE. For example, the statement INSERT INTO customs (counter, visits) VALUES (10, 11) RETURNING id; will insert the given values into the customs table and then return the id for the new row. SQLite has no support for such a RETURNING clause, but it can return the last inserted "rowid" for a table. This ability was added to the sqlitex library in version 1.2.0. This article discusses the method.
- JOINs for INSERT and UPDATE statements are not supported. (PR welcome if you can find a way to implement them.)
- SQLite does not support modifying or deleting rows in ALTER TABLE statements. The SQLite docs describe an algorithm for implementing this functionality. However, the algorithm will require changes to the way foreign key constraints are handled. (Again, a PR is welcome if you wish to implement this.)