The 2026 Drupal Business Survey is open. Your response will help shape the future of the Drupal business ecosystem.
By Scaythe on
I have a MySQL database used/modified by multiple applications.
I don't want to just convert it into a drupal database because I'd rather keep it simple and independent.
What I need is to be able to have entities present in that external database kept synchronized with drupal as nodes.
So basically :
* import the entities in the external database as nodes, either periodically or have the other applications warn drupal to do an import when they modify data.
* when an imported node is edited, update the external database with the new values (and maybe also when new nodes are created or deleted, update the external database accordingly).
Is there a module that already does that ?
If not, would a module that does that be of interest to anyone ?
Comments
same use case here
Hi,
I've exactly the same use case. Currently I import an external database content in Drupal's database using the (great) Migrate module and Node references for references between tables. It works fine, that way I can reuse most of the Drupal's functionality on my data, but I'd prefer to work on the original database to keep it simple and independent (to make it clear, the database should be accessible by other clients). I'm quite a brand newbie in Drupal and just learning to write my own modules. I understand it should be possible with the Entity model and the Schema API. What I'm looking for is to implement the CRUD operations directly on the tables of my external database. Unfortunately Drupal's documentation is quite overwhelming for a beginner and I've no idea where to start from. Before (re)inventing a (squared) wheel, I'm also looking if a module for that already exists, or at least an example of code working on a toy database that would be easy to adapt.
Thanks to all Drupal's contributors for this great software.
Hi,As I found no module that
Hi,
As I found no module that did what I needed when I originally posted, I began developing one myself.
I ended up with a working proof of concept module, that did not implement everything that was needed, but had a pretty good base.
As it is, it can :
* Associate a drupal node type to a table in an external database with a form or "manually" with the CCK, and if the table has foreign keys, follow them and associate more fields.
* Import the items from the external tables as drupal nodes, either from the drupal interface or triggered by an xml-rpc request.
* Export back to the external database changes made in drupal nodes.
It's been in that state for a few months now, since I had more pressing matters, but i will go back to it in the near future.
In the meantime, if you are interested, I created a sandbox project here : http://drupal.org/sandbox/Scaythe/1430052
Many thanks
Hello Scaythe
many thanks for your experimental module, I have downloaded it and I will dive into it as soon as possible. I will let you know if it suits my needs, as well as any issue that I could meet while giving it a try. I'm also ready to participate to its development when I'll feel more at ease with Drupal. I'm convinced that if it is well done, it could become a quite popular code base or module.
Kind regards,
Corentor
sync with external database
Do you think this module could be configured to work with an external database running under .Net on IIS? My use case is, in general, similar to yours. I would like the world-facing Drupal site to have access to look up information from an in-house database and display it. It is possible I may want to import the data as nodes and have a live update between Drupal and the other database. But I don't know if that is asking too much. (Or asking for trouble.)
I _may_ end up with a budget to develop this feature and I will certainly circle back to this thread if that becomes the case.
A list of some of the Drupal sites I have designed and/or developed can be viewed at motioncity.com
I used the Drupal Database
I used the Drupal Database API as much as possible, so for the most part it should be possible to use any database driver and be ok.
Still, there may be a few places where I used something specific to MySQL (or just not supported by every database system Drupal can connect to), but it shouldn't be too much of a problem to change them to work with a few more database systems.
some questions about extdb
Hello again.
I've made a try with Scaythe's module and I think it does have a lot of potential. I couldn't make it work though, and I must do something wrong or not in the right order. Maybe someone can help (most likely Scaythe himself, or one of the early users of his module) ?
Here is what I do :
1. I enable the module "External DB Sync". It gives me two new content types, "Database link column-field" and "Database link table-type".
2. I understand that I have to create a custom content type, that will be used to map the database table I want to import. I do it.
3. I create a content of the type "Database link table-type". I give it a title, the key of the database I want to access to (I understand that it is the key from the variable $databases in the settings.php file), the name of the database table I want to import and the name of the content type I want to use for the importation (the content type I have just created). I guess one must give the machine name and not the display name for the content type ?
4. Then I'm not sure what I'm supposed to do. I see two options : add a content of type "Database link column-field" (4a), or "Import from external database" (4b) from the content manager. At first glance, I understand that the importation (4b) can be done only when the mapping between the database table and the content type (4a) has ben defined.
4a. When I try to create a node of type "Database link column-field" from the content manager, I get a form to fill, with a title, a column name (from a database table, I guess) and a field name (from a Drupal content type, once again I suppose in its machine and not its display form). I see also a checkbox without label (from the source of extdb.install, I understand that it's to specify if the column is a primary key, but weirdly the label doesn't show up for me).
At last, one can see a select box with the label "Owning table-type link". I was expecting to be able to choose the table-type node created in step 3 but I must have missed something since it doesn't offer me this possibility. The select box remains empty and offers me only to "choose a value", but gives me no value to choose. As a consequence I can't submit the form (since the table-type selection is mandatory).
4b. When I try to "Import from external database", I can select the database link table-type I have created in step 3 with a checkbox (I guess one can choose several ones at the same time), and then click on an "Import" button. The importation takes a while, then I get an error message.
Notice : Undefined index: node dans _extdb_import_table() (ligne 62 dans /.../drupal_root/sites/all/modules/custom/external_database_sync/extdb.import.inc).
Warning : array_keys() [function.array-keys]: The first argument should be an array dans _extdb_import_table() (ligne 62 dans /.../drupal_root/sites/all/modules/custom/external_database_sync/extdb.import.inc).
I suspect that it is because I have never specified how my table fields were to be mapped with content types fields, the step 4a not working for me.
Thanks for any help !
3. and 4a. Yes you have to
3. and 4a. Yes you have to give the machine name.
4a. About the checkbox without a label, I remember having some trouble getting the caption to display, and having to do some weird stuff to get it working. Maybe you have a newer version of Drupal where the normal way would work and not my workaround.
4a. About the empty select box, you did everything right, you just have to create the table-type link and it should appear in that box. I don't know exactly why that doesn't work. That field is of type node_reference from the references module I set as a dependency, but it looks like that type is actually defined by its submodule Node Reference, so if you haven't enabled it try that. Otherwise I'm not sure, maybe an update of references or drupal itself changed something i was relying on.
About the error you get, it comes from the fact that your table-type link doesn't own at least one column field link marked as primary key.
One thing you can try is to use the form I made to do most of the work for you, maybe it can bypass the empty select box problem. You can access it in the manage fields section of the type you want to link and add fields to, with the "Add fields from external table" link at the top.
Hello Scaythe. Thank you very
Hello Scaythe. Thank you very much for your answer. I couldn't find the time yet to come back to this issue and to try your solution (I'm on several other projects at the same time). I hope to come back to this in the current of march and will keep you informed. All the best. Corentor.