Sqlite.Ecto Functionality

The table below compares the different functionality available between the PostgreSQL adapter, SQLite adapter (Sqlite.Ecto), and barebones SQLite. Use it to determine what Ecto functionality you can use with Sqlite.Ecto 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.Ecto, and this table will be updated as they are implemented.

Supported Functionality	PostgreSQL	Sqlite.Ecto	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

Notes

1. 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. There is an issue to implement this functionality in Sqlite.Ecto.
2. In SQLite, foreign key constraints must be turned on explicitly for each new database connection with: PRAGMA foreign_keys = ON;. Sqlite.Ecto does this by default for each connection.
3. 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. Support for returning was added to Sqlite.Ecto in version 0.0.2. This article discusses the method.
4. There is an issue to implement JOINs for INSERT and UPDATE statements.
5. 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. This issue is tracking the necessary changes to Sqlite.Ecto foreign key constraints.