Skip to content

Commit 068bb89

Browse files
oceanocean
authored
Merge pull request #39 from ocean/random-rowid-support
2 parents 88a46cb + d97ff56 commit 068bb89

4 files changed

Lines changed: 345 additions & 40 deletions

File tree

‎AGENTS.md‎

Lines changed: 144 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1298,6 +1298,108 @@ mix ecto.migrate # Run migrations
12981298
mix ecto.rollback # Rollback last migration
12991299
```
13001300

1301+
#### RANDOM ROWID Support (libSQL Extension)
1302+
1303+
For security and privacy, use RANDOM ROWID to generate pseudorandom row IDs instead of sequential integers:
1304+
1305+
```elixir
1306+
# Create table with random row IDs (prevents ID enumeration attacks)
1307+
defmodule MyApp.Repo.Migrations.CreateSessions do
1308+
use Ecto.Migration
1309+
1310+
def change do
1311+
create table(:sessions, options: [random_rowid: true]) do
1312+
add :token, :string, null: false
1313+
add :user_id, references(:users, on_delete: :delete_all)
1314+
add :expires_at, :utc_datetime
1315+
1316+
timestamps()
1317+
end
1318+
1319+
create unique_index(:sessions, [:token])
1320+
end
1321+
end
1322+
```
1323+
1324+
**Benefits:**
1325+
- **Security**: Prevents ID enumeration attacks (guessing valid IDs)
1326+
- **Privacy**: Doesn't leak business metrics through sequential IDs
1327+
- **Unpredictability**: Row IDs are pseudorandom, not sequential
1328+
1329+
**Usage:**
1330+
```elixir
1331+
# Basic usage
1332+
create table(:sessions, options: [random_rowid: true]) do
1333+
add :token, :string
1334+
end
1335+
1336+
# With composite primary key
1337+
create table(:audit_log, options: [random_rowid: true]) do
1338+
add :user_id, :integer, primary_key: true
1339+
add :action_id, :integer, primary_key: true
1340+
add :timestamp, :integer
1341+
end
1342+
1343+
# With IF NOT EXISTS
1344+
create_if_not_exists table(:sessions, options: [random_rowid: true]) do
1345+
add :token, :string
1346+
end
1347+
```
1348+
1349+
**Restrictions:**
1350+
- Mutually exclusive with WITHOUT ROWID (per libSQL specification)
1351+
- Mutually exclusive with AUTOINCREMENT (per libSQL specification)
1352+
- LibSQL extension - not available in standard SQLite
1353+
1354+
**SQL Output:**
1355+
```sql
1356+
CREATE TABLE sessions (...) RANDOM ROWID
1357+
```
1358+
1359+
#### ALTER COLUMN Support (libSQL Extension)
1360+
1361+
LibSQL supports modifying column attributes with ALTER COLUMN (not available in standard SQLite):
1362+
1363+
```elixir
1364+
defmodule MyApp.Repo.Migrations.ModifyUserColumns do
1365+
use Ecto.Migration
1366+
1367+
def change do
1368+
alter table(:users) do
1369+
# Change column type
1370+
modify :age, :string, default: "0"
1371+
1372+
# Add NOT NULL constraint
1373+
modify :email, :string, null: false
1374+
1375+
# Add DEFAULT value
1376+
modify :status, :string, default: "active"
1377+
1378+
# Add foreign key reference
1379+
modify :team_id, references(:teams, on_delete: :nilify_all)
1380+
end
1381+
end
1382+
end
1383+
```
1384+
1385+
**Supported Modifications:**
1386+
- Type affinity changes (`:integer` → `:string`, etc.)
1387+
- NOT NULL constraints
1388+
- DEFAULT values
1389+
- CHECK constraints
1390+
- REFERENCES (foreign keys)
1391+
1392+
**Important Notes:**
1393+
- Changes only apply to **new or updated rows**
1394+
- Existing data is **not revalidated** or modified
1395+
- This is a **libSQL extension** - not available in standard SQLite
1396+
1397+
**SQL Output:**
1398+
```sql
1399+
ALTER TABLE users ALTER COLUMN age TO age TEXT DEFAULT '0'
1400+
ALTER TABLE users ALTER COLUMN email TO email TEXT NOT NULL
1401+
```
1402+
13011403
### Basic Queries
13021404

13031405
#### Insert
@@ -1719,33 +1821,61 @@ Ecto types map to SQLite types as follows:
17191821

17201822
### Ecto Migration Notes
17211823

1722-
Most Ecto migrations work perfectly. SQLite limitations:
1824+
Most Ecto migrations work perfectly. LibSQL provides extensions beyond standard SQLite:
17231825

17241826
```elixir
1725-
# ✅ SUPPORTED
1726-
create table(:users)
1727-
alter table(:users) do: add :field, :type
1728-
drop table(:users)
1729-
create index(:users, [:email])
1730-
rename table(:old), to: table(:new)
1731-
rename table(:users), :old_field, to: :new_field
1827+
# ✅ FULLY SUPPORTED
1828+
create table(:users) # CREATE TABLE
1829+
create table(:sessions, options: [random_rowid: true]) # RANDOM ROWID (libSQL extension)
1830+
alter table(:users) do: add :field, :type # ADD COLUMN
1831+
alter table(:users) do: modify :field, :new_type # ALTER COLUMN (libSQL extension)
1832+
alter table(:users) do: remove :field # DROP COLUMN (libSQL/SQLite 3.35.0+)
1833+
drop table(:users) # DROP TABLE
1834+
create index(:users, [:email]) # CREATE INDEX
1835+
rename table(:old), to: table(:new) # RENAME TABLE
1836+
rename table(:users), :old_field, to: :new_field # RENAME COLUMN
1837+
1838+
# ⚠️ LIBSQL EXTENSIONS (not in standard SQLite)
1839+
alter table(:users) do: modify :age, :string # ALTER COLUMN - libSQL only
1840+
create table(:sessions, options: [random_rowid: true]) # RANDOM ROWID - libSQL only
1841+
```
1842+
1843+
**Important Notes:**
1844+
1845+
1. **ALTER COLUMN** is a libSQL extension (not available in standard SQLite)
1846+
- Supported operations: type changes, NOT NULL, DEFAULT, CHECK, REFERENCES
1847+
- Changes only apply to new/updated rows; existing data is not revalidated
17321848

1733-
# ❌ NOT SUPPORTED
1734-
alter table(:users) do: modify :field, :new_type # Can't change column type
1735-
alter table(:users) do: remove :field # Can't drop column (SQLite < 3.35.0)
1849+
2. **DROP COLUMN** requires SQLite 3.35.0+ or libSQL
1850+
- Cannot drop PRIMARY KEY columns, UNIQUE columns, or referenced columns
17361851

1737-
# Workaround: Recreate table
1852+
3. **RANDOM ROWID** is a libSQL extension for security/privacy
1853+
- Prevents ID enumeration attacks
1854+
- Mutually exclusive with WITHOUT ROWID and AUTOINCREMENT
1855+
1856+
**Standard SQLite Workaround (if not using libSQL's ALTER COLUMN):**
1857+
1858+
If you need to modify columns on standard SQLite (without libSQL's extensions), recreate the table:
1859+
1860+
```elixir
17381861
defmodule MyApp.Repo.Migrations.ChangeUserAge do
17391862
use Ecto.Migration
17401863

