npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

gatsby-source-sqlite

v1.0.0

Published

Source plugin for pulling data into Gatsby from SQLite database.

Downloads

321

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.

Currently only one-to-one and one-to-many relationship are supported. If you have a use case for many-to-many relationship, raise an issue, and I'll look into it.

// 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:

| Field | Required? | Description | | ----------------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | statement | Required | the SQL query statement to be executed. Stored procedures are supported, e.g. 'CALL myProcedureThatReturnsResult(1, 1)' | | idFieldName | Required | column that is unique for each record. This column must be returned by the statement. | | name | Required | name for the query. Will impact the value for the graphql type | | parentName | Optional | name for the parent entity. In a one-to-many relationship, this field should be specified on the child entity (entity with many records). | | foreignKey | Optional | foreign key to join the parent entity. | | cardinality | Optional | the relationship between the parent and this entity. Possible values: "OneToMany", "OneToOne". Default to "OneToMany". (Note: many-to-many relationship is currently not supported.) | | remoteImageFieldNames | Optional | columns that contain image url which you want to download and utilize Gatsby image processing capability. |