🚀 Feature Request: Allow turning off foreign key check while migrating database.

[aperture147]

Some table altering tasks in SQLite cannot be achieved without replacing it with a new table (by dropping - recreating the table) (like adding foreign keys, changing primary keys, updating column type). Dropping a table which has column referenced by other tables with ON DELETE CASCADE will delete all records of the child tables.

Example:
Assume that we have Table A and B. Table B has column a_id referencing (ON DELETE CASCADE) table A primary key id. For some reason I have to modify Table A by dropping and recreating table A (with original value), ALL records from table B are deleted by cascading.
I'm trying to find a workaround for this situation as mentioned in this sqlx issue and some suggestion from Matt in Discord, but none of them work:

1. Try bracketing the migration with PRAGMA foreign_keys = OFF; and PRAGMA foreign_keys = ON;:

PRAGMA foreign_keys = OFF;
create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;
PRAGMA foreign_keys = ON;

2. Try bracketing the migration with PRAGMA defer_foreign_keys = OFF; and PRAGMA defer_foreign_keys = ON;:

PRAGMA defer_foreign_keys = ON;
create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;
PRAGMA defer_foreign_keys = OFF;

3. Try committing the current transaction and create a new one later:

COMMIT TRANSACTION;
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;

COMMIT TRANSACTION;
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;

The workaround 1 and 2 completed but all records in table B is still deleted. The workaround 3 threw and error like this:

✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions.

  D1 runs your SQL in a transaction for you.
  Please export an SQL file from your SQLite database and try again.

Currently I have to copy data from table B to a temporally table then reinsert it to table B later.

create table b_temp;
insert into table b_temp select from b ...;

create table a_temp ...;
insert into table a_temp select from a ...;
drop table a;
alter table a_temp rename to a;

insert Into table b select from b_temp ...;
drop table b_temp;

This is a huge problem when I could have table B1, B2, B3 and more, all of them reference to table A, each table contains a few hundred thousands of records and more important, table B1, B2, B3 could be referenced from other tables too. This is really inefficient since it would definitely make a hit to billing metrics and might impact the traffic and make a migration takes longer to finish (which might affect the traffic to my D1).

Will there be a feature address this situation or is there any existing solution?

[aperture147]

Hi, is there anyone reading this issue?

[geelen]

Hi @aperture147, my apologies. I'm looking into this exact issue as part of https://github.com/cloudflare/workers-sdk/issues/5683. Will make a record to update this issue once I have a resolution.

[aperture147]

Hi @aperture147, my apologies. I'm looking into this exact issue as part of #5683. Will make a record to update this issue once I have a resolution.

Thanks. It would be nice to have disable foreign key check option on remote migration. For now I still have to drop and recreate every single tables and recreate it again to add foreign keys.

[Gerbuuun]

Just want to add to this that this is indeed very annoying. Any changes to a table that requires recreating it will cascade to other referenced tables. I cannot make the required changes to my database atm.. (at least not trivially)

[colecrouter]

I kept running into various errors trying something similar (foreign key constraint failed, LLVM syntax errors). I noticed @aperture147's second example is backwards.

PRAGMA defer_foreign_keys = ON;

CREATE TABLE ...

PRAGMA defer_foreign_keys = OFF;

Doing it exactly like this worked for me. Hope this helps.

Not sure exactly where my issue was. Possible causes:

• Not capitalizing ON/OFF
• Not turning off after
• Not putting it at the very start/end of file
• Missing semicolon

Docs are a bit messy as far as this goes; one page uses true/false, another uses on/off. Neither use semicolons.

[Gerbuuun]

Does not work for me. Rows are still deleted or set to null.

edit: this is my migration file btw

PRAGMA defer_foreign_keys = ON;
--> statement-breakpoint

