Subscriptions
Create, update, delete, and query the available Subscriptions in Stripe.
Table Specific Information
Select
Server-Side Query Support
The 本製品 uses the Stripe API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the 本製品.
- Id, CustomerId, PlanId, Status, MetadataAggregate, AutomaticTaxEnabled, CollectionMethod, TestClock and AccountId support the following operator: =.
- CreatedAt supports the following operators: =, !=, >, >=, <, <=.
You can select:
A subscription by specifying its Id:
SELECT * FROM Subscriptions WHERE Id = 'mySubscriptionId'
Subscriptions created after a specific date (Created may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range):
SELECT * FROM Subscriptions WHERE CreatedAt > '2016-01-03'
Subscriptions that belong to a customer:
SELECT * FROM Subscriptions WHERE CustomerId = 'cus_12345678'
Subscriptions that belong to a plan:
SELECT * FROM Subscriptions WHERE PlanId = 'myPlanId'
Subscriptions with a specific status:
SELECT * FROM Subscriptions WHERE Status = 'active'
Subscriptions with metadata:
SELECT * from Subscriptions where MetadataAggregate ='{\"fdgm\":\"gdfgkm\"}'
Insert
CustomerId and ItemsAggregate columns are required to create a new subscription:
INSERT INTO Subscriptions (CustomerId, TrialEnd, ItemsAggregate,CancelAtPeriodEnd) VALUES ('cus_PDYvuww0WynoTg', '2023-12-22', '[{\"price\": \"43\",\"quantity\":\"12\"}]', true)
Alternatively, you can provide CustomerId and PlanId columns to create a new subscription:
INSERT INTO Subscriptions (CustomerId, PlanId, TrialEnd,CancelAtPeriodEnd) VALUES ('cus_LMchUD47S8Eumg', 'price_1ONAgvCZ8rn6wdu','2024-12-22', true)
Update
To update a subscription, specify the Id:
UPDATE Subscriptions SET ApplicationFeePercent = 0, MetadataAggregate='[{\"order\":\"14453\"}]' WHERE Id = 'sub_A9WZGVTbvgBJ4t'
Delete
To delete a subscription, specify the Id.
DELETE FROM Subscriptions WHERE Id = 'sub_A9WZGVTbvgBJ4t'
Columns
Name | Type | ReadOnly | References | Description |
Id [KEY] | String | True |
The Id of the subscription. | |
CustomerId | String | False |
Customers.Id |
The Id of the customer who owns the subscription. |
PlanId | String | False |
Plans.Id |
The Id of the plan. |
PlanActive | Boolean | True |
Whether the plan can be used for new purchases. | |
PlanAggregateUsage | String | True |
Specifies a usage aggregation strategy for plans of usage_type=metered. | |
PlanAmount | Integer | True |
The unit amount in cents to be charged, represented as a whole integer if possible. | |
PlanAmountDecimal | String | True |
The unit amount in cents to be charged, represented as a decimal string with at most 12 decimal places. | |
PlanBillingScheme | String | True |
Describes how to compute the price per period. | |
PlanCreated | Datetime | True |
Time at which the object was created. Measured in seconds since the Unix epoch. | |
PlanCurrency | String | True |
Three-letter ISO currency code, in lowercase. Must be a supported currency. | |
PlanInterval | String | True |
The frequency at which a subscription is billed. One of day, week, month or year. | |
PlanIntervalCount | Integer | True |
The number of intervals (specified in the interval attribute) between subscription billings. | |
PlanLivemode | Boolean | True |
Has the value true if the object exists in live mode or the value false if the object exists in test mode. | |
PlanMetadata | String | True |
Set of key-value pairs that you can attach to an object. | |
PlanMeter | String | True |
The meter tracking the usage of a metered price. | |
PlanNickname | String | True |
A brief description of the plan, hidden from customers. | |
PlanObject | String | True |
String representing the object's type. Objects of the same type share the same value. | |
PlanProduct | String | True |
The product whose pricing this plan determines. | |
PlanTiersMode | String | True |
Defines if the tiering price should be graduated or volume based. | |
PlanTransformUsage | String | True |
Apply a transformation to the reported usage or set quantity before computing the amount billed. | |
PlanTrialPeriodDays | Integer | True |
Default number of trial days when subscribing a customer to this plan using trial_from_plan=true. | |
PlanUsageType | String | True |
Configures how the quantity per period should be determined. | |
ApplicationFeePercent | Decimal | False |
A positive decimal that represents the fee percentage of the subscription invoice amount that will be transferred to the application of the Stripe account owner each billing period. | |
CancelAtPeriodEnd | Boolean | False |
If the subscription has been canceled with the at_period_end flag set to true, cancel_at_period_end on the subscription will be true. You can use this attribute to determine whether a subscription that has a status of active is scheduled to be canceled at the end of the current period. | |
CanceledAt | Datetime | True |
If the subscription has been canceled, the date of that cancellation. If the subscription was canceled with cancel_at_period_end, canceled_at will still reflect the date of the initial cancellation request, not the end of the subscription period when the subscription is automatically moved to a canceled state. | |
CreatedAt | Datetime | True |
The creation date. | |
CurrentPeriodEnd | Datetime | True |
End of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created. | |
CurrentPeriodStart | Datetime | True |
Start of the current period that the subscription has been invoiced for. | |
CouponId | String | False |
The id of the discount coupon. | |
EndedAt | Datetime | True |
If the subscription has ended (either because it was canceled or because the customer was switched to a subscription to a new plan), the date the subscription ended. | |
Quantity | Double | True |
The quantity of the plan to which the customer should be subscribed. For example, if your plan is $10/user/month, and your customer has 5 users, you could pass 5 as the quantity to have the customer charged $50 (5 x $10) monthly. | |
StartDate | Datetime | True |
Date the most recent update to this subscription started. | |
Status | String | True |
The status of the subscription 使用できる値は次のとおりです。active, past_due, unpaid, canceled, incomplete, incomplete_expired, trialing, all, ended, paused | |
TaxPercent | Decimal | True |
If provided, each invoice created by this subscription will apply the tax rate, increasing the amount billed to the customer. | |
TrialEnd | Datetime | False |
If the subscription has a trial, the end of that trial. | |
TrialStart | Datetime | True |
If the subscription has a trial, the beginning of that trial. | |
MetadataAggregate | String | False |
The set of key/value pairs that you can attach to a subscription object. | |
BillingCycleAnchor | Datetime | False |
Determines the date of the first full invoice, and, for plans with month or year intervals, the day of the month for subsequent invoices. | |
DefaultPaymentMethod | String | False |
ID of the default payment method for the subscription | |
ItemsAggregate | String | False |
List of subscription items, each with an attached price. | |
LatestInvoice | String | True |
The ID of the most recent invoice this subscription has generated. | |
PendingSetupIntent | String | True |
You can use this SetupIntent to collect user authentication when creating a subscription without immediate payment or updating a subscription's payment method, allowing you to optimize for off-session payments. | |
PendingUpdate | String | True |
If specified, pending updates that will be applied to the subscription once the latest_invoice has been paid. | |
BillingThresholds | String | False |
Define thresholds at which an invoice will be sent, and the subscription advanced to a new billing period | |
AutomaticTaxEnabled | Boolean | False |
Automatic tax settings for this subscription. | |
AutomaticTaxLiabilityAccount | String | False |
The connected account being referenced when type is account. | |
AutomaticTaxLiabilityType | String | False |
Type of the account referenced. | |
CollectionMethod | String | False |
Either charge_automatically, or send_invoice. When charging automatically, Stripe will attempt to pay this subscription at the end of the cycle using the default source attached to the customer. 使用できる値は次のとおりです。charge_automatically, send_invoice | |
DaysUntilDue | Integer | False |
Number of days a customer has to pay invoices generated by this subscription. This value is null for subscriptions where collection_method=charge_automatically | |
DefaultSource | String | False |
ID of the default payment source for the subscription. | |
DefaultTaxRates | String | False |
The tax rates that will apply to any subscription item that does not have tax_rates set. Invoices created will have their default_tax_rates populated from the subscription. | |
LiveMode | Boolean | True |
Has the value true if the object exists in live mode or the value false if the object exists in test mode. | |
NextPendingInvoiceItemInvoice | Datetime | True |
Specifies the approximate timestamp on which any pending invoice items will be billed according to the schedule provided at pending_invoice_item_interval | |
PauseCollectionBehavior | String | False |
If specified, payment collection for this subscription will be paused. The payment collection behavior for this subscription while paused. One of keep_as_draft, mark_uncollectible, or void. | |
PauseCollectionResumesAt | Datetime | False |
If specified, payment collection for this subscription will be paused. The time after which the subscription will resume collecting payments. | |
PaymentSettings | String | False |
Payment settings passed on to invoices created by the subscription. | |
PendingInvoiceItemInterval | String | False |
Specifies an interval for how often to bill for any pending invoice items. It is analogous to calling Create an invoice for the given subscription at the specified interval. Specifies invoicing frequency. Either day, week, month or year. | |
PendingInvoiceItemIntervalCount | Integer | False |
Specifies an interval for how often to bill for any pending invoice items. It is analogous to calling Create an invoice for the given subscription at the specified interval. The number of intervals between invoices. | |
Schedule | String | True |
The schedule attached to the subscription | |
TestClock | String | True |
ID of the test clock this customer belongs to. | |
TransferData | String | False |
The account (if any) the subscription's payments will be attributed to for tax reporting, and where funds from each payment will be transferred to for each of the subscription's invoices. | |
CancelAt | Datetime | False |
A date in the future at which the subscription will automatically get canceled. | |
Description | String | False |
The subscription's description. | |
CancellationDetailsComment | String | False |
Details about why this subscription was cancelled. Additional comments about why the user canceled the subscription, if the subscription was canceled explicitly by the user. | |
CancellationDetailsFeedback | String | False |
Details about why this subscription was cancelled. The customer submitted reason for why they canceled, if the subscription was canceled explicitly by the user. | |
CancellationDetailsReason | String | False |
Details about why this subscription was cancelled. The reason why this subscription was cancelled. | |
Currency | String | False |
Three-letter ISO currency code, in lowercase. Must be a supported currency. | |
TrialSettingsEndBehaviorPaymentMethod | String | False |
Settings related to subscription trials. Indicates how the subscription should change when the trial ends if the user did not provide a payment method. 使用できる値は次のとおりです。cancel, pause, create_invoice | |
OnBehalfOf | String | False |
The account on behalf of which to charge, for each of the subscription?s invoices. | |
DiscountId | String | True |
The Id of the discount. | |
DiscountCheckoutSession | String | True |
The Checkout session that this coupon is applied to, if it is applied to a particular session in payment mode. Will not be present for subscription mode. | |
DiscountCustomer | String | True |
The ID of the customer associated with this discount. | |
DiscountStart | Datetime | True |
If the subscription has a trial, the beginning of that trial. | |
DiscountEnd | Datetime | True |
If the subscription has a trial, the end of that trial. | |
DiscountInvoice | String | True |
The invoice that the discount's coupon was applied to, if it was applied directly to a particular invoice. | |
DiscountInvoiceItem | String | True |
The invoice line item id that the discount's coupon was applied to if it was applied directly to a invoice line item. | |
DiscountPromotionCode | String | True |
The promotion code applied to create this discount.The promotion code applied to create this discount. | |
DiscountSubscription | String | True |
The subscription that this coupon is applied to, if it is applied to a particular subscription. | |
DiscountCouponCreatedAt | Datetime | True |
The creation date. | |
DiscountCouponCurrency | String | True |
If amount_off has been set, the three-letter ISO code for the currency of the amount to take off. | |
DiscountCouponName | String | True |
Name of the coupon displayed to customers on, for instance, invoices or receipts. | |
DiscountCouponDuration | String | True |
One of forever, once, and repeating. Describes how long a customer who applies this coupon will get the discount. | |
DiscountCouponDurationInMonths | Integer | True |
the number of months the coupon applies. | |
DiscountCouponAmountOff | Integer | True |
Amount (in the currency specified) that is taken off the subtotal of any invoices for this customer. | |
DiscountCouponPercentOff | Integer | True |
Percent that will be taken off the subtotal of any invoices for this customer for the duration of the coupon. For example, a coupon with a percent_off of 50 will make a $100 invoice $50 instead. | |
DiscountCouponValid | Boolean | True |
Taking account of the above properties, whether this coupon can still be applied to a customer. | |
DiscountCouponMaxRedemptions | Integer | True |
Maximum number of times this coupon can be redeemed, in total, before it is no longer valid. | |
DiscountCouponRedeemBy | Datetime | True |
Date after which the coupon can no longer be redeemed. | |
DiscountCouponTimesRedeemed | Integer | True |
Number of times this coupon has been applied to a customer. | |
DiscountCouponObject | String | True |
String representing the object's type. Objects of the same type share the same value. | |
DiscountCouponLiveMode | Boolean | True |
Has the value true if the object exists in live mode or the value false if the object exists in test mode. | |
DiscountCouponMetadataAggregate | String | True |
Set of key-value pairs that you can attach to an object. | |
Application | String | True |
ID of the Connect Application that created the subscription. | |
BillingCycleAnchorConfigDayofMonth | Integer | False |
The day of the month of the billing_cycle_anchor. | |
BillingCycleAnchorConfigHour | Integer | False |
The hour of the day of the billing_cycle_anchor. | |
BillingCycleAnchorConfigminute | Integer | False |
The minute of the hour of the billing_cycle_anchor. | |
BillingCycleAnchorConfigmonth | Integer | False |
The month to start full cycle billing periods. | |
BillingCycleAnchorConfigsecond | Integer | False |
The second of the minute of the billing_cycle_anchor. | |
InvoiceSettingsAccountTaxIds | String | False |
Invoice settings account tax ids. | |
InvoiceSettingsIssuerType | String | False |
Invoice settings issuer type. | |
InvoiceSettingsIssuerAccount | String | False |
The connected account being referenced when InvoiceSettingsIssuerType is account. |
Pseudo-Columns
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。
Name | Type | Description |
IsProrate | Boolean |
Flag indicating whether to prorate switching plans during a billing cycle. |
ProrationDate | Date |
If set, the proration will be calculated as though the subscription was updated at the given time. It can also be used to implement custom proration logic, such as prorating by day instead of by second, by providing the time that you wish to use for proration calculations. |
PaymentBehavior | String |
Use allow_incomplete to transition the subscription to status=past_due if a payment is required but cannot be paid. This allows you to manage scenarios where additional user actions are needed to pay a subscription?s invoice. |
ProrationBehavior | String |
Determines how to handle prorations when the billing cycle changes (e.g., when switching plans, resetting billing_cycle_anchor=now, or starting a trial), or if an item?s quantity changes. The default value is create_prorations. |
TrialPeriodDays | Integer |
The number of trial period days before the customer is charged for the first time. If set, trial_period_days overrides the default trial period days of the plan the customer is being subscribed to. |
AccountId | String |
The Id of the connected account to get subscriptions for. |