17411864
def up do
17421865
create table(:users_new) do
1743-
# Define new schema
1866+
add :id, :integer, primary_key: true
1867+
add :name, :string
1868+
add :email, :string
1869+
add :age, :string # Changed from :integer
1870+
timestamps()
17441871
end
17451872

1746-
execute "INSERT INTO users_new SELECT * FROM users"
1873+
execute "INSERT INTO users_new (id, name, email, age, inserted_at, updated_at) SELECT id, name, email, CAST(age AS TEXT), inserted_at, updated_at FROM users"
17471874
drop table(:users)
17481875
rename table(:users_new), to: table(:users)
1876+
1877+
# Recreate indexes
1878+
create unique_index(:users, [:email])
17491879
end
17501880
end
17511881
```

‎CHANGELOG.md‎

Lines changed: 57 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -7,25 +7,30 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
77

88
## [Unreleased]
99

10-
### Investigated but Not Supported
11-
12-
- **Hooks Investigation**: Researched implementation of SQLite hooks (update hooks and authorizer hooks) for CDC and row-level security
13-
- **Update Hooks (CDC)**: Cannot be implemented due to Rustler threading limitations
14-
- SQLite's update hook runs on managed BEAM threads
15-
- Rustler's `OwnedEnv::send_and_clear()` can ONLY be called from unmanaged threads
16-
- Would cause panic: "send_and_clear: current thread is managed"
17-
- **Authorizer Hooks (RLS)**: Cannot be implemented due to synchronous callback requirements
18-
- Requires immediate synchronous response (Allow/Deny/Ignore)
19-
- No safe way to block waiting for Elixir response from scheduler thread
20-
- Would risk deadlocks with scheduler thread blocking
21-
- **Result**: Both `add_update_hook/2`, `remove_update_hook/1`, and `add_authorizer/2` return `{:error, :unsupported}`
22-
- **Alternatives provided**: Comprehensive documentation of alternative approaches:
23-
- For CDC: Application-level events, database triggers, polling, Phoenix.Tracker
24-
- For RLS: Application-level auth, database views, query rewriting, connection-level privileges
25-
- See Rustler issue: https://github.com/rusterlium/rustler/issues/293
26-
2710
### Added
2811

12+
- **RANDOM ROWID Support (libSQL Extension)**
13+
- Added support for libSQL's RANDOM ROWID table option to generate pseudorandom rowid values instead of consecutive integers
14+
- **Security/Privacy Benefits**: Prevents ID enumeration attacks and leaking business metrics through sequential IDs
15+
- **Usage**: Pass `options: [random_rowid: true]` to `create table()` in migrations
16+
- **Example**:
17+
```elixir
18+
create table(:sessions, options: [random_rowid: true]) do
19+
add :token, :string
20+
add :user_id, :integer
21+
timestamps()
22+
end
23+
```
24+
- **Compatibility**: Works with all table configurations (single PK, composite PK, IF NOT EXISTS)
25+
- **Restrictions**: Mutually exclusive with WITHOUT ROWID and AUTOINCREMENT (per libSQL specification)
26+
- **Validation**: Early validation of mutually exclusive options with clear error messages (connection.ex:386-407)
27+
- Raises `ArgumentError` if RANDOM ROWID is combined with WITHOUT ROWID
28+
- Raises `ArgumentError` if RANDOM ROWID is combined with AUTOINCREMENT on any column
29+
- Prevents libSQL runtime errors by catching conflicts during migration compilation
30+
- SQL output: `CREATE TABLE sessions (...) RANDOM ROWID`
31+
- Added 7 comprehensive tests covering RANDOM ROWID with various configurations and validation scenarios
32+
- Documentation: See [libSQL extensions guide](https://github.com/tursodatabase/libsql/blob/main/libsql-sqlite3/doc/libsql_extensions.md#random-rowid)
33+
2934
- **SQLite Extension Loading Support (`enable_extensions/2`, `load_ext/3`)**
3035
- Load SQLite extensions dynamically from shared library files
3136
- **Security-first design**: Extension loading disabled by default, must be explicitly enabled
@@ -91,6 +96,41 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
9196
- Updated documentation in README.md with examples for all encryption scenarios
9297
- See [Turso Encryption Documentation](https://docs.turso.tech/cloud/encryption) for key generation and requirements
9398

99+
### Clarifications
100+
101+
- **ALTER TABLE ALTER COLUMN Support (Already Implemented)**
102+
- **Fully supported** since v0.6.0 - libSQL's ALTER COLUMN extension for modifying column attributes
103+
- **Capabilities**: Modify type affinity, NOT NULL, CHECK, DEFAULT, and REFERENCES constraints
104+
- **Usage**: Use `:modify` in migrations as with other Ecto adapters
105+
- **Example**:
106+
```elixir
107+
alter table(:users) do
108+
modify :age, :string, default: "0" # Change type and default
109+
modify :email, :string, null: false # Add NOT NULL constraint
110+
end
111+
```
112+
- **Important**: Changes only apply to new/updated rows; existing data is not revalidated
113+
- **Implementation**: `lib/ecto/adapters/libsql/connection.ex:213-219` handles `:modify` changes
114+
- SQL output: `ALTER TABLE users ALTER COLUMN age TO age TEXT DEFAULT '0'`
115+
- This is a **libSQL extension** beyond standard SQLite (SQLite does not support ALTER COLUMN)
116+
117+
### Investigated but Not Supported
118+
119+
- **Hooks Investigation**: Researched implementation of SQLite hooks (update hooks and authorizer hooks) for CDC and row-level security
120+
- **Update Hooks (CDC)**: Cannot be implemented due to Rustler threading limitations
121+
- SQLite's update hook runs on managed BEAM threads
122+
- Rustler's `OwnedEnv::send_and_clear()` can ONLY be called from unmanaged threads
123+
- Would cause panic: "send_and_clear: current thread is managed"
124+
- **Authorizer Hooks (RLS)**: Cannot be implemented due to synchronous callback requirements
125+
- Requires immediate synchronous response (Allow/Deny/Ignore)
126+
- No safe way to block waiting for Elixir response from scheduler thread
127+
- Would risk deadlocks with scheduler thread blocking
128+
- **Result**: Both `add_update_hook/2`, `remove_update_hook/1`, and `add_authorizer/2` return `{:error, :unsupported}`
129+
- **Alternatives provided**: Comprehensive documentation of alternative approaches:
130+
- For CDC: Application-level events, database triggers, polling, Phoenix.Tracker
131+
- For RLS: Application-level auth, database views, query rewriting, connection-level privileges
132+
- See Rustler issue: https://github.com/rusterlium/rustler/issues/293
133+
94134
## [0.8.1] - 2025-12-18
95135
96136
### Fixed

‎lib/ecto/adapters/libsql/connection.ex‎

Lines changed: 37 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -184,10 +184,10 @@ defmodule Ecto.Adapters.LibSql.Connection do
184184
column_definitions =
185185
Enum.map_join(columns, ", ", &column_definition(&1, composite_pk))
186186

187-
table_options = table_options(table, columns)
187+
{table_constraints, table_suffix} = table_options(table, columns)
188188

189189
[
190-
"CREATE TABLE#{if_not_exists} #{table_name} (#{column_definitions}#{table_options})"
190+
"CREATE TABLE#{if_not_exists} #{table_name} (#{column_definitions}#{table_constraints})#{table_suffix}"
191191
]
192192
end
193193

@@ -384,23 +384,51 @@ defmodule Ecto.Adapters.LibSql.Connection do
384384
defp column_default({:fragment, expr}), do: " DEFAULT #{expr}"
385385

386386
defp table_options(table, columns) do
387+
# Validate mutually exclusive options (per libSQL specification)
388+
if table.options && Keyword.get(table.options, :random_rowid, false) do
389+
# RANDOM ROWID is mutually exclusive with WITHOUT ROWID
390+
if Keyword.get(table.options, :without_rowid, false) do
391+
raise ArgumentError,
392+
"RANDOM ROWID and WITHOUT ROWID are mutually exclusive options (per libSQL specification)"
393+
end
394+
395+
# RANDOM ROWID is mutually exclusive with AUTOINCREMENT on any column
396+
autoincrement_column =
397+
Enum.find(columns, fn {:add, _name, _type, opts} ->
398+
Keyword.get(opts, :autoincrement, false)
399+
end)
400+
401+
if autoincrement_column do
402+
{:add, col_name, _type, _opts} = autoincrement_column
403+
404+
raise ArgumentError,
405+
"RANDOM ROWID and AUTOINCREMENT (on column #{inspect(col_name)}) are mutually exclusive options (per libSQL specification)"
406+
end
407+
end
408+
387409
pk =
388410
Enum.filter(columns, fn {:add, _name, _type, opts} ->
389411
Keyword.get(opts, :primary_key, false)
390412
end)
391413

392-
cond do
393-
length(pk) > 1 ->
414+
# Composite primary key constraint (goes inside CREATE TABLE parentheses)
415+
table_constraints =
416+
if length(pk) > 1 do
394417
pk_names = Enum.map_join(pk, ", ", fn {:add, name, _type, _opts} -> quote_name(name) end)
395418
", PRIMARY KEY (#{pk_names})"
396-
397-
table.options ->
398-
# Handle custom table options
419+
else
399420
""
421+
end
400422

401-
true ->
423+
# Table suffix options (go after closing parenthesis)
424+
table_suffix =
425+
if table.options && Keyword.get(table.options, :random_rowid, false) do
426+
" RANDOM ROWID"
427+
else
402428
""
403-
end
429+
end
430+
431+
{table_constraints, table_suffix}
404432
end
405433

406434
## Query Helpers

0 commit comments

Comments
 (0)