gss
gatsby-source-sqlite
npm i gatsby-source-sqlite
gss

gatsby-source-sqlite

Source plugin for pulling data into Gatsby from a SQLite database file

by Philipp Hartenfeller

1.0.0 (see all)License:MITTypeScript:Not FoundCategories:Gatsby SQLite
npm i gatsby-source-sqlite
Readme

gatsby-source-sqlite

This is just forked from malcolm-kee's awesome gatsby-source-mysql plugin. I just changed query logic and replaced "mysql" with "sqlite" everywhere. So all credits to him!!!

version license

Source plugin for pulling data into Gatsby from a SQLite database file.

How to use

// In your gatsby-config.js
module.exports = {
  plugins: [
    {
      resolve: `gatsby-source-sqlite`,
      options: {
        fileName: './data/mydb.sqlite',
        queries: [
          {
            statement: 'SELECT * FROM country',
            idFieldName: 'Code',
            name: 'country'
          }
        ]
      }
    }
    // ... other plugins
  ]
};

And then you can query via GraphQL with the type allSqlite<Name> where <Name> is the name for your query.

Below is a sample query, however, it is probably different from yours as it would dependent on your configuration and your SQL query results.

Use GraphiQL to explore the available fields.

query {
  allSqliteCountry {
    edges {
      node {
        Code
        Name
        Population
      }
    }
  }
}

Multiple Queries

When you have multiple queries, add another item in the queries option with different name.

// In your gatsby-config.js
module.exports = {
  plugins: [
    {
      resolve: `gatsby-source-sqlite`,
      options: {
        fileName: './data/mydb.sqlite',
        queries: [
          {
            statement: 'SELECT * FROM country',
            idFieldName: 'Code',
            name: 'country'
          },
          {
            statement: 'SELECT * FROM city',
            idFieldName: 'ID',
            name: 'city'
          }
        ]
      }
    }
    // ... other plugins
  ]
};

Joining Queries

It's possible to join the results of the queries by providing parentName, foreignKey, and cardinality to the query object.

// In your gatsby-config.js
module.exports = {
  plugins: [
    {
      resolve: `gatsby-source-sqlite`,
      options: {
        fileName: './data/mydb.sqlite',
        queries: [
          {
            statement: 'SELECT * FROM country',
            idFieldName: 'Code',
            name: 'country'
          },
          {
            statement: 'SELECT * FROM city',
            idFieldName: 'ID',
            name: 'city',
            parentName: 'country',
            foreignKey: 'CountryCode',
            cardinality: 'OneToMany'
          }
        ]
      }
    }
    // ... other plugins
  ]
};

In the example above, country and city is one-to-many relationship (one country to multiple cities), and they are joined with country.Code = city.CountryCode.

With the configuration above, you can query a country joined with all the related cities with

query {
  allSqliteCountry {
    edges {
      node {
        Code
        Name
        Population
        cities {
          Name
        }
      }
    }
  }
}

It also works the other way, i.e. you can query the country when getting the city

query {
  allSqliteCity {
    edges {
      node {
        Name
        country {
          Name
        }
      }
    }
  }
}

Download Image for Image Processing

If your queries stores the remote url for image and you would like to utilize image processing of Gatsby, provide remoteImageFieldNames to the query object.

Make sure you've installed both gatsby-plugin-sharp and gatsby-transform-sharp packages and add them to your gatsby-config.js.

For example, assuming you have a actor table where the profile_url column stores the remote image url, e.g. 'https://cdn.pixabay.com/photo/2014/07/10/11/15/balloons-388973_1280.jpg'.

// In your gatsby-config.js
module.exports = {
  plugins: [
    `gatsby-plugin-sharp`,
    `gatsby-transformer-sharp`,
    {
      resolve: `gatsby-source-sqlite`,
      options: {
        fileName: './data/mydb.sqlite',
        queries: [
          {
            statement: 'SELECT * FROM actor',
            idFieldName: 'actor_id',
            name: 'actor',
            remoteImageFieldNames: ['profile_url']
          }
        ]
      }
    }
    // ... other plugins
  ]
};

Then you can query all the images like below. (Note that you have to filter null value for the records whose profile_url is empty).

import React from 'react';
import { useStaticQuery, graphql } from 'gatsby';
import Img from 'gatsby-image';

export const SqlImage = () => {
  const data = useStaticQuery(graphql`
    {
      allSqliteActor {
        edges {
          node {
            SqliteImage {
              childImageSharp {
                fluid(maxWidth: 300) {
                  ...GatsbyImageSharpFluid
                }
              }
            }
          }
        }
      }
    }
  `);

  const images = data.allSqliteActor.edges
    .filter(edge => !!edge.node.SqliteImage)
    .map(edge => edge.node.SqliteImage.childImageSharp.fluid);

  return (
    <div>
      {images.map((img, index) => (
        <Img fluid={img} key={index} />
      ))}
    </div>
  );
};

If you have multiple columns with image url, pass down multiple values to remoteImageFieldNames and use SqliteImages in your graphql query, which will be an array of images.

Plugin options

  • connectionDetails (required): options when establishing the connection. Refer to Sqlite connection options
  • queries (required): an array of object for your query. Each object could have the following fields:
FieldRequired?Description
statementRequiredthe SQL query statement to be executed.
idFieldNameRequiredcolumn that is unique for each record. This column must be returned by the statement. The name id is already reserverd and not allowed here!!!
nameRequiredname for the query. Will impact the value for the graphql type
parentNameOptionalname for the parent entity. In a one-to-many relationship, this field should be specified on the child entity (entity with many records).
foreignKeyOptionalforeign key to join the parent entity.
cardinalityOptionalthe relationship between the parent and this entity. Possible values: "OneToMany", "OneToOne". Default to "OneToMany". (Note: many-to-many relationship is currently not supported.)
remoteImageFieldNamesOptionalcolumns that contain image url which you want to download and utilize Gatsby image processing capability.

Downloads/wk

36

GitHub Stars

0

LAST COMMIT

1yr ago

MAINTAINERS

1

CONTRIBUTORS

4

OPEN ISSUES

0

OPEN PRs

0
VersionTagPublished
1.0.0
latest
1yr ago
No tutorials found
Add a tutorial