DROP INDEX `user_email_idx`;
--> statement-breakpoint
DROP INDEX `user_public_idx`;
--> statement-breakpoint
CREATE TABLE `new_user` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`public_id` text NOT NULL,
	`first_name` text NOT NULL,
	`last_name` text NOT NULL,
	`email` text NOT NULL,
	`verified` integer NOT NULL,
	`created_at` text NOT NULL,
	`updated_at` text NOT NULL,
	`deleted_at` text
);
--> statement-breakpoint
INSERT INTO `new_user` (`id`, `public_id`, `first_name`, `last_name`, `email`, `verified`, `created_at`, `updated_at`, `deleted_at`)
SELECT `id`, `public_id`, `first_name`, `last_name`, `email`, `verified`, `created_at`, `updated_at`, `deleted_at`
FROM `user`;
--> statement-breakpoint
DROP TABLE `user`;
--> statement-breakpoint
ALTER TABLE `new_user` RENAME TO `user`;
--> statement-breakpoint
UPDATE sqlite_sequence SET seq = (SELECT MAX(ID) FROM `user`) WHERE name = 'user';
--> statement-breakpoint
CREATE UNIQUE INDEX `user_public_idx` ON `user` (`public_id`);
--> statement-breakpoint
CREATE UNIQUE INDEX `user_email_idx` ON `user` (`email`);
--> statement-breakpoint

PRAGMA defer_foreign_keys = OFF;
--> statement-breakpoint

edit 2:
first renaming the old table and then creating the new table (which is highly discouraged by the sqlite docs) also does not work

[aperture147]

I kept running into various errors trying something similar (foreign key constraint failed, LLVM syntax errors). I noticed @aperture147's second example is backwards.

PRAGMA defer_foreign_keys = ON;

CREATE TABLE ...

PRAGMA defer_foreign_keys = OFF;

Doing it exactly like this worked for me. Hope this helps.

Not sure exactly where my issue was. Possible causes:

• Not capitalizing ON/OFF
• Not turning off after
• Not putting it at the very start/end of file
• Missing semicolon

Docs are a bit messy as far as this goes; one page uses true/false, another uses on/off. Neither use semicolons.

Hi, sorry for the wrong example. I've corrected the original example. Actually I've tried defer_foreign_keys = ON then defer_foreign_keys = OFF first but it does not work, so I later tried OFF then ON (although it does not make any sense) and it expectedly does not work either. That's why the example has the defer_foreign_key backward.

It seems like defer_foreign_keys only works for inserting new records, I've tried defer_foreign_keys for inserting new records and it works. But it does not work on deleting old records or dropping the table as shown.

[hrueger]

To be honest, I'd probably rather mark that as a bug - I almost lost data today due to a migration silently setting those values to NULL 😬
If you need an easily runnable reproduction, checkout https://github.com/hrueger/prisma-24540 (not prisma specific). If there's anything I can test or help, just let me know 👍

[mhuretski]

Hi, are there any updates on this?

As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

[aperture147]

Hi, are there any updates on this?

As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

+1. Is there any update on this?

[IDovgalyuk]

Hi, are there any updates on this?

As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

A workaround that works for me:

PRAGMA defer_foreign_keys = ON;
--> statement-breakpoint

CREATE TABLE `new_user` (
	`id` integer PRIMARY KEY NOT NULL,
	`first_name` text,
	`last_name` text NOT NULL
);
--> statement-breakpoint

INSERT INTO `new_user` (`id`, `first_name`, `last_name`)
SELECT `id`, `first_name`, `last_name`
FROM `user`;
--> statement-breakpoint

-- Making ids of related tables invalid, so they are not effected by ON DELETE CASCADE / SET NULL
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

DROP TABLE `user`;
--> statement-breakpoint

ALTER TABLE `new_user` RENAME TO `user`;
--> statement-breakpoint

-- And making ids correct again
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

PRAGMA defer_foreign_keys = OFF;
--> statement-breakpoint

[lykhonis]

Hi, are there any updates on this?
As far as I understand current workaround is to copy all related tables affected by ON DELETE CASCADE, drop original tables and then rename new ones back to original names?

A workaround that works for me:

PRAGMA defer_foreign_keys = ON;
--> statement-breakpoint

CREATE TABLE `new_user` (
	`id` integer PRIMARY KEY NOT NULL,
	`first_name` text,
	`last_name` text NOT NULL
);
--> statement-breakpoint

