Add /export and /import endpoints

[rijkvanzanten]

It would be extremely cool if Directus had a native way of exporting and importing (existing) data and whole Directus setups. Moving from a locally installed Directus to hosted should/could be as easy as hitting export, logging in to hosted, and hitting import.

Different "types" of import/export?:

• Schema only
  Just import/export your table setup & corresponding records in directus_* tables
• Data only
  Just import/export the data
• Everything
  A full database dump. Useful for backups & migrating between servers / hosted-local

Thoughts @wellingguzman @benhaynes ?

[benhaynes]

Absolutely. Being a new feature, let's make sure all the Core/existing features are built first – but then I think this would be an amazing addition to Directus. I've spoken with many clients that cite importing as a big need... and exporting really goes a long way towards our view of data portability.

[rijkvanzanten]

Lets also include support for exporting / importing to JSON. That makes it also pretty easy to maintain the default schema for installation. I was thinking about the following structure:

{
  "<table>": [
    { "<field>": "<value>" },
    { "<field>": "<value>" }
  ]
}

For example, the Directus installation schema / start-data could look like:

{
  "directus_fields": [
    {
      "id": 1,
      "collection": "directus_users",
      "field": "avatar",
      "type": "FILE",
      "interface": "single-file",
      "options": null,
      "locked": 1,
      "etc": "..."
    }
  ]
}

NOTE: This is only for values, not the schema itself

[benhaynes]

Interesting idea. SQL should come first since it's "native" to our storage... but JSON is a nice to have format too! And easier to read and create manually.

[rijkvanzanten]

Preferably it supports SQL (dump), JSON, and CSV formats.

Editing large datasets in Excel / Numbers and then importing them into Directus would be extremely powerful.

[WoLfulus]

I'd love something like this

[sedatkaplan]

importing and exporting database schema will make Directus expansion fast. think of a database schema for accounting, erp or maybe school management etc. and also this will enhance your marketplace.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[benhaynes]

To achieve better clarity/visibility, we are now tracking feature requests within the Feature Request project board.

This issue being closed does not mean it's not being considered.

[philleepflorence]

I have existing custom CSV import and export endpoints, as most of our clients using Directus have spreadsheets of hundreds of rows of data and also want to export data as CSV to give to accountants and managers and such.
Could be on the pipeline soon if I can merge the custom with the core.

https://cdn.philleepflorence.com/uploads/00000000137.jpg is what it looks like in v6.x

[benhaynes]

If we can define the MVP features/options I can draft a design for this. Then we can look into mapping what you come up with into Core (or as an included extension). Thanks @philleepflorence!

[philleepflorence]

Okay, will do!

[yagobski]

Any progress on the view side of this feature. We are a lot of users of Directus waiting for an Export to CSV solution

[rijkvanzanten]

No new updates yet. I know there's a lot of people who want this, but there's also a lot of people who want bugs fixed, or want other features, or want me to get them coffee want better integrations etc etc. We're doing what we can with the resources we have, but I have to prioritize bugs, general stability, and adding tests before anything else.

@yagobski if you wanna help out, it would be great if you could write out a spec (rfc) for how this would ideally work for your use case 😊 (as detailed as possible).

[philleepflorence]

In the meantime until added to the core there are extensions for CSV import and export here:

https://github.com/philleepflorence/directus-8-extensions

[yagobski]

Thanks, @philleepflorence I tried your extensions but don't seem to work with directus-8.
the best case for me is just a button Export to CSV near the table view that will export the data of columns that I see on the table. It means if apply filtering or ordering that will be the exact data what i get on CSV.

[benhaynes]

Another reason this feature isn't getting AS much attention as it should, is that you can technically work around it in the database directly (since Directus is pure SQL). Other features/bugs are Directus specific and only work if we fix/add them... but this can be done with a database administration tool (less convenient, I know). Just trying to help add some context here.

[darioseidl]

Is it okay, if I ask here? I'm setting up Directus for one of our projects and want to share the schema with other devs.

