Gatsby: [feat]: add gatsby-source-sql

[DSchau]

Summary

It'd be awesome to be able to (at build time!) inject content from a SQL database, just like we do for other source types, e.g. filesystem, Contentful, etc.

Motivation

• People may have useful data stored away in SQL that could be exposed to enable helpful things, e.g. documentation, useful data, etc.
• The plugin will need to connect to a SQL database, and then pre-load queries (e.g. pass the SQL queries directly to the plugin?)

Further Detail

The general idea is that the source plugin will need to be able to connect to a SQL database (MySQL, MSSQL, ... ?; happy to start small and grow it in the future!) and query that data to inject into the GraphQL content layer for further processing via Gatsby.

Consider mysql, mssql for various means to inject data

See other source plugins for more info on how to build this out, e.g. gatsby-source-contentful, gatsby-source-wordpress, etc.

[daubaris]

me me me! Could you also direct on where to start? :)

[DSchau]

@daubaris 👋 Absolutely! So I'd recommend checking out one of the source plugins I linked, and kinda re-tracing from there!

So high level, this is what this will look like:

• Create/copy a directory in packages/gatsby-source-sql
• Implement the appropriate Node APIs (probably sourceNodes)
• Open up a PR (early/WIP is fine, just mark it as such!) and we'll be happy to give some feedback

If you're interested, we'd love to help out with a pairing session, as well. Sign up for those here

And let me know if I can help with this further :)

[garetmckinley]

If I were to suggest sqlite support as well, would that be lumped into this plugin or it's own thing?

[DSchau]

@garetmckinley good question! I'm not sure how to best resolve that complexity to be honest with you.

_Ideally_ there'd be one shared sql plugin, but the more I think about it, I'm not sure how tenable that is because the _engine_ will have to change between MySQL, mssql, SQLLite, etc.

So we may be able to introduce some shared helpers, but I think this plugin may take additional plugins, and those plugins should implement some API perhaps?

[DSchau]

(very similar to gatsby-transformer-remark if you're familiar!)

[garetmckinley]

Yeah, I think that makes the most sense! I'll keep an eye on the progress of this issue, because I have a use-case for gatsby with sqlite. I'd be more than happy to work on a sqlite plugin for this source when the time comes.

[DSchau]

@garetmckinley that would be so great! Stay tuned 👀

[oorestisime]

Once this progresses i d like to look for some postgres integration as well.

[koreyboone]

Is this claimed?

[edgarrmondragon]

Is this open? If so I would like to work on it if someone's not doing it already.

[jonniebigodes]

@mrfunnyshoes do it if not even for the learning experience.

[DSchau]

@mrfunnyshoes yep! Feel free to tackle this, we'd love to see this!

[edgarrmondragon]

Thanks! Do you guys think using knex be overkill for this task?

[DSchau]

@mrfunnyshoes. Whoa, I’ve never heard of that! That seems like a nice, flexible option!

All for it!

[malcolm-kee]

I just implemented something similar, but for mysql only. It is just a thin wrapper around the library mysql. See https://github.com/malcolm-kee/gatsby-source-mysql

[DSchau]

@malcolm-kee great! I think there's still some value in what @mrfunnyshoes proposed, so perhaps we can work on integrating gatsby-source-sql into _this_ repo whereas yours can live under your own name!

Looks great, though - happy to see more plugins relating to this! Nice work!

[malcolm-kee]

@dschau agree! That is much useful than only support mysql. Just share as another reference 😁

[edgarrmondragon]

Hey guys! I've got a working version here. Let me know what you think. I'm actually new to contributing to a FOSS project.

PS: I think It would be nice to nest the resulting data inside the fieldName for the query, much in the way gatsby-source-graphql does.

[malcolm-kee]

@mrfunnyshoes my suggestions:

1. Place the environment variables instructions into its own section as it's not part of the plugin.
2. Link to specific section of the knex docs instead of the docs landing page, to be more friendly to developer new to knex.
3. I think your TODO would include publish to npm registry, but I not sure what's the process for plugin within gatsby repo. Perhaps @DSchau could advise?

Other than that, great works!

[edgarrmondragon]

Thanks @malcolm-kee, will look into your suggestions!

[calcsam]

@mrfunnyshoes this is incredible.

Would you consider it production ready? If so, could you publish this to NPM? This will submit it to the plugin library as well!

cc @DSchau

[LekoArts]

https://github.com/gatsbyjs/gatsby/pull/8875

There’s is already this PR

[DSchau]

@malcolm-kee we'd love to have this plugin in the official Gatsby repository. I haven't taken a super close look at the code, but the functionality you're able to build in (in a database agnostic way) looks _perfect_.

Perhaps we schedule some time next week, and I can walk you through how we'd get this PR'd up to the official Gatsby monorepo? Feel free to use my Calendly and we can get this up and running.

Again--thank you! This looks great!

[malcolm-kee]

@DSchau are you intends to message @mrfunnyshoes for gatsby-source-sql?

[edgarrmondragon]

Hey guys @DSchau, @malcolm-kee! Yes, I think we could start working on the PR. Should I publish this to npm already, or will that be part of adding it to the monorepo?

[LekoArts]

@mrfunnyshoes Hi! So is your plugin ready to go for a PR against Gatsby's repo? If so we should close this PR https://github.com/gatsbyjs/gatsby/pull/8875 and you can open up yours. Please let us know if you need help with the PR.

I don't think you need to publish to npm as the monorepo will handle this once your PR is merged 👍

[edgarrmondragon]

Thanks @LekoArts! I _think_ the plugin is ready for PR, I'm not completely sure how to proceed with it though. I'll start by following these instructions and come here with any doubts.

[LekoArts]

If you have problems you can also join our Discord (https://discord.gg/jUFVxtB) and ask there 👍 (Or open the PR and we'll talk in the PR)

[edgarrmondragon]

Thanks @LekoArts, I submitted a PR

[gatsbot[bot]]

Hiya!

This issue has gone quiet. Spooky quiet. 👻

We get a lot of issues, so we currently close issues after 30 days of inactivity. It’s been at least 20 days since the last update here.

If we missed this issue or if you want to keep it open, please reply here. You can also add the label "not stale" to keep this issue open!

Thanks for being a part of the Gatsby community! 💪💜

[gatsbot[bot]]

Hey again!

It’s been 30 days since anything happened on this issue, so our friendly neighborhood robot (that’s me!) is going to close it.

Please keep in mind that I’m only a robot, so if I’ve closed this issue in error, I’m HUMAN_EMOTION_SORRY. Please feel free to reopen this issue or create a new one if you need anything else.

Thanks again for being part of the Gatsby community!

[m-allanson]

Reopening as there is a related PR open for this.

[sidharthachatterjee]

Closing this since https://github.com/gatsbyjs/gatsby/pull/11100#issuecomment-486755549

Keep an eye out for @mrfunnyshoes's fantastic plugin that will enable this!

[muescha]

FYI:
if someone search for this - the plugin is here available:

https://github.com/edgarrmondragon/gatsby-source-sql