INSERT INTO `new_user` (`id`, `first_name`, `last_name`)
SELECT `id`, `first_name`, `last_name`
FROM `user`;
--> statement-breakpoint

-- Making ids of related tables invalid, so they are not effected by ON DELETE CASCADE / SET NULL
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

DROP TABLE `user`;
--> statement-breakpoint

ALTER TABLE `new_user` RENAME TO `user`;
--> statement-breakpoint

-- And making ids correct again
UPDATE `order` SET `user_id` = -`user_id`
--> statement-breakpoint

PRAGMA defer_foreign_keys = OFF;
--> statement-breakpoint

Unfortunately this still nukes any other data that is bound by on delete cascade. Be very careful with this. What it seems to me currently there is only one way to migrate tables/columns by exporting all data, droping all tables, recreating, and inserting data back in.

[IDovgalyuk]

Unfortunately this still nukes any other data that is bound by on delete cascade. Be very careful with this. What it seems to me currently there is only one way to migrate tables/columns by exporting all data, droping all tables, recreating, and inserting data back in.

Found even better approach.
Please consider this sample where we need recreate target table in transaction.

-- ARRANGE
CREATE TABLE `target` (
  `id` integer PRIMARY KEY NOT NULL,
  `data` text NOT NULL
);
INSERT INTO `target` (`id`, `data`)
VALUES (1, 'target_val_1'), (2, 'target_val_2');

CREATE TABLE `on_delete_restrict` (
  `id` integer PRIMARY KEY NOT NULL,
  `target_id` integer NULL,
  `data` text NOT NULL,
  FOREIGN KEY (`target_id`) REFERENCES `target` (`id`) ON DELETE RESTRICT
);
INSERT INTO `on_delete_restrict` (`id`, `target_id`, `data`)
VALUES (1, 1, 'on_delete_restrict_val_1'), (2, 2, 'on_delete_restrict_val_2');

CREATE TABLE `on_delete_cascade` (
  `id` integer PRIMARY KEY NOT NULL,
  `target_id` integer NULL,
  `data` text NOT NULL,
  FOREIGN KEY (`target_id`) REFERENCES `target` (`id`) ON DELETE CASCADE
);
INSERT INTO `on_delete_cascade` (`id`, `target_id`, `data`)
VALUES (1, 1, 'on_delete_cascade_val_1'), (2, 2, 'on_delete_cascade_val_2');

CREATE TABLE `on_delete_set_null` (
  `id` integer PRIMARY KEY NOT NULL,
  `target_id` integer NULL,
  `data` text NOT NULL,
  FOREIGN KEY (`target_id`) REFERENCES `target` (`id`) ON DELETE SET NULL
);

INSERT INTO `on_delete_set_null` (`id`, `target_id`, `data`)
VALUES (1, 1, 'on_delete_set_null_val_1'), (2, 2, 'on_delete_set_null_val_2');
  
-- ACT
BEGIN;

PRAGMA defer_foreign_keys = ON;

-- IMPORTANT to make PK invalid before dropping table
UPDATE `target` SET `id` = - `id`;

CREATE TABLE `__new_target` (
  `id` integer PRIMARY KEY NOT NULL,
  `data` text NOT NULL
);

INSERT INTO `__new_target` (`id`, `data`)
SELECT `id`, `data` || '_updated'
FROM `target`;

DROP TABLE `target`;

ALTER TABLE `__new_target` RENAME TO `target`;

-- IMPORTANT to make PK valid again
UPDATE `target` SET `id` = - `id`;
  
PRAGMA defer_foreign_keys = OFF;

COMMIT;

-- ASSERT
/*
	id	data
	1	target_val_1_updated
	2	target_val_2_updated
*/
SELECT * FROM target;

/*
	id	target_id	data
	1	1		on_delete_restrict_val_1
	2	2		on_delete_restrict_val_2
*/
SELECT * FROM `on_delete_restrict`;

/*
	id	target_id	data
	1	1		on_delete_cascade_val_1
	2	2		on_delete_cascade_val_2
*/
SELECT * FROM `on_delete_cascade`;

/*
	id	target_id	data
	1	1		on_delete_set_null_val_1
	2	2		on_delete_set_null_val_2
*/
SELECT * FROM `on_delete_set_null`;

/* empty table */
SELECT * FROM `pragma_foreign_key_check`;

This works on local database.

[takakikasuga]

• 🚀 Feature Request: Allow turning off foreign key check while migrating database. #5438 (comment)

I found this problem too!!

but this problam is written in document!!

https://developers.cloudflare.com/d1/sql-api/sql-statements/#pragma-defer_foreign_keys--onoff

Note that setting PRAGMA defer_foreign_keys = ON does not prevent ON DELETE CASCADE actions from being executed. While foreign key constraint checks are deferred until the end of a transaction, ON DELETE CASCADE operations will remain active, consistent with SQLite's behavior.

[flashblaze]

Would be really helpful if there is any update on this. I'm using drizzle with this and after adding a column to my table which was being referenced by another table, the other table's data was completely nuked

[aperture147]

Is there any plan to address this problem? Currently there's no way to delete a table for recreation.

This problem comes from SQLite itself, not from D1. It's mentioned in document:

Calling PRAGMA defer_foreign_keys = off does not disable foreign key enforcement outside of the current transaction. If you have not resolved outstanding foreign key violations at the end of your transaction, it will fail with a FOREIGN KEY constraint failed error.

It seems like you've forgot to set the ON DELETE clause while creating the foreign key. If you don't set that, it will be default to ON DELETE RESTRICT, which does not allow you to delete any row in the parent table. Since the DROP TABLE command will delete all rows before actually deleting the table, it will restrict you from doing it.

By the way the problem I'm raising here might not related to the problem you are having, and what you want to do not be what you need. Even if the PRAGMA foreign_keys = OFF does bypass the foreign key checking, it will create a lot of orphaned rows which reference to nowhere, and setting ON DELETE CASCADE or ON DELETE SET NULL will nuke all the children table by deleting all the rows or set the column that reference the parent table to NULL.

[Joelsz]

I am using wrangler and was initially running them in seperate commands. Trying now in a single command it seems to have worked fine.

PRAGMA defer_foreign_keys = on;DROP TABLE table1;ALTER TABLE table1_new RENAME TO table1;PRAGMA defer_foreign_keys = off;

Thanks @aperture147 for pointing me in that direction.

Edit: It worked but deleted the reference value due to the ON DELETE SET NULL. I guess I will update all tables to ON DELETE NO ACTION

[zjx20]

I just experienced a data loss issue as well. After some investigation, I found the reproduction steps in SQLite:

1. Run a brand new SQLite database, for example, sqlite3 /tmp/test.db

2. Paste the following statements into the SQLite interactive console to create the parent and child tables and insert one record into each.

   -- Create the parent table
   CREATE TABLE parent (
       id INTEGER PRIMARY KEY
   );
   
   -- Create the child table with the ON DELETE CASCADE constraint
   CREATE TABLE child (
       id INTEGER PRIMARY KEY,
       parent_id INTEGER,
       CONSTRAINT "test1" FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE
   );
   
   -- Insert some data
   INSERT INTO parent (id) VALUES (1);
   INSERT INTO child (id, parent_id) VALUES (1, 1);
   
   -- Check the data
   SELECT * FROM parent;
   SELECT * FROM child;

3. Immediately after, run the following statements to simulate a migration of the parent table.

   -- Enforce foreign key constraint
   PRAGMA foreign_keys=ON;
   
   -- Simulate a migration for the parent table
   BEGIN;
   PRAGMA defer_foreign_keys=ON;
   PRAGMA foreign_keys=OFF;
   
   CREATE TABLE new_parent (
       id INTEGER PRIMARY KEY
   );
   INSERT INTO "new_parent" ("id") SELECT "id" FROM "parent";
   DROP TABLE parent;
   ALTER TABLE "new_parent" RENAME TO "parent";
   PRAGMA foreign_keys=ON;
   PRAGMA defer_foreign_keys=OFF;
   COMMIT;
   
   -- Check the data, the child table becomes empty!
   SELECT * FROM parent;
   SELECT * FROM child;

4. After execution, it was found that the data in the child table was deleted synchronously.

I feel that one of the problems is that the PRAGMA foreign_keys=OFF; statement within the transaction does not work. If we remove the PRAGMA foreign_keys=ON; statement before the transaction (which defaults to OFF), and re-execute the SQL, the data in the child table is preserved.

However, this is unfortunately not a solution, because when performing a migration, the entire SQL file is wrapped in a transaction, which means that PRAGMA foreign_keys=OFF; within the migration script is always ineffective.

As a workaround, I eventually changed all ON DELETE CASCADE to ON DELETE RESTRICT. While this makes the business logic a bit more troublesome, at least I won't lose data anymore.

[IDovgalyuk]

You can't change foreign_keys during the transaction
https://sqlite.org/foreignkeys.html

It is not possible to enable or disable foreign key constraints in the middle of a [multi-statement transaction](https://sqlite.org/lang_transaction.html) (when SQLite is not in [autocommit mode](https://sqlite.org/c3ref/get_autocommit.html)). Attempting to do so does not return an error; it simply has no effect.

Try this workaround, it's works for me with ON DELETE CASCADE:

-- Enforce foreign key constraint
PRAGMA foreign_keys=ON;

-- Simulate a migration for the parent table
BEGIN;
PRAGMA defer_foreign_keys=ON;

CREATE TABLE new_parent (
    id INTEGER PRIMARY KEY
);
INSERT INTO "new_parent" ("id") SELECT "id" FROM "parent";

UPDATE parent set id = -id
DROP TABLE parent;

ALTER TABLE "new_parent" RENAME TO "parent";
PRAGMA defer_foreign_keys = OFF;
COMMIT;

-- Check the data, the child table becomes empty!
SELECT * FROM parent;
SELECT * FROM child;

[alsuren]

I just experienced a data loss issue as well. After some investigation, I found the reproduction steps in SQLite:

@zjx20 Thanks for the sqlite3 reproduction. This really helped with my investigation (running things like sqlite3 :memory: -cmd '.read fixtures/on-delete-cascade-1-schema.sql' -cmd '.read fixtures/on-delete-cascade-3-no-transaction-migration.sql' let me iterate quickly and keep track of what I had done).

I feel that one of the problems is that the PRAGMA foreign_keys=OFF; statement within the transaction does not work. If we remove the PRAGMA foreign_keys=ON; statement before the transaction (which defaults to OFF), and re-execute the SQL, the data in the child table is preserved.

This is correct. As @IDovgalyuk points out from the sqlite docs, PRAGMA foreign_keys=OFF does nothing if you're already inside a transaction.

However, this is unfortunately not a solution, because when performing a migration, the entire SQL file is wrapped in a transaction, which means that PRAGMA foreign_keys=OFF; within the migration script is always ineffective.

As a workaround, I eventually changed all ON DELETE CASCADE to ON DELETE RESTRICT. While this makes the business logic a bit more troublesome, at least I won't lose data anymore.

There is another potential workaround that I can think of. It requires explaining a bunch of D1 internals though.

Wrangler currently executes D1 migrations using the /query HTTP API endpoint, which automatically wraps everything in a transaction. This is the same as how wrangler d1 execute --remote DB --command 'select 1'.

There is another way though: D1 also has a /import endpoint. Rather than wrapping your query in a transaction, this achieves atomicity by:

• saving a bookmark before it starts
• rejecting all queries while it is running (rather than just leaving them in a queue until it finishes)
• if any query fails, restores to the bookmark and resets the underlying durable object state.

It was built this way so that long running data import jobs can take longer than the timeout on a single call to the /query endpoint.

You can use the /import method by running wrangler d1 execute --remote DB --file 'your-file.sql' and it will execute statements without wrapping them a transaction (so your PRAGMA foreign_keys=OFF should work).

There isn't currently a way to use the /import endpoint for running migrations that are tracked by D1's migration system, so you would need to keep track of it yourself for now.

We have a ticket internally (CFSQL-1230) to switch migrations to use /import instead of /query. We would either make it opt-in (either globally in the wrangler config file or using a PRAGMA at the top of each migration file) or just switch all migrations over if it is safe to do so. I think all of the work for that ticket would happen in the wrangler codebase in this repo.

[zjx20]

This is great news! I actually use Prisma ORM to manage my table structure, and the migration SQL it generates includes PRAGMA foreign_keys=OFF. So, if wrangler could execute migration scripts outside of a transaction, the problem would no longer exist.

[franciscohermida]

I tried running "wrangler d1 execute DB --file 'your-file.sql'" locally and still got similar issues as OP described (in my case some columns being set to null as configured in prisma schema onDelete: SetNull)

is this supposed to only work with --remote or does it not solve all migration issues when using an ORM?

[alsuren]

is this supposed to only work with --remote or does it not solve all migration issues when using an ORM?

For now, yes. We don't implement the same time-travel based pseudo-transactions for wrangler d1 execute --file locally that we do remotely. If this is blocking local dev then you could potentially run your migrations with the sqlite3 cli directly.

(I realise that this is workarounds on top of workarounds. Sorry. I have added a note to our internal ticket that we should implement locking and rollbacks locally in a way that mirrors what we do remotely)

[franciscohermida]

is this supposed to only work with --remote or does it not solve all migration issues when using an ORM?

For now, yes. We don't implement the same time-travel based pseudo-transactions for wrangler d1 execute --file locally that we do remotely. If this is blocking local dev then you could potentially run your migrations with the sqlite3 cli directly.

(I realise that this is workarounds on top of workarounds. Sorry. I have added a note to our internal ticket that we should implement locking and rollbacks locally in a way that mirrors what we do remotely)

It doesn't work even when executing sql directly on the remote d1 instance, repro here in case it helps:
https://github.com/franciscohermida/cf-remote-d1-workaround

Not sure if I'm missing something or just doing it wrong.

[alsuren]

@franciscohermida I have made some tweaks to your example over at https://github.com/franciscohermida/cf-remote-d1-workaround/pull/2/files . With those tweaks (and also the wrangler.jsonc pointing at a db that I own), I was able to make wrangler d1 export give me before/after outputs that don't show evidence of a cascading delete remotely.

I've not attempted to fix the local version. It should be possible to migrate .wrangler/state/v3/d1/miniflare-D1DatabaseObject/*.sqlite without using wrangler, as long as you're careful about taking backups and rolling back if anything goes wrong.

[franciscohermida]

Thanks to @alsuren's help the workaround demo is now working in this repo https://github.com/franciscohermida/cf-remote-d1-workaround

I recommend anyone dealing with these cascading issues to have a look at it

[half2me]

hey @franciscohermida thanks for that repo. I changed my migration to use wrangler d1 execute instead of migrate and now works perfectly.

[xdivby0]

I am in the same boat and currently locked everything down with on delete restrict. Which is - as @zjx20 said - quite troublesome for overall code quality when a simple crud delete involves several steps of deleting rows from related tables.

If the import thing is working for most people, that means we can expect the migrate command that handles migrations for us to use that import endpoint at some point? Is this something you guys at cloudflare are working on or is it on the back burner? I'm asking in the interest of transparency so that we as customers of D1 can make informed decisions wether to implement a workaround with self-managed migrations or to wait.

[alsuren]

Is this something you guys at cloudflare are working on or is it on the back burner?

@xdivby0 It's not been scheduled. Doing it "properly" involves a bunch of edge cases around local vs remote development, and e.g. doing the right thing when a local migration fails halfway through. If you need this, please use the workaround for now (and also leave a reaction on this issue so that the workers-sdk team badger us about it more).

[swalker326]

Also ran into this issue. D1 time travel saved the day but now what? We can create a work around for the generated migration but any future migrations that are generated, folks will just have to know of this oddity. I'm not really sure what the next steps are.

[aperture147]

Yeah the time travel feature saved me countless of time over the years, but the problem is here still... may we have a tech advance like postgres-lite that combine the best of both postgres and sqlite, but for now we still need to deal with this sqlite limitation.