Schema only
Just import/export your table setup & corresponding records in directus_* tables

It's fine for me to do that with mysqldump, but I'm not quite sure which directus_ tables would be considered part of the schema, and which are "data"? Has anyone worked that out yet?

So far, I would dump all directus tables with data, except for directus_activity, directus_files, directus_revisions, directus_user_sessions which I would dump with mysqldump --no-data:

# make a dump of directus tables
mysqldump db directus_collection_presets directus_collections directus_fields directus_folders directus_migrations directus_permissions directus_relations directus_roles directus_settings directus_users directus_webhooks > schema/db-schema-dump.sql

# make a dump of directus tables, without data
mysqldump --no-data db directus_activity directus_files directus_revisions directus_user_sessions >> schema/db-schema-dump.sql

# make a dump of all non-directus tables, without data
mysqldump --no-data db $(mysql --batch --silent --execute "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME NOT LIKE 'directus\_%';") >> schema/db-schema-dump.sql

[rijkvanzanten]

@darioseidl While the exact split between "data" and "schema" is a little vague ,this is a rough overview (all collections prefixed by directus_:

Collection	Description
activity	Activity log; who did what when
collection_presets	Last state of layouts and saved bookmarks
collections	What collections exist / are managed
fields	Fields and their configuration
folders	Folders to group files *
migrations	Keeps track of what migrations have run
permissions	What the user roles are allowed to access
relations	How certain fields relate to eachother
revisions	Stores item deltas on crud operations
roles	What user roles exist in the platform
settings	key/value of general settings
user_sessions	What users are current logged in
users	Users and their login info
webhooks	Webhooks and when they fire

* Not yet in use

As for which ones to migrate, I'd generally recommend all of them in order to prevent mismatches in foreign keys between the tables. That being said, collection fields, and relations are definitely required for "schema" stuff (without those, you'd lose info like what interface is used, order on the form etc).

users / roles / permission could be truncated, but you'd have no way to login, so make sure you have at least 1 active user and the admin/public roles present in roles

The others can technically be empty and still have directus work with the schema in place.

[darioseidl]

Thanks @rijkvanzanten ! That helps me a lot to understand the tables.

I was worried about mismatched foreign keys, but so far the above script (skipping the data in directus_activity, directus_files, directus_revisions, directus_user_sessions) has worked well for me.

[ThaDaVos]

As the core will be moved to Laravel, maybe this can help: https://docs.laravel-excel.com/3.1/getting-started/

I was also hoping something a like would be implemented already - currenlty using Directus for translations only and using the API endpoints to extract and then transform them to a i18n compatible layout - next step, do it in reverse 😂

[sweetyams]

+1 for this feature - would be extremely helpful!

[Russ93]

Has there been any work done on this? Would be very helpful to export the data on the screen to a tsv or a csv.

[benhaynes]

This has been added in v9, but will not be back-ported to v8 (unless done so by the community).

[tchapi]

Hi @benhaynes
Do you have any roadmap for the v9, where that import/export feature is included ?

Thanks a lot !

[benhaynes]

There's no roadmap yet, but we're working on adding one. Export and backup are already in v9, import might come a bit later.

[tchapi]

Thanks for your answer.

When you're talking about v9 BTW, you're talking about the Node/Vue v9 API + APP (directus/next), or the PHP v9 (directus/api-next, which is only the API and not the admin panel if I'm not mistaken ? Not sure that I understand correctly the differences here to be honest, pardon my newbie question)

[rijkvanzanten]

@tchapi this might clear up a couple things: https://github.com/directus/next/discussions/425#discussioncomment-86139 🙂

[tchapi]

Oh, that's great, thanks so much! I hadn't seen that comment previously, sorry. Great work on v9 (node) BTW and I'm eager to test more the future release candidates ;)

[nithiz]

@rijkvanzanten The link to the discussion does not seem to work, i think the whole repo is gone?

[benhaynes]

The repos have been renamed... this is the repo he was linking to, but I don't know the specific Discussion.

https://github.com/directus/directus/discussions