Importing and using external DB tables

How do I...
#1

How do we best deal with pre-existing (or 3rd party) DB data in a UF project?

e.g. We want to use geo data (countries, locations) taken from an external repository.

The simple way is to just import those in the DB, and jump straight to the Model/Controller code but… how do we handle the 3rd party data in terms of best practices, and migration classes + seeding?

Thanks

#2

If you have a large amount of data in an existing database, that you want to convert into seed data, it might be easiest to simply use raw insert statements in your migration’s seed method:

Capsule::connection()->insert("
    INSERT INTO `courses` (`id`, `institution_id`, `course_num`, `title`, `subject`, `flag_visible`, `created_at`, `updated_at`, `deleted_at`) VALUES
    (1, 3, 'MATH003', 'Developmental Mathematics', 'Algebra', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL),
    (2, 3, 'MATH010', 'Algebra for MATH110', 'Algebra', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL),
    (3, 3, 'MATH011', 'Algebra for MATH111', 'Algebra', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL),
    (4, 3, 'MATH013', 'Algebra for MATH113', 'Algebra', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL),
    (5, 3, 'MATH015', 'Algebra for MATH115', 'Algebra', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL),
    (6, 3, 'MATH110', 'Elementary Mathematical Models', 'Algebra', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL),
    (7, 3, 'MATH111', 'Introduction to Probability', 'Probability/Statistics', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL),
    (8, 3, 'MATH112', 'College Algebra with Applications and Trigonometry', 'Trigonometry', 1, '2016-01-01 05:00:00', '2016-01-01 05:00:00', NULL)
");

The tricky part is if you have foreign keys in this data - you will need to make sure that they match up correctly with any data you have in other migrations.

